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

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

RE: RE: parse/execute ratio

From: <oracle-l-bounce_at_freelists.org>
Date: Tue, 2 Mar 2004 10:31:52 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC2C@bosmail00.bos.il.pqe>


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
-----------------------------------------------------------------
Received on Tue Mar 02 2004 - 09:28:47 CST

Original text of this message

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