| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: parse/execute ratio
Nope, I added 'and invalidations = 0' to my query, but I still have SQL where LOADS>PARSE_CALLS. I also added 'and executions > 0', which dropped the
number down to 10, but I still have 10 SQLs that satify:
select sql_text,
loads,
parse_calls,
invalidations,
executions
-----Original Message-----
From: Tanel Põder [mailto:tanel.poder.003_at_mail.ee]
Sent: Tuesday, March 02, 2004 2:35 PM
To: oracle-l_at_freelists.org
Subject: Re: parse/execute ratio
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.
> 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_at_mail.ee]
> Sent: Tuesday, March 02, 2004 1:53 PM
> To: oracle-l_at_freelists.org
> 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_at_il.proquest.com>
> To: <oracle-l_at_freelists.org>
> 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_at_hds.com]
> > Sent: Tuesday, March 02, 2004 12:19 PM
> > To: 'oracle-l_at_freelists.org'
> > 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_at_freelists.org
> > >[mailto:oracle-l-bounce_at_freelists.org]
> > >Sent: Tuesday, March 02, 2004 7:32 AM
> > >To: oracle-l_at_freelists.org
> > >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_at_freelists.org]On Behalf Of
> > >ryan.gaffuri_at_cox.net
> > >Sent: Tuesday, March 02, 2004 9:17 AM
> > >To: oracle-l_at_freelists.org
> > >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_at_il.proquest.com>
> > >> Date: 2004/03/02 Tue AM 09:12:52 EST
> > >> To: <oracle-l_at_freelists.org>
> > >> 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_at_cox.net [mailto:ryan.gaffuri_at_cox.net]
> > >> Sent: Tue 3/2/2004 8:59 AM
> > >> To: oracle-l_at_freelists.org
> > >> 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_at_freelists.org
> > >> put 'unsubscribe' in the subject line.
> > >> --
> > >> Archives are at http://www.freelists.org/archives/oracle-l/
> > >> FAQ is at http://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_at_freelists.org
> > >put 'unsubscribe' in the subject line.
> > >--
> > >Archives are at http://www.freelists.org/archives/oracle-l/
> > >FAQ is at http://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_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://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_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Mar 02 2004 - 13:39:59 CST
 |
 |