statistics_level相关

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