Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: TKPROF Parse/Execute count

Re: TKPROF Parse/Execute count

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 29 May 2004 07:03:07 +0000 (UTC)
Message-ID: <c99cjb$1cb$1@titan.btinternet.com>

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...

> 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
Received on Sat May 29 2004 - 02:03:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US