Re: Re-parsing for SQL associated with PL/SQL cursor variables

A copy of this was sent to "Adrian Bowen" <adrian_at_raptor.win-uk.net> (if that email address didn't require changing) On Tue, 21 Sep 1999 13:11:05 +0100, you wrote:

>I have a number of utility functions in a PL/SQL package which return cursor
>variables - they essentially do some massaging of their arguments, then do
>an OPEN FOR, always with the same SQL statement (apart from bind variable
>values). The caller then FETCHes rows as required and finally CLOSEs the
>cursor variable.
>
>From my tkprof output, it would appear that the parse count and execution
>count of the SQL used by the cursor variables is always the same - even
>though my shared pool is not full, and all other SQL from the package (i.e.
>that associated with CURSOR declarations) is successfully cached, and not
>re-parsed.
>
>Is this to be expected? Is there something about cursor variable SQL that
>causes it not to be cached?
>
>Ta for any help
>
>Adrian Bowen
>
>
>
>
>

Cursor variables are not cached as other PL/SQL cursors are. Since you can open the cursor variable with many different statements at runtime, eg:

  if ( x = 5 ) then

     open cv for select * from emp;
  else

     open cv for select * from dept;
  end if;

they cannot be cached (else you would get the wrong answer).

The upside to this is that you are seeing SOFT PARSES, not HARD ones.

Here is an example with the TKPROF output showing the difference:

From the first run -- nothing parse into shared sql as yet:

declare

    type rc is ref cursor;
    cv rc;
begin

    open cv for SELECT * from ALL_OBJECTS WHERE OBJECT_ID > 0; end;

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.01          0          0          0           1

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 10991 (TKYTE)

SELECT *
FROM
 ALL_OBJECTS WHERE OBJECT_ID > 0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.03       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.03          0          0          0           0

Now the second run -- after logging out and logging back in

declare

    type rc is ref cursor;
    cv rc;
begin

    open cv for SELECT * from ALL_OBJECTS WHERE OBJECT_ID > 0; end;

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.01          0          0          0           1

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 10991 (TKYTE)

SELECT *
FROM
 ALL_OBJECTS WHERE OBJECT_ID > 0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Note the PARSE times for the query -- measurable in run 1 (the HARD PARSE), not measurable in run 2 -(SOFT PARSE, got a hit in the shared sql area).

so your query is in fact cached in the shared sql area, its just not being held as an OPEN CURSOR in plsql (as static sql would be)

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 21 1999 - 09:02:26 CDT