Analysis of automatic maintenance tasks for ORACLE11G

ORACLE 11G automatic maintenance task:
An automatic maintenance task is a database maintenance operation task automatically started according to rules. For example, the system automatically collects statistics used by the query optimizer. The automatic maintenance task runs automatically in the maintenance window. The so-called automatic maintenance window is based on the predefined interval window.
The oracle11g database has three predefined Automatic maintenance tasks: ■ Automatic Optimizer Statistics Collection: ■ Automatic Segment Advisor www.2cto.com ■ Automatic SQL Tuning Advisor (this is the new Automatic maintenance task added by oracle 11g)
By default, these three tasks are configured to run in all maintenance windows. Maintenance window: The maintenance window is a continuous interval used to manage automatic maintenance tasks. The maintenance window is the oracle scheduling window and belongs to the window group MAINTENANCE_WINDOW_GROUP. 1. Configure Automatic maintenance tasks: 1. Enable and disable maintenance tasks: Use DBMS_AUTO_ADMIN pl/SQL package to enable or disable tasks: indbms_auto_task_admin.disable (client_name => 'SQL tuning advisor ', operation => NULL, window_name => NULL); END; enable the task: BEGINdbms_auto_task_admin.enable (client_name => 'SQL tuning advisor', operation => NULL, www.2cto.com window_name => NULL ); END; here, the client_name parameter can be queried through the data dictionary view DBA_AUTOTASK_CLIENT. Sys @ ORCL11> select client_name from dba_autotask_client; CLIENT_NAME using auto optimizer stats collectionauto space advisorsql tuning advisor if you want to ENABLE or DISABLE all window auto maintenance tasks, call the ENABLE or DISABLE process: EXECUTE finished;
2: enable or disable a maintenance window. By default, all maintenance tasks run in all predefined maintenance windows. You can enable or disable automation tasks in a maintenance window. BEGINdbms_auto_task_admin.disable (client_name => 'SQL tuning advisor', operation => NULL, window_name => 'Monday _ window'); END; the preceding example disables the SQL tuning advisor for the maintenance window monday_window.
Ii. Configuration and maintenance window:
However, you can use the DBMS_SCHEDULER package to modify window properties. 1: Modify the maintenance WINDOW -- disable the maintenance WINDOW BEGINdbms_scheduler.disable (name => 'Saturday _ window'); -- modify the maintenance WINDOW attribute: dbms_scheduler.set_attribute (name => 'Saturday _ Window ', attribute => 'duration', value => numtodsinterval (4, 'hour'); -- enable the maintenance WINDOW www.2cto.com dbms_scheduler.enable (name => 'Saturday _ window'); END; /For the currently opened window, you need to disable it first, and then modify and enable it. The configuration takes effect immediately. If you do not modify the attribute through these three processes, the attribute will not take effect, until the next window is opened. 2: Create a new window: Upper (window_name => 'early _ MORNING_WINDOW ', duration => numtodsinterval (1, 'hour'), resource_plan => 'default _ MAINTENANCE_PLAN ', repeat_interval => 'freq = DAILY; BYHOUR = 5; BYMINUTE = 0; BYSECOND = 0'); dbms_scheduler.add_window_group_member (group_name => 'Maintenance _ WINDOW_GROUP ', window_list => 'early _ MORNING_WINDOW '); END;/3: delete window: partition (group_name => 'Maintenance _ WINDOW_GROUP', window_list => 'early _ MORNING_WINDOW '); END; www.2cto.com/3: tracking job running status:
You can query the view worker to track the job running status: SQL> select client_name, job_name, job_start_time from worker; CLIENT_NAME JOB_NAME JOB_START_TIME when there is already auto optimizer stats ORA $ apr-12 10.0020.2.039000 PM + 08: 00 auto space advisor ORA $ AT_SA_SPC_SY_2 09-APR-12 10.0000002.050000 PM + 08: 00sql tuning advisor ORA $ AT_SQ_ SQL _SW_3 09-APR-12 10.0000002.015000 PM + 08:00 from Lao Feng's blog