hard parse and a variable inlist

From: <ryan.gaffuri@xxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Mon, 8 Mar 2004 12:09:35 -0500
I asked a question about hard parses on Friday. What I am trying to do is tell 
whether my variable inlist code is incurring a hard parse or not before moving 
forward. Here is the test I am doing.

1. I create the variable inlist package(please see below, its generic)
2. I then check for parsing in my current session with this query:

select b.name,a.*
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and a.sid = (select distinct sid from v$mystat)
and name like '%parse%'

3. Run the test
4. Check for parsing again to see if hard parse increments.
5. I then change the value that I am selecting on in my test(ie variable := 1, 
is not variable := 2) and run again
6. I run the parse script and get a hard parse.
7. However, I also trace the pl/sql with a 10046 trace and when I check the 
'parse' row of the statistics table, I get only .01 CPU usage and nothing else, 
which implies to me that a hard parse is not occurring.

The code I am using is below and is generic. Can someone help me to understand 
the discrepency(I hope this question isn't too long).

CREATE OR REPLACE PACKAGE BODY GetInList AS
FUNCTION GetNumberList (pString IN VARCHAR2) RETURN NumberTableType
IS
--  l_string        long default pString || ',';
  l_string        VARCHAR2(2000);
  l_data          NumberTableType := NumberTableType();
  n               NUMBER;
BEGIN
--  DBMS_OUTPUT.PUT_LINE (pString);
  l_string := ltrim (rtrim (pString,''''),'''') || ',';
--  DBMS_OUTPUT.PUT_LINE (l_string);
  LOOP
    EXIT WHEN l_string IS NULL;
    n := INSTR (l_string,',');
    l_data.extend;
    l_data(l_data.count) :=
           LTRIM( RTRIM( SUBSTR( l_string,1,n-1)));
    l_string := SUBSTR (l_string,n+1);
  END LOOP;
  RETURN l_data;
END;
END GetInList;
/

-- test script (we change the value of InTable(1) to test for a hard parse. 
declare
  TYPE VarcharTable IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
  InTable VarcharTable;
  InList VARCHAR2(200);
  CURSOR TestCur IS
    SELECT col
    FROM tab
    WHERE col in 
      ( SELECT * FROM TABLE (CAST (GETINLIST.GetNumberList (InList) AS 
NUMBERTABLETYPE)) );
BEGIN
  InTable(1) := '''10''';
  FOR ListId IN InTable.First..InTable.Last LOOP
    InList := InTable(ListId);
    DBMS_OUTPUT.PUT_LINE ('InList = ' || InList);
    FOR TestRec IN TestCur LOOP
      DBMS_OUTPUT.PUT_LINE (TestRec.col);
    END LOOP;
  END LOOP;
END;


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
hard parse and a variable inlist

Other related posts:
