2 Replies Latest reply: Dec 13, 2013 7:31 AM by Saxena RSS

    XMLAGG Query Performance degrading Exponentially.

    Saxena

      There is a serious performance issue with my query using XMLAGG

       

       

      CREATE TABLE tmp_test_xml

      (

        acc_ID     NUMBER(12),

        CUS_DTLS  CLOB

      )

       

       

      INSERT INTO tmp_test_xml

      SELECT tab.acc_id   acc_id

                 ,  XMLSERIALIZE(DOCUMENT XMLELEMENT ("AccountHolders"

             ,XMLAGG (XMLELEMENT ("AccountHolder"

                   ,XMLELEMENT ("Gender"                     ,  tab.sex_cde                   )

                   ,XMLELEMENT ("Name"                       ,  tab.name                        )

                   ,XMLFOREST  (tab.drivers_licence        AS "DL"           )

                   ,XMLFOREST  (tab.empr_name            AS "emp_name"        )

                       ,XMLELEMENT ("Address"               ,  tab.addr  )

                     ..

                     ...

                    ...

                ) )) ) AS cus_dtls

             FROM TABLE tab

                   group by tab.acc_id

       

       

      table 'TABLE' has 3 Million records

       

      The performance of the Insert degrades as follows :

       

      INSERT

      10K recs - 1 sec

      30K recs - 45 secs

      50K recs - 3 mins

      100K recs - 16 mins

       

       

      Please let me know if I can improve the performance somehow. I can imagine how i can insert 3 million records in there ..

       

      There is no table space issues. Tried a million recs without XMLAGG- 2 mins.

       

      Is there any other way to aggregate my xml data . Actually I am trying to aggregate data for all customers for one account .

       

      Version info :

      ------------------------------------------------------------------------------------------------------------

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      PL/SQL Release 11.2.0.3.0 - Production

      CORE 11.2.0.3.0 Production

      TNS for Linux: Version 11.2.0.3.0 - Production

      NLSRTL Version 11.2.0.3.0 - Production

       

      Cheers !!

      Saxena

        • 1. Re: XMLAGG Query Performance degrading Exponentially.
          Jason_(A_Non)

          Why do you think the issue is with XMLAgg?

           

          Try with the following table definition instead

          CREATE TABLE tmp_test_xml
          (
            acc_ID     NUMBER(12),
            CUS_DTLS  XMLTYPE -- changed storage. Defaults to SECUREFILE BINARY XML in your version
          )
          

          and also remove the XMLSERIALIZE from your SQL statement as well.

           

          The performance degradation you show reads like a memory leak, so just testing to see if it is in the conversion from an XMLType to a CLOB.  You could also open a SR with Oracle Support on the issue as well as they would have better insight.

          • 2. Re: XMLAGG Query Performance degrading Exponentially.
            Saxena

            BINGO !! Thanks Jason !!!

             

            Yes probably the conversion from an XMLType to a CLOB was the culprit.!!

            XMLAGG was innocent here ...

             

            I removed the XMLSERIALIZE and changed the destination table column type to xmltype.. and it worked like a charm...

             

            1 million records loaded in less than 5 mins !!!

             

            CHeers !!

            Saxena