Skip to Main Content
  • Questions

  • Does Passing a Variable affect performance ?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Raghu.

Asked: September 29, 2015 - 7:29 pm UTC

Last updated: October 01, 2015 - 12:22 am UTC

Version: 11.2.0.3.0

Viewed 1000+ times

You Asked

I am having a script as attached, in which I am trying to process/parse XML in a table(STAGE_TBL) in XMLTYPE column and insert the parsed data into another table(PROCESSED_DATA_TBL). The XML can be huge upto 2MB, which results into about 2000+ rows of parsed data.
The issue I am seeing is when I pass an XML object to a procedure(STAGE_TBL_PROCESS) to parse its taking about 10 seconds per XML, but rather than passing XML if I directly fetch it from table in the procedure(STAGE_TBL_PROCESS) by passing the ID its taking about 0.15 seconds.
As per document all IN params are passed by reference, then why is this performance variation?


Database Details
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

Note:I couldn't perform SQL_TRACE or DBMS_STATS as I don't have access to them.

/*
This one is taking .15 seconds to process an XML with about 2000 rp_sendRow elements
*/
DECLARE
  CURSOR NewStage IS
        SELECT * 
        FROM STAGE_TBL
        WHERE  status = 'N' 
        ORDER BY PUT_TIME ASC;
      SUBTYPE rt_NewStage IS NewStage % rowtype;

  ROW_COUNT           INTEGER := 0;   -- Return value from calling the procedure
  READ_COUNT          INTEGER := 0;   -- Number of rows read from the stage table
  INSERT_COUNT_TOTAL  INTEGER := 0;   -- Number of Inserts Inven records
  ERROR_COUNT         INTEGER := 0;   -- Number of Inven inserts that did inserted more then 1 row in Inven
  PROCESS_STATUS      STATUS.MmsStatus;
  STATUS_DESCRIPTION  STATUS.MmsStatusReason;
  ERRMSG              VARCHAR2(500);
  
PROCEDURE STAGE_TBL_PROCESS (IDDATA IN RAW, PROCESS_STATUS OUT VARCHAR2, STATUS_DESCRIPTION OUT VARCHAR2, ROW_COUNT OUT NUMBER) AS 
/*
  This procedure is to parse the XML from STAGE_TBL and populate the data from XML to PROCESSED_DATA_TBL table
  
  IN PARAMS
  ----------
  IDDATA              -   ID from STAGE_TBL
  xData                 -   XMLType field from XML_DOCUMENT of STAGE_TBL
  
  OUT PARAMS
  -----------
  PROCESS_STATUS        -   The STATUS of parsing and populating PROCESSED_DATA_TBL
  STATUS_DESCRIPTION    -   The description of the STATUS of parsing and populating PROCESSED_DATA_TBL
  ROW_COUNT             -   Number of rows inserted into PROCESSED_DATA_TBL
*/
BEGIN
     INSERT ALL INTO PROCESSED_DATA_TBL  
        (PD_ID,  
        STORE,  
        SALES_NBR,  
        UNIT_COST,  
        ST_FLAG,  
        ST_DATE,  
        ST,  
        START_QTY,  
        START_VALUE,  
        START_ON_ORDER,  
        HAND,  
        ORDERED,  
        COMMITED,  
        SALES,  
        RECEIVE,  
        VALUED,  
        ID_1,  
        ID_2,  
        ID_3,  
        UNIT_PRICE,  
        EFFECTIVE_DATE,  
        STATUS,  
        STATUS_DATE,  
        STATUS_REASON)  
        VALUES (IDDATA   
              ,store  
              ,SalesNo  
              ,UnitCost         
              ,StWac  
              ,StDt           
              ,St          
              ,StartQty           
              ,StartValue         
              ,StartOnOrder       
              ,Hand      
              ,Ordered     
              ,COMMITED      
              ,Sales       
              ,Rec      
              ,Valued    
              ,Id1            
              ,Id2            
              ,Id3            
              ,UnitPrice     
              ,to_Date(EffectiveDate||' '||EffectiveTime, 'YYYY-MM-DD HH24:MI:SS')  
              ,'N'      
              ,SYSDATE  
              ,'XML PROCESS INSERT')      
  WITH T AS 
  ( SELECT STG.XML_DOCUMENT FROM STAGE_TBL STG WHERE STG.ID = IDDATA)   
