Automatic Optimizer Statistics Collection

?? 在Oracle 10g之前并沒有自動收集統計信息的機制，從Oracle 10g開始引入了自動收集統計信息的功能，這個功能在Oracle 10g中被稱為自動統計信息收集（Automatic Statistics Gathering）;

?? 在Oracle 11g中被稱為自動優化器統計信息收集（Automatic Optimizer Statistics Collection）。自動統計信息收集作業能夠每天收集普通對象和數據字典的統計信息，但不會收集X$系列表的內部對象統計信息。

??Oracle的初始化參數STATISTICS_LEVEL控制收集統計信息的級別，有三個參數值：
??1、 BASIC：收集基本的統計信息
??2、TYPICAL：收集大部分統計信息（數據庫的默認設置）
??3、 ALL：收集全部統計信息
??當使用Oracle自動收集統計信息時，必須要確保Oracle的參數STATISTICS_LEVEL的值為TYPICAL或者ALL。默認值為TYPICAL，該值可以確保數據庫自我管理功能所需求的所有主要統計信息的正確收集，及提供最好的綜合性能。這個默認值可以能勝任大多數的環境，并且Oracle不推薦去修改該值。

自動收集統計信息機制

在Oracle 10g中，如果表中變更的行數（字典表SYS.MON_MODS_ALL中記錄的INSERT+UPDATE+DELETE的總數）超過表的總行數（SYS.TAB中記錄的目標表總記錄數）的10%時或自上次自動統計信息收集作業完成之后目標表被執行過TRUNCATE操作，那么該表的統計信息就變為陳舊狀態，Oracle就會在指定時間段自動收集統計信息。

– 查看表中變更的行數（insert,update,deletes）

select * from SYS.MON_MODS_ALL$ WHERE OBJ#='468923';

–DBA_TAB_MODIFICATIONS視圖（基表為SYS.MON_MODS_ALL）記錄了從上次收集統計信息以來表中DML操作變化的數據量，包括執行INSERT、UPDATE和DELETE影響的行數(對于rollback操作的記錄，仍然更新到DBA_TAB_MODIFICATIONS表，Oracle不會在rollback之后再去更新USER_TAB_MODIFICATIONS表)，以及是否執行過TRUNCATE操作,(此表信息默認情況是15分鐘更新).另外，DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO可以將內存中的數據快速刷新到數據字典SYS.MON_MODS_ALL中。

《DBA_TAB_MODIFICATIONS表的刷新策略測試》http://www.sunline.cc/db/414692

smon會刷新統計結果到數據字典：DBA_TAB_MODIFICATIONS,oracle使用這些表的數據去判斷表的統計數據是否過期,如果當表的數據改變超過10%或有truncate 操作,oracle就認為該表的統計數據已經過期.

-- 示例
08:25:00 SQL> create table t1 (id number,name varchar(10));
Table created.
08:28:55 SQL> select * from DBA_TAB_MODIFICATIONS where table_name='T1';
no rows selected
08:29:21 SQL> insert into t1 values(1,'a');
1 row created.
08:29:36 SQL> commit;
-- 如果需要查看最新信息，可以手動更新該表的信息：
08:29:38 SQL> EXEC dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
08:29:42 SQL> select * from DBA_TAB_MODIFICATIONS where table_name='T1';
TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS        UPDATES    DELETES TIMESTAMP          TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------ --- -------------
SYS                            T1                                                                                                    1              0          0 11-JUN-22          NO              0

??在Oracle 10g中，這個10%（STALE_PERCENT）是無法修改的，如果表非常大，那么10%其實是非常多的數據，這就造成統計信息不準確。
?? 在Oracle 11g中，這個10%（STALE_PERCENT）是可以修改的，
??全局（DBMS_STATS.SET_GLOBAL_PREFS）;
??數據庫級別（DBMS_STATS.SET_DATABASE_PREFS）;
??用戶級別（DBMS_STATS.SET_SCHEMA_PREFS）;
??表級別（DBMS_STATS.SET_TABLE_PREFS）;
其中，數據庫級別和用戶級別都是調用表級別的存儲過程DBMS_STATS.SET_TABLE_PREFS來對表進行設置的。

表級別的設定如下所示：
1、修改為5%（范圍從1-100）：

EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',5);

EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',NULL);

SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'TB_NAME') FROM DUAL;

SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT') FROM DUAL;

《oracle 自動收集統計信息任務測試》http://www.sunline.cc/db/414757

維護窗口

