Statspack Levels

[Further Reading on Statspack]

There are currently five different levels of statspack snapshots, defined as follows in the table stats$level_description (9i version):

SNAP_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         

Now, I happen to be quite keen on the idea of collecting stats at level 0 every fifteen minutes – most systems can afford the overhead, and the level of detail is often enough to help you pin down critical performance issues.

On the other hand, it would be quite nice to collect segment statistics at the same time, as there are some items from v$waitstat, v$sysstat, or v$system_event that you might like to correlate against work done on specific segments. But you can’t collect segment statistics (level 7) unless you also collect SQL statements (level 5), and SQL Plans (level 6) – and collecting the SQL with its plans can be very expensive, even after you’ve adjusted the default collection levels.

But Oracle has given us the source code of the statspack package – and it’s not difficult to figure out how you can change the source code to your own ends. Of course, there is a small risk involved, and you have to remember to check that things haven’t changed on every upgrade, but here’s an example of a very simple change that you may find useful:

Look for the code lines:

     IF l_snap_level >= 5 THEN 
       snap_sql; 
     END IF;    /* snap level >=5 */ 

and change the condition to:

     IF l_snap_level >= 5 and l_snap_level != 8 THEN 

Finally insert into the stats$level_description table a row for your new level.

insert into stats$level_description values (8,'This is my level...'); 

From this point onwards, you can take snapshots at level 8. (Note – if you want to block snapshots for (say) levels 5, 6, and 10 you could simply delete these values from the table when you first install statspack).

The effect of this bit of hacking is that you can still request snapshots at levels 5, 6, or 7 and get the normal results,  but if you request level 8 you will get the segment statistics without getting the SQL and execution plans.

Of course, you can get more sophisticated and subtle. You might introduce a further level that allows you to trap the SQL and the segment statistics without trapping the plans. (At present, the check for level 6 happens inside the procedure snap_sql if you want to do this). If you do get adventurous, though, make sure that the standard report doesn’t crash with ‘no data’ errors before you ship your modified package into production.

One final warning – always monitor the impact of running a snapshot before you decide on the frequency; the greater the work, the longer the time-interval you should choose; and always consider modifying the capture thresholds – the logical I/O threshold for segment statistics is a surprisingly low 10,000 – and almost certainly ought to be increased if you decide to implement my little bit of hackery.

[Further Reading on Statspack]