Changing The STATSPACK Statistics Level
Just before Christmas, I was working on a project that required STATSPACK, and I couldn't for the life of me remember how to change the STATSPACK statistics level. Anyway, as a reminder to myself, here's how it's done.
From Oracle Magazine's "Diagnosing Performance with Statspack"
"DBAs can change the amount of information or detail of statistics Statspack gathers by specifying a snapshot level. The level you choose dictates how much data Statspack collects. Level 5 is the default.
- Level 0: Statspack collects general performance statistics such as wait statistics, system events, system statistics, rollback-segment data, row cache, SGA, background events, session events, lock statistics, buffer-pool statistics, and parent latch data.
- Level 5: Statspack collects all the statistics it gathers at level 0 plus performance data about high-resource-usage SQL statements.
- Level 10: Statspack collects all the statistics from level 0 and level 5 as well as child-latch information. At level 10, the snapshot can sometimes take longer to gather data because level 10 can be resource-intensive. You should use it only on the advice of Oracle personnel.
Levels 5 and 10 capture high-resource SQL statements that exceed any of the following four threshold parameters:
- the number of executions of the SQL statement (default = 100)
- the number of disk reads the SQL statement performs (default = 1,000)
- the number of parse calls the SQL statement performs (default = 1,000)
- the number of buffer gets the SQL statement performs (default = 10,000)
If a SQL statement's resource usage exceeds any one of these threshold values, Statspack captures the statement when it takes a snapshot."
To specify the statistics level for a particular statspack snapshot, use the command;
SQL> execute statspack.snap -
(i_snap_level=> statistics_level);
To change the default value for this and all future snapshots, use the command;
SQL> execute statspack.snap -
(i_snap_level=> statistics_level, i_modify_parameter=> 'true');
Bear in mind though, that the default statistics level is actually 5 - which is usually enough to capture all the information you need (long running SQL queries, in my case). With Oracle 9i, level 6 stores the explain plans for these SQL statements, whilst with 9.2 level 7 gathers segment statistics. As the article says, only use 10 if you're asked to by Oracle Support.
There's a couple of useful powerpoint presentations on using STATSPACK to tune Oracle databases that you might want to download;
- Using Statspack in Oracle8i and 9i to Identify Problems by Ian Jones of Database Specialists Inc, and
- The Ins and Outs Of STATSPACK by Susan McClain of Team Lead
whilst these three Oracle articles by Connie Dialeris and Graham Wood are 'required reading' for interpreting and acting on the STATSPACK reports