--      This is to parse and fetch the data from XML              
        SELECT E.* FROM T, XMLTABLE('rp_send/rp_sendRow' PASSING T.XML_DOCUMENT COLUMNS
                               store            VARCHAR(20) PATH 'store'   
                              ,SalesNo          VARCHAR(20) PATH 'sales'  
                              ,UnitCost         NUMBER      PATH 'cost'  
                              ,StWac            VARCHAR(20) PATH 'flag'  
                              ,StDt             DATE        PATH 'st-dt'  
                              ,St               NUMBER      PATH 'st'  
                              ,StartQty         NUMBER      PATH 'qty'  
                              ,StartValue       NUMBER      PATH 'value'  
                              ,StartOnOrder     NUMBER      PATH 'start-on-order'  
                              ,Hand             NUMBER      PATH 'hand'  
                              ,Ordered            NUMBER      PATH 'order'  
                              ,Commited           NUMBER      PATH 'commit'  
                              ,Sales            NUMBER      PATH 'sales'  
                              ,Rec              NUMBER      PATH 'rec'  
                              ,Valued            NUMBER      PATH 'val'  
                              ,Id1              VARCHAR(30) PATH 'id-1'  
                              ,Id2              VARCHAR(30) PATH 'id-2'  
                              ,Id3              VARCHAR(30) PATH 'id-3'  
                              ,UnitPrice        NUMBER      PATH 'unit-pr'  
                              ,EffectiveDate    VARCHAR(30) PATH 'eff-dt'  
                              ,EffectiveTime    VARCHAR(30) PATH 'eff-tm'  
        ) E;     
      ROW_COUNT           := SQL%ROWCOUNT;  -- Not the # of all the rows inserted. 
      PROCESS_STATUS      := STATUS.PROCESSED;
      IF ROW_COUNT < 1 THEN       -- The insert failed Row Count = 0  No exception thrown 
        PROCESS_STATUS      := STATUS.ERROR;
        STATUS_DESCRIPTION  := 'ERROR Did not insert into Pos Inventory. Reason Unknown';
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
        ROW_COUNT           := 0;
        PROCESS_STATUS      := STATUS.ERROR;
        STATUS_DESCRIPTION  := 'SqlCode:' || SQLCODE || ' SqlErrMsg:' || SQLERRM;
END;


BEGIN
  DBMS_OUTPUT.enable(NULL);
  FOR A_NewStage IN NewStage
  LOOP
      READ_COUNT := READ_COUNT + 1;
      STAGE_TBL_PROCESS(A_NewStage.ID, PROCESS_STATUS, STATUS_DESCRIPTION, ROW_COUNT);
      INSERT_COUNT_TOTAL := INSERT_COUNT_TOTAL + ROW_COUNT;
      IF(ROW_COUNT <= 0 OR PROCESS_STATUS = STATUS.ERROR) THEN
          ERROR_COUNT := ERROR_COUNT + 1;
          UPDATE STAGE_TBL 
          SET status              = PROCESS_STATUS,
              status_DATE         = SYSDATE,
              status_DESCRIPTION  = STATUS_DESCRIPTION
          WHERE ID                  = A_NewStage.ID; 
        ELSE 
          UPDATE STAGE_TBL 
          SET status              = PROCESS_STATUS,
              status_DATE         = SYSDATE,
              status_DESCRIPTION  = STATUS_DESCRIPTION,
              SHRED_DT                = SYSDATE
          WHERE ID                  = A_NewStage.ID; 
        END IF; 
        COMMIT;
  END LOOP;
  COMMIT;
  IF ERROR_COUNT > 0 THEN
     ERRMSG := '** ERROR: ' || ERROR_COUNT || ' Stage records did not insert in to the Processed table correctly';
     RAISE_APPLICATION_ERROR(-20001,ErrMsg);  
  END IF;
    EXCEPTION 
      WHEN OTHERS THEN
          RAISE;
END ;

