oracle 11g Automated Database Maintenance Tasks

oracle 11g有三个自动维护的task，

 

1、Automatic Optimizer Statistics Collection

替那些没有统计信息或是只有小量统计的信息的对象收集统计信息。

2、Automatic Segment Advisor

标示那些有空间可回收的segment，并且对碎片的清除给出建议

3、Automatic SQL Tuning Advisor

检查高负载 SQL 语句的性能，并提出如何优化这些语句的建议

 

maintence windows

automated maintenance tasks运行的时间。加入maintenance window很长，除了SQL Tuning Advisor外其他的自动维护tasks每四个小时重启。

 

二、配置Automated Maintenance Tasks

通过DBMS_AUTO_TASK_ADMIN 包来enable、disable Maintenance Tasks，

 

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

 

在制定的维护窗口停用维护任务：

BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor', 
    operation   => NULL, 
    window_name => 'MONDAY_WINDOW');
END;
/

其中client_name在DBA_AUTOTASK_CLIENT数据字典视图中列出：

 

SQL> select  CLIENT_NAME from dba_autotask_client;

CLIENT_NAME
--------------------------------------------------------------------------------
auto optimizer stats collection
auto space advisor
sql tuning advisor

 

如果要停用或是启用所有的automated maintenance tasks，调用ENABLE or DISABLE 存储过程：

EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

 

三、Configuring Maintenance WindowsDBMS_SCHEDULER 包中的 过程 SET_ATTRIBUTE来修改windows的属性：

BEGIN
  dbms_scheduler.disable(
    name  => 'SATURDAY_WINDOW');
  dbms_scheduler.set_attribute(
    name      => 'SATURDAY_WINDOW',
    attribute => 'DURATION',
    value     => numtodsinterval(4, 'hour'));
  dbms_scheduler.enable(
    name => 'SATURDAY_WINDOW');
END;
/

在修改前必须停用windows，不然只能在下次打开的时候才能生效。

 

About Resource Allocations for Automated Maintenance Tasks

 

Consumer Group/subplan	Level 1	Level 2	Maximum Utilization Limit
ORA$AUTOTASK_SUB_PLAN	-	25%	90
ORA$DIAGNOSTICS	-	5%	90
OTHER_GROUPS	-	70%
SYS_GROUP	75%	-

Table 26-2 Automated Maintenance Tasks Database Dictionary Views

 

View Name	Description
DBA_AUTOTASK_CLIENT_JOB	Contains information about currently running Scheduler jobs created for automated maintenance tasks. It provides information about some objects targeted by those jobs, as well as some additional statistics from previous instantiations of the same task. Some of this additional data is taken from generic Scheduler views.
DBA_AUTOTASK_CLIENT	Provides statistical data for each automated maintenance task over 7-day and 30-day periods.
DBA_AUTOTASK_JOB_HISTORY	Lists the history of automated maintenance task job runs. Jobs are added to this view after they finish executing.
DBA_AUTOTASK_WINDOW_CLIENTS	Lists the windows that belong to MAINTENANCE_WINDOW_GROUP, along with the Enabled or Disabled status for the window for each maintenance task. Primarily used by Enterprise Manager.
DBA_AUTOTASK_CLIENT_HISTORY	Provides per-window history of job execution counts for each automated maintenance task. This information is viewable in the Job History page of Enterprise Manager.

 

 

 

View Name	Description
DBA_AUTOTASK_CLIENT_JOB	Contains information about currently running Scheduler jobs created for automated maintenance tasks. It provides information about some objects targeted by those jobs, as well as some additional statistics from previous instantiations of the same task. Some of this additional data is taken from generic Scheduler views.
DBA_AUTOTASK_CLIENT	Provides statistical data for each automated maintenance task over 7-day and 30-day periods.
DBA_AUTOTASK_JOB_HISTORY	Lists the history of automated maintenance task job runs. Jobs are added to this view after they finish executing.
DBA_AUTOTASK_WINDOW_CLIENTS	Lists the windows that belong to MAINTENANCE_WINDOW_GROUP, along with the Enabled or Disabled status for the window for each maintenance task. Primarily used by Enterprise Manager.
DBA_AUTOTASK_CLIENT_HISTORY	Provides per-window history of job execution counts for each automated maintenance task. This information is viewable in the Job History page of Enterprise Manager.