Re: DBMS_SQL - Parse Step Question

I think TKyte has answered this one before, I think, but regarding OCI rather than PL/SQL.

There is a difference between a 'parse call', and a 'parse'. Whilst you send a 'parse call' for the statement to Oracle, the first step following that call is the low-cost check to the shared pool; only if this check on the shared pool fails do you get a 'real' parse of the statement.

Later versions of Oracle have two parse stats in v$sysstat -

    parse count(total)
    parse count(hard)

given that it doesn't have a

    parse count(easy)
I assume that (hard) parses are ones which fall into the 'real' parsing process. So you can test accordingly by dbms_sql.parseing the same statement lots of times and seeing which count increases more.

--

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

Jonathan Gennick wrote in message
<37ac3a10.3580558_at_netnews.worldnet.att.net>...
>comp.databases.oracle.server you wrote:
>
>>We're having a little argument here.
>>
>>If we use dbms_sql to define, parse and execute a sql statement, does it
>>really get parsed EVERY time?
>
>I tend to doubt it. I would think it would be found in the
>shared pool.
>
>>There is nothing definitive in any of the manuals we have.
>>
>>Can anyone who KNOWS settle our aurgument??
>
>I know how you could prove it one way or another. Trace
>files report the number of parses. So you could *prove* the
>behavior by turning tracing on for a session, running some
>pl/sql that repeatedly defined, parsed, and executed the
>same code, and viewing the results in the trace file (using
>TKPROF of course). Your trace file results will show the
>following for each SQL statement that you execute in your
>session:
>
>call count
>------- ------ ---
>Parse 0
>Execute 4998
>Fetch 4998
>------- ------ ---
>total 9996
>
>If the parse count is equal to the number of timesy ou
>executed your query, then you can be pretty certain that
>dbms_sql.parse is forcing a parce each time. Tthe only other
>explanatioin would be a very small shared pool.
>
>Let me know how it turns out if you do this.
>
>regards,
>
>Jonathan
>
>_____________________________________________________
>jonathan_at_gennick.com
>http://gennick.com
>Brighten the Corner Where You Are
>
Received on Tue Jul 27 1999 - 01:59:30 CDT