statistics_level参数与oracle自动收集统计信息相关,取值有ALL、TYPICAL、 BASIC。可以从v$statistics_level视图看到不同取值对各种统计信息收集的影响,以及不同统计信息影响哪些统计结果视图。
STATISTICS_NAME | DESCRIPTION | SESSION_STATUS | SYSTEM_STATUS | ACTIVATION_LEVEL | STATISTICS_VIEW_NAME | SESSION_SETTABLE |
Buffer Cache Advice | Predicts the impact of different cache sizes on number of physical reads | ENABLED | ENABLED | TYPICAL | V$DB_CACHE_ADVICE | NO |
MTTR Advice | Predicts the impact of different MTTR settings on number of physical I/Os | ENABLED | ENABLED | TYPICAL | V$MTTR_TARGET_ADVICE | NO |
Timed Statistics | Enables gathering of timed statistics | ENABLED | ENABLED | TYPICAL | YES | |
Timed OS Statistics | Enables gathering of timed operating system statistics | DISABLED | DISABLED | ALL | YES | |
Segment Level Statistics | Enables gathering of segment access statistics | ENABLED | ENABLED | TYPICAL | V$SEGSTAT | NO |
PGA Advice | Predicts the impact of different values of pga_aggregate_target on the performance of memory intensive SQL operators | ENABLED | ENABLED | TYPICAL | V$PGA_TARGET_ADVICE | NO |
Plan Execution Statistics | Enables collection of plan execution statistics | DISABLED | DISABLED | ALL | V$SQL_PLAN_STATISTICS | YES |
Shared Pool Advice | Predicts the impact of different values of shared_pool_size on elapsed parse time saved | ENABLED | ENABLED | TYPICAL | V$SHARED_POOL_ADVICE | NO |
Modification Monitoring | Enables modification monitoring | ENABLED | ENABLED | TYPICAL | NO | |
Longops Statistics | Enables Longops Statistics | ENABLED | ENABLED | TYPICAL | V$SESSION_LONGOPS | NO |
Bind Data Capture | Enables capture of bind values used by SQL statements | ENABLED | ENABLED | TYPICAL | V$SQL_BIND_CAPTURE | NO |
Ultrafast Latch Statistics | Maintains statistics for ultrafast latches in the fast path | ENABLED | ENABLED | TYPICAL | NO | |
Threshold-based Alerts | Controls if Threshold-based Alerts should be enabled | ENABLED | ENABLED | TYPICAL | NO | |
Global Cache Statistics | RAC Buffer Cache statistics | ENABLED | ENABLED | TYPICAL | NO | |
Global Cache CPU Statistics | RAC Buffer Cache CPU statistics | DISABLED | DISABLED | ALL | NO | |
Active Session History | Monitors active session activity using MMNL | ENABLED | ENABLED | TYPICAL | V$ACTIVE_SESSION_HISTORY | NO |
Undo Advisor, Alerts and Fast Ramp up | Transaction layer manageability features | ENABLED | ENABLED | TYPICAL | V$UNDOSTAT | NO |
Streams Pool Advice | Predicts impact on Streams perfomance of different Streams pool sizes | ENABLED | ENABLED | TYPICAL | V$STREAMS_POOL_ADVICE | NO |
Time Model Events | Enables Statics collection for time events | ENABLED | ENABLED | TYPICAL | V$SESS_TIME_MODEL | YES |
Plan Execution Sampling | Enables plan lines sampling | ENABLED | ENABLED | TYPICAL | V$ACTIVE_SESSION_HISTORY | YES |
Automated Maintenance Tasks | Controls if Automated Maintenance should be enabled | ENABLED | ENABLED | TYPICAL | NO | |
SQL Monitoring | Controls if SQL Monitoring should be enabled | ENABLED | ENABLED | TYPICAL | V$SQL_MONITORING | YES |
Adaptive Thresholds Enabled | Controls if Adaptive Thresholds should be enabled | ENABLED | ENABLED | TYPICAL | NO | |
V$IOSTAT_* statistics | Controls if I/O stats in v$iostat_ should be enabled | ENABLED | ENABLED | TYPICAL | NO |
常见的修改statistics_level值为BASIC时,会出现ORA-00830错误。此错误表示当前instance运行在asmm模式下,需要关闭sga auto tune后才能修改。这里要提下,设置sga_target为非0,表示启用sga auto tune;设置sga_target为0,表示关闭sga auto tune。但是,sga_target无论如何都不会超越sga_max_size设定值。这也是sga_max_size的唯一作用了。
官方解释如下:
[oracle@localhost admin]$ oerr ora 00830
00830, 00000, "cannot set statistics_level to BASIC with auto-tune SGA enabled"
// *Cause: The user attempted to set statistics_level to BASIC with auto-tune
// SGA enabled which cannot be done because auto-tune SGA cannot work
// with statistics_level set to BASIC.
// *Action: Disable auto-tune SGA and try setting the statistics_level to
// BASIC again.
[oracle@localhost admin]$
操作下:
SYS>show parameter statistics
NAME TYPE VALUE------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
SYS>alter system set statistics_level=BASIC;
alter system set statistics_level=BASIC
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled
SYS>show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 392M
SYS>alter system set sga_target=0;
System altered.
SYS>alter system set statistics_level=basic;
System altered.
再来看看v$statistics_level视图,可以看到受此影响,索引的自动统计信息收集都关掉了。
STATISTICS_NAME | DESCRIPTION | SESSION_STATUS | SYSTEM_STATUS | ACTIVATION_LEVEL | STATISTICS_VIEW_NAME | SESSION_SETTABLE |
Buffer Cache Advice | Predicts the impact of different cache sizes on number of physical reads | DISABLED | DISABLED | TYPICAL | V$DB_CACHE_ADVICE | NO |
MTTR Advice | Predicts the impact of different MTTR settings on number of physical I/Os | DISABLED | DISABLED | TYPICAL | V$MTTR_TARGET_ADVICE | NO |
Timed Statistics | Enables gathering of timed statistics | DISABLED | DISABLED | TYPICAL | YES | |
Timed OS Statistics | Enables gathering of timed operating system statistics | DISABLED | DISABLED | ALL | YES | |
Segment Level Statistics | Enables gathering of segment access statistics | DISABLED | DISABLED | TYPICAL | V$SEGSTAT | NO |
PGA Advice | Predicts the impact of different values of pga_aggregate_target on the performance of memory intensive SQL operators | DISABLED | DISABLED | TYPICAL | V$PGA_TARGET_ADVICE | NO |
Plan Execution Statistics | Enables collection of plan execution statistics | DISABLED | DISABLED | ALL | V$SQL_PLAN_STATISTICS | YES |
Shared Pool Advice | Predicts the impact of different values of shared_pool_size on elapsed parse time saved | DISABLED | DISABLED | TYPICAL | V$SHARED_POOL_ADVICE | NO |
Modification Monitoring | Enables modification monitoring | DISABLED | DISABLED | TYPICAL | NO | |
Longops Statistics | Enables Longops Statistics | DISABLED | DISABLED | TYPICAL | V$SESSION_LONGOPS | NO |
Bind Data Capture | Enables capture of bind values used by SQL statements | DISABLED | DISABLED | TYPICAL | V$SQL_BIND_CAPTURE | NO |
Ultrafast Latch Statistics | Maintains statistics for ultrafast latches in the fast path | DISABLED | DISABLED | TYPICAL | NO | |
Threshold-based Alerts | Controls if Threshold-based Alerts should be enabled | DISABLED | DISABLED | TYPICAL | NO | |
Global Cache Statistics | RAC Buffer Cache statistics | DISABLED | DISABLED | TYPICAL | NO | |
Global Cache CPU Statistics | RAC Buffer Cache CPU statistics | DISABLED | DISABLED | ALL | NO | |
Active Session History | Monitors active session activity using MMNL | DISABLED | DISABLED | TYPICAL | V$ACTIVE_SESSION_HISTORY | NO |
Undo Advisor, Alerts and Fast Ramp up | Transaction layer manageability features | DISABLED | DISABLED | TYPICAL | V$UNDOSTAT | NO |
Streams Pool Advice | Predicts impact on Streams perfomance of different Streams pool sizes | DISABLED | DISABLED | TYPICAL | V$STREAMS_POOL_ADVICE | NO |
Time Model Events | Enables Statics collection for time events | DISABLED | DISABLED | TYPICAL | V$SESS_TIME_MODEL | YES |
Plan Execution Sampling | Enables plan lines sampling | DISABLED | DISABLED | TYPICAL | V$ACTIVE_SESSION_HISTORY | YES |
Automated Maintenance Tasks | Controls if Automated Maintenance should be enabled | DISABLED | DISABLED | TYPICAL | NO | |
SQL Monitoring | Controls if SQL Monitoring should be enabled | DISABLED | DISABLED | TYPICAL | V$SQL_MONITORING | YES |
Adaptive Thresholds Enabled | Controls if Adaptive Thresholds should be enabled | DISABLED | DISABLED | TYPICAL | NO | |
V$IOSTAT_* statistics | Controls if I/O stats in v$iostat_ should be enabled | DISABLED | DISABLED | TYPICAL | NO |
但是,这里要注意,db_cache_advice 参数和timed_statistics参数单独控制了Buffer Cache Advice和Timed Statistics的自动统计信息收集。如下:
SYS>show parameter db_cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice stringOFF
db_cache_size big integer 208M
SYS>alter system set db_cache_advice=on;
System altered.
SYS>show parameter statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level stringBASIC
timed_os_statistics integer0
timed_statistics booleanFALSE
SYS>alter system set timed_statistics=true;
System altered.
select *
from v$statistics_level a
where a.STATISTICS_NAME in ('Buffer Cache Advice', 'Timed Statistics');
STATISTICS_NAME | DESCRIPTION | SESSION_STATUS | SYSTEM_STATUS | ACTIVATION_LEVEL | STATISTICS_VIEW_NAME | SESSION_SETTABLE |
Buffer Cache Advice | Predicts the impact of different cache sizes on number of physical reads | ENABLED | ENABLED | TYPICAL | V$DB_CACHE_ADVICE | NO |
Timed Statistics | Enables gathering of timed statistics | ENABLED | ENABLED | TYPICAL | YES |