/*
This one is taking 10 seconds to process an XML with about 2000 rp_sendRow elements
*/
DECLARE
  CURSOR NewStage IS
        SELECT * 
        FROM STAGE_TBL
        WHERE  status = 'N' 
        ORDER BY PUT_TIME ASC;
      SUBTYPE rt_NewStage IS NewStage % rowtype;

  ROW_COUNT           INTEGER := 0;   -- Return value from calling the procedure
  READ_COUNT          INTEGER := 0;   -- Number of rows read from the stage table
  INSERT_COUNT_TOTAL  INTEGER := 0;   -- Number of Inserts Inven records
  ERROR_COUNT         INTEGER := 0;   -- Number of Inven inserts that did inserted more then 1 row in Inven
  PROCESS_STATUS      STATUS.MmsStatus;
  STATUS_DESCRIPTION  STATUS.MmsStatusReason;
  ERRMSG              VARCHAR2(500);
  
PROCEDURE STAGE_TBL_PROCESS (IDDATA IN RAW, xData IN STAGE_TBL.XML_DOCUMENT%TYPE, PROCESS_STATUS OUT VARCHAR2, STATUS_DESCRIPTION OUT VARCHAR2, ROW_COUNT OUT NUMBER) AS 
/*
  This procedure is to parse the XML from STAGE_TBL and populate the data from XML to PROCESSED_DATA_TBL table
  
  IN PARAMS
  ----------
  IDDATA              -   ID from STAGE_TBL
  xData                 -   XMLType field from XML_DOCUMENT of STAGE_TBL
  
  OUT PARAMS
  -----------
  PROCESS_STATUS        -   The STATUS of parsing and populating PROCESSED_DATA_TBL
  STATUS_DESCRIPTION    -   The description of the STATUS of parsing and populating PROCESSED_DATA_TBL
  ROW_COUNT             -   Number of rows inserted into PROCESSED_DATA_TBL
*/
BEGIN
     INSERT ALL INTO PROCESSED_DATA_TBL  
        (PD_ID,  
        STORE,  
        SALES_NBR,  
        UNIT_COST,  
        ST_FLAG,  
        ST_DATE,  
        ST,  
        START_QTY,  
        START_VALUE,  
        START_ON_ORDER,  
        HAND,  
        ORDERED,  
        COMMITED,  
        SALES,  
        RECEIVE,  
        VALUED,  
        ID_1,  
        ID_2,  
        ID_3,  
        UNIT_PRICE,  
        EFFECTIVE_DATE,  
        STATUS,  
        STATUS_DATE,  
        STATUS_REASON)  
        VALUES (IDDATA   
              ,store  
              ,SalesNo  
              ,UnitCost         
              ,StWac  
              ,StDt           
              ,St          
              ,StartQty           
              ,StartValue         
              ,StartOnOrder       
              ,Hand      
              ,Ordered     
              ,COMMITED      
              ,Sales       
              ,Rec      
              ,Valued    
              ,Id1            
              ,Id2            
              ,Id3            
              ,UnitPrice     
              ,to_Date(EffectiveDate||' '||EffectiveTime, 'YYYY-MM-DD HH24:MI:SS')  
              ,'N'      
              ,SYSDATE  
              ,'XML PROCESS INSERT')      
--      This is to parse and fetch the data from XML              
        SELECT E.* FROM XMLTABLE('rp_send/rp_sendRow' PASSING xDATA COLUMNS
                               store            VARCHAR(20) PATH 'store'   
                              ,SalesNo          VARCHAR(20) PATH 'sales'  
                              ,UnitCost         NUMBER      PATH 'cost'  
                              ,StWac            VARCHAR(20) PATH 'flag'  
                              ,StDt             DATE        PATH 'st-dt'  
                              ,St               NUMBER      PATH 'st'  
                              ,StartQty         NUMBER      PATH 'qty'  
                              ,StartValue       NUMBER      PATH 'value'  
                              ,StartOnOrder     NUMBER      PATH 'start-on-order'  
                              ,Hand             NUMBER      PATH 'hand'  
                              ,Ordered            NUMBER      PATH 'order'  
                              ,Commited           NUMBER      PATH 'commit'  
                              ,Sales            NUMBER      PATH 'sales'  
                              ,Rec              NUMBER      PATH 'rec'  
                              ,Valued            NUMBER      PATH 'val'  
                              ,Id1              VARCHAR(30) PATH 'id-1'  
                              ,Id2              VARCHAR(30) PATH 'id-2'  
                              ,Id3              VARCHAR(30) PATH 'id-3'  
                              ,UnitPrice        NUMBER      PATH 'unit-pr'  
                              ,EffectiveDate    VARCHAR(30) PATH 'eff-dt'  
                              ,EffectiveTime    VARCHAR(30) PATH 'eff-tm'  
        ) E;     
      ROW_COUNT           := SQL%ROWCOUNT;  -- Not the # of all the rows inserted. 
      PROCESS_STATUS      := STATUS.PROCESSED;
      IF ROW_COUNT < 1 THEN       -- The insert failed Row Count = 0  No exception thrown 
        PROCESS_STATUS      := STATUS.ERROR;
        STATUS_DESCRIPTION  := 'ERROR Did not insert into Pos Inventory. Reason Unknown';
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
        ROW_COUNT           := 0;
        PROCESS_STATUS      := STATUS.ERROR;
        STATUS_DESCRIPTION  := 'SqlCode:' || SQLCODE || ' SqlErrMsg:' || SQLERRM;
