[zt] Oracle10g 自動收集收集CBO統計資訊設定

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

這個自動任務預設情況下在工作日晚上10：00-6：00和週末全天開啟(工作日晚10點執行,  週六,日早6點跑,  時間不是很長 )。呼叫DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統計資訊。

該過程首先檢測統計資訊缺失和陳舊的物件。然後確定優先順序，再開始進行統計資訊。

說明：當做完統計資訊後，如果對物件的行數修改達到10%，DBMS_STATS就認為是統計資訊過舊。

可以透過以下查詢這個JOB的執行情況：
SQL> 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');

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

對於易變物件的變化，可以人工收集統計資訊(DBMS_STATS)外 主要兩種處理方式：

一種就是刪除統計資訊。使它的統計資訊為空，對於任何統計資訊缺失的表,oracle會用動態取樣特性自動產生統計資訊。如果使用久的統計資訊 就可能產生錯誤的執行計劃。
需要設定optimizer_dynamic_sampling為2（ORACLE10G預設值）或以上都可以啟動此特性。

optimizer_dynamic_sampling ,提供在SQL分析的時候，自動根據不同的Level（0-10）以不同的準確度分析SQL中未被analyze過的表，意在為CBO提供更多的統計信 息。在Oracle9iR2中引入，預設為Level 1，10g預設為2 。

Level 0: Do not dynamically sample the table(s)

Level 1：Sample tables that have not been analyzed if there is more than one table in the query,the table in question has not been analyzed and it has no indexes,and the optimizer determines that the query plan would be affected based on the size of this objects

Level 2:Sample all unanalyzed tables referenced in the query using default sampling amounts(small sample)

Level 3 -- Level 10 ........更詳細的sample而已。

例如：dbms_stats.delete_table_stats(‘table_name’ ,‘VOLATILE_TABLE’)

另一種就是設定為表進行鎖定,這樣就可以不更新統計資訊（注意LOCK表以後，表就不能修改了）。

dbms_stats.lock_table_stats(‘table_name’ ,‘VOLATILE_TABLE’)；  

 

http://hi.baidu.com/dbatao/blog/item/f3effdae2791cccb7dd92a4a.html   

 

 

 

Metalink 上的解釋：

Subject:  How to check what automatic statistics collection is scheduled on 10g
  Doc ID:  Note:377143.1 Type:  BULLETIN
  Last Revision Date:  16-FEB-2007 Status:  PUBLISHED
***
This article is being delivered in Draft form. and may contain errors.
Please use the MetaLink "Feedback" button to advise Oracle of any issues related to this article.
PURPOSE

This Note provides information on the How to check what automatic statistics collection is scheduled on 10g

SCOPE AND APPLICATION

Users collecting statistics on database objects for use by the Cost Based Optimizer (CBO)


How to check what automatic statistics collection is scheduled on 10g

With Oracle 10g, the gathering of statistics has become automated.
The GATHER_STATS_JOB that is built in the database creation process schedules automatic statistics collection.
The job initiates a 'program' of statistics gathering appropriate for the database in question.

The job details can be viewed by querying the DBA_SCHEDULER_JOBS view:
  select job_name, job_type, program_name, schedule_name, job_class
  from dba_scheduler_jobs
  where job_name = 'GATHER_STATS_JOB';

  JOB_NAME                       JOB_TYPE         PROGRAM_NAME        SCHEDULE_NAME             JOB_CLASS
  ------------------------------ ---------------- ------------------- ------------------------- ------------------------------
  GATHER_STATS_JOB                                GATHER_STATS_PROG   MAINTENANCE_WINDOW_GROUP  AUTO_TASKS_JOB_CLASS

The output shows that the 'GATHER_STATS_JOB' schedules a program 'GATHER_STATS_PROG' in the 'MAINTENANCE_WINDOW_GROUP' time schedule.
The PROGRAM_NAME 'GATHER_STATS_PROG' starts the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC stored procedure:
  select  PROGRAM_ACTION
  from dba_scheduler_programs
  where PROGRAM_NAME = 'GATHER_STATS_PROG';

  PROGRAM_ACTION
  ----------------------------------------------
  dbms_stats.gather_database_stats_job_proc

The job is scheduled according to the value of the SCHEDULE_NAME field.
In this example, the schedule being used is: 'MAINTENANCE_WINDOW_GROUP'.
This schedule is defined in the DBA_SCHEDULER_WINGROUP_MEMBERS view:
  select *
  from DBA_SCHEDULER_WINGROUP_MEMBERS
  where WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP';

  WINDOW_GROUP_NAME         WINDOW_NAME
  ------------------------- ------------------------------
  MAINTENANCE_WINDOW_GROUP  WEEKNIGHT_WINDOW
  MAINTENANCE_WINDOW_GROUP  WEEKEND_WINDOW

The meaning of these 'windows' can be found in 'DBA_SCHEDULER_WINDOWS':
  select window_name, repeat_interval, duration
  from dba_scheduler_windows
  where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW')

  WINDOW_NAME       REPEAT_INTERVAL                                                        DURATION
  ----------------- ---------------------------------------------------------------------- -------------
  WEEKNIGHT_WINDOW  freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00
  WEEKEND_WINDOW    freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                    +002 00:00:00


The meaning of these entries is as follows:

The WEEKNIGHT_WINDOW is scheduled each week day at 10PM. and should last a maximum of 8 hours.
The WEEKEND_WINDOW is scheduled each Saturday at 0AM and should last 2 days max

If the START_DATE and END_DATE columns (Not shown) are NULL, then this job will run continuously.

All these definitions can be found in the $ORACLE_HOME/rdbms/admin/catmwin.sql script.