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