RE: RE: parse/execute ratio
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Mar 2004 10:31:52 -0500
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
-----------------------------------------------------------------
Other related posts:
