Stop/Enable/Disable Oracle Auto Task Jobs Using SQL – Space advisor, Optimiser stats collection, SQL tuning advisor

Oracle 11g onwards there are three different automatic maintenance tasks:
1.   Automatic Optimizer Stats Collection - Gathers the statistics for all schema objects and the respective database task is called ‘auto optimizer stats collection’
2.   Automatic Segment Advisor – This identifies the segments that could be the candidates for the reorganization to save the space and respective database task is called ‘Space advisor’

3.   Automatic Tuning Advisor – This identifies and attempts to tune high load sqls and respective task is called 'sql tuning advisor'
 
Below are the respective database jobs for each task.

a.   ORA$AT_SA_SPC_SY_nnn for Space advisor tasks
b.   ORA$AT_OS_OPT_SY_nnn for Optimiser stats collection tasks
c.   ORA$AT_SQ_SQL_SW_nnn for Space advisor tasks

Example for running jobs
SQL> SELECT job_name, state FROM DBA_SCHEDULER_JOBS WHERE state='RUNNING';
 
JOB_NAME                       STATE
------------------------------ ---------------
ORA$AT_OS_OPT_SY_10727         RUNNING
ORA$AT_SA_SPC_SY_10728         RUNNING
 
Script to disable the task
 
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
PL/SQL procedure successfully completed.
 
To check the status

SQL> select client_name, status
from dba_autotask_client  2  ;
 
CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               DISABLED
sql tuning advisor                                               ENABLED
 
SQL>select WINDOW_NAME,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR from DBA_AUTOTASK_WINDOW_CLIENTS;
 
WINDOW_NAME                    OPTIMIZE SEGMENT_ SQL_TUNE
------------------------------ -------- -------- --------
MONDAY_WINDOW                  ENABLED  DISABLED ENABLED
TUESDAY_WINDOW                 ENABLED  DISABLED ENABLED
WEDNESDAY_WINDOW               ENABLED  DISABLED ENABLED
THURSDAY_WINDOW                ENABLED  DISABLED ENABLED
FRIDAY_WINDOW                  ENABLED  DISABLED ENABLED
SATURDAY_WINDOW                ENABLED  DISABLED ENABLED
SUNDAY_WINDOW                  ENABLED  DISABLED ENABLED
 
7 rows selected.
 
Script to enable the task

BEGIN
  dbms_auto_task_admin.enable(
    client_name =>  'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
 
PL/SQL procedure successfully completed.
 
SQL> select WINDOW_NAME,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR from DBA_AUTOTASK_WINDOW_CLIENTS;
 
WINDOW_NAME                    OPTIMIZE SEGMENT_ SQL_TUNE
------------------------------ -------- -------- --------
MONDAY_WINDOW                  ENABLED  ENABLED  ENABLED
TUESDAY_WINDOW                 ENABLED  ENABLED  ENABLED
WEDNESDAY_WINDOW               ENABLED  ENABLED  ENABLED
THURSDAY_WINDOW                ENABLED  ENABLED  ENABLED
FRIDAY_WINDOW                  ENABLED  ENABLED  ENABLED
SATURDAY_WINDOW                ENABLED  ENABLED  ENABLED
SUNDAY_WINDOW                  ENABLED  ENABLED  ENABLED
 

7 rows selected.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google