January 14, 2007

Statspack Levels

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.

Related

[…] @ 3:58 pm UTC Feb 9,2007 I’ve always been a little nervous about advising people on the snapshot level and snapshot frequency for running statspack.snap(). In general level 0 every 15 minutes seems to […]

[…] with an insanely bad execution plan; clearly one to look at. Trouble is, though, that unless you set statspack to run at level 6 you don’t capture the execution plans – unlike the AWR which capture them by default. […]