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
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 22 2006 - 16:43:07 CST
