RE: parse/execute ratio
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Mar 2004 20:53:02 +0200
Well, the LOADS column in V$SQL is very close to hard parses for a given SQL
in shared pool. Whenever a SQL is invalidated or partially flushed from
shared pool, the loads column increases on next parse of the SQL.
Tanel.
----- Original Message -----
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, March 02, 2004 7:21 PM
Subject: RE: RE: parse/execute ratio
> John,
>
> Agreed, the info is available there, w/ respect to a session, or
system-wide. But, Ryan had focused on V$SQL, and from the point of view of
an individual child cursor, I don't think that level of detail as to what
types of parses have taken place, is available.
>
> -Mark
>
> -----Original Message-----
> From: John Kanagaraj [mailto:john.kanagaraj@xxxxxxx]
> Sent: Tuesday, March 02, 2004 12:19 PM
> To: 'oracle-l@xxxxxxxxxxxxx'
> Subject: RE: RE: parse/execute ratio
>
>
> 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
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> 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:
