10g和11g自動統計的區別

Oracle 10g 和11g 自動統計的區別。

 

10g：

oracle10g中，自動統計資訊收集作業名為GATHER_STATS_JOB

SELECT JOB_NAME, PROGRAM_NAME, SCHEDULE_NAME

  FROM DBA_SCHEDULER_JOBS

 WHERE JOB_NAME = 'GATHER_STATS_JOB';

 

 

GATHER_STATS_JOB實際上是呼叫了DBMS_STATS包裡的儲存過程dbms_stats.gather_database_stats_job_proc，也就是說10g中自動統計資訊收集作業的核心邏輯就是透過呼叫此儲存過程來實現的。

select program_action from dba_scheduler_programs where program_name='GATHER_STATS_PROG'

 

10G中自動統計資訊作業也配置的維護視窗有兩個。

select * from dba_scheduler_wingroup_members where window_group_name='MAINTENANCE_WINDOW_GROUP'

 

從如下查詢可以看到維護視窗WEEKNIGHT_WINDOW從週一到週五晚上的十點開始執行，最長執行8個小時，即最長可以執行到第二天早上6點。

維護視窗WEEKEND_WINDOW從週六早上零點開始執行，最長執行2天（48小時），即最長可以執行到下週一早上零點。

select window_name, repeat_interval, duration

  from dba_scheduler_windows

 where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW')

   and enabled = 'TRUE';

 

從以下查詢結果可以看到，oracle並沒有對兩個維護視窗施加資源控制，也就意味著oracle 10g 中的自動統計資訊收集作業在執行是可以無限制地消耗系統資源。

select window_name,resource_plan from dba_scheduler_windows;

 

檢視oracle自動統計實際情況

SELECT actual_start_date,job_name,status from

( select * from dba_scheduler_job_run_details

  where job_name='GATHER_STATS_JOB' ORDER BY LOG_DATE DESC)

WHERE ROWNUM<4;

都是晚上10點開始執行的，而且都執行成功了。

 

oracle10g自動統計的缺陷：

1、可配置的維護視窗太少，只有兩個，這不夠靈活。

在這樣的維護視窗設定下，週一至週五，週六至週日的維護視窗分別都是一個整體，如果修改了WEEKNIGHT_WINDOW的設定，那麼週一至週五的自動統計資訊收集作業就都得遵從修改後的設定（WEEKEND_WINDOW同理），如果想把週二和週三的作業改成晚上9點執行，這麼簡單一個需求，只有這麼兩個維護視窗是無法實現的。

2、oracle10g並沒有對兩個維護視窗施加資源控制，也就意味著oracle 10g 中的自動統計資訊收集作業在執行是可以無限制地消耗系統資源。這樣很可能會影響到系統正常的業務處理。

 

 

11g：

1、取消了名為GATHER_STATS_JOB的作業。

2、引入了一個名為GATHER_STATS_PROG的自動執行任務，專用於自動收集統計資訊，它其實上就是10g中的GATHER_STATS_JOB對應的應用程式GATHER_STATS_PROG。自動執行任務GATHER_STATS_PROG所對應客戶端的名稱為“auto optimizer stats collection”

select  client_name,task_name,operation_name,ststus from dba_autotask_task;

3、GATHER_STATS_PROG實際上也是呼叫了DBMS_STATS包裡的儲存過程dbms_stats.gather_database_stats_job_proc，也就是說11g中自動統計資訊收集作業的核心邏輯就是透過呼叫此儲存過程來實現的。和10g是一樣的。

4、維護視窗增加到了7個每天一個，分別是MONDAY_WINDOW  TUESDAY_WINDOW   WEDNESDAY_WINDOW  THURSDAY_WINDOW  FRIDAY_WINDOW  SATURDAY_WINDOW SUNDAY_WINDOW

SELECT WINDOW_NAME,AUTOTASK_STATUS FROM DBA_AUTOTASK_WINDOW_CLIENTS;

5、從下邊的查詢可以看出，週一至週五晚上的十點開始執行，最長執行4個小時，即最長可以執行到第二天早上2點。

    週六維護視窗從早晨6點開始執行，最長執行20個小時，即最長可以執行到第二天（週日）凌晨兩點。

   週日維護視窗從週日早上6點開始執行，最長執行20個小時，即最長可以執行到第二天（週一）凌晨兩點

Select window_name,repeat_interval,duration from dba_scheduler_windows and enabled = 'TRUE';

6、從如下查詢可以看出，已經對7個視窗施加了資源控制，其對應的resource plan的名稱為"DEFAULT_MAINTENANCE_PLAN",這也就意味著，11g的自動統計資訊收集作業在執行時所消耗的資源會受到控制。

7、11g自動執行任務GATHER_STATS_PROG，每次執行時會先生成名為“ORA$AT_OS_OPT_XXX”的作業，然後再執行這個作業。

SELECT actual_start_date,job_name,status from

( select * from dba_scheduler_job_run_details

  where job_name  like 'ORA$AT_OS_OPT%') ORDER BY LOG_DATE DESC)

WHERE ROWNUM<4;

 

從如下查詢可以看出ORA$AT_OS_OPT_XXX實際上對應的就是它們的維護視窗，並且它們就是自動執行任務GATHER_STATS_PROG所對應客戶端“auto optimizer stats collection”生成的作業

SELECT  client_name,window_name,job_name,job_start_time from

   (select * from dba_autotask_job_history where client_name='auto optimizer stats collection' order by window_start_time desc)

where rownum<4;

 

 

10G

禁掉週一至週五自動統計的收集作業：

BEGIN

DBMS_SCHEDULER.DISABLE(

name=>' "SYS"."WEEKNIGHT_WINDOW" ',

force=>TRUE);

END;

/

 

11g

禁掉週一自動統計的收集作業，將週二的起始執行時間調整到晚上11點，將週三的起始執行時間調整到晚上9點，並同時將週三最長執行時間調整為5個小時：

 

週一：

BEGIN

DBMS_SCHEDULER.DISABLE(

name=>' "SYS"."MONDAY_WINDOW" ',

force=>TRUE);

END;

/

 

週二：

注意，要改可配置的週二維護視窗，就要先DISABLE,然後再改，然後在ENABLE

 

BEGIN

DBMS_SCHEDULER.DISABLE(

name=>' "SYS"."TUESDAY_WINDOW" ',

force=>TRUE);

END;

/

 

這裡將週二維護視窗的repeat_interval的byhour屬性設為23，即代表晚上11點

 

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name=>' "SYS"."TUESDAY_WINDOW" ',

attribute=> 'repeat_interval',

value=> 'freq=daily;byday=TUE;byhour=23;byminute=0;bysecond=0');

END;

/

 

BEGIN

DBMS_SCHEDULER.ENABLE(

name=>' "SYS"."TUESDAY_WINDOW" ',

force=>TRUE);

END;

/

 

週三：

調整為五個小時：

先禁掉，將duration設定為300分鐘，即表示最長執行時間為5個小時

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name=>' "SYS"."WEDNESDAY_WINDOW" ',

attribute=> 'duration',

value=> numtodsinterval (300, 'minute'));

END;

/

然後啟用