title: Passing a huge parameter to oracle procedure have a performance hit?
body: <p>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?</p>
  
  <p>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</p>
  
  <p>Note:I couldn't perform SQL_TRACE or DBMS_STATS as I don't have access to them.</p>
  
  <pre><code>/*
  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 &lt; 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 &lt;= 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 &gt; 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 &lt; 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 &lt;= 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 &gt; 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 
  &lt;?xml version = \"1.0\" encoding = \"UTF-8\"?&gt;  
  &lt;rp_send xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"&gt;  
      &lt;rp_sendRow&gt;  
          &lt;store&gt;0123&lt;/store&gt;  
          &lt;sales&gt;022399190&lt;/sales&gt;  
          &lt;cost&gt;0.01&lt;/cost&gt;  
          &lt;flag&gt;true&lt;/flag&gt;  
          &lt;st-dt&gt;2013-04-19&lt;/st-dt&gt;  
          &lt;st&gt;146.51&lt;/st&gt;  
          &lt;qty&gt;13.0&lt;/qty&gt;  
          &lt;value&gt;0.0&lt;/value&gt;  
          &lt;start-on-order&gt;0.0&lt;/start-on-order&gt;  
          &lt;hand&gt;0.0&lt;/hand&gt;  
          &lt;order&gt;0.0&lt;/order&gt;  
          &lt;commit&gt;0.0&lt;/commit&gt;  
          &lt;sales&gt;0.0&lt;/sales&gt;  
          &lt;rec&gt;0.0&lt;/rec&gt;  
          &lt;val&gt;0.0&lt;/val&gt;  
          &lt;id-1/&gt;  
          &lt;id-2/&gt;  
          &lt;id-3/&gt;  
          &lt;unit-pr&gt;13.0&lt;/unit-pr&gt;  
          &lt;eff-dt&gt;2015-06-16&lt;/eff-dt&gt;  
          &lt;eff-tm&gt;09:12:21&lt;/eff-tm&gt;  
      &lt;/rp_sendRow&gt;  
  &lt;/rp_send&gt;  
  </code></pre>