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 |
---|---|
| 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. |
| Provides statistical data for each automated maintenance task over 7-day and 30-day periods. |
| Lists the history of automated maintenance task job runs. Jobs are added to this view after they finish executing. |
| Lists the windows that belong to |
| 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 |
---|---|
| 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. |
| Provides statistical data for each automated maintenance task over 7-day and 30-day periods. |
| Lists the history of automated maintenance task job runs. Jobs are added to this view after they finish executing. |
| Lists the windows that belong to |
| 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. |