[统计信息系列7]Oracle11g的⾃动统计信息收集
（⼀）统计信息收集概述
在Oracle 11g中，默认有3个⾃动任务，分别是：⾃动统计信息收集、SQL调优顾问、段空间调整顾问，查看⽅法如下：
SQL> SELECT CLIENT_NAME,TASK_NAME,OPERATION_NAME,STATUS FROM dba_autotask_task;
CLIENT_NAME TASK_NAME OPERATION_NAME STATUS
-------------------------------- -------------------------- -------------------------- --------
sql tuning advisor AUTO_SQL_TUNING_PROG automatic sql tuning task ENABLED
auto optimizer stats collection gather_stats_prog auto optimizer stats job ENABLED
auto space advisor auto_space_advisor_prog auto space advisor job ENABLED
灰⾊背景⾏代表⾃动统计信息收集，使⽤的任务为gather_stats_prog。gather_stats_prog调⽤了
DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程
SQL> SELECT PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION FROM dba_scheduler_programs WHERE PROGRAM_NAME =
'GATHER_STATS_PROG';
PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION
------------------------------ ---------------- --------------------------------------------------------------------------------
GATHER_STATS_PROG STORED_PROCEDURE dbms_stats.gather_databa_stats_job_proc
在Oracle 11g中，⼀共配置了7个⾃动维护窗⼝，每天⼀个窗⼝
SQL> SELECT WINDOW_NAME,AUTOTASK_STATUS FROM dba_autotask_window_clients ;
WINDOW_NAME AUTOTASK_STATUS
------------------------------ ---------------
MONDAY_WINDOW ENABLED
TUESDAY_WINDOW ENABLED
蓝浆果
WEDNESDAY_WINDOW ENABLED
THURSDAY_WINDOW ENABLED
FRIDAY_WINDOW ENABLED
SATURDAY_WINDOW ENABLED
SUNDAY_WINDOW ENABLED
每个窗⼝的运⾏时间如下：
SQL> SELECT a.WINDOW_NAME,a.REPEAT_INTERVAL,a.duration FROM dba_scheduler_windows a WHERE ENABLED = 'TRUE';
魔方如何复原WINDOW_NAME REPEAT_INTERVAL DURATION
------------------ ------------------------------------------------------- -----------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bycond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bycond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bycond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bycond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bycond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bycond=0 +000 20:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bycond=0 +000 20:00:00
可以看到，从周⼀到周五，窗⼝运⾏时间为晚上22点开始，最多运⾏4个⼩时，周六周⽇从早上6点开始，最多运⾏20个⼩时。
在窗⼝任务启动时，⾃动任务GATHER_STATS_PROG每次运⾏时会先⽣成ORA$AT_OS_OPT_xxx的作业，然后再执⾏这个作业。
SQL> SELECT a.JOB_NAME,a.ACTUAL_START_DATE,a.RUN_DURATION,a.STATUS
2 FROM dba_scheduler_job_run_details a
3 WHERE a.JOB_NAME LIKE 'ORA$AT_OS_OPT%';
JOB_NAME ACTUAL_START_DATE RUN_DURATION STATUS
--------------------- ---------------------------------- ---------------- ------------
ORA$AT_OS_OPT_SY_1 25-MAY-20 10.00.02.042065 PM PRC +000 00:01:24 SUCCEEDED
ORA$AT_OS_OPT_SY_21 30-MAY-20 09.25.57.005710 AM PRC +000 00:00:37 SUCCEEDED母亲的简谱
ORA$AT_OS_OPT_SY_41 30-MAY-20 01.26.30.842460 PM PRC +000 00:00:43 SUCCEEDED
ORA$AT_OS_OPT_SY_61 30-MAY-20 05.26.41.292037 PM PRC +000 00:00:31 SUCCEEDED
总结：Oracle 11g⾃动统计信息收集是通过每天执⾏⾃动任务gather_stats_prog来实现的，它每天会⾃动⽣成ORA$AT_OS_OPT_xxx 的作业，然后执⾏作业来收集统计信息，其本质也是执⾏了DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程。
（⼆）统计信息收集策略
每次⾃动收集统计信息，并不是对所有表都进⾏收集，Oracle只对那些已经统计信息失效的对象进⾏收集，那么Oracle如何判断哪些对象的统计信息失效了呢？
在Oracle 11g中，如果参数STATISTICS_LEVEL的值为TYPICAL(默认)或者ALL，则DBA_TAB_MODIFICATIONS会记录⾃上次⾃动统计信息收集完成之后对⽬标表的inrt、update、delete的操作影响⾏数，并且还会记录⾃从上次⾃动收集统计信息之后是否发⽣过truncate。需要注意的是DBA_TAB_MODIFICATIONS并不会实时更新，如果需要查看最新信息，可以⼿动更新该表的信息：
EXEC dbms_stats.flush_databa_monitoring_info();
Oracle收集失效的统计信息的策略：⾃上次⾃动统计信息收集作业完成之后，如果DBA_TAB_MODIFICATIONS中记录的
126youxiangINSERT+UPDATE+DELETE所影响的⾏记录之和超过了DBA_TABLES中⽬标表记录数的10%，或者是⾃上次统计信息收集完成之后⽬标表执⾏过truncate操作，那么Oracle会认为⽬标表的统计信息已经失效，⾃动统计信息收集作业就会对⽬标表重新收集统计信息。
（三）禁⽤/启⽤⾃动统计信息收集
在某些情况下，需要禁⽤⾃动统计信息的收集，可以使⽤以下3种⽅法，每种⽅法禁⽤范围不同。
（3.1）使⽤以下⽅法可以禁⽤/启⽤⾃动统计信息收集
SQL> EXEC dbms_auto_task_admin.disable(client_name=> 'auto optimizer stats collection',operation=> NULL,window_name=> NULL);
确认是否已经关闭：
SELECT WINDOW_NAME,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR FROM歌手孙浩
DBA_AUTOTASK_WINDOW_CLIENTS
如果要启⽤，可以使⽤如下⽅法重新打开⾃动统计信息收集：
SQL> EXEC dbms_auto_able(client_name=> 'auto optimizer stats collection',operation=> NULL,window_name=> NULL);
再次查询，确认已经开启：
勇气是什么
（3.2）使⽤DBMS_SCHEDULER.DISABLE可以禁⽤维护窗⼝，从⽽禁⽤统计信息收集
例⼦1：禁掉周⼀的⾃动维护作业，包括统计信息收集、段顾问、sql调优顾问
EXEC dbms_scheduler.disable(NAME=> 'SYS.MONDAY_WINDOW',FORCE=> TRUE)
结果如下：
SELECT a.WINDOW_abled FROM dba_scheduler_windows a where a.window_name = 'MONDAY_WINDOW';
启⽤周⼀的⾃动维护作业，包括统计信息收集、段顾问、sql调优顾问
EXEC able(NAME=>'SYS.MONDAY_WINDOW');
（3.3）使⽤DBMS_SCHEDULER.DISABLE可以禁⽤维护窗⼝中的统计信息收集
例⼦2：禁掉周⼆的⾃动统计信息收集，段顾问、sql调优顾问保持开启
EXEC dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>'TUESDAY_WINDOW');
查询结果如下：
SELECT WINDOW_NAME,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR FROM
DBA_AUTOTASK_WINDOW_CLIENTS ;
再次开启：
EXEC dbms_auto_able(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>'TUESDAY_WINDOW');
（四）调整⾃动统计信息收集
默认的统计信息如下，从周⼀到周五，窗⼝运⾏时间为晚上22点开始，最多运⾏4个⼩时，周六周⽇从早上6点开始，最多运⾏20个⼩时。
乐脉丸我们可以对其进⾏修改，修改的⽅法如下：
1.先禁⽤窗⼝：DBMS_SCHEDULER.DISABLE()
2.修改窗⼝的属性：DBMS_SCHEDULER.SET_ATTRIBUTE()
3.启⽤窗⼝：DBMS_SCHEDULER.ENABLE()
例⼦1：将周⼆的起始执⾏时间调整到23点
-- 1.禁⽤窗⼝
EXEC dbms_scheduler.disable(NAME=> 'SYS.MONDAY_WINDOW',FORCE=> TRUE)
-- 2.修改启动时间为23点
EXEC dbms_scheduler.t_attribute(name => 'SYS.MONDAY_WINDOW',attribute => 'REPEAT_INTERVAL',value =>
'freq=daily;byday=TUE;byhour=23;byminute=0; bycond=0');
-- 3.启⽤窗⼝
EXEC able(NAME=>'SYS.MONDAY_WINDOW');
查看结果：
（五）列的直⽅图统计信息收集⽅式修改
在Oracle 11g中，Oracle默认直⽅图的统计信息收集⽅式是AUTO，即Oracle会根据负载以及列的使⽤情况来确定对哪些列收集直⽅图信息，为了更好地利⽤直⽅图统计信息的同时保持执⾏计划的稳定，推荐对直⽅图统计信息的收集策略是对已经存在直⽅图的列才收集直⽅图统计信息，即以REPEAT⽅式收集。
查看默认的直⽅图收集策略：
SQL> SELECT _prefs('METHOD_OPT') FROM dual;
DBMS_STATS.GET_PREFS('METHOD_O
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
修改直⽅图策略：
SQL> EXEC dbms_stats.t_global_prefs(pname => 'METHOD_OPT',pvalue => 'FOR ALL COLUMNS SIZE REPEAT');
PL/SQL procedure successfully completed
查看修改后的默认的直⽅图收集策略：
SQL> SELECT _prefs('METHOD_OPT') FROM dual;
DBMS_STATS.GET_PREFS('METHOD_O
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE REPEAT
（六）统计信息阈值修改
在Oracle 11g中，默认统计信息的收集阈值为10%，即10%的⾏数据发⽣变化或者执⾏了truncate，才会再次收集统计信息。我们可以使⽤下⾯的⽅法针对单个表修改阈值。
例⼦1：修改test01表的统计信息收集阈值为5%。
查看初始的阈值：
SQL> SELECT _prefs(pname => 'STALE_PERCENT',ownname => 'LIJIAMAN',tabname => 'TEST01') FROM dual;
DBMS_STATS.GET_PREFS(PNAME=>'S
--------------------------------------------------------------------------------
xxx大屁股
10
修改阈值为5：
SQL> EXEC dbms_stats.t_table_prefs(ownname => 'LIJIAMAN',tabname => 'TEST01',pname =>
'STALE_PERCENT',pvalue => 5);
PL/SQL procedure successfully completed
确认修改后的阈值：
SQL> SELECT _prefs(pname => 'STALE_PERCENT',ownname => 'LIJIAMAN',tabname => 'TEST01') FROM dual;
DBMS_STATS.GET_PREFS(PNAME=>'S
--------------------------------------------------------------------------------
5
需要注意的是：当阈值为0时，不管数据如何变化，每天都会⾃动收集统计信息。
【完】
