| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: parse/execute ratio
Mark/Ryan,
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 http://www.klove.com
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org]
>Sent: Tuesday, March 02, 2004 7:32 AM
>To: oracle-l_at_freelists.org
>Subject: RE: RE: parse/execute ratio
>
>
>Ryan,
>
>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,
>
>-Mark
>
>-----Original Message-----
>From: Bobak, Mark [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of
>ryan.gaffuri_at_cox.net
>Sent: Tuesday, March 02, 2004 9:17 AM
>To: oracle-l_at_freelists.org
>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" <Mark.Bobak_at_il.proquest.com>
>> Date: 2004/03/02 Tue AM 09:12:52 EST
>> To: <oracle-l_at_freelists.org>
>> 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: ryan.gaffuri_at_cox.net [mailto:ryan.gaffuri_at_cox.net]
>> Sent: Tue 3/2/2004 8:59 AM
>> To: oracle-l_at_freelists.org
>> 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: http://www.orafaq.com
>> ----------------------------------------------------------------
>> To unsubscribe send email to: oracle-l-request_at_freelists.org
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at http://www.freelists.org/archives/oracle-l/
>> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>> -----------------------------------------------------------------
>>
>>
>>
>>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Mar 02 2004 - 11:13:51 CST
 |
 |