04 August 2006
How to select the top SQL in Statspack
I wanted to get the top 50 SQL statements for the tuning benchmark and I was thinking of whether it is possible to do it automatically from the Statspack, instead selecting from stats$sql_summary and such.
Snap procedure in 9i has a parameter num_sql, which is reflected in the table:
STATS$STATSPACK_PARAMETER.NUM_SQL with the default value of 50.
Interestingly, it does not do anything. I assume Oracle was planning to make it work in 10g, but then Statspack was replaced with the Automatic Workload Repository in 10g, and we never got to see it work.
Just an observation.
Snap procedure in 9i has a parameter num_sql, which is reflected in the table:
STATS$STATSPACK_PARAMETER.NUM_SQL with the default value of 50.
Interestingly, it does not do anything. I assume Oracle was planning to make it work in 10g, but then Statspack was replaced with the Automatic Workload Repository in 10g, and we never got to see it work.
Just an observation.
