開啟oracle10g統計資訊自動收集功能

轉載於:-----&gt>http://blog.csdn.net/hobbypei/article/details/7816650
有兩種方法，分別如下：方法一： 
SYSDBA登入 
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB'); 
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB'); 
方法二： 
alter system set "_optimizer_autostats_job"=false scope=spfile; 
alter system set "_optimizer_autostats_job"=true scope=spfile; 
Pfile可以直接修改初始化引數檔案然後重新啟動資料庫。

從Oracle Database 10g開始，Oracle在建庫後就預設建立了一個名為GATHER_STATS_JOB的定時任務，用於自動收集CBO的統計資訊。

這個自動任務預設情況下在工作日晚上10：00-6：00和週末全天開啟。呼叫DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統計資訊。該過程首先檢測統計資訊缺失和陳舊的物件。然後確定優先順序，再開始進行統計資訊。

可以透過以下查詢這個JOB的執行情況：

select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

其實同在10點執行的Job還有一個AUTO_SPACE_ADVISOR_JOB：

SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;

JOB_NAME LAST_START_DATE
------------------------------ ----------------------------------------
AUTO_SPACE_ADVISOR_JOB 04-DEC-07 10.00.00.692269 PM +08:00
GATHER_STATS_JOB 04-DEC-07 10.00.00.701152 PM +08:00
FGR$AUTOPURGE_JOB
PURGE_LOG 05-DEC-07 03.00.00.169059 AM PRC

然而這個自動化功能已經影響了很多系統的正常執行，晚上10點對於大部分生產系統也並非空閒時段。
而自動分析可能導致極為嚴重的閂鎖競爭，進而可能導致資料庫Hang或者Crash。

所以建議最好關閉這個自動統計資訊收集功能：

exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

自動化永遠而嚴重的隱患相伴隨！

 

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

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_DATEJOB_NAME STATUS

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

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

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

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

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

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

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

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

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

select program_action from dba_scheduler_programs where program_name='GATHER_STATS_PROG'

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具體的行為，那麼根據你自己的業務情況，相信肯定有個合適的選擇。