hard parse and a variable inlist

From: <ryan.gaffuri_at_cox.net>
Date: Mon, 8 Mar 2004 12:09:35 -0500
Message-Id: <20040308170934.PFZK11398.lakemtao02.cox.net@smtp.east.cox.net>

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.

I create the variable inlist package(please see below, its generic)
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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Mon Mar 08 2004 - 11:06:51 CST