11 Replies Latest reply: Aug 5, 2013 7:23 AM by odie_63 RSS

    XMLTABLE TAKING TIME.

    943494

      Hi All,

       

      I have a table named car and iam inserting values into it from car_xml table (from column xml_value it is xml type).

      with small xml file it is inserting withing seconds.

      But if i use large xml files around 2.27 MB size then it is taking minimum 2 minutes to process.

      Below is the query iam using with XMLTABLE.

      Is there any  other way to optimize this query or any other technique other than XMLTABLE. Please guide.

      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

       

       

      QUERY :-

      INSERT INTO CAR ( MODEL_NO , COMPANY )

      select XTAB.MODEL_NO,  XTAB.COMPANY

                                 FROM CAR_XML,

             XMLTABLE(XMLNAMESPACES('http://car.viva.com/schema/Create' AS "gs"),

             'for $i in /gs:rowset/gs:row/gs:engine/gs:model/gs:country return $i'

             PASSING XML_VALUE

                      COLUMNS

                      MODEL_NO VARCHAR2(240) PATH 'gs:model_no' ,

                      COMPANY VARCHAR2(240) PATH 'gs:company' )  XTAB

        • 1. Re: XMLTABLE TAKING TIME.
          Jason_(A_Non)

          The slowness is a result of the way the XML is being parsed by by the query.  If you were do to an explain plan on your query, you would see a row for

          COLLECTION ITERATOR PICKLER FETCH

          This is an in-memory operation that Oracle is performing to read through the XML and find what it needs.  The larger the XML, the slower the overall operation takes due to the amount of data that is parsed each time.

           

          That said, you do have some options in 10.2 to avoid this issue.  You pick up far better options starting with 11.1, but I won't include those.  To be lazy, two options are:

          1) Register a schema and insert the XML into the table created as a result of registration and then run your SELECT statement against that table.  I have an rough example at A_Non On XML: Options for Slow Performance Parsing Large XML - Part 2

           

          2) Parse the XML purely in PL/SQL as an XMLType and use FORALL to bulk insert the parsed data into your table.  An example can be seen at A_Non On XML: Options for Slow Performance Parsing Large XML

          • 2. Re: XMLTABLE TAKING TIME.
            odie_63

            and option 3 : XSL transformation + DBMS_XMLSTORE

            • 3. Re: XMLTABLE TAKING TIME.
              943494

              Jason,   Odie Thank You.

               

              Jason if i use 'Parse the XML purely in PL/SQL' then the code may look less user friendly for support purpose. since there are lot of columns.

              i will try the register schema option.

               

              Odie can you please provide a link for  XSL transformation + DBMS_XMLSTORE? Thank You once again.

              • 4. Re: XMLTABLE TAKING TIME.
                odie_63

                Odie can you please provide a link for  XSL transformation + DBMS_XMLSTORE?

                I remember having posted a similar example here but can't find it anymore.

                Here's another :

                 

                DBMS_XMLSTORE can parse XML in canonical format and perform DML on the corresponding table.

                It's relatively efficient because a SAX parser is used in the process.

                 

                The obvious drawback is that the input XML has to be transformed to a canonical format, if not already in that format.

                A canonical XML format is like this :

                <ROOT>

                  <ROW>

                    <COL1>ABC</COL1> 

                    <COL2>123</COL2>

                  </ROW>

                  <ROW>

                    ...

                </ROOT>

                where COL1, COL2 must be the exact (case-sensitive) column names of the target table.

                The ROW tag can be set to something else, and the ROOT tag name is irrelevant.

                 

                Let's look at a live example.

                My input XML will be a 50,000-row Excel spreadsheet saved as SpreadsheetML 2003 format (you can see what it looks like in this article, §2.b : How To : Read and Write Office 2003 Excel XML Files).

                My target table is :

                create table smltable (

                  rec_id      integer

                , description varchar2(80)

                , rec_value   varchar2(30)

                );

                which matches the tabular structure of the spreadsheet.

                 

                This stylesheet will transform the SpreadsheetML format into my expected canonical format :

                 

                <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                  exclude-result-prefixes="ss">
                  <xsl:template match="/">
                    <ROOT>
                      <xsl:apply-templates select="ss:Workbook/ss:Worksheet/ss:Table/ss:Row"/>
                    </ROOT>
                  </xsl:template>
                  <xsl:template match="ss:Row">
                    <R>
                      <REC_ID><xsl:value-of select="ss:Cell[1]/ss:Data"/></REC_ID>
                      <DESCRIPTION><xsl:value-of select="ss:Cell[2]/ss:Data"/></DESCRIPTION>
                      <REC_VALUE><xsl:value-of select="ss:Cell[3]/ss:Data"/></REC_VALUE>
                    </R>
                  </xsl:template> 
                </xsl:stylesheet>
                
                

                 

                Putting it all together :

                SQL> declare

                  2 

                  3    xsl xmltype := xmltype(

                  4  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

                  5    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

                  6    exclude-result-prefixes="ss">

                  7    <xsl:template match="/">

                  8      <ROOT>

                  9        <xsl:apply-templates select="ss:Workbook/ss:Worksheet/ss:Table/ss:Row"/>

                10      </ROOT>

                11    </xsl:template>

                12    <xsl:template match="ss:Row">

                13      <R>

                14        <REC_ID><xsl:value-of select="ss:Cell[1]/ss:Data"/></REC_ID>

                15        <DESCRIPTION><xsl:value-of select="ss:Cell[2]/ss:Data"/></DESCRIPTION>

                16        <REC_VALUE><xsl:value-of select="ss:Cell[3]/ss:Data"/></REC_VALUE>

                17      </R>

                18    </xsl:template>

                19  </xsl:stylesheet>');

                20 

                21    doc xmltype;

                22    ctx dbms_xmlstore.ctxHandle;

                23    res number;

                24 

                25  begin

                26    -- sample input XML file, size ~ 13 MB

                27    doc := xmltype(bfilename('TEST_DIR', 'smldata.xml'), nls_charset_id('AL32UTF8'));

                28 

                29    ctx := dbms_xmlstore.newContext('SMLTABLE');

                30    -- set the row tag to "R" :

                31    dbms_xmlstore.setRowTag(ctx, 'R');

                32 

                33    -- very important - declare the target columns :

                34    for r in ( select column_name

                35               from user_tab_columns

                36               where table_name = 'SMLTABLE' )

                37    loop

                38      dbms_xmlstore.setUpdateColumn(ctx, r.column_name);

                39    end loop;

                40 

                41    -- transform and insert the data

                42    res := dbms_xmlstore.insertXML(ctx, doc.transform(xsl));

                43    dbms_xmlstore.closeContext(ctx);

                44 

                45    dbms_output.put_line(res || ' rows inserted.');

                46 

                47  end;

                48  /

                 

                50000 rows inserted.

                 

                PL/SQL procedure successfully completed

                 

                SQL> select count(*) from smltable;

                 

                  COUNT(*)

                ----------

                     50000

                 

                • 5. Re: XMLTABLE TAKING TIME.
                  943494

                  Jason,

                  To try xml schema registration method, it seems in my database oracle function  dbms_xmlSchema is not available.

                  only dbms_xmlstore, dbms_xmlgen are available.

                   

                  Version :- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi,  PL/SQL Release 10.2.0.3.0 - Production

                   

                  To get dbms_xmlSchema, DBA has to do something? please guide.  Thank You.

                  • 6. Re: XMLTABLE TAKING TIME.
                    odie_63

                    To get dbms_xmlSchema, DBA has to do something?

                    XML DB is probably not installed.

                    Check if user XDB exists, if not, your DBA will have to install the product.

                    • 7. Re: XMLTABLE TAKING TIME.
                      943494

                      Thanks Odie, Thank You very much.

                      DBA is telling he cannot install this feature. he is having some problem i beleive.

                      Jason's method using pl/sql is fast but it required lot of record variable to initialize and code may less freindly for support.

                       

                      now for canonical format using  dbms_xmlstore, below is xml look like, is this a correct structure for dbms_xmlstore?

                      i am just giving sample data, we have a much bigger xml. Thank You.

                       

                      <gs:rowset xmlns:gs="'http://car.viva.com/schema/Create">

                      <gs:row>

                      <gs:car>

                      <gs:address_1>Lemon st.</gs:address_1>

                      <gs:address_2>Glascow.</gs:address_2> 

                      </gs:car>

                      </gs:row>

                      </gs:rowset>

                      • 8. Re: XMLTABLE TAKING TIME.
                        odie_63

                        now for canonical format using  dbms_xmlstore, below is xml look like, is this a correct structure for dbms_xmlstore?

                        Check my explanation again.

                        It depends on the target column names (case-sensitive).

                        Are your target columns named "address_1" and "address_2" ?

                        Furthermore, you have a complexType here, DBMS_XMLSTORE can't handle it.

                         

                        Most likely, you'll need to preprocess the file with XSLT.

                        • 9. Re: XMLTABLE TAKING TIME.
                          943494

                          Jason, Odie,

                           

                          My ignorance, one more question please.

                          In the xml schema registration technique, dbms_xmlSchema.registerSchema should run only once?

                          is it required to register everytime when code starts? Thank You.

                          • 10. Re: XMLTABLE TAKING TIME.
                            odie_63

                            In the xml schema registration technique, dbms_xmlSchema.registerSchema should run only once?

                            Yes, you only have to register it once.

                            Actually, you can consider this step as a DDL operation.

                            • 11. Re: XMLTABLE TAKING TIME.
                              943494

                              Thank You.