Oracle Database - Statistics - STATISTICS_LEVEL parameter

> Database > Oracle Database > Oracle Database - Optimizer Statistics

1 - About

The STATISTICS_LEVEL parameter controls the levels of statistics gathered:

  • BASIC: will disables the collection of many of the important statistics
  • TYPICAL (Default): will ensure collection of all major statistics required
  • ALL: will add timed operating system statistics and plan execution statistics to the TYPICAL level.

3 - Management

3.1 - Query

You can query it for instance with the show parameter command:

show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
statistics_level                     string      TYPICAL
Advertising

3.2 - Level

See the view V$STATISTICS_LEVEL for more details.

ACTIVAT STATISTICS_NAME SESSION_ STATISTICS_VIEW_NAME DESCRIPTION
TYPICAL Active Session History ENABLED V$ACTIVE_SESSION_HISTORY Monitors active session activity using M
Adaptive Thresholds Enabled ENABLED Controls if Adaptive Thresholds should b
Automated Maintenance Tasks ENABLED Controls if Automated Maintenance should
Automatic DBOP Monitoring ENABLED V$SQL_MONITOR Controls if automatic DBOP Monitoring sh
Bind Data Capture ENABLED V$SQL_BIND_CAPTURE Enables capture of bind values used by S
Buffer Cache Advice ENABLED V$DB_CACHE_ADVICE Predicts the impact of different cache s
Global Cache Statistics ENABLED RAC Buffer Cache statistics
Longops Statistics ENABLED V$SESSION_LONGOPS Enables Longops Statistics
MTTR Advice ENABLED V$MTTR_TARGET_ADVICE Predicts the impact of different MTTR se
Modification Monitoring ENABLED Enables modification monitoring
OLAP row load time precision ENABLED Sets precision of olap row load time sta
PGA Advice ENABLED V$PGA_TARGET_ADVICE Predicts the impact of different values
Plan Execution Sampling ENABLED V$ACTIVE_SESSION_HISTORY Enables plan lines sampling
SQL Monitoring ENABLED V$SQL_MONITORING Controls if SQL Monitoring should be ena
Segment Level Statistics ENABLED V$SEGSTAT Enables gathering of segment access stat
Shared Pool Advice ENABLED V$SHARED_POOL_ADVICE Predicts the impact of different values
Streams Pool Advice ENABLED V$STREAMS_POOL_ADVICE Predicts impact on Streams perfomance of
Threshold-based Alerts ENABLED Controls if Threshold-based Alerts shoul
Time Model Events ENABLED V$SESS_TIME_MODEL Enables Statics collection for time even
Timed Statistics ENABLED Enables gathering of timed statistics
Ultrafast Latch Statistics ENABLED Maintains statistics for ultrafast latch
Undo Advisor, Alerts and Fast Ramp up ENABLED V$UNDOSTAT Transaction layer manageability features
V$IOSTAT_* statistics ENABLED Controls if I/O stats in v$iostat_ shoul
ALL Global Cache CPU Statistics DISABLED RAC Buffer Cache CPU statistics
Plan Execution Statistics DISABLED V$SQL_PLAN_STATISTICS Enables collection of plan execution sta
Timed OS Statistics DISABLED Enables gathering of timed operating sys
Advertising
db/oracle/statistics/level.txt · Last modified: 2017/09/06 19:30 by gerardnico