oracle 10g的自動統計分析

Oracle 10g下cbo的自動統計分析功能很大程式的解決了因為9i下統計資訊失效或者陳舊導致的sql語句選擇了不合適的執行計劃，該統計分析功能是透過dba_scheduler_jobs檢視下的gather_stats_job來對系統的物件進行統計分析，gather_stats_job在星期一到星期五在晚上10點執行，雙休從早上6點到星期一早上6點，該統計分析由於需要對資料庫的所有物件進行分析，所以可能該job並沒有完成，檢視dba_scheduler_job_run_details可以對job執行是否完畢，可以看出在大型的生產庫中很多情況該job都沒有執行完畢就中止了。

SQL> select log_date,job_name,status from dba_scheduler_job_run_details where job_name='GATHER_STATS_JOB' order by log_id;

LOG_DATE JOB_NAME STATUS

-------------------------------------------------- -------------------- --------------------

07-4月 -12 06.01.40.434786 上午 +08:00 GATHER_STATS_JOB STOPPED

09-4月 -12 10.27.13.012514 下午 +08:00 GATHER_STATS_JOB STOPPED

10-4月 -12 10.33.26.498880 下午 +08:00 GATHER_STATS_JOB STOPPED

11-4月 -12 10.08.14.335380 下午 +08:00 GATHER_STATS_JOB STOPPED

12-4月 -12 10.24.57.878692 下午 +08:00 GATHER_STATS_JOB STOPPED

13-4月 -12 10.09.23.672033 下午 +08:00 GATHER_STATS_JOB STOPPED

14-4月 -12 06.01.43.666416 上午 +08:00 GATHER_STATS_JOB STOPPED

那麼gather_stats_job呼叫的是什麼了，透過檢視dba_scheduler_program可以檢視

select program_action from dba_scheduler_programs where program_name='GATHER_STATE_GROG'

dbms_stats.gather_database_stats_job_proc

可以看出gather_stats_job呼叫dbms_stats.gather_database_stats_job_proc的procedure來檢測統計資訊缺失和失效的物件，然後確定優先順序 進行統計和分析資訊。

（統計資訊缺失可能都知道，但是失效如何解釋，oracle中規定了修改的行達到了資料的10%，則就意味統計資訊失效，關於這個修改的行則是oracle的另外一個檢視sys.dba_tab_modification中的記錄來對錶等物件的統計資訊是否失效來進行判斷）

Cbo進行自動統計分析，不過如果檢視生產系統中的表可以發現user_tables中的last_analyze_time不是近端時間的表大有所在，cbo進行自動統計分析需要表中的缺少統計資訊，或者修改超過10%，如果沒有達到，cbo下的自動分析並不會對該物件進行分析。

Cbo正是因為其根據統計資訊而採取的最優的執行計劃而減小了無謂的消耗，提高sql的效能，可以這麼說cbo是完全依賴於統計資訊，如果缺失cbo可以採取動態取樣，oracle 10g下optimizer_dynamic_sampling預設是2，那麼9i下的optimizer_dynamic_sampling是1。

（1） sql語句中必須有相應的至少一個沒有采樣資訊的表

（2） 未分析的表沒有索引

（3） 未分析的表佔用的資料塊要大於動態取樣預設的資料塊（32個）

9i下需要對錶進行alter table tablename monitor 才會對錶的修改進行監控，進而寫入到dba_tab_monitoring檢視中，而10g下預設都會對錶進行監控。

這裡說一下最近犯過的一個不知道算不算錯誤，生產庫由於出現了大量的db file sequential read影響了效能，由於檢視sql語句發現該邏輯讀都很小，基本都是主鍵產生等待，根據awr的sga advisor增加了sga大小，然後重啟資料庫，居然發現了一個30多g的大表主鍵不停的fts，這可以說非常的不正常，隱式轉換，統計資訊失效但是由於設定的optimizer_dynamic_sampling覺得不是統計資訊失效造成的，很有可能是隱式轉換或者cbo的錯誤執行計劃導致，由於沒有metalink也無法去追蹤具體的由來的，當時檢視自動分析依然存在在晚上10點後會進行自動統計分析，第二天後業務恢復正常，選擇了unique index。

不過當時自己其實完全可以利用dbms_stats.restore_table_stats(‘DESKTOP’,’ARTICLE’,sysdate-60/1440)恢復統計資訊到一個小時前，不過案例不在，也是一個很痛苦的事情。

而統計資訊儲存的時間可以透過select dbms_stats.get_stats_hisory_retention from dual;

不過和undo_retention一樣該引數並不是一個擔保值

Select dbms_stats.get_stats_history_availability from dual;

統計資訊的可用時間。

關閉cbo的自動統計分析dbms_scheduler.disable(‘GATHER_STATS_JOB’)，cbo下的自動分析需要消耗一定量的latch可能造成latch競爭，不過合理的統計資訊卻是執行計劃合理的保證，所以這裡也沒有什麼關於是否開啟於關閉自動統計分析的建議，不過如果能真正瞭解cbo具體的行為，那麼根據你自己的業務情況，相信肯定有個合適的選擇。

[@more@]