Some Oracle Statspack tips

Although AWWR is now available for V10, and is (probably) better than statspack, it is an option that requires a license. I tend to install statspack onto a database so that I can still get performance information. There are a couple of things I've picked up over the years about statspack that are worth looking at.

The first is the statspack level - by default it's level 5, but I prefer level 7, mainly because it lists the top 5 objects.

To see the statspack levels available:

SELECT * FROM stats$level_description ORDER BY snap_level;

Note that V8i has levels 0,5 and 10; V9 has levels 0,5,6,7 and 10.

To change the level you run this command:

exec statspack.snap(i_snap_level => 7, i_modify_parameter => 'true');



If there are a large number of snapshots you may see this when you run an spreport.sql:

Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
PRD PRD ##### 02 May 2010 22:00 10


In order to see the snap_ids you will need to change the column format in the spreport.sql (or sprepins.sql) . I tend to copy the $ORACLE_HOME/rdbms/admin/spreport.sql (or sprepins.sql) to a backup copy and make a couple of changes.

Change this line in spreport.sql

column snap_id heading 'Snap|Id' format 9990;

to

column snap_id heading 'Snap|Id' format 9999999990;

Running the spreport.sql again shows this:

Instance DB Name Id Snap Started Level
------------ ------------ ----------- ----------------- -----
Comment
----------------------

PRD PRD 33739 06 May 2010 10:00 10


It's wrapped the columns, but at least the snap_id is now visible.



Another change I like to make is the number of lines in the SQL Statements. By default this is only set to 5. This means that the top SQL sections only show the first 5 lines of the statement, which doesn't really show enough.

Change this line:

define top_n_sql = 5;

to

define top_n_sql = 65;

or how ever many lines you think you want. This will now display more, or all, of the SQL statement which you can copy to run an explain on.