Re: statspack snap level 7 as execution plan rep

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 **

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