Shulou(Shulou.com)06/01 Report--

The default automatic maintenance tasks in oracle 11g fall into three categories:

Automatic Optimizer Statistics Collection (automatic optimizer statistics collection)

Collect Optimizer (optimizer) statistics for all Schema objects in the database that have no statistics or only outdated statistics. SQL query optimizer (SQL query optimizer) uses the statistics collected by this task to improve the performance of SQL execution. *

Automatic Segment Advisor (automatic Segment Guide)

Identify segments that have available recycling space and make recommendations on how to eliminate debris in these segments. You can also manually run Segment Advisor to get more up-to-date suggestions, or get suggestions for segments that are not checked by Automatic Segment Advisor that are likely to do space recycling. *

* Automatic SQL Tuning Advisor (automatic SQL optimization guide)

Check the performance of high-load SQL statements and make recommendations on how to optimize them. You can configure this guide to automatically apply the recommended SQL profile.

Enable / disable maintenance tasks:

Client_name comes from:

Select * from DBA_AUTOTASK_CLIENT

-disable

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

-enable

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 arguments:

-disable EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;--- enable EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;--- result select * from DBA_AUTOTASK_WINDOW_CLIENTS

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 backward compatibility, WEEKNIGHT_WINDOW and WEEKEND_WINDOW still exist.

The window opening duration has changed in 11g. Monday to Friday from 10:00 to 2 a.m., Saturday to Sunday from 6 a.m. to 2 a.m.

Modify the run time of the automatic task starting at 23:00 and lasting 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); 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 session in the SYS_GROUP consumer group will be given priority. (the sessions in this group are session SYS and SYSTEM created by the user account. Any unused resource allocation SYS_GROUP in the session is then shared by sessions that belong to other consumer groups and subplans in the plan Of this allocation, 25% is used for maintenance tasks, 5% for background processes that perform diagnostic operations, and 70% for user sessions. Limit ORA$AUTOTASK_SUB_PLAN and ORA$DIAGNOSTICS to 90 for maximum utilization. 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;-maintenance window group dba_scheduler_wingroup_members-maintenance window group corresponding window dba_scheduler_windows-maintenance window history information DBA_AUTOTASK_CLIENT_JOB-query automatic collection task executing jobDBA_AUTOTASK_JOB_HISTORY-query automatic collection task history execution status DBA_AUTOTASK_CLIENT_HISTORY-query automatic collection task history job

Reference: scheduler maintenance tasks or Autotasks (document ID 1526120.1)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.