Re: Top SQL from statspack

Norman Dunbar <Norman_at_REMOVETHIS.Dunbar-IT.co.uk> wrote:

>run the Statspack snapshot at level 6.
>
>Run the SPREPORT script to get the top SQL stuff as normal, note the
>hash value.
>
>Run the SPREPSQL and give it the hash it wants.
>

According to the doc, level 5 should suffice to collect sql with high resource consumption:

 The following descriptions were extracted using select * from perfstat.stats$level_description:

• 0: This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information
• 5: This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels
• 6: This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels
• 7: This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels
• 10: This level includes capturing Child Latch statistics, along with all data captured by lower levels

The mere fact that I don't find the sql statements in the stats$sql_summary table support your advice that level 6 is needed, but this contradicts the documentation.

Regards
Rick Denoire Received on Tue Dec 21 2004 - 15:46:11 CST