Re: statspack snap level 7 as execution plan rep
- From: "Alex Gorbachev" <gorbyx@xxxxxxxxx>
- To: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- Date: Wed, 22 Nov 2006 17:43:07 -0500
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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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
> --
> //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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
