10g和11g自動任務的區別

10g和11g自動任務的區別[@more@]

Oracle10g增加了一個新的任務，用來自動分析資料庫，用於增加CBO執行的正確性。這個任務就是GATHER_STATS_JOB，
任務定義週一到週五的夜晚執行，和週六日全天執行。在oracle11g中job的名稱有些不同。

　oracle這個自動收集資訊的出發點很好，但是，大部分系統晚上的負載也很高，或者執行計劃改變，會引起新的效能問題。
而且oracle這個自動收集資訊的job非常耗效能。

oracle10g的系統自動job
SQL> select job_name,comments from dba_scheduler_jobs;

JOB_NAME COMMENTS
------------------------------ --------------------------------------------------------------------------------
AUTO_SPACE_ADVISOR_JOB auto space advisor maintenance job
GATHER_STATS_JOB Oracle defined automatic optimizer statistics collection job
FGR$AUTOPURGE_JOB file group auto-purge job
PURGE_LOG purge log job
MGMT_STATS_CONFIG_JOB OCM Statistics collection job.
MGMT_CONFIG_JOB Configuration collection job.
RLM$SCHDNEGACTION
RLM$EVTCLEANUP

8 rows selected

SQL>

oracle11g的自動job
SQL> select job_name,comments from dba_scheduler_jobs;

JOB_NAME COMMENTS
------------------------------ --------------------------------------------------------------------------------
SM$CLEAN_AUTO_SPLIT_MERGE auto clean job for auto split merge
RSE$CLEAN_RECOVERABLE_SCRIPT auto clean job for recoverable script
FGR$AUTOPURGE_JOB file group auto-purge job
BSLN_MAINTAIN_STATS_JOB Oracle defined automatic moving window baseline statistics computation job
DRA_REEVALUATE_OPEN_FAILURES Reevaluate open failures for DRA
HM_CREATE_OFFLINE_DICTIONARY Create offline dictionary in ADR for DRA name translation
ORA$AUTOTASK_CLEAN Delete obsolete AUTOTASK repository data
FILE_WATCHER File watcher job
PURGE_LOG purge log job
MGMT_STATS_CONFIG_JOB OCM Statistics collection job.
MGMT_CONFIG_JOB Configuration collection job.

11 rows selected

SQL>

11g中的幾個job說明：

1. ORA$AUTOTASK_CLEAN
The job is created by the 11g script catmwin.sql which mentions that this job is an autotask repository data ageing job.
It runs the procedure ora$age_autotask_data.

2. HM_CREATE_OFFLINE_DICTIONARY
The job is created by the 11g script catmwin.sql which mentions that this is a job for creation of offline dictionary
for Database Repair Advisor.

The system job SYS.HM_CREATE_OFFLINE_DICTIONARY executes the dbms_hm.create_offline_dictionary package which creates a LogMiner offline dictionary in the ADR for DRA name translation service. The job for generating the logminer dictionary is scheduled during the maintenance window. This job can be disabled. ‘

3. DRA_REEVALUATE_OPEN_FAILURES
The job is created by the 11g script catmwin.sql which mentions that this is a job for reevaluate open failures for
Database Repair Advisor. The job executes the procedure dbms_ir.reevaluateopenfailures.

4. MGMT_CONFIG_JOB -

comes with the OCM(Oracle Configuration Manager) installation - This is a configuration collection job.
The job is created by the script ocmjb10.sql by running procedure ‘ORACLE_OCM.MGMT_CONFIG.collect_config’.

5. MGMT_STATS_CONFIG_JOB
This is an OCM Statistics collection job created in ocmjb10.sql by running ‘ORACLE_OCM.MGMT_CONFIG.collect_stats’.

6. BSLN_MAINTAIN_STATS_JOB (替換了10G的GATHER_STATS job)
This job replaces the old GATHER_STATS job. It is a compute statistics job. This job runs the BSLN_MAINTAIN_STATS_PROG program on the BSLN_MAINTAIN_STATS_SCHED schedule. The program BSLN_MAINTAIN_STATS_PROG will keep the default baseline’s statistics up-to-date.

7. XMLDB_NFS_CLEANUP_JOB
The job is created in xdbu102.sql and runs the procedure dbms_xdbutil_int.cleanup_expired_nfsclients.

oracle10g，11gjob的工作原理

oracle是透過維護視窗來完成系統的自動job的，系統的維護視窗透過檢視dba_scheduler_windows

SQL> select window_name,resource_plan,comments from dba_scheduler_windows;

WINDOW_NAME RESOURCE_PLAN COMMENTS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Monday window for maintenance tasks
TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Tuesday window for maintenance tasks
WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Wednesday window for maintenance tasks
THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Thursday window for maintenance tasks
FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Friday window for maintenance tasks
SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Saturday window for maintenance tasks
SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Sunday window for maintenance tasks
WEEKNIGHT_WINDOW Weeknight window - for compatibility only
WEEKEND_WINDOW Weekend window - for compatibility only

9 rows selected

SQL>

維護視窗組有哪些維護組
select * from dba_scheduler_wingroup_members;

select t1.window_name,
t1.repeat_interval,
t1.duration,
t2.window_group_name
from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
where t1.window_name = t2.window_name
and t2.window_group_name in
('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');

常用檢視：
select * from dba_scheduler_programs
select * from dba_scheduler_jobs
select * from dba_scheduler_running_jobs
select * from dba_scheduler_job_run_details
select * from dba_scheduler_schedules
select * from dba_scheduler_wingroup_members

select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_JOB_HISTORY;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_CLIENT_HISTORY;

10g關閉自動收集job

select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

所以建議最好關閉自動統計資訊收集功能：
exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

啟動自動統計資訊收集功能
exec DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');

手工收集統計資訊：
SQL> exec dbms_stats.gather_schema_stats('detail',options=>'gather stale',estimate_percent =>10);

--收集沒有分析過的表的統計資訊
begin
dbms_stats.gather_schema_stats(ownname=>scott,options=>'gather empty');
end;

--重新分析修改量超過10%的表（這些修改包括插入、更新和刪除）
begin
dbms_stats.gather_schema_stats(ownname=>scott,options=>'gather stale');
end;

11g關閉自動收集job
The automated maintenance tasks infrastructure (known as AutoTask) schedules tasks to
run automatically in Oracle Scheduler windows known as maintenance windows. By default,
one window is scheduled for each day of the week. Automatic optimizer statistics collection
runs as part of AutoTask and is enabled by default to run in all predefined maintenance windows.

If for some reason automatic optimizer statistics collection is disabled, then you can enable
it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
When you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

手工收集統計資訊：
SQL> exec dbms_stats.gather_schema_stats('detail',options=>'gather stale',estimate_percent =>10);

begin
dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>NULL);
end;
/


begin
dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>NULL);
end;
/


begin
dbms_auto_task_admin.disable(client_name=>'sql tuning advisor',operation=>NULL,window_name=>NULL);
end;
/

select client_name,status from dba_autotask_client;

參考文件

http://blog.csdn.net/wyzxg/article/details/6831479