Re: DBMS_SQL - Parse Step Question

A copy of this was sent to "Ian" <WobbleLover_at_yahoo.com> (if that email address didn't require changing) On Tue, 27 Jul 1999 09:18:35 +1200, 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?
>

yes it does however PARSEING involves looking in the shared pool to see if the statement is already there.

The first time a statement hits a database, it takes longer to 'parse' then the second time (since its already parsed, we just have to find it and verify we can use that parsed copy).

>If the exact same statement is used it seems to make sense that the
>cursor.parse(); step would check the sga and if the statement already exists
>do only the error checking phase, rather than the full parse to figure out
>the execution plan.
>

that is correct (although not sure what you mean by error checking phase).

>My friend thinks that the execution of the cursor.parse(); would force the
>full parse to occur.
>

that is incorrect. flushing the shared pool or otherwise invalidating the cursor (adding an index to the table, analyzing the table, etc) would invalidate cursors pointing to that table, causing them to be really reparsed next time.

>I think that it would be treated the same way as for a normally executed sql
>statement.
>
>There is nothing definitive in any of the manuals we have.
>
>Can anyone who KNOWS settle our aurgument??

One empirical way is to use SQL_TRACE and TIMED_STATISTICS. I ran the following script in SQLPLUS:

disconnect
connect tkyte/tkyte

alter session set sql_trace=true;

SeLeCt CoUnT(*) FrOm AlL_ObJeCtS;

disconnect
connect tkyte/tkyte

alter session set sql_trace=true;

SeLeCt CoUnT(*) FrOm AlL_ObJeCtS;

Using the funny mixed case lets me run a query I'm pretty sure no one else has run, disconnect, reconnect, and rerun the statement. The SQL traces (formatted using TKPROF) show:

SeLeCt CoUnT(*)
FrOm
 AlL_ObJeCtS

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

Parse        1      0.05       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     10.73      16.64        892     214774          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     10.78      16.69        892     214774          4           1

and then:

SeLeCt CoUnT(*)
FrOm
 AlL_ObJeCtS

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        2     10.25      10.43          5     214773          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     10.25      10.43          5     214773          4           1

We parsed both times but the second parse was a 'soft' parse -- we found it in the shared pool. it happened much faster then second time then the first.

Bear in mind tho, that (some big number) * (some small number) is still a big number so even soft parses should be avoided whenever possible. They are faster but they are not for free...

>
>Ian
>

--
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 Jul 27 1999 - 07:31:04 CDT