8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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...