Oracle統計資訊自動收集

Oracle提供了統計資料自動收集功能。在部署安裝軟體過程中，其中有一個步驟便是提示是否啟用這個功能(預設是啟用這個功能)。這個功能貌似帶來了統計資料採集上的便捷，但是其中卻隱藏著效能隱患。在7*24小時的系統中這種自動執行的任務很有可能對系統效能帶來衝擊。因此，我們酌情需要修改或是禁掉這項功能。10g和11g的處理方法有所不同，以下會分別闡述。

一、Oracle 10g的統計資訊收集

1、自動統計作業的查詢

Oracle 10g版本中，自動統計的job名為GATHER_STATS_JOB，該任務預設在工作日晚上22：00-6：00和週末全天為執行視窗期，在執行視窗期內，該作業都會執行，根據stop_on_window_close屬性來決定如果在視窗期結束以後該作業還沒有執行完畢，是繼續執行還是結束執行。

該作業呼叫DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統計資訊，該過程與另一個過程DBMS_STATS.GATHER_DATABASE_STATS功能相近，但有優先處理順序的考慮，首先檢測統計資訊缺失和陳舊的物件，然後確定優先順序，更新越多的表將越優先統計。

收集統計資訊的表物件是之前從來沒有收集過的或者更新記錄數超過當前總記錄數10%的表。記錄數的更改量由Oracle資料庫自動監控，在初始化引數statistics_level設定為TYPICAL或者ALL時，自動監控即會生效。在表user_tab_modifications中記錄了所有被監控的表的資料量更改資訊，該資訊的更新會稍微滯後於真實的修改，可以透過DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO過程立刻將更改的資訊更新到USER_TAB_MODIFICATIONS表中。對於更新之後再rollback的記錄，仍然算為已經受影響的記錄，Oracle不會在rollback之後再去更新USER_TAB_MODIFICATIONS表。

下面是在10g中檢視自動收集統計作業的方法：

col program_name for a30

col schedule_name for a30

col last_run_duration for a30

select owner, job_name, program_name, schedule_name, job_class, enabled, auto_drop, run_count, to_char(last_start_date, 'yyyy-mm-dd hh24:mi:ss') last_start_time, last_run_duration, state from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

OWNER JOB_NAME PROGRAM_NAME SCHEDULE_NAME JOB_CLASS ENABL AUTO_ RUN_COUNT LAST_START_TIME LAST_RUN_DURATION STATE

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

SYS GATHER_STATS_JOB GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP AUTO_TASKS_JOB_CLASS TRUE FALSE 29 2017-01-28 06:00:05 +000000000 00:00:38.535000 SCHEDULED

以下檢視自動收集統計作業的歷次執行明細，該表會保留最近一個月的記錄：

col owner for a10

col job_name for a20

col status for a10

col run_duration for a20

col session_id for a10

col cpu_used for a20

select owner, job_name, status, to_char(actual_start_date, 'yyyy-mm-dd hh24:mi:ss') actual_start_time, run_duration, session_id, cpu_used

from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB' order by actual_start_date desc;

即便作業被刪除，我們仍可以透過log記錄檢視作業的歷史執行情況：

col owner for a10

col job_name for a30

col operation for a20

col status for a10

select to_char(log_date, 'yyyy-mm-dd hh24:mi:ss') log_time, owner, job_name, job_class, operation, status from dba_scheduler_job_log

where job_name = 'GATHER_STATS_JOB' order by log_date desc;

如果這個作業正在執行中，可以透過以下查詢看到：

col elapsed_time for a30

select job_name, session_id, elapsed_time, cpu_used from dba_scheduler_running_jobs;

2、自動統計作業的關閉和啟用

方法一：

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;

3、修改自動統計作業的執行時間

1)檢視自動統計作業的時間視窗資訊：

col window_name for a20

col repeat_interval for a80

col duration for a20

select t1.window_name, t1.repeat_interval, t1.duration

from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2

where t1.window_name = t2.window_name

and t2.window_group_name in

(select window_group_name

from dba_scheduler_wingroup_members

where window_group_name in

(select schedule_name

from dba_scheduler_jobs

where job_name = 'GATHER_STATS_JOB'));

2)根據工廠實際開班時間修改這兩個視窗，例如可以利用中午吃飯時間進行自動統計，以便避開對生產的干擾。在SYS下執行：

begin

dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW',

'REPEAT_INTERVAL',

'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=11;byminute=0; bysecond=0');

dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW',

'DURATION',

'+000 00:30:00');

dbms_scheduler.set_attribute('WEEKEND_WINDOW',

'REPEAT_INTERVAL',

'freq=daily;byday=SAT,SUN;byhour=11;byminute=0;bysecond=0');

dbms_scheduler.set_attribute('WEEKEND_WINDOW',

'DURATION',

'+000 00:30:00');

end;

/

3)修改成功後再次檢視視窗時間表資訊已達到目的：

二、針對Oracle 11g的處理方法

1、檢視自動統計任務及其狀態

select client_name, status from dba_autotask_client;

CLIENT_NAME STATUS

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

auto optimizer stats collection ENABLED

auto space advisor ENABLED

sql tuning advisor ENABLED

其中“auto optimizer stats collection”便是我們要尋找的自動收集統計資訊的任務名稱，它的狀態目前是啟用狀態。

在11g之前，當表的資料量修改超過總資料量的10%，就會自動收集統計資訊，這個STALE_PERCENT=10%是無法修改的，如果表非常大，10%是非常多的資料，有可能造成統計資訊不準確。在11g之後，STALE_PERCENT=10%是可以修改的，分為全域性(DBMS_STATS.SET_GLOBAL_PREFS)和表級別(DBMS_STATS.SET_TABLE_PREFS)。表級別的設定：

修改為5%(範圍從1-100)：

exec dbms_stats.set_table_prefs(null,'table_name','STALE_PERCENT',5);

恢復為10%：

exec dbms_stats.set_table_prefs(null,'table_name','STALE_PERCENT',null);

查詢百分比：

select dbms_stats.get_prefs('STALE_PERCENT',null,'table_name') from dual;

2、禁止自動統計任務

我們可以使用DBMS_AUTO_TASK_ADMIN包完成這個任務。

exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => null, window_name => null);

select client_name, status from dba_autotask_client;

CLIENT_NAME STATUS

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

auto optimizer stats collection DISABLED

auto space advisor ENABLED

sql tuning advisor ENABLED

此時“auto optimizer stats collection”任務已經被禁用。

3、啟用自動統計任務

exec dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => null, window_name => null);

select client_name, status from dba_autotask_client;

CLIENT_NAME STATUS

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

auto optimizer stats collection ENABLED

auto space advisor ENABLED

sql tuning advisor ENABLED

4、修改自動統計任務的執行時間

1)查詢自動統計任務的執行視窗時間表：

select t1.window_name, t1.repeat_interval, t1.duration

from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2

where t1.window_name = t2.window_name

and t2.window_group_name in

(select window_group

from dba_autotask_client t3

where t3.client_name = 'auto optimizer stats collection');

2)根據需要修改這些視窗的時間表，方法同10g中呼叫dbms_scheduler.set_attribute過程。

3)實際上，還有另外兩個自動任務：

select client_name, status from dba_autotask_client;

CLIENT_NAME STATUS

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

auto optimizer stats collection ENABLED

auto space advisor ENABLED

sql tuning advisor ENABLED

利用上面的查詢可以驗證，它們與自動統計任務實際上是預設被安排在同一時間表上的，也可以根據需要修改。

三、手動收集統計資訊

對於資料量很大的庫，一般是建議關閉掉自動收集的任務，透過自己手工寫儲存過程，再把儲存過程加入JOB中去收集統計資訊。以下是一個手動收集統計資訊的例子：

create or replace procedure manual_gather_stats is
  /**
  * 表的手動統計資訊收集
  */
  my_username varchar2(20);
 
  cursor stale_table is
    select segment_name
      from user_segments
     where segment_name in (select table_name
                              from user_tab_statistics
                             where last_analyzed is null
                                or stale_stats = 'YES')
     group by segment_name;
 
  stale stale_table%rowtype;
 
begin
  dbms_stats.flush_database_monitoring_info;
  select username into my_username from user_users;
  for stale in stale_table loop
    dbms_stats.gather_table_stats(ownname          => my_username,
                                  tabname          => stale.segment_name,
                                  estimate_percent => null,
                                  degree           => null,
                                  method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
                                  cascade          => true);
  end loop;
end;
/