【DB筆試面試629】在Oracle中，自動收集統計資訊的機制有哪些？10g和11g在自動收集統計資訊方面有哪些區別？

• 2019 年 10 月 10 日
• 筆記

題目部分

在Oracle中，自動收集統計資訊的機制有哪些？10g和11g在自動收集統計資訊方面有哪些區別？

♣

答案部分

對於Oracle而言，準確的統計資訊對於CBO來說是非常重要的，因為這直接關係到CBO能否對目標SQL生成合適的、正確的執行計劃。所以DBA應該使自己維護的資料庫中的統計資訊盡量準確。在Oracle 10g之前並沒有自動收集統計資訊的機制，從Oracle 10g開始引入了自動收集統計資訊的功能，這個功能在Oracle 10g中被稱為自動統計資訊收集（Automatic Statistics Gathering），在Oracle 11g中被稱為自動優化器統計資訊收集（Automatic Optimizer Statistics Collection）。自動統計資訊收集作業能夠每天收集普通對象和數據字典的統計資訊，但不會收集X$系列表的內部對象統計資訊。Oracle的初始化參數STATISTICS_LEVEL控制收集統計資訊的級別，有三個參數值：

l BASIC：收集基本的統計資訊

l TYPICAL：收集大部分統計資訊（資料庫的默認設置）

l ALL：收集全部統計資訊

當使用Oracle自動收集統計資訊時，必須要確保Oracle的參數STATISTICS_LEVEL的值為TYPICAL或者ALL。默認值為TYPICAL，該值可以確保資料庫自我管理功能所需求的所有主要統計資訊的正確收集，及提供最好的綜合性能。這個默認值可以能勝任大多數的環境，並且Oracle不推薦去修改該值。

DBA可以根據Oracle提供的腳本$ORACLE_HOME/rdbms/admin/catmwin.sql查看統計資訊收集作業的整體搭建流程。有興趣的讀者可以研究下此腳本的內容。

Oracle 10g和11g的自動統計資訊收集機制有所不同，詳見下表：

自動收集統計資訊的運行日誌查詢：

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中對統計資訊自動收集的功能進行了加強。在Oracle 10g中，如果表中變更的行數（字典表SYS.MON_MODS_ALL$中記錄的INSERT+UPDATE+DELETE的總數）超過表的總行數（SYS.TAB$中記錄的目標表總記錄數）的10%時或自上次自動統計資訊收集作業完成之後目標表被執行過TRUNCATE操作，那麼該表的統計資訊就變為陳舊狀態，Oracle就會在指定時間段自動收集統計資訊。在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來對錶進行設置的。

表級別的設定如下所示：

l 修改為5%（範圍從1-100）：EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',5);

l 恢復為10%：EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',NULL);

l 查詢表百分比：SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'TB_NAME') FROM DUAL;

l 查詢全局百分比：SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT') FROM DUAL;

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

SQL> SET line 9999  PAGESIZE 9999  SQL> col WINDOW_NAME format a18  SQL> col REPEAT_INTERVAL format a55  SQL> col DURATION format a15  SQL> col resource_plan format a25  SQL> SELECT T1.WINDOW_NAME,    2         T1.REPEAT_INTERVAL,    3         T1.DURATION,    4         T1.ENABLED,    5         T1.RESOURCE_PLAN    6    FROM DBA_SCHEDULER_WINDOWS T1, DBA_SCHEDULER_WINGROUP_MEMBERS T2    7   WHERE T1.WINDOW_NAME = T2.WINDOW_NAME    8     AND T2.WINDOW_GROUP_NAME IN    9         ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');  WINDOW_NAME        REPEAT_INTERVAL                                         DURATION        ENABL RESOURCE_PLAN  ------------------ ------------------------------------------------------- --------------- ----- -------------------------  SUNDAY_WINDOW      freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0    +000 20:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN  FRIDAY_WINDOW      freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN  WEDNESDAY_WINDOW   freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN  TUESDAY_WINDOW     freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN  SATURDAY_WINDOW    freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0    +000 20:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN  THURSDAY_WINDOW    freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN  MONDAY_WINDOW      freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN  

Oracle 11g的默認的維護窗口配置覆蓋了下面的時間段：

l 每個工作日的晚上10點到第二天凌晨2點，持續4小時

l 每個周六上午6點到周日凌晨2點，周日上午6點到周一凌晨2點，都是持續20個小時

晚上10點到凌晨2點，這個時段往往是業務的高峰期，給本已緊張的系統帶來更大的負擔。所以，有必要把自動執行的時間改到空閑的時段。這個要根據各自的業務進行判斷，如，系統可調整為：周一到周五，凌晨1點開始，持續5個小時；周六、周日，凌晨1點開始，持續10個小時。

用SYS用戶執行如下語句即可：

begin    sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0');    sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');  end;  /  begin    sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0');    sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');  end;  /  begin    sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0');    sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');  end;  /  begin    sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0');    sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');  end;  /  begin    sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0');    sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');  end;  /  begin    sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0');    sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 10:00:00');  end;  /  begin    sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0');    sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 10:00:00');  end;  /  

DBA_TAB_MODIFICATIONS視圖（基表為SYS.MON_MODS_ALL$）記錄了從上次收集統計資訊以來表中DML操作變化的數據量，包括執行INSERT、UPDATE和DELETE影響的行數，以及是否執行過TRUNCATE操作。另外，DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO可以將記憶體中的數據快速刷新到數據字典SYS.MON_MODS_ALL$中。

示例如下：

SYS@orclasm > CREATE TABLE T_MON_20170602_LHR AS SELECT * FROM DBA_OBJECTS;  Table created.  SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';  no rows selected  SYS@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_MON_20170602_LHR');  PL/SQL procedure successfully completed.  SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';  no rows selected  SYS@orclasm > DELETE FROM T_MON_20170602_LHR WHERE ROWNUM <=10000;  10000 rows deleted.  SYS@orclasm > COMMIT;  Commit complete.  SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';  no rows selected  SYS@orclasm > EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;  PL/SQL procedure successfully completed.  SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP  FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';  TABLE_NAME                        INSERTS    UPDATES    DELETES TIMESTAMP  ------------------------------ ---------- ---------- ---------- -------------------  T_MON_20170602_LHR                      0          0      10000 2017-06-02 19:26:03  

本文選自《Oracle程式設計師面試筆試寶典》，作者：小麥苗