END;


BEGIN
  DBMS_OUTPUT.enable(NULL);
  FOR A_NewStage IN NewStage
  LOOP
      READ_COUNT := READ_COUNT + 1;
      STAGE_TBL_PROCESS(A_NewStage.ID, A_NewStage.XML_DOCUMENT, PROCESS_STATUS, STATUS_DESCRIPTION, ROW_COUNT);
      INSERT_COUNT_TOTAL := INSERT_COUNT_TOTAL + ROW_COUNT;
      IF(ROW_COUNT <= 0 OR PROCESS_STATUS = STATUS.ERROR) THEN
          ERROR_COUNT := ERROR_COUNT + 1;
          UPDATE STAGE_TBL 
          SET status              = PROCESS_STATUS,
              status_DATE         = SYSDATE,
              status_DESCRIPTION  = STATUS_DESCRIPTION
          WHERE ID                  = A_NewStage.ID; 
        ELSE 
          UPDATE STAGE_TBL 
          SET status              = PROCESS_STATUS,
              status_DATE         = SYSDATE,
              status_DESCRIPTION  = STATUS_DESCRIPTION,
              SHRED_DT                = SYSDATE
          WHERE ID                  = A_NewStage.ID; 
        END IF; 
        COMMIT;
  END LOOP;
  COMMIT;
  IF ERROR_COUNT > 0 THEN
     ERRMSG := '** ERROR: ' || ERROR_COUNT || ' Stage records did not insert in to the Processed table correctly';
     RAISE_APPLICATION_ERROR(-20001,ErrMsg);  
  END IF;
    EXCEPTION 
      WHEN OTHERS THEN
          RAISE;
END ;

My XML with just one rp_sendRow element, it can go upto 2000 rp_sendRow elements 
<?xml version = \"1.0\" encoding = \"UTF-8\"?>  
<rp_send xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">  
    <rp_sendRow>  
        <store>0123</store>  
        <sales>022399190</sales>  
        <cost>0.01</cost>  
        <flag>true</flag>  
        <st-dt>2013-04-19</st-dt>  
        <st>146.51</st>  
        <qty>13.0</qty>  
        <value>0.0</value>  
        <start-on-order>0.0</start-on-order>  
        <hand>0.0</hand>  
        <order>0.0</order>  
        <commit>0.0</commit>  
        <sales>0.0</sales>  
        <rec>0.0</rec>  
        <val>0.0</val>  
        <id-1/>  
        <id-2/>  
        <id-3/>  
        <unit-pr>13.0</unit-pr>  
        <eff-dt>2015-06-16</eff-dt>  
        <eff-tm>09:12:21</eff-tm>  
    </rp_sendRow>  
</rp_send>

and Connor said...

Sorry, I cant reproduce your results...Here's mine, which is based on yours but turned into a test case that someone could actually run - see how it goes on your system. Its using a 10meg xml with 20,000 'rp_sendrow'

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL>
SQL> set timing off
SQL> drop table STAGE_TBL purge;
drop table STAGE_TBL purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table STAGE_TBL ( id int, XML_DOCUMENT xmltype);

Table created.

