DevOps, CI/CD and Automation

Hi everybody,

I've faced an issue that I hope I can find help with.

There's a task to parse a large (~15 MB) XML text and update a table based on it. So I've created a procedure that takes one XMLType parameter and does the job. Pretty straightforward. However, the thing is that, if I use XMLTable, which is the preferred method, to parse the XML, it runs for hours. Once I replace XMLTable with TABLE(XMLSequence()) method, the procedure completes in under two minutes on the same machine. Looks very strange to me.

Any ideas what could be causing such a poor performance of XMLTable?
Oracle version is 11.2.0.2.0

h1. Table structure

create table Points (
  member_id int not null,
  point_id int,
  country_numeric character(3),
  place national character varying(50),
  name national character varying(255),
  currencies national character varying(255),
  address national character varying(255),
  contact national character varying(40),
  contact_phone national character varying(40),
  details national character varying(255),
  enabled_date date,
  works national character varying(255),
  active character default 1 check (active in (0, 1)) not null,
  unique (member_id, point_id)
);

h1. XMLTable method
runs for many hours if input parameter is ~15 MB long

  create procedure update_Points (
    p_Points in xmltype
  ) as
  begin
    insert into Points (member_id, point_id, country_numeric, place, name, currencies, address, contact, contact_phone, details, enabled_date, works)
    select
      ap.member_id,
      ap.point_id,
      ap.country_numeric,
      ap.place,
      ap.name,
      ap.currencies,
      ap.address,
      ap.contact,
      ap.contact_phone,
      ap.details,
      to_date(ap.enabled_date, 'DD.MM.YYYY'),
      ap.works
    from
      xmltable('for $Point in /document/directory/reply[@type=''Points'']/data/row return element Point { attribute member_id { $Point/column[@col=''1'']/@value }, attribute point_id { $Point/column[@col=''2'']/@value }, attribute country_numeric { $Point/column[@col=''3'']/@value }, attribute place { $Point/column[@col=''4'']/@value }, attribute name { $Point/column[@col=''5'']/@value }, attribute currencies { $Point/column[@col=''6'']/@value }, attribute address { $Point/column[@col=''7'']/@value }, attribute contact { $Point/column[@col=''8'']/@value }, attribute contact_phone { $Point/column[@col=''9'']/@value }, attribute details { $Point/column[@col=''10'']/@value }, attribute enabled_date { $Point/column[@col=''11'']/@value }, attribute works { $Point/column[@col=''12'']/@value } }'
        passing p_Points
        columns
          member_id int path '@member_id',
          point_id int path '@point_id',
          country_numeric character(3) path '@country_numeric',
          place national character varying(50) path '@place',
          name national character varying(255) path '@name',
          currencies national character varying(255) path '@currencies',
          address national character varying(255) path '@address',
          contact national character varying(40) path '@contact',
          contact_phone national character varying(40) path '@contact_phone',
          details national character varying(255) path '@details',
          enabled_date character(10) path '@enabled_date',
          works national character varying(255) path '@works') ap;
  end;

h1. TABLE(XMLSequence()) method
runs for 2 minutes with the same input parameter

  create procedure update_Points (
    p_Points in xmltype
  ) as
  begin
    insert into Points (member_id, point_id, country_numeric, place, name, currencies, address, contact, contact_phone, details, enabled_date, works)
    select
      value(x).extract('row/column[@col=''1'']/@value').getStringVal() member_id,
      value(x).extract('row/column[@col=''2'']/@value').getStringVal() point_id,
      value(x).extract('row/column[@col=''3'']/@value').getStringVal() country_numeric,
      value(x).extract('row/column[@col=''4'']/@value').getStringVal() place,
      extractValue(value(x), '/row/column[@col=''5'']/@value') name,
      value(x).extract('row/column[@col=''6'']/@value').getStringVal() currencies,
      value(x).extract('row/column[@col=''7'']/@value').getStringVal() address,
      value(x).extract('row/column[@col=''8'']/@value').getStringVal() contact,
      value(x).extract('row/column[@col=''9'']/@value').getStringVal() contact_phone,
      value(x).extract('row/column[@col=''10'']/@value').getStringVal() details,
      to_date(value(x).extract('row/column[@col=''11'']/@value').getStringVal(), 'DD.MM.YYYY') enabled_date,
      value(x).extract('row/column[@col=''12'']/@value').getStringVal() works
    from
      table(xmlsequence(extract(p_Points, '/document/directory/reply[@type=''Points'']/data/row'))) x;
  end;

h1. Small XML sample

<?xml version="1.0"?>
<document>
  <directory>
    <reply type="Points">
      <data>
        <row>
          <column col="1" value="0"></column>
          <column col="2" value=""></column>
          <column col="3" value="643"></column>
          <column col="4" value="Something"></column>
          <column col="5" value="&quot;Sample&quot;"></column>
          <column col="6" value=""></column>
          <column col="7" value="Blah"></column>
          <column col="8" value="Bar"></column>
          <column col="9" value="0123456789"></column>
          <column col="10" value=""></column>
          <column col="11" value="01.01.2010"></column>
          <column col="12" value=""></column>
        </row>
      </data>
    </reply>
  </directory>
</document>

Edited by: 999663 on Apr 15, 2013 1:21 PM