Re: DBMS_SQL - Parse Step Question

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Tue, 27 Jul 1999 04:53:17 GMT
Message-ID: <37ac3a10.3580558@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 Mon Jul 26 1999 - 23:53:17 CDT