SQL> drop table tt purge;
drop table tt purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into STAGE_TBL values (1,xmltype('<?xml version = "1.0" encoding = "UTF-8"?>
  2  <rp_send xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  3      <rp_sendRow>
  4          <store>0123</store>
  5          <salesno>022399190</salesno>
  6          <cost>0.01</cost>
  7          <flag>true</flag>
  8          <st-dt>2013-04-19</st-dt>
  9          <st>146.51</st>
 10          <qty>13.0</qty>
 11          <value>0.0</value>
 12          <start-on-order>0.0</start-on-order>
 13          <hand>0.0</hand>
 14          <order>0.0</order>
 15          <commit>0.0</commit>
 16          <sales>0.0</sales>
 17          <rec>0.0</rec>
 18          <val>0.0</val>
 19          <id-1/>
 20          <id-2/>
 21          <id-3/>
 22          <unit-pr>13.0</unit-pr>
 23          <eff-dt>2015-06-16</eff-dt>
 24          <eff-tm>09:12:21</eff-tm>
 25      </rp_sendRow>
 26  </rp_send>'));

1 row created.

SQL>
SQL> create table tt as
  2  WITH T AS
  3          ( SELECT STG.XML_DOCUMENT FROM STAGE_TBL STG where rownum = 1)
  4          SELECT E.* FROM T,
  5          XMLTABLE(
  6          '/rp_send/rp_sendRow' PASSING T.XML_DOCUMENT
  7          COLUMNS
  8                                 store            VARCHAR(20) PATH 'store'
  9                                ,SalesNo          VARCHAR(20) PATH 'salesno'
 10                                ,UnitCost         NUMBER      PATH 'cost'
 11                                ,StWac            VARCHAR(20) PATH 'flag'
 12                                ,StDt             DATE        PATH 'st-dt'
 13                                ,St               NUMBER      PATH 'st'
 14                                ,StartQty         NUMBER      PATH 'qty'
 15                                ,StartValue       NUMBER      PATH 'value'
 16                                ,StartOnOrder     NUMBER      PATH 'start-on-order'
 17                                ,Hand             NUMBER      PATH 'hand'
 18                                ,Ordered            NUMBER    PATH 'order'
 19                                ,Commited           NUMBER    PATH 'commit'
 20                                ,Sales            NUMBER      PATH 'sales'
 21                                ,Rec              NUMBER      PATH 'rec'
 22                                ,Valued            NUMBER     PATH 'val'
 23                                ,Id1              VARCHAR(30) PATH 'id-1'
 24                                ,Id2              VARCHAR(30) PATH 'id-2'
 25                                ,Id3              VARCHAR(30) PATH 'id-3'
 26                                ,UnitPrice        NUMBER      PATH 'unit-pr'
 27                                ,EffectiveDate    VARCHAR(30) PATH 'eff-dt'
 28                                ,EffectiveTime    VARCHAR(30) PATH 'eff-tm'
 29          ) E;

Table created.

SQL>
SQL>
SQL> delete from tt;

1 row deleted.

SQL> delete from STAGE_TBL;

1 row deleted.

SQL> commit;

Commit complete.

SQL>
SQL> declare
  2    c clob;
  3    x varchar2(32767);
  4  begin
  5    dbms_lob.createtemporary(c,true);
  6  x := '<?xml version = "1.0" encoding = "UTF-8"?>
  7  <rp_send xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
  8
  9    dbms_lob.writeappend(c,length(x),x);
 10
 11  x := '<rp_sendRow>
 12          <store>0123</store>
 13          <salesno>022399190</salesno>
 14          <cost>0.01</cost>
 15          <flag>true</flag>
 16          <st-dt>2013-04-19</st-dt>
 17          <st>146.51</st>
 18          <qty>13.0</qty>
 19          <value>0.0</value>
 20          <start-on-order>0.0</start-on-order>
 21          <hand>0.0</hand>
 22          <order>0.0</order>
 23          <commit>0.0</commit>
 24          <sales>0.0</sales>
 25          <rec>0.0</rec>
 26          <val>0.0</val>
 27          <id-1/>
 28          <id-2/>
 29          <id-3/>
 30          <unit-pr>13.0</unit-pr>
 31          <eff-dt>2015-06-16</eff-dt>
 32          <eff-tm>09:12:21</eff-tm>
 33      </rp_sendRow>';
 34
 35      for i in 1 .. 20000 loop
 36    dbms_lob.writeappend(c,length(x),x);
 37     end loop;
 38
 39  x := '</rp_send>';
 40    dbms_lob.writeappend(c,length(x),x);
 41
 42   delete from STAGE_TBL;
 43   insert into STAGE_TBL values (1,xmltype(c));
 44   commit;
 45   dbms_lob.freetemporary(c);
 46  end;
 47  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select dbms_lob.getlength(s.xml_Document.getclobval()) from stage_tbl s;

DBMS_LOB.GETLENGTH(S.XML_DOCUMENT.GETCLOBVAL())
-----------------------------------------------
                                       10440121

SQL>
SQL>
SQL> set timing on
SQL>
SQL> DECLARE
  2   rc int;
  3
  4  PROCEDURE STAGE_TBL_PROCESS(r out int) AS
  5  BEGIN
  6       INSERT ALL INTO TT
  7          (
  8              STORE
  9              ,SALESNO
 10              ,UNITCOST
 11              ,STWAC
 12              ,STDT
 13              ,ST
 14              ,STARTQTY
 15              ,STARTVALUE
 16              ,STARTONORDER
 17              ,HAND
 18              ,ORDERED
 19              ,COMMITED
 20              ,SALES
 21              ,REC
 22              ,VALUED
 23              ,ID1
 24              ,ID2
 25              ,ID3
 26              ,UNITPRICE
 27              ,EFFECTIVEDATE
 28              ,EFFECTIVETIME
 29          )
 30          VALUES (     STORE
 31              ,SALESNO
 32              ,UNITCOST
 33              ,STWAC
 34              ,STDT
 35              ,ST
 36              ,STARTQTY
 37              ,STARTVALUE
 38              ,STARTONORDER
 39              ,HAND
 40              ,ORDERED
 41              ,COMMITED
 42              ,SALES
 43              ,REC
 44              ,VALUED
 45              ,ID1
 46              ,ID2
 47              ,ID3
 48              ,UNITPRICE
 49              ,EFFECTIVEDATE
 50              ,EFFECTIVETIME
 51  )
 52          WITH T AS
 53          ( SELECT STG.XML_DOCUMENT FROM STAGE_TBL STG)
 54          SELECT E.* FROM T, XMLTABLE('rp_send/rp_sendRow' PASSING T.XML_DOCUMENT
 55          COLUMNS
 56                                 store            VARCHAR(20) PATH 'store'
 57                                ,SalesNo          VARCHAR(20) PATH 'sales'
 58                                ,UnitCost         NUMBER      PATH 'cost'
 59                                ,StWac            VARCHAR(20) PATH 'flag'
 60                                ,StDt             DATE        PATH 'st-dt'
 61                                ,St               NUMBER      PATH 'st'
 62                                ,StartQty         NUMBER      PATH 'qty'
 63                                ,StartValue       NUMBER      PATH 'value'
 64                                ,StartOnOrder     NUMBER      PATH 'start-on-order'
 65                                ,Hand             NUMBER      PATH 'hand'
 66                                ,Ordered            NUMBER      PATH 'order'
 67                                ,Commited           NUMBER      PATH 'commit'
 68                                ,Sales            NUMBER      PATH 'sales'
 69                                ,Rec              NUMBER      PATH 'rec'
 70                                ,Valued            NUMBER      PATH 'val'
 71                                ,Id1              VARCHAR(30) PATH 'id-1'
 72                                ,Id2              VARCHAR(30) PATH 'id-2'
 73                                ,Id3              VARCHAR(30) PATH 'id-3'
 74                                ,UnitPrice        NUMBER      PATH 'unit-pr'
 75                                ,EffectiveDate    VARCHAR(30) PATH 'eff-dt'
 76                                ,EffectiveTime    VARCHAR(30) PATH 'eff-tm'
 77          ) E;
 78          r := sql%rowcount;
 79  END;
 80
 81
 82  BEGIN
 83    STAGE_TBL_PROCESS(rc);
 84    COMMIT;
 85  END ;
 86  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.57
SQL>
SQL>
SQL> DECLARE
  2    rc int;
  3  PROCEDURE STAGE_TBL_PROCESS (xdata STAGE_TBL.XML_DOCUMENT%TYPE, r out int) AS
  4  BEGIN
  5       INSERT ALL INTO TT
  6          (
  7              STORE
  8              ,SALESNO
  9              ,UNITCOST
 10              ,STWAC
 11              ,STDT
 12              ,ST
 13              ,STARTQTY
 14              ,STARTVALUE
 15              ,STARTONORDER
 16              ,HAND
 17              ,ORDERED
 18              ,COMMITED
 19              ,SALES
 20              ,REC
 21              ,VALUED
 22              ,ID1
 23              ,ID2
 24              ,ID3
 25              ,UNITPRICE
 26              ,EFFECTIVEDATE
 27              ,EFFECTIVETIME
 28          )
 29          VALUES (     STORE
 30              ,SALESNO
 31              ,UNITCOST
 32              ,STWAC
 33              ,STDT
 34              ,ST
 35              ,STARTQTY
 36              ,STARTVALUE
 37              ,STARTONORDER
 38              ,HAND
 39              ,ORDERED
 40              ,COMMITED
 41              ,SALES
 42              ,REC
 43              ,VALUED
 44              ,ID1
 45              ,ID2
 46              ,ID3
 47              ,UNITPRICE
 48              ,EFFECTIVEDATE
 49              ,EFFECTIVETIME
 50  )
 51          SELECT E.* FROM XMLTABLE('rp_send/rp_sendRow' PASSING xDATA COLUMNS
 52                                 store            VARCHAR(20) PATH 'store'
 53                                ,SalesNo          VARCHAR(20) PATH 'salesno'
 54                                ,UnitCost         NUMBER      PATH 'cost'
 55                                ,StWac            VARCHAR(20) PATH 'flag'
 56                                ,StDt             DATE        PATH 'st-dt'
 57                                ,St               NUMBER      PATH 'st'
 58                                ,StartQty         NUMBER      PATH 'qty'
 59                                ,StartValue       NUMBER      PATH 'value'
 60                                ,StartOnOrder     NUMBER      PATH 'start-on-order'
 61                                ,Hand             NUMBER      PATH 'hand'
 62                                ,Ordered            NUMBER      PATH 'order'
 63                                ,Commited           NUMBER      PATH 'commit'
 64                                ,Sales            NUMBER      PATH 'sales'
 65                                ,Rec              NUMBER      PATH 'rec'
 66                                ,Valued            NUMBER      PATH 'val'
 67                                ,Id1              VARCHAR(30) PATH 'id-1'
 68                                ,Id2              VARCHAR(30) PATH 'id-2'
 69                                ,Id3              VARCHAR(30) PATH 'id-3'
 70                                ,UnitPrice        NUMBER      PATH 'unit-pr'
 71                                ,EffectiveDate    VARCHAR(30) PATH 'eff-dt'
 72                                ,EffectiveTime    VARCHAR(30) PATH 'eff-tm'
 73          ) E;
 74          r := sql%rowcount;
 75  END;
 76
 77
 78  BEGIN
 79    FOR x in ( select * from stage_tbl )
 80    LOOP
 81        STAGE_TBL_PROCESS(x.XML_DOCUMENT,rc);
 82    end loop;
 83  END ;
 84  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.61

SQL> select count(*) from tt;

  COUNT(*)
----------
     40000
SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Also asked in OTN

Marcus, September 30, 2015 - 9:35 am UTC

Maybe answered in then OTN PL/SQL forum by odie_63
https://community.oracle.com/thread/3797202
-> parsing depends on storage type - inefficient in pre 11.2.0.4 versions of PL/SQL
Connor McDonald
September 30, 2015 - 10:16 am UTC

Thanks for the update. I dont have an 11.2.0.3 to test with.

Thanks for Test case

Raghu Laghuvaram, September 30, 2015 - 8:50 pm UTC

I ran in my system and the results were far apart from yours. When I parsed using variable than from table it took 16 minutes(Elapsed: 00:16:41.033) for me. And as explained by odie_63 in the OTN( https://community.oracle.com/thread/3797202 ) and review by Marcus from LA/Bavaria, it seems like parsing depends on storage type - inefficient in pre 11.2.0.4 versions of PL/SQL and in my version its using the in-memory DOM-like representation of the XML document.
Connor McDonald
October 01, 2015 - 12:22 am UTC

I would have a chat to support. If its a bug in 11.2.0.3 and is fixed in 11.2.0.4, then there might be a backport patch available.

Having said that, with v11 now in extended support, it would be good idea to be on 11.2.0.4 anyway, but I know thats a fairly significant exercise.

Thanks for Pointing the Issue

Raghu Laghuvaram, September 30, 2015 - 8:51 pm UTC

As explained by you and odie_63 in the OTN( https://community.oracle.com/thread/3797202 ) it seems like parsing depends on storage type - inefficient in pre 11.2.0.4 versions of PL/SQL and in my version its using the in-memory DOM-like representation of the XML document.

Thanks for help.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here