ORACLE11G Automatic maintenance tasks

The default automatic maintenance tasks in Oracle 11g are divided into three categories:
Automatic Optimizer Statistics Collection (Automatic Optimizer statistics collection)
Collects Optimizer (optimizer) statistics for all Schema objects that have no statistics or only obsolete statistics in the database. SQL query optimizer uses the statistics collected by this task to improve the performance of SQL execution. *

Automatic Segment Advisor (automatic segment guidance)
Identify segments with available reclaim space and suggest how to eliminate fragmentation in these segments. You can also run Segment advisor manually to get more up-to-date suggestions, or to get suggestions for Automatic Segment advisor that are not checked for sections that might do space reclamation. *

* Automatic SQL Tuning Advisor (Automatic SQL optimization guide)
Examine the performance of high-load SQL statements and suggest how to optimize these statements. You can configure this guidance to automatically apply the proposed SQL profile.

To enable/Disable maintenance tasks:
Client_name from:
select * from DBA_AUTOTASK_CLIENT

---disable

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

---Enabled

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

To enable or disable all automatic maintenance tasks for all windows, call the Enable or disable procedure without parameters:

---禁用EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;---启用EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;---结果select * from DBA_AUTOTASK_WINDOW_CLIENTS;

To disable a specific Maintenance window:

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

The difference between 10g and 11g predefined scheduler windows:
Oracle10g:weeknight_window and Weekend_window
Oracle11g:monday_window .... Sunday_window.
For backwards compatibility, Weeknight_window and Weekend_window still exist.
The duration of the window opening is changed in 11g. Monday to Friday from 10 o'clock to 2 o'clock in the morning, Saturday to Sunday from 6 o'clock in the morning to 2 o'clock in the morning.

Modify automatic task run time starts at 23 o'clock, duration 60 Minutes:

exec dbms_scheduler.disable( name => ‘MONDAY_WINDOW’, force => TRUE);exec dbms_scheduler.set_attribute( name => ‘MONDAY_WINDOW’, attribute => ‘repeat_interval’,value => ‘freq=daily;byday=MON;byhour=23;byminute=0;bysecond=0’);exec dbms_scheduler.set_attribute( name => ‘MONDAY_WINDOW’, attribute => ‘DURATION’,value => numtodsinterval(60,‘minute‘)));exec dbms_scheduler.enable( name => ‘MONDAY_WINDOW’);

Results view:
select * from dba_scheduler_windows;

The Default_maintenance_plan resource plan is used by default:

SQL> select window_name, resource_plan from dba_scheduler_windows;WINDOW_NAME            RESOURCE_PLAN------------------------------ ------------------------------MONDAY_WINDOW              DEFAULT_MAINTENANCE_PLANTUESDAY_WINDOW             DEFAULT_MAINTENANCE_PLANWEDNESDAY_WINDOW           DEFAULT_MAINTENANCE_PLANTHURSDAY_WINDOW            DEFAULT_MAINTENANCE_PLANFRIDAY_WINDOW              DEFAULT_MAINTENANCE_PLANSATURDAY_WINDOW            DEFAULT_MAINTENANCE_PLANSUNDAY_WINDOW              DEFAULT_MAINTENANCE_PLANWEEKNIGHT_WINDOWWEEKEND_WINDOW

Default Resource Management

DEFAULT_MAINTENANCE_PLAN defines the following resource allocations:Consumer Group/subplan  Level 1     Level 2     Maximum Utilization LimitORA$AUTOTASK_SUB_PLAN   -           25%         90ORA$DIAGNOSTICS         -           5%          90OTHER_GROUPS            -           70%  SYS_GROUP               75%         -

In this plan, any sessions in the Sys_group consumer group will receive priority. (The sessions in this group are session SYS and system created by the user account.) Any resource allocations that are not used in the session are then shared by the sessions that belong to other consumer groups and Sys_group in the plan. In this allocation, 25% is used for maintenance tasks, 5% for background processes to perform diagnostic operations, and 70% for user sessions. For maximum utilization limit Ora$autotask_sub_plan and Ora$diagnostics are 90. Therefore, even if the CPU is idle, the plan cannot be allocated more than 90% of the CPU resources.

Related views:

dba_scheduler_window_groups;  --维护窗口组dba_scheduler_wingroup_members --维护窗口组对应窗口dba_scheduler_windows --维护窗口历史信息DBA_AUTOTASK_CLIENT_JOB --查询自动收集任务正在执行的jobDBA_AUTOTASK_JOB_HISTORY --查询自动收集任务历史执行状态DBA_AUTOTASK_CLIENT_HISTORY --查询自动收集任务历史job

Reference: Scheduler maintenance task or Autotasks (document ID 1526120.1)

ORACLE11G Automatic maintenance tasks