The STATISTICS_LEVEL is the initial database parameter used to enable/disable advisories statistics collections.
There here 3 levels for the STATISTICS_LEVEL parameter :
- BASIC : Desactivates all the advisory statistics collections
- TYPICAL : Activates some advisory statistics collections
- ALL : All advisory are activated
SQL>alter system set statistics_level=basic;
System altered.
SQL>SELECT statistics_name,
session_status,
system_status,
activation_level,
session_settable
FROM v$statistics_level
order by statistics_name;
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Active Session History DISABLED DISABLED TYPICAL NO
Adaptive Thresholds Enabled DISABLED DISABLED TYPICAL NO
Automated Maintenance Tasks DISABLED DISABLED TYPICAL NO
Bind Data Capture DISABLED DISABLED TYPICAL NO
Buffer Cache Advice DISABLED DISABLED TYPICAL NO
Global Cache Statistics DISABLED DISABLED TYPICAL NO
Longops Statistics DISABLED DISABLED TYPICAL NO
MTTR Advice DISABLED DISABLED TYPICAL NO
Modification Monitoring DISABLED DISABLED TYPICAL NO
PGA Advice DISABLED DISABLED TYPICAL NO
Plan Execution Sampling DISABLED DISABLED TYPICAL YES
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Plan Execution Statistics DISABLED DISABLED ALL YES
SQL Monitoring DISABLED DISABLED TYPICAL YES
Segment Level Statistics DISABLED DISABLED TYPICAL NO
Shared Pool Advice DISABLED DISABLED TYPICAL NO
Streams Pool Advice DISABLED DISABLED TYPICAL NO
Threshold-based Alerts DISABLED DISABLED TYPICAL NO
Time Model Events DISABLED DISABLED TYPICAL YES
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES
Ultrafast Latch Statistics DISABLED DISABLED TYPICAL NO
Undo Advisor, Alerts and Fast DISABLED DISABLED TYPICAL NO
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
V$IOSTAT_* statistics DISABLED DISABLED TYPICAL NO
23 rows selected.
SQL>alter system set statistics_level=typical;
System altered.
SQL>SELECT statistics_name,
session_status,
system_status,
activation_level,
session_settable
FROM v$statistics_level
order by statistics_name;
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Active Session History DISABLED DISABLED TYPICAL NO
Adaptive Thresholds Enabled DISABLED DISABLED TYPICAL NO
Automated Maintenance Tasks DISABLED DISABLED TYPICAL NO
Bind Data Capture DISABLED DISABLED TYPICAL NO
Buffer Cache Advice DISABLED DISABLED TYPICAL NO
Global Cache Statistics DISABLED DISABLED TYPICAL NO
Longops Statistics DISABLED DISABLED TYPICAL NO
MTTR Advice DISABLED DISABLED TYPICAL NO
Modification Monitoring DISABLED DISABLED TYPICAL NO
PGA Advice DISABLED DISABLED TYPICAL NO
Plan Execution Sampling ENABLED DISABLED TYPICAL YES
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Plan Execution Statistics DISABLED DISABLED ALL YES
SQL Monitoring ENABLED DISABLED TYPICAL YES
Segment Level Statistics DISABLED DISABLED TYPICAL NO
Shared Pool Advice DISABLED DISABLED TYPICAL NO
Streams Pool Advice DISABLED DISABLED TYPICAL NO
Threshold-based Alerts DISABLED DISABLED TYPICAL NO
Time Model Events ENABLED DISABLED TYPICAL YES
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES
Ultrafast Latch Statistics DISABLED DISABLED TYPICAL NO
Undo Advisor, Alerts and Fast DISABLED DISABLED TYPICAL NO
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
V$IOSTAT_* statistics DISABLED DISABLED TYPICAL NO
23 rows selected.
SQL>alter system set statistics_level=all;
System altered.
SQL>SELECT statistics_name,
session_status,
system_status,
activation_level,
session_settable
FROM v$statistics_level
order by statistics_name;
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Active Session History DISABLED DISABLED TYPICAL NO
Adaptive Thresholds Enabled DISABLED DISABLED TYPICAL NO
Automated Maintenance Tasks DISABLED DISABLED TYPICAL NO
Bind Data Capture DISABLED DISABLED TYPICAL NO
Buffer Cache Advice DISABLED DISABLED TYPICAL NO
Global Cache Statistics DISABLED DISABLED TYPICAL NO
Longops Statistics DISABLED DISABLED TYPICAL NO
MTTR Advice DISABLED DISABLED TYPICAL NO
Modification Monitoring DISABLED DISABLED TYPICAL NO
PGA Advice DISABLED DISABLED TYPICAL NO
Plan Execution Sampling ENABLED DISABLED TYPICAL YES
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
Plan Execution Statistics ENABLED DISABLED ALL YES
SQL Monitoring ENABLED DISABLED TYPICAL YES
Segment Level Statistics DISABLED DISABLED TYPICAL NO
Shared Pool Advice DISABLED DISABLED TYPICAL NO
Streams Pool Advice DISABLED DISABLED TYPICAL NO
Threshold-based Alerts DISABLED DISABLED TYPICAL NO
Time Model Events ENABLED DISABLED TYPICAL YES
Timed OS Statistics ENABLED DISABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES
Ultrafast Latch Statistics DISABLED DISABLED TYPICAL NO
Undo Advisor, Alerts and Fast DISABLED DISABLED TYPICAL NO
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_SETTABLE
------------------------------ --------------- --------------- ---------------- ----------------
V$IOSTAT_* statistics DISABLED DISABLED TYPICAL NO
23 rows selected.
The gathering of statistics is performed each day and stored in a repository for 30 days.
Gathering statistics
Use the DBMS_STATS to retrieve statistics for the entire database, a schema or a specific table (DBMS_STATS.GATHER_*_STATS()).
For the database : DBMS_STATS.GATHER_DATABASE_STATS()
For schema : DBMS_STATS.GATHER_SCHEMA_STATS()
For a table : DBMS_STATS.GATHER_TABLE_STATS()
Use DBMS_STATS.GATHER_*_STATS() when 10% of the database, schema or table changes during the last 24 hours.
Removing statistics from the repository
Use the DBMS_STATS.DELETE_*_STATS() procedures.
Statistics preferences
The DBA can assign default or prefered parameters values for users that invoke the DBMS_STATS package.
The DBMS_STATS.SET_*_PREFS() procedures of the package allows the DBA to set preferences for the following parameters :
CASCADE
DEGREE
ESTIMATE_PERCENT
NO_INVALIDATE
METHOD_OPT
GRANULARITY
INCREMENTAL
PUBLISH
STALE_PERCENT
Preferences can be deleted using DBMS_STATS.DELETE_*_PREFS()
It is all possible to reset values to the initial setup using DBMS_STATS.RESET_PARAM_PREFS()
Remark : If preferences have been setted at a table level, setting preferences at the schema or database level overwritting preferences setted previously at the table level.
View statistics parameters preferences
Object prefrences can be retrieve from the DBA_TAB_STAT_PREFS table.
For preferences setted are a global level, use the DBMS_STATS.GET_PREFS() function of the DBMS_STATS package.
