STATSPACK Statistics Level


"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.

Setting the Statistics Levels

In order for Oracle to collect those statistics, you must have proper initialization parameters set in the instance. The parameter is STATISTICS_LEVEL and is set in the init.ora. The good news is that this is modifiable via ALTER SYSTEM command and some underlying parameters are even modifiable via ALTER SESSION. This parameter can take three values:

1. BASIC: At this setting Oracle des not collect any stats. Although this is not recommended, you may decide to set this in a fine-tuned production system to save some overhead.

2. TYPICAL: This is the default value. In this setting, Oracle collects the following statistics.

• Buffer Cache ? These statistics advise the DBA how to tune the multiple buffer pools. The statistics can also be collected by setting another parameter DB_CACHE_ADVICE independently using initialization file, stored parameter file, ALTER SYSTEM or ALTER SESSION. If it's independently set, that setting takes preference over the statistics level setting.
• Mean Time to Recover ? These statistics help the DBA set an acceptable Mean Time to Recover (MTTR) setting, sometimes due to the requirements from Service Level Agreements with the users.
• Shared Pool Sizing ? Oracle can provide valuable clues to size the shared pool effectively based on the the usage and these statistics provide information on that.
• Segment Level Statistics ? These statistics are collected at the segment level to help determine the wait events occurring at each segment. We are interested in these statistics.
• PGA Target ? These statistics help tune the Program Global Area effectively based on the usage.
• Timed Statistics ? This is an old concept. The timed statistics were enabled in earlier versions with the initialization parameter timed_statistics. However, the statistic was so useful that Oracle made it default with the setting of statistic_level. It can be set independently, too; and if set, overrides the statistics_level setting.

3. ALL: In this setting,  all of the above statistics are collected as well as an additional two.

Row Source Execution Stats ?These statistics help tune the sql statements by storing the execution statistics with the parser. This can provide an extremely useful tool in the development stages.

Timed OS Statistics ? Along with the timed statistics, if the operating system permits it, Oracle can also collect timed stats from the host. Certain operating systems like Unix allow it. It too can be set independently; and if set, overrides the statistics_level setting.

If you set these via any of the three methods, Initialization File, ALTER SYSTEM or ALTER SESSION, you can find out the current setting by querying the view V$STATISTICS_LEVEL as follows:

SELECT ACTIVATION_LEVEL, STATISTICS_NAME, SYSTEM_STATUS, SESSION_STATUS FROM V$STATISTICS_LEVEL ORDER BY ACTIVATION_LEVEL, STATISTICS_NAME;

So, set the STATISTICS_LEVEL to TYPICAL either by ALTER SYSTEM or by an initialization parameter file. Do not forget to restart the database if you choose the latter.