This test was on 9.2.0.4 on Solaris 2.8.
-----Original Message-----
From: Tanel Põder [mailto:tanel.poder.003_at_mail.ee]
Sent: Tuesday, March 02, 2004 4:35 PM
To: oracle-l_at_freelists.org
Subject: Re: parse/execute ratio
LOADS in V$SQL can increment without precedent invalidations, for example
when only some heaps of shared SQL are kicked out of shared pool (e.g. heap
6 storing query execution plan) and the SQL is reparsed after that. You can
easily verify it by caching a cursor and flushing shared pool, the cursor
remains there, having invalidations 0 and loads > 1 after next parse.
However, as I wrote in a previous mail, the parse_count and executions count
tend to cumulate despite the flush, so this part of your question is still
unanswered. I tried to see if there is any difference if some cursor heaps
are aged out by normal shared pool LRU mechanisms, but brief (and maybe
incorrect) test showed no difference from manual shared pool flush.
On which version and platform are you?
Tanel.
- Original Message -----
From: "Bobak, Mark" <Mark.Bobak_at_il.proquest.com>
To: <oracle-l_at_freelists.org>
Sent: Tuesday, March 02, 2004 9:43 PM
Subject: 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
> from v$sql
> where loads > parse_calls
> and invalidations = 0
> and executions > 0
> /
>
>
>
> -----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.
>
> ----- Original Message -----
> From: "Bobak, Mark" <Mark.Bobak_at_il.proquest.com>
> To: <oracle-l_at_freelists.org>
> 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_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
> > -----------------------------------------------------------------
> >
>
>
> ----------------------------------------------------------------
> 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
-----------------------------------------------------------------
Received on Tue Mar 02 2004 - 15:44:40 CST
