Slow performance when parsing xmltype column (oracle 11g)

Question

I have created 2 tables

create table t1 ( issue_date date, xml_col xmltype);

create table t2 ( col1 VARCHAR2(30 CHAR), col2 VARCHAR2(30 CHAR), col3 VARCHAR2(100 CHAR), col4 VARCHAR2(10 CHAR), col5 VARCHAR2(100 CHAR));

Moreover I have a trigger after insert on t1, which calls a procedure in order to parse the xml_col and assign values to t2:

create or replace TRIGGER t1_TRGR
AFTER INSERT ON t1
FOR EACH ROW

DECLARE
  new_REC     t1%ROWTYPE;
BEGIN
  new_REC.issue_date := :NEW.issue_date;
  new_REC.xml_col    := :NEW.xml_col; 

  update_t2(new_REC);

END t1_TRGR;
/



PROCEDURE update_t2(new_rec IN t1%rowtype )
AS
pragma autonomous_transaction;

  CURSOR GET_T2_CUR( p_xml_data  IN XMLTYPE ) IS
   SELECT *
     FROM XMLTABLE('/xml/Page/contents/item'  
              PASSING p_xml_data
              COLUMNS    
                 COL1   VARCHAR2(30  CHAR) PATH 'col1',
                 COL2   VARCHAR2(30  CHAR) PATH 'col2',
                 COL3   VARCHAR2(100 CHAR) PATH 'col3',
                 COL4   VARCHAR2(10  CHAR) PATH 'col4',
                 COL5   VARCHAR2(100 CHAR) PATH 'col5');

  v_xml         XMLTYPE;
  v_counter     INTEGER := 0;
  v_error_num   INTEGER := 0;
  v_limit       INTEGER(6) := 50;
  v_commit      INTEGER(6) := 50;

  e_bulk_errors  exception;
  PRAGMA exception_init(e_bulk_errors, -24381);

  --Types
  type t2_recs is table of T2%rowtype index by pls_integer;
  v_t2_tab t2_recs; 

BEGIN
  v_xml := new_rec.xml_col;

  open GET_T2_CUR(v_xml);
  loop
     begin
        fetch GET_T2_CUR bulk collect into v_t2_tab limit v_limit;

             forall i in v_t2_tab.first .. v_t2_tab.last save exceptions
                    insert into t2 values v_t2_tab(i);
                           v_counter := v_counter + sql%rowcount;
         exit when GET_T2_CUR%notfound;

     exception
        when e_bulk_errors then
             v_counter   := v_counter + sql%rowcount;
             v_error_num := v_error_num + sql%bulk_exceptions.count();

             for j in 1 .. sql%bulk_exceptions.count() loop
               null;
             end loop;
           commit;
     end;

     if mod(v_counter + v_error_num, v_commit) = 0 then
        commit;
     end if;

  end loop;

  close GET_T2_CUR;
  commit;

END update_t2;
/

INSERT INTO t1 VALUES(SYSDATE, 
XMLTYPE('<xml><Page><contents>
      <item> 
        <col1>11111111</col1>
        <col2>345</col2>
        <col3>asdada</col3>
        <col4>gfhgfh</col4>
        <col5>wwrregf3</col5>
      </item>
      <item> 
        <col1>1145476511111</col1>
        <col2>34ewt5</col2>
        <col3>aswerdada</col3>
        <col4>gfhgf345h</col4>
        <col5>wwrsdf45egf3</col5>
      </item></contents></Page>
  <Page><contents><item> 
        <col1>7899</col1>
        <col2>fgd</col2>
        <col3>ukkilik</col3>
        <col4>gfhgfh</col4>
        <col5>fdghhhh</col5>
      </item>
      <item> 
        <col1>045435</col1>
        <col2>34ewt5</col2>
        <col3>rtiu</col3>
        <col4>gfhgf345h</col4>
        <col5>kokyu</col5>
      </item>
    </contents></Page></xml>'));

The performance is slow.

Any ideas please ? for index or something else ..?

--oracle version 11.1

Answer 1

I don't see any reason for such an overkilling procedure. Create a view from the table:

CREATE VIEW t2 as
SELECT *
FROM XMLTABLE('/xml/Page/contents/item'  
          PASSING p_xml_data
          COLUMNS    
             COL1   VARCHAR2(30  CHAR) PATH 'col1',
             COL2   VARCHAR2(30  CHAR) PATH 'col2',
             COL3   VARCHAR2(100 CHAR) PATH 'col3',
             COL4   VARCHAR2(10  CHAR) PATH 'col4',
             COL5   VARCHAR2(100 CHAR) PATH 'col5');

Or make a simple insert:

INSERT INTO t2 (col1, col2, col3, col4, col5)
SELECT *
FROM XMLTABLE('/xml/Page/contents/item'  
          PASSING p_xml_data
          COLUMNS    
             COL1   VARCHAR2(30  CHAR) PATH 'col1',
             COL2   VARCHAR2(30  CHAR) PATH 'col2',
             COL3   VARCHAR2(100 CHAR) PATH 'col3',
             COL4   VARCHAR2(10  CHAR) PATH 'col4',
             COL5   VARCHAR2(100 CHAR) PATH 'col5');