?? Oracle 10g的自動統計信息收集功能沒有資源限制，但Oracle 11g的統計信息收集功能在資源管理上面限制了對系統資源使用，其對應的RESOURCE_PLAN的名稱為DEFAULT_MAINTENANCE_PLAN。用戶可以根據各自系統的業務場景來配置是否開啟自動收集統計信息，也可以調整窗口調度的開始時間、持續時間和資源組限制等。

Oracle 11g的默認的維護窗口配置覆蓋了下面的時間段：
l 每個工作日的晚上10點到第二天凌晨2點，持續4小時
l 每個周六上午6點到周日凌晨2點，周日上午6點到周一凌晨2點，都是持續20個小時
*注：周六與周日的執行頻率是：從6點開始每4小時執行維護一次，詳細見下圖執行日志：
（圖中開始執行時間調整為每天凌晨3點開始執行）

自動收集統計信息的運行日志查詢：

SELECT JRD.LOG_ID,
       JRD.JOB_NAME,
       N.JOB_CLASS,
       TO_CHAR(JRD.ACTUAL_START_DATE, 'YYYY-MM-DD HH24:MI:SS') ACTUAL_START_DATE,
       TO_CHAR(JRD.LOG_DATE, 'YYYY-MM-DD HH24:MI:SS') LOG_DATE,
       JRD.STATUS,
       JRD.ERROR#,
       JRD.RUN_DURATION,
       JRD.ADDITIONAL_INFO
  FROM DBA_SCHEDULER_JOB_LOG N, DBA_SCHEDULER_JOB_RUN_DETAILS JRD
 WHERE N.LOG_ID = JRD.LOG_ID
   AND N.JOB_NAME LIKE 'ORA$AT_OS_OPT_%' --11g
-- AND N.JOB_NAME = 'GATHER_STATS_JOB' --10g
 ORDER BY JRD.LOG_ID DESC;

*注：Oracle 11g自動統計信息收集是通過每天執行自動任務gather_stats_prog來實現的，它每天會自動生成ORA$AT_OS_OPT_xxx的作業，然后執行作業來收集統計信息，其本質也是執行了DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存儲過程。

查看維護窗口

SET line 9999  PAGESIZE 9999
 col WINDOW_NAME format a18
 col REPEAT_INTERVAL format a55
 col DURATION format a15
 col resource_plan format a25
 SELECT T1.WINDOW_NAME,
         T1.REPEAT_INTERVAL,
         T1.DURATION,
         T1.ENABLED,
         T1.RESOURCE_PLAN
    FROM DBA_SCHEDULER_WINDOWS T1, DBA_SCHEDULER_WINGROUP_MEMBERS T2
   WHERE T1.WINDOW_NAME = T2.WINDOW_NAME
     AND T2.WINDOW_GROUP_NAME IN
         ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');

禁用任務

BEGIN
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection',operation   => NULL,window_name => NULL);
end;

啟用任務：

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection',operation   => NULL,window_name => NULL);
end;

查看狀態：

SQL>  select CLIENT_NAME, STATUS   from DBA_AUTOTASK_CLIENT;
	CLIENT_NAME                         STATUS
	----------------------------------- --------
	auto optimizer stats collection     ENABLED
	auto space advisor                  ENABLED
	sql tuning advisor                  ENABLED

將自動任務執行時間改為早上3點：

exec dbms_scheduler.disable( name => 'MONDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=MON;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'MONDAY_WINDOW');

exec dbms_scheduler.disable( name => 'TUESDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'TUESDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=TUE;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'TUESDAY_WINDOW');

exec dbms_scheduler.disable( name => 'WEDNESDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'WEDNESDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=WED;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'WEDNESDAY_WINDOW');

exec dbms_scheduler.disable( name => 'THURSDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'THURSDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'THURSDAY_WINDOW');

exec dbms_scheduler.disable( name => 'FRIDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'FRIDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=FRI;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'FRIDAY_WINDOW');

exec dbms_scheduler.disable( name => 'SATURDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=SAT;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'SATURDAY_WINDOW');

exec dbms_scheduler.disable( name => 'SUNDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=SUN;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'SUNDAY_WINDOW');

解鎖并重新收集統計信息

--解鎖某個表的統計信息
EXEC dbms_stats.unlock_table_stats(ownname => '***',tabname => '***'); 
--重新收集某個表的統計信息
EXEC dbms_stats.gather_table_stats(ownname => '***',tabname => '***',cascade => true);
-- 數據庫中哪些表的統計信息被鎖定：
SELECT owner,table_name,stattype_locked FROM DBA_TAB_STATISTICS a WHERE a.stattype_locked IN ('ALL','DATA','CACHE')
-- 備注：stattype_locked為空代表統計信息未鎖定。

?????????????????????????文章推薦