ORACLE STATISTICS_LEVEL

STATISTICS_LEVEL


Property Description
Parameter type String
Syntax STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }
Default value TYPICAL
Modifiable ALTER SESSION, ALTER SYSTEM


STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.

The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

* Automatic Workload Repository (AWR) Snapshots
* Automatic Database Diagnostic Monitor (ADDM)
* All server-generated alerts
* Automatic SGA Memory Management
* Automatic optimizer statistics collection
* Object level statistics
* End to End Application Tracing (V$CLIENT_STATS)
* Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
* Service level statistics
* Buffer cache advisory
* MTTR advisory
* Shared pool sizing advisory
* Segment level statistics
* PGA Target advisory
* Timed statistics
* Monitoring of statistics

When the STATISTICS_LEVEL parameter is modified by ALTER SYSTEM, all advisories or statistics are dynamically turned on or off, depending on the new value of STATISTICS_LEVEL. When modified by ALTER SESSION, the following advisories or statistics are turned on or off in the local session only. Their system-wide state is not changed:

* Timed statistics
* Timed OS statistics
* Plan execution statistics

The V$STATISTICS_LEVEL view displays information about the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter.


The initialization parameter STATISTICS_LEVEL controls quite a number of functionalities in the Oracle 10g database.
Its default value is TYPICAL.

By setting it to BASIC you switch off functionalities like automatic statistics collection for performance baselines, statistics sampeling for the active session history, table monitoring and a lot of more functionalities. If the parameter is set to BASIC it is still possible to manually set the parameters DB_CACHE_ADVICE, TIMED_STATISTICS and TIMED_OS_STATISTICS.

The parameter can also be set to ALL which causes the collection of additional statistics for timed operating system statistics and for the row source executions.
Oracle strongly recommends to collect the statistics at least at TYPICAL level.

STATISTICS_LEVEL can be set at system as well as at session level. If set on session level the following advisories or statistics get turned on or off, but their systemwide setting is not changed:
- Timed Statistics
- Timed OS Statistics
- Plan Excution Statistics

In order to find out which functionalities are controlled by STATISTICS_LEVEL you can query the dynamic performance view v$statistics_level which displays the status of the statistics/advisories controlled by STATISTICS_LEVEL:

SYS @10gR2 SQL > show parameter statistics_l
NAME TYPE VALUE
------------------------- ----------
statistics_level string TYPICAL

SYS @10gR2 SQL > DESC v$statistics_level
Name Null? Type
----------------------------------------- -------- ----------------------------
STATISTICS_NAME VARCHAR2(64)
DESCRIPTION VARCHAR2(4000)
SESSION_STATUS VARCHAR2(8)
SYSTEM_STATUS VARCHAR2(8)
ACTIVATION_LEVEL VARCHAR2(7)
STATISTICS_VIEW_NAME VARCHAR2(64)
SESSION_SETTABLE VARCHAR2(3)

SYS @10gR2 SQL > SELECT STATISTICS_NAME, ACTIVATION_LEVEL, SYSTEM_STATUS, STATISTICS_VIEW_NAME, SESSION_SETTABLE
2 FROM v$statistics_level;
STATISTICS_NAME ACTIVAT SYSTEM_S STATISTICS_VIEW_NAME SES
------------------------ ------------ ---------------------------------------------------- ---
Buffer Cache Advice TYPICAL ENABLED V$DB_CACHE_ADVICE NO
MTTR Advice TYPICAL ENABLED V$MTTR_TARGET_ADVICE NO
Timed Statistics TYPICAL ENABLED YES
Timed OS Statistics ALL DISABLED YES
Segment Level Statistics TYPICAL ENABLED V$SEGSTAT NO
PGA Advice TYPICAL ENABLED V$PGA_TARGET_ADVICE NO
Plan Execution Statistics ALL DISABLED V$SQL_PLAN_STATISTICS YES
Shared Pool Advice TYPICAL ENABLED V$SHARED_POOL_ADVICE NO
Modification Monitoring TYPICAL ENABLED NO
Longops Statistics TYPICAL ENABLED V$SESSION_LONGOPS NO
Bind Data Capture TYPICAL ENABLED V$SQL_BIND_CAPTURE NO
Ultrafast Latch Statistics TYPICAL ENABLED NO
Threshold-based Alerts TYPICAL ENABLED NO
Global Cache Statistics TYPICAL ENABLED NO
Active Session History TYPICAL ENABLED V$ACTIVE_SESSION_HISTORY NO
Undo Advisor, Alerts and FastRamp up TYPICAL ENABLED V$UNDOSTAT NO
16 rows selected