This discussion is archived
11 Replies Latest reply: Aug 5, 2013 5:23 AM by odie_63 RSS

XMLTABLE TAKING TIME.

943494 Newbie
Currently Being Moderated

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) Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    and option 3 : XSL transformation + DBMS_XMLSTORE

  • 3. Re: XMLTABLE TAKING TIME.
    943494 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Thank You.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points