Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: TKPROF Parse/Execute count
To avoid any form of parse call after the first one, a session has to go through a specific set of steps that say to the Oracle kernel (roughly):
Here's an sql statement, parse it, pin the resulting information in memory, and tell me where you've put it.
Subsequent calls can then be
go to this location and execute the
thing you find there
rather than
check through the library cache to
see if you done this one before etc.
(which is one of the possible strategies
on a parse call).
If you use the 'parse, pin, and re-use' method, you have to end your session, or explicitly release the parse information when you no longer need it, so that the free memory in the library cache doesn't get all used up.
Since there is no way you could guarantee that a user would remember to send in a 'get rid of cursor X information' when they had finished with a statement, SQL*Plus does not use the parse-once method.
On the plus side, there are several different levels to the parse call, and most of your 11 calls would have been one of the cheaper, the "been there, done that, know what I'm doing" versions.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st "obiru" <boogab00_at_yahoo.com> wrote in message news:32948959.0405281105.11220953_at_posting.google.com...Received on Sat May 29 2004 - 02:03:07 CDT
> Obviously there's something I don't understand about parsing. The
> following is a tkprof excerpt from a trace I did on my own sql*plus
> session. I just entered the query once, and ran it 11 times by
> entering "/" and hitting return. Does anyone know why I had 11 parses
> and 11 executes?
>
> select count(*)
> from
> dba_tables
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 11 0.04 0.04 0 3 0
> 0
> Execute 11 0.00 0.00 0 0 0
> 0
> Fetch 22 1.64 1.80 11 194975 44
> 11
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 44 1.68 1.84 11 194978 44
> 11
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 5