8 Replies Latest reply: Sep 24, 2012 12:28 AM by 924065 RSS

    Selecting through XMLSerialize( {xmltype column} )

    924065
      In JDBC, while retrieving the XmlType column, we tried out using two ways
      a) Use XmlSerialize( CONTENT {XmlColumn} ) in the SQL-Query and retrieve it as CLOB in Java. This is faster, but it truncates the un-necessary precision data in numeric values. For example, this returns 0.625 as .625.
      b) Use {XmlColumn} in the SQL-Query and retrieve it as SQLXML in Java. This returns the data, as we have stored it, but it is 2x slower than the above approach on an average.

      Is this the expected behavior (or) something can be changed to provide better performance with approach 2?

      In SQLPlus, when i try to see the autotrace stats, its getting hung while i don't do XmlSerialize.

      Oracle Version : 11.2.0.3

      Thanks,
      Gokul.

      Edited by: KSGokul on Sep 17, 2012 2:23 AM
        • 1. Re: Selecting through XMLSerialize( {xmltype column} )
          Marco Gralike
          You are addressing multiple issues here:

          - If your SQL*Plus session is "hung" then you are probably using the wrong (too old) version.

          To be able to give you a proper answer, we would need database version (exact/all digits) and JDBC version/flavor and the statements would also help ( XmlSerialize( CONTENT {XmlColumn} ) - SQL-Query /SQLXML )
          • 2. Re: Selecting through XMLSerialize( {xmltype column} )
            924065
            Thanks for your reply.

            - If your SQL*Plus session is "hung" then you are probably using the wrong (too old) version.
            You are right here. i have a client which is a older version(11.2.0.1)

            - To be able to give you a proper answer, we would need database version (exact/all digits) and JDBC version/flavor and the statements would also help ( XmlSerialize( CONTENT {XmlColumn} ) - SQL-Query /SQLXML )
            I have already mentioned the DB version(11.2.0.3) in the question, as an update.
            JDBC Driver - oracle-jdbc-11.2.0.3.jar, oracle-xdb-11.2.0.3.jar

            Statements :
            a) select XmlSerialize( CONTENT xml_content ) from table;
            Vs
            b) select xml_content from table;

            where xml_content = xmltype column name
            table = table name

            Please let me know, if you need any more details.

            Thanks,
            Gokul.
            • 3. Re: Selecting through XMLSerialize( {xmltype column} )
              Marco Gralike
              KSGokul wrote:
              Statements :
              a) select XmlSerialize( CONTENT xml_content ) from table;
              Vs
              b) select xml_content from table;

              where xml_content = xmltype column name
              table = table name

              Please let me know, if you need any more details.
              I am in search of the "xml_content" bit, that is the used functions and operators, that is XQuery, XML/SQL part.
              • 4. Re: Selecting through XMLSerialize( {xmltype column} )
                odie_63
                Is your XMLType column schema-based?
                Binary XML or Object-Relational storage?
                • 5. Re: Selecting through XMLSerialize( {xmltype column} )
                  924065
                  I am right now not in a position to disclose the xml_content used. but i can say that its of size 21k and i use Binary XML Secure File as the storage option. I have compression enabled. Do you think you would be able to help out without looking at the actual XML?

                  Or if you try it out in any ~21k XML content, are you not facing the same issue?

                  Thanks,
                  Gokul.
                  • 6. Re: Selecting through XMLSerialize( {xmltype column} )
                    924065
                    Yes XML Type column is schema bases and it is stored as Binary XML.

                    Thanks,
                    Gokul.
                    • 7. Re: Selecting through XMLSerialize( {xmltype column} )
                      odie_63
                      I am right now not in a position to disclose the xml_content used. but i can say that its of size 21k and i use Binary XML Secure File as the storage option. I have compression enabled. Do you think you would be able to help out without looking at the actual XML?

                      Or if you try it out in any ~21k XML content, are you not facing the same issue?
                      At least, give us the code you're using (not pseudocode), or a simplified version of it sufficient to reproduce the issue.

                      Thanks.
                      • 8. Re: Selecting through XMLSerialize( {xmltype column} )
                        924065
                        OK. This is what i was exactly referring to. Doing XmlSerialize reduces the consistent gets.

                        SQL> select XmlSerialize(CONTENT page_content) from TRADE where rownum <= 1;


                        Execution Plan
                        ----------------------------------------------------------
                        Plan hash value: 1828496826

                        ----------------------------------------------------------------------------
                        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                        ----------------------------------------------------------------------------
                        | 0 | SELECT STATEMENT | | 1 | 1014 | 2 (0)| 00:00:01 |
                        |* 1 | COUNT STOPKEY | | | | | |
                        | 2 | TABLE ACCESS FULL| TRADE | 1 | 1014 | 2 (0)| 00:00:01 |
                        ----------------------------------------------------------------------------

                        Predicate Information (identified by operation id):
                        ---------------------------------------------------

                        1 - filter(ROWNUM<=1)


                        Statistics
                        ----------------------------------------------------------
                        0 recursive calls
                        0 db block gets
                        *3  consistent gets*_
                        0 physical reads
                        0 redo size
                        1173 bytes sent via SQL*Net to client
                        944 bytes received via SQL*Net from client
                        5 SQL*Net roundtrips to/from client
                        0 sorts (memory)
                        0 sorts (disk)
                        1 rows processed


                        SQL> select page_content from TRADE where rownum <= 1;


                        Execution Plan
                        ----------------------------------------------------------
                        Plan hash value: 1828496826

                        ----------------------------------------------------------------------------
                        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                        ----------------------------------------------------------------------------
                        | 0 | SELECT STATEMENT | | 1 | 1014 | 2 (0)| 00:00:01 |
                        |* 1 | COUNT STOPKEY | | | | | |
                        | 2 | TABLE ACCESS FULL| TRADE | 1 | 1014 | 2 (0)| 00:00:01 |
                        ----------------------------------------------------------------------------

                        Predicate Information (identified by operation id):
                        ---------------------------------------------------

                        1 - filter(ROWNUM<=1)


                        Statistics
                        ----------------------------------------------------------
                        0 recursive calls
                        0 db block gets
                        *6  consistent gets*_
                        0 physical reads
                        0 redo size
                        3872 bytes sent via SQL*Net to client
                        1836 bytes received via SQL*Net from client
                        3 SQL*Net roundtrips to/from client
                        0 sorts (memory)
                        0 sorts (disk)
                        1 rows processed