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

When you OPEN FOR, the statement always has to go through a parse call - but this may turn out to be a very quick, cheap, 'soft' parse rather than a full-blooded 'hard' parse.

You can check either by setting timed_statistics=true, and checking the time to parse for the first and subsequent parse calls in the trace file, or by checking the 'parses (hard)' count in v$sysstat.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Adrian Bowen wrote in message <7s7sok$ck3$1_at_laura.pcug.co.uk>...
>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?
Received on Tue Sep 21 1999 - 08:06:16 CDT