Oracle Scratchpad

January 14, 2007

Statspack Levels

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 9:24 pm GMT Jan 14,2007

[Further Reading on Statspack]

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

---------- ----------------------------------------------------------- 
         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 
     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]


  1. […] @ 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 […]

    Pingback by Statspack 10g « Oracle Scratchpad — February 9, 2007 @ 10:30 pm GMT Feb 9,2007 | Reply

  2. Hi

    it is very nice document for stats pack levels and differences between the levels. so this document definitely help full.

    Comment by Aseem — July 17, 2009 @ 5:48 am BST Jul 17,2009 | Reply

  3. […] 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. […]

    Pingback by Analysing Statspack 13 « Oracle Scratchpad — January 7, 2013 @ 6:46 pm GMT Jan 7,2013 | Reply

  4. […] Statspack (at level 7) and the AWR report have several “Top N” sections for segment statistics. If we examine […]

    Pingback by Index Usage | Oracle Scratchpad — January 14, 2020 @ 11:27 am GMT Jan 14,2020 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: