周五在一家客戶的調優會議中討論了一個由于統計信息陳舊導致SQL執行計劃偏差的問題，這是一個10g的庫并且禁用了自動收集統計信息的定時作業GATHER_STATS_JOB；當問及應用程序開發商為何要禁用自動統計信息收集時，開發商的一位工程師說因為該庫的數據量較大，考慮到該JOB每天都會將所有大表統計一遍可能要花費大量時間所以予以停用。 這里就存在一個問題，GATHER_STATS_JOB自動統計作業是每次打開都會將數據庫中所有的對象的統計信息都收集一遍嗎？細心的朋友一定會發覺實際上該JOB的運行時間是時長時短的，同時絕對不是如這位開發工程師所說的會每天都重復統計所有表。 10g的官方文檔中對該GATHER_STATS_JOB描述為"The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows)." 以上這段描述還是比較清晰的，MAINTENANCE_WINDOW_GROUP維護窗口組中的工作日窗口(WEEKNIGHT_WINDOW,周一到周五)會在每個工作日的22:00啟動并于第二天的6:00結束，在周末該維護窗口組中的周末窗口(WEEKEND_WINDOW)從周六Sat的0點開始并持續48小時（你不難發現這2個窗口在周六0點到6點之間存在overlay，實際的情況是WEEKEND_WINDOW窗口是從周六的0點整到周一的0點，具體可以觀察dba_scheduler_windows視圖的NEXT_START_DATE列，這里不再贅述)。在數據庫一直打開的情況下，GATHER_STATS_JOB會伴隨維護窗口一起被啟動，默認情況下如果到維護窗口關閉該JOB仍未結束則將被終止(這取決于該JOB的屬性stop_on_window_close)，剩下的有待收集信息的對象將在下一個維護窗口中得到處理；如果數據庫一直處于關閉的狀態，并在某維護窗口的時間范圍內該DB被打開，那么相應的維護窗口會被立即激活(ACTIVE)，同時GATHER_STATS_JOB自動作業也會被啟動，但該自動作業僅會在一個窗口中自動運行一次(因REASON="ORA-01014: ORACLE shutdown in progress"等原因失敗的不算做一次)。 以上介紹了GATHER_STATS_JOB的運行周期，和我們要介紹的問題沒有直接的聯系。我們這里要談的是，GATHER_STATS_JOB自動統計信息收集作業每次啟動時是由針對性地收集統計信息的而非對數據庫中所有schema下的對象都分析一遍；以上引用的文字中介紹了該JOB挑選分析對象的條件，即:

對象之前從未收集過統計信息，或由于某些原因沒有統計信息

對象的統計信息相對陳舊(stale)，是否陳舊的評判標準是由上次收集信息到此次收集期間被修改過的行數超過10%

條件1顯得理所當然，剔除一些復雜的情況，一個對象沒有統計信息的原因往往是這個對象剛剛被創建或者加載到數據庫中，并且用戶沒有手動地去收集過統計信息，那么Oracle有充分的理由去分析這些對象。而后者則體現了查詢優化器對統計信息陳舊度的容忍在超過10%的情況下導致執行計劃偏差的可能性將大幅上升，為了遏制這種勢頭有必要再次統計這些對象。 讓我們來看看GATHER_STATS_JOB針對"陳舊"(stale)統計信息的實際表現: SQL> select * from global_name;GLOBAL_NAME ------------------------------------ www.oracledatabase12g.comSQL> select * from v$version;BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - ProductionSQL> conn maclean/maclean Connected.SQL> create table need_analyze tablespace users as select rownum t1 from dba_objects where rownum<10001; Table created. SQL> select count(*) from need_analyze;COUNT(*) ----------10000SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';NUM_ROWS BLOCKS ---------- ----------/* 以上創建了一張具有10000行記錄的測試用表,因為是新建的所以沒有num_rows和blocks等等統計信息 *//* 手動調用GATHER_STATS_JOB自動作業 */SQL> begin dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true); end; /SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';NUM_ROWS BLOCKS ---------- ----------10000 20/* 刪除999條記錄，即不到10%的數據 */SQL> delete need_analyze where rownum<1000; 999 rows deleted. SQL> commit; Commit complete./* 再次調用GATHER_STATS_JOB */begin dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true); end; //* 可以看到統計信息并未被更新 */SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';NUM_ROWS BLOCKS ---------- ----------10000 20SQL> delete need_analyze where rownum<2; 1 row deleted. SQL> commit; Commit complete.SQL> begin dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true); end; / 2 3 4 PL/SQL procedure successfully completed.SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';NUM_ROWS BLOCKS ---------- ----------10000 20SQL> delete need_analyze where rownum<2; 1 row deleted. SQL> commit; Commit complete.SQL> begin dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true); end; / 2 3 4 PL/SQL procedure successfully completed.SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';NUM_ROWS BLOCKS ---------- ----------8999 20/* 可以看到修改的行數必須超過10%后才會被收集 */ 有的朋友肯定要問Oracle是怎么知道某張表是否有過DML操作，而DML操作又涉及到了多少行數據呢？這都是通過表監控特性(a table monitoring facility)來實現的，當初始化參數STATISTICS_LEVEL設置為TYPICAL或ALL時默認啟用這種特性。Oracle會默認監控表上的INSERT,UPDATE,DELETE以及表是否被TRUNCATE截斷，并記錄這些操作數量的近似值到數據字典。我們可以通過訪問user_tab_modifications視圖來了解這些信息: SQL> delete need_analyze; 8999 rows deleted.SQL> commit; Commit complete.SQL> select * from user_tab_modifications where table_name='NEED_ANALYZE'; no rows selected/* 從實際的DML操作完成到*_tab_modifications視圖到更新可能存在幾分鐘的延遲 *//* 通過dbms_stats包中的FLUSH_DATABASE_MONITORING_INFO存儲過程可以將這些監控數據刷新到字典中 */SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; PL/SQL procedure successfully completedSQL> col table_name for a20SQL> select table_name,inserts,updates,deletes,timestamp from user_tab_modifications where table_name='NEED_ANALYZE';TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP -------------------- ---------- ---------- ---------- --------- NEED_ANALYZE 0 0 8999 26-MAR-11/* 可以看到*_tab_modifications視圖中記錄了上次收集統計信息以來NEED_ANALYZE表上刪除過8999條記錄，因為測試時僅用少量的串行DML,所以這里十分精確 */SQL> set autotrace on; /* 通過以上執行計劃可以猜測，monitoring監控數據來源于MON_MODS_ALL$基表上 */SQL> desc sys.MON_MODS_ALL$;Name Null? Type----------------------------------------- -------- ----------------------------OBJ# NUMBERINSERTS NUMBERUPDATES NUMBERDELETES NUMBERTIMESTAMP DATEFLAGS NUMBERDROP_SEGMENTS NUMBERSQL> select * from mon_mods_all$ where obj#=(select object_id from dba_objects where object_name='NEED_ANALYZE');OBJ# INSERTS UPDATES DELETES TIMESTAMP FLAGS DROP_SEGMENTS ---------- ---------- ---------- ---------- --------- ---------- -------------52565 0 0 8999 26-MAR-11 0 0/* 需要注意的該mon_mods_all$修改監控基表僅記錄上次該對象統計信息以來的修改(modify)情況，并不能做為某張表的實際修改歷史記錄來利用 */ 雖然我們現在對GATHER_STATS_JOB在如何選擇分析對象的條件上更清晰了，但是不少朋友可能還是會疑惑難道Oracle不對那些長久以來沒有顯著修改的表一直不予以收集信息嗎？這似乎有悖于我們的常識，試看下例子: / * NEED_ANALYZE現在扮演一張靜態表，它上次被分析是在2011年3月26日 */SQL> select last_analyzed from dba_tables where table_name='NEED_ANALYZE';LAST_ANAL --------- 26-MAR-11SQL> select sysdate from dual; SYSDATE --------- 26-MAR-11SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> host [maclean@rh8 ~]$ su - root Password: /* 我們把時鐘調快到2012年的12月30日，希望我們能安全度過2012! */[root@rh8 ~]# date -s "2012-12-30 00:00:00" Sun Dec 30 00:00:00 CST 2012 [root@rh8 ~]# date Sun Dec 30 00:00:01 CST 2012[maclean@rh8 ~]$ exit exitSQL> startup; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218292 bytes Variable Size 75499788 bytes Database Buffers 83886080 bytes Redo Buffers 7168000 bytes Database mounted. Database opened.SQL> select sysdate from dual;SYSDATE --------- 30-DEC-12/* 再次手動調用GATHER_STATS_JOB自動作業 */SQL> set timing on; SQL> begin dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true); end; /2 3 4 PL/SQL procedure successfully completed.Elapsed: 00:00:00.33/* :-)，運行結束不到1s */SQL> select last_analyzed from dba_tables where table_name='NEED_ANALYZE';LAST_ANAL --------- 26-MAR-11 是的，默認情況下GATHER_STATS_JOB不會反復去分析那些靜態表，無論過去"多久"。 好了，我們需要對GATHER_STATS_JOB和DBMS_STATS包下屬的統計信息收集存儲過程(gather_*_stats)有一個飽滿的認識，他們遠沒有我們想象的那么2，實際上這個GATHER_STATS_JOB調用的PROGRAM存儲過程是DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC，雖然這是一個內部存儲過程(interal procedure)；但實際上它和我們經常手動調用的DBMS_STATS.GATHER_DATABASE_STATS收集整庫統計信息的存儲過程在以GATHER AUTO選項運行時的行為方式上幾乎一致，主要的區別是GATHER_DATABASE_STATS_JOB_PROC總是優先收集那些急需收集統計信息的對象，這保證了在維護窗口關閉之前那些最需要收集的統計信息總是能得到滿足。而在手動調用GATHER_DATABASE_STATS等存儲過程時因為不需要考慮窗口時間線的限制，而不論優先級。 to be continued .............

轉載于:https://www.cnblogs.com/macleanoracle/archive/2013/03/19/2967702.html

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的Does GATHER_STATS_JOB gather all objects' stats every time?的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。