Re: RE: parse/execute ratio
- From: "Ryan" <ryan.gaffuri@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Mar 2004 21:30:27 -0500
you got a link for bjorn ensig's article. I did a search and cant find it.
----- Original Message -----
From: <oracle-l-bounce@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, March 02, 2004 10:31 AM
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:
