Re: statspack snap level 7 as execution plan rep

Correct - you might just get "lucky" as in this case. Tim Gorman has a set of scripts at his site that can mine your STATSPACK data for just such a thing!

John

On 11/22/06, Alex Gorbachev <gorbyx_at_gmail.com> wrote:
>
> Right. But taking the diff in the number of executions for the period
> we can judge if it was executed or not. On the other hand, it's very
> easy to miss a "top" statement is it was aged out at the moment of a
> snap as John explained.
> I guess it all depends on particular case - I had some applications
> where I was able to use this technique with more or less high
> probability of capturing majority/most of statements.
>
> On 11/22/06, John Kanagaraj <john.kanagaraj_at_gmail.com> wrote:
> > Alex (M),
> >
> > Please note that STATSPACK's SQL capture does NOT *really* capture Top
> SQL
> > for the *period*. What it does capture is current Top SQL as seen in
> V$SQL,
> > based on the thresholds that Alex G mentions. Thus, this snapshot could
> > possibly include SQL that executed a while ago, (and not during the
> > STATSPACK snapshot period in question) but is retained in the lib cache
> and
> > has a high count for the threshold values being checked. In other words,
> > this is NOT equivalent to the Top SQL captured by 10g AWR which captures
> Top
> > SQL for the period in question. So you could have genuine SQL that
> executed
> > during the snapshot period (and caused a slowdown in performance) and
> either
> > did not meet the threshold requirements or got aged out from the shared
> pool
> > (not pinned, etc.). This is a subtle issue in STATSPACK that is not very
> > well known :)
> >
> >
> > On 11/21/06, Alex Gorbachev <gorbyx_at_gmail.com> wrote:
> > > You might want to change default thresholds that statspack uses to
> > > identify statements to include. Still you won't get guarantee that all
> > > statements are captured. You might not need it though... depends on
> > > your targets.
> > >
> > > On 11/20/06, amonte <ax.mount_at_gmail.com> wrote:
> > > > Hi
> > > >
> > > > I wonder if any of you use statspack to store a execution plan
> > repository?
> > > > When run in level 7. Is it worthy? Oracle 9.2.0.8 in HP-UX.
> > > >
> > > > TIA
> > > >
> > > > Alex
> > > >
> > >
> > >
> > > --
> > > Best regards,
> > > Alex Gorbachev
> > >
> > > The Pythian Group
> > > Sr. Oracle DBA
> > >
> > > http://www.pythian.com/blogs/author/alex/
> > > http://blog.oracloid.com
> > > --
> > > http://www.freelists.org/webpage/oracle-l
> > >
> > >
> > >
> >
> >
> > --
> > John Kanagaraj <><
> > DB Soft Inc
> > Phone: 408-970-7002 (W)
> >
> > Disappointment is always inevitable; Discouragement is invariably
> optional
> >
> > ** The opinions and facts contained in this message are entirely mine
> and do
> > not reflect those of my employer or customers **
> >
>
>
> --
> Best regards,
> Alex Gorbachev
>
> The Pythian Group
> Sr. Oracle DBA
>
> http://www.pythian.com/blogs/author/alex/
> http://blog.oracloid.com
>

-- 
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is always inevitable; Discouragement is invariably optional

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

--
http://www.freelists.org/webpage/oracle-l