Re: parse/execute ratio
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Mar 2004 21:34:49 +0200
Hi!
When a SQL is invalidated, the parse_calls and executions get reset to zero,
but the loads column does not.
One interesting thing about flushing shared pool on 9.2.0.4 W2k - right
after flush the parse_calls and executions columns are set to zero, but
after the next parse (causing a reload), the column statistics continue to
be incremented from their previous values, so it seems that internally they
aren't reset.
Tanel.
----- Original Message -----
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, March 02, 2004 9:24 PM
Subject: RE: parse/execute ratio
> Hmm...hadn't thought of that. I did some quick poking around, and a quick
IxOra reference. That seems to be absolutely true.
>
> I did find a few counterexamples, though, I think. If LOADS is hard
parses and PARSE_CALLS is total parses, then why do I have rows
> in V$SQL where LOADS > PARSE_CALLS?
>
> Any thoughts, Tanel? (Or anyone else?)
>
> Hmm....a slightly closer look at those SQLs, and they all seem to be
recursive SQL. Not sure what's going on there.
>
> -Mark
>
> -----Original Message-----
> From: Tanel Põder [mailto:tanel.poder.003@xxxxxxx]
> Sent: Tuesday, March 02, 2004 1:53 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: parse/execute ratio
>
>
> 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
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> 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:
