Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: parse/execute ratio

RE: RE: parse/execute ratio

From: John Kanagaraj <>
Date: Tue, 2 Mar 2004 09:18:58 -0800
Message-ID: <>


How about good ol' V$SYSSTAT and V$SESSTAT to get this information? Look at 'parse count (total)' and 'parse count (hard)' (resolve v$sesstat using v$statname).

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at

>-----Original Message-----
>Sent: Tuesday, March 02, 2004 7:32 AM
>Subject: RE: RE: parse/execute ratio
>This gets a bit hairy. I'll do my best, let me know if I lose
>you. (Others let me know if I put my foot in my mouth.)
>There are more than just soft and hard parses. First, if you
>have a brand new, totally unique SQL, that's definitely going
>to be a hard parse.
>Once that's done, there are degrees of soft parses. First,
>perhaps the text of the SQL matches exactly, but it's
>submitted by a different user,
>and so resolves to different objects. That's a different type
>of parse. Then, there are cases where the text matches, and
>everything resolves
>to the same objects. If that keeps happening, you'll start
>getting session cursor cache hits.
>So, without getting into all the ugly details (which I'm not
>entirely sure I could totally elucidate without more digging
>and reading anyhow),
>that's a bit of an oversimplification of how it can work in
>some cases. There are some good sources around for more
>detail, such as Steve Adams' book,
>Bjorn Engsig's cursor sharing paper on OTN, etc.
>However, to answer your question: You can't absolutely
>definitively tell. Consider that V$SQL is at the child cursor
>level. So, AT MOST one of the
>PARSE_CALLS was a full hard parse. The rest must have been
>some sort of hard parse. In fact, if the child_number = 0,
>then the very first parse was hard,
>all others will some variant of soft parse. If child_number
>> 0, then the SQL was already there, so all parses attributed
>to this child must be some form
>of soft parse.
>Hope that helps,
>-----Original Message-----
>From: Bobak, Mark []On Behalf Of
>Sent: Tuesday, March 02, 2004 9:17 AM
>Subject: Re: RE: parse/execute ratio
>ok you lost me. soft parses get listed as parse_calls. How do
>we find out if there is only a soft parse and no hard parse?
>> From: "Bobak, Mark" <>
>> Date: 2004/03/02 Tue AM 09:12:52 EST
>> To: <>
>> Subject: RE: parse/execute ratio
>> Yes, that would be the ideal situation.
>> But, don't forget, lots of apps will do one parse per execute.
>> They will be soft parses, and perhaps even session cache cursor hits,
>> but they still get counted as parses (or PARSE_CALLS).
>> I'd say any child cursors whose EXECUTIONS is >1 and
>increasing is being
>> reused.
>> Hope that helps,
>> -Mark
>> -----Original Message-----
>> From: []
>> Sent: Tue 3/2/2004 8:59 AM
>> To:
>> Cc:
>> Subject: parse/execute ratio
>> To see if a query is being reused i can go to v$sql and look
>at parse_calls to executions right? parse_calls should be low
>and executions high?
>> ----------------------------------------------------------------
>> Please see the official ORACLE-L FAQ:
>> ----------------------------------------------------------------
>> To unsubscribe send email to:
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at
>> FAQ is at
>> -----------------------------------------------------------------
>Please see the official ORACLE-L FAQ:
>To unsubscribe send email to:
>put 'unsubscribe' in the subject line.
>Archives are at
>FAQ is at

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Mar 02 2004 - 11:13:51 CST

Original text of this message