STATISTICS_LEVEL

The STATISTICS_LEVEL parameter was introduced in Oracle9i Release 2 (9.2) to control all major statistics collections or advisories in the database. The level of the setting affects the number of statistics and advisories that are enabled:

BASIC: No advisories or statistics are collected.

TYPICAL: The following advisories or statistics are collected:

Buffer cache advisory
MTTR advisory
Shared Pool sizing advisory
Segment level statistics
PGA target advisory
Timed statistics

ALL: All of TYPICAL, plus the following:

Timed operating system statistics
Row source execution statistics

The parameter is dynamic and can be altered using the following.

ALTER SYSTEM SET statistics_level=basic;
ALTER SYSTEM SET statistics_level=typical;
ALTER SYSTEM SET statistics_level=all;

Current settings for parameters can be shown as follows.

SHOW PARAMETER statistics_level
SHOW PARAMETER timed_statistics

Oracle can only manage statistic collections and advisories whose parameter setting is undefined in the spfile. By default the TIMED_STATISTICS parameter is set to TRUE so this must be reset for it to be controled by the statistics level, along with any other conflicting parameters.

ALTER SYSTEM RESET timed_statistics scope=spfile sid='*';

This setting will not take effect until the database is restarted.

At this point the affect of the statistics level can be shown using the following query.

COLUMN statistics_name      FORMAT A30 HEADING "Statistics Name"
COLUMN session_status       FORMAT A10 HEADING "Session|Status"
COLUMN system_status        FORMAT A10 HEADING "System|Status"
COLUMN activation_level     FORMAT A10 HEADING "Activation|Level"
COLUMN session_settable     FORMAT A10 HEADING "Session|Settable"

SELECT statistics_name,
       session_status,
       system_status,
       activation_level,
       session_settable
FROM   v$statistics_level
ORDER BY statistics_name;

A comparison between the levels can be shown as follows.

SQL> ALTER SYSTEM SET statistics_level=basic;

System altered.

SQL> SELECT statistics_name,
  2         session_status,
  3         system_status,
  4         activation_level,
  5         session_settable
  6  FROM   v$statistics_level
  7  ORDER BY statistics_name;

                               Session    System     Activation Session
Statistics Name                Status     Status     Level      Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice            DISABLED   DISABLED   TYPICAL    NO
MTTR Advice                    DISABLED   DISABLED   TYPICAL    NO
PGA Advice                     DISABLED   DISABLED   TYPICAL    NO
Plan Execution Statistics      DISABLED   DISABLED   ALL        YES
Segment Level Statistics       DISABLED   DISABLED   TYPICAL    NO
Shared Pool Advice             DISABLED   DISABLED   TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        YES
Timed Statistics               DISABLED   DISABLED   TYPICAL    YES

8 rows selected.

SQL> ALTER SYSTEM SET statistics_level=typical;

System altered.

SQL> SELECT statistics_name,
  2         session_status,
  3         system_status,
  4         activation_level,
  5         session_settable
  6  FROM   v$statistics_level
  7  ORDER BY statistics_name;

                               Session    System     Activation Session
Statistics Name                Status     Status     Level      Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO
MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO
PGA Advice                     ENABLED    ENABLED    TYPICAL    NO
Plan Execution Statistics      DISABLED   DISABLED   ALL        YES
Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO
Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        YES
Timed Statistics               ENABLED    ENABLED    TYPICAL    YES

8 rows selected.

SQL> ALTER SYSTEM SET statistics_level=all;

System altered.

SQL> SELECT statistics_name,
  2         session_status,
  3         system_status,
  4         activation_level,
  5         session_settable
  6  FROM   v$statistics_level
  7  ORDER BY statistics_name;

                               Session    System     Activation Session
Statistics Name                Status     Status     Level      Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO
MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO
PGA Advice                     ENABLED    ENABLED    TYPICAL    NO
Plan Execution Statistics      ENABLED    ENABLED    ALL        YES
Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO
Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO
Timed OS Statistics            ENABLED    ENABLED    ALL        YES
Timed Statistics               ENABLED    ENABLED    TYPICAL    YES

8 rows selected.

SQL>

Hope this helps. Regards Tim...

Back to the Top.