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

Automatic maintenance tasks are provided in the Oracle database to ensure that the Oracle database can run more efficiently. There are also differences in the types and execution of automated tasks in different versions of Oracle. In this paper, 10g and 11g are mainly used to understand the automatic tasks in Oracle.

1. Types

There are two types of automatic tasks in 10g:

AUTO_SPACE_ADVISOR_JOB, Automated Space Advisor JOB

GATHER_STATS_JOB, automatic collection of statistics JOB

There are three types of automatic tasks in 11g:

Auto optimizer stats collection, automatic statistics collection consultant

Auto space advisor, automatic space consultant

Sql tuning advisor,sql tuning Advisor

2. The way of realization

The automatic tasks in 10g are run as SCHEDULER's JOB, and the relevant JOB can be found in the dba_scheduler_jobs view.

SQL > set linesize 200SQL > col owner for a30SQL > col job_name for a30SQL > select owner,job_name from dba_scheduler_jobs where job_class='AUTO_TASKS_JOB_CLASS' OWNER JOB_NAME---SYS AUTO_SPACE_ADVISOR_JOBSYS GATHER_STATS_JOB

The automated tasks in 11g are implemented using 11g's new feature AUTOTASK. The relevant information can be found in the dba_autotask_client view.

Sys@TEST > SELECT client_name,status FROM dba_autotask_client;CLIENT_NAME STATUS-- auto optimizer stats collection ENABLEDauto space advisor ENABLEDsql tuning advisor ENABLED

3. Maintenance window time

The execution time of automatic tasks is different in the two versions.

10g is divided into two time periods, WEEKNIGHT_WINDOW and WEEKEND_WINDOW, which can be found in the dba_scheduler_windows view. Their running start times and durations are:

WEEKNIGHT_WINDOW, which starts at 22:00:00 every Monday through Friday night and lasts 8 hours

WEEKEND_WINDOW, Saturday, 00: 00:00, lasting 2 days

The maintenance window in 11g is divided into seven, that is, the maintenance window on the first day, which can be adjusted more finely according to different requirements, which can be found in the dba_scheduler_windows view, which is MONDAY_WINDOW,TUESDAY_WINDOW,WEDNESDAY_WINDOW,THURSDAY_WINDOW,FRIDAY_WINDOW,SATURDAY_WINDOW,SUNDAY_WINDOW. The start time and duration are:

It starts at 22:00:00 from Monday to Friday and lasts for 4 hours, and starts at 6:00:00 on Saturday and Sunday for 20 hours.

4. Maintenance mode

Due to the different ways of implementation in 10g and 11g, the maintenance methods are naturally different.

10g is maintained in the same way as a normal SCHEDULER JOB.

-- disable JOBSQL > exec dbms_scheduler.disable ('AUTO_SPACE_ADVISOR_JOB'); PL/SQL procedure successfully completed.SQL > select enabled from dba_scheduler_jobs where job_name='AUTO_SPACE_ADVISOR_JOB';ENABLED-FALSE-- enable JOBSQL > exec dbms_scheduler.ENABLE (' AUTO_SPACE_ADVISOR_JOB'); PL/SQL procedure successfully completed.SQL > select enabled from dba_scheduler_jobs where job_name='AUTO_SPACE_ADVISOR_JOB' ENABLED-TRUE

The following focuses on the maintenance of automatic tasks in 11g.

1) disable and enable automatic maintenance tasks

-- deactivate sql tuning advisorsys@TEST > BEGIN 2 dbms_auto_task_admin.disable (3 client_name = > 'sql tuning advisor', 4 operation = > NULL, 5 window_name = > NULL); 6 END; 7 / PL/SQL procedure successfully completed.sys@TEST > select client_name,status from dba_autotask_client CLIENT_NAME STATUS-- auto optimizer stats collection ENABLEDauto space advisor ENABLEDsql tuning advisor DISABLED -- enable sql tuning advisorsys@TEST > BEGIN 2 dbms_auto_task_admin.enable (3 client_name = > 'sql tuning advisor' 4 operation = > NULL, 5 window_name = > NULL) 6 END; 7 / PL/SQL procedure successfully completed.sys@TEST > select client_name,status from dba_autotask_client CLIENT_NAME STATUS-- auto optimizer stats collection ENABLEDauto space advisor ENABLEDsql tuning advisor ENABLED -- disable all automatic tasks under all maintenance windows sys@TEST > exec dbms_auto_task_admin.disable PL/SQL procedure successfully completed.sys@TEST > select window_name,autotask_status from dba_autotask_window_clients WINDOW_NAME AUTOTASK_STATUS-- MONDAY_WINDOW DISABLEDTUESDAY_WINDOW DISABLEDWEDNESDAY_WINDOW DISABLEDTHURSDAY_WINDOW DISABLEDFRIDAY_WINDOW DISABLEDSATURDAY_WINDOW DISABLEDSUNDAY_WINDOW DISABLED7 rows selected.-- enables all automatic tasks under all maintenance windows sys@TEST > exec dbms_auto_task_admin.enable PL/SQL procedure successfully completed.sys@TEST > select window_name,autotask_status from dba_autotask_window_clients WINDOW_NAME AUTOTASK_STATUS-- WEDNESDAY_WINDOW ENABLEDFRIDAY_WINDOW ENABLEDSATURDAY_WINDOW ENABLEDTHURSDAY_WINDOW ENABLEDTUESDAY_WINDOW ENABLEDSUNDAY_WINDOW ENABLEDMONDAY_WINDOW ENABLED7 rows selected.-- deactivates automatic tasks sys@TEST > BEGIN 2 dbms_auto_task_admin.disable (3 client_name = > 'sql tuning advisor') for a maintenance window 4 operation = > NULL, 5 window_name = > 'MONDAY_WINDOW') 6 END; 7 / PL/SQL procedure successfully completed.sys@TEST > select window_name,autotask_status,sql_tune_advisor from dba_autotask_window_clients WINDOW_NAME AUTOTASK_STATUS SQL_TUNE_ADVISOR---MONDAY_WINDOW ENABLED DISABLEDTUESDAY_WINDOW ENABLED ENABLEDWEDNESDAY_WINDOW ENABLED ENABLEDTHURSDAY_WINDOW ENABLED ENABLEDFRIDAY_WINDOW ENABLED ENABLEDSATURDAY_WINDOW ENABLED ENABLEDSUNDAY_WINDOW ENABLED ENABLED7 rows selected.

2. Configure the maintenance window

This is similar to the 10g one, using the dbms_scheduler package.

-modify the properties of the maintenance window to change the duration of the SATURDAY_WINDOW to 4 hours sys@TEST > BEGIN 2 dbms_scheduler.disable (3 name = > 'SATURDAY_WINDOW'); 4 dbms_scheduler.set_attribute (5 name = >' SATURDAY_WINDOW', 6 attribute = > 'DURATION', 7 value = > numtodsinterval (4,' hour')); 8 dbms_scheduler.enable (9 name = > 'SATURDAY_WINDOW'); 10 END 11 / PL/SQL procedure successfully completed.sys@TEST > SELECT duration FROM dba_scheduler_windows WHERE window_name='SATURDAY_WINDOW';DURATION----+000 04:00:00

11g automatic maintenance tasks refer to the official document: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN12344

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.