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

advertisements

_____________________________________________________________________________________________________________________

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.

_____________________________________________________________________________________________________________________

Website Stats