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_FAILURESThe 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_JOBThis 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_JOBThe 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关闭自动收集jobThe 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);

begindbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>NULL);end;/begindbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>NULL);end;/begindbms_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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7916042/viewspace-1057718/，如需转载，请注明出处，否则将追究法律责任。