RE: RE: parse/execute ratio

  • From: John Kanagaraj <john.kanagaraj@xxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 2 Mar 2004 09:18:58 -0800

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

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-----Original Message-----
>From: oracle-l-bounce@xxxxxxxxxxxxx 
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 
>Sent: Tuesday, March 02, 2004 7:32 AM
>To: oracle-l@xxxxxxxxxxxxx
>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@xxxxxxxxxxxxx]On Behalf Of
>ryan.gaffuri@xxxxxxx
>Sent: Tuesday, March 02, 2004 9:17 AM
>To: oracle-l@xxxxxxxxxxxxx
>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@xxxxxxxxxxxxxxx>
>> Date: 2004/03/02 Tue AM 09:12:52 EST
>> To: <oracle-l@xxxxxxxxxxxxx>
>> 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@xxxxxxx [mailto:ryan.gaffuri@xxxxxxx]
>> Sent:        Tue 3/2/2004 8:59 AM
>> To:  oracle-l@xxxxxxxxxxxxx
>> 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@xxxxxxxxxxxxx
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at //www.freelists.org/archives/oracle-l/
>> FAQ is at //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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: