RE: RE: parse/execute ratio
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Mar 2004 22:13:25 -0600
Please remember that there's one aspect of *any* parse call (hard,
"soft", "softer", or otherwise) that cannot be avoided: the inter-dbcall
latency that will show up in your extended SQL trace file as time
consumed waiting for the event named 'SQL*Net message from client'. In
many applications I see these days, the total latency from these events
completely dominate the total end-user response time of the application.
The ONLY way to fix this problem is to STOP THE APP from making
unnecessary dbcalls. In this conversation, the topic at hand is parse
calls. In many applications, using too small of an array fetch size or
doing one-row-at-a-time inserts, updates, or deletes causes the same
problem.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ryan
Sent: Tuesday, March 02, 2004 8:30 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: RE: parse/execute ratio
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
