How to deactivate segment advisor…

…Without Oracle Grid Control, unless would be too easy…

On very big databases segment advisor daily / week-end job is a performance killer and you normally never check the result or not more than around twice a year. So why not deactivate it and launch it manually on a small perimeter (per tablespace) and really work on the result ?

Deactivate segment advisor with Oracle 10gR2

Is the job activated on your database ?:

col owner FOR a10
col comments FOR a60
SET lines 130
SELECT OWNER,JOB_NAME,ENABLED,COMMENTS FROM DBA_SCHEDULER_JOBS;
 
OWNER      JOB_NAME                       ENABL COMMENTS
---------- ------------------------------ ----- ------------------------------------------------------------
SYS        PURGE_LOG                      TRUE  purge LOG job
SYS        FGR$AUTOPURGE_JOB              FALSE FILE GROUP auto-purge job
SYS        GATHER_STATS_JOB               TRUE  Oracle defined automatic optimizer STATISTICS collection job
SYS        AUTO_SPACE_ADVISOR_JOB         TRUE  auto SPACE advisor maintenance job
ORACLE_OCM MGMT_CONFIG_JOB                TRUE  Configuration collection job.
ORACLE_OCM MGMT_STATS_CONFIG_JOB          TRUE  OCM STATISTICS collection job.
 
6 ROWS selected.

Job name is AUTO_SPACE_ADVISOR_JOB so activated on your database (GATHER_STATS_JOB is the optimizer automatic gathering statistics).

Has it already killed your server, let’s check previous runs:

SELECT * FROM
(SELECT log_date,job_name,status,actual_start_date,run_duration
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name='AUTO_SPACE_ADVISOR_JOB'
ORDER BY log_id DESC)
WHERE rownum<=2;
 
LOG_DATE
---------------------------------------------------------------------------
JOB_NAME                                                          STATUS
----------------------------------------------------------------- ------------------------------
ACTUAL_START_DATE
---------------------------------------------------------------------------
RUN_DURATION
---------------------------------------------------------------------------
16-FEB-10 10.22.50.458799 PM +01:00
AUTO_SPACE_ADVISOR_JOB                                            SUCCEEDED
16-FEB-10 10.00.03.594673 PM +01:00
+000 00:22:47
 
LOG_DATE
---------------------------------------------------------------------------
JOB_NAME                                                          STATUS
----------------------------------------------------------------- ------------------------------
ACTUAL_START_DATE
---------------------------------------------------------------------------
RUN_DURATION
---------------------------------------------------------------------------
16-FEB-10 12.24.08.046837 AM +01:00
AUTO_SPACE_ADVISOR_JOB                                            SUCCEEDED
15-FEB-10 10.00.02.574503 PM +01:00
+000 02:24:05

So previous run completed in 22 minutes and 2 and half hours…!

When is running segment advisor job ?

col SCHEDULE_NAME FOR a30
SELECT JOB_NAME,SCHEDULE_TYPE,SCHEDULE_NAME FROM DBA_SCHEDULER_JOBS;
 
JOB_NAME                       SCHEDULE_TYP SCHEDULE_NAME
------------------------------ ------------ ------------------------------
PURGE_LOG                      NAMED        DAILY_PURGE_SCHEDULE
FGR$AUTOPURGE_JOB              CALENDAR
GATHER_STATS_JOB               WINDOW_GROUP MAINTENANCE_WINDOW_GROUP
AUTO_SPACE_ADVISOR_JOB         WINDOW_GROUP MAINTENANCE_WINDOW_GROUP
MGMT_CONFIG_JOB                WINDOW_GROUP MAINTENANCE_WINDOW_GROUP
MGMT_STATS_CONFIG_JOB          CALENDAR
 
6 ROWS selected.

So running only during MAINTENANCE_WINDOW_GROUP group window…

SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;
 
WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ -----------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW

So group window is made of two windows, let’s see when they run:

SELECT WINDOW_NAME,REPEAT_INTERVAL FROM DBA_SCHEDULER_WINDOWS;
 
WINDOW_NAME       REPEAT_INTERVAL
----------------- --------------------------------------------------------------------------------
WEEKNIGHT_WINDOW  freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
WEEKEND_WINDOW    freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

Or

col LAST_START_DATE FOR a40
col WINDOW_NAME FOR a17
col NEXT_START_DATE FOR a40
col DURATION FOR a15
SET lines 130
SELECT WINDOW_NAME,NEXT_START_DATE,LAST_START_DATE,duration FROM DBA_SCHEDULER_WINDOWS;
 
WINDOW_NAME       NEXT_START_DATE                          LAST_START_DATE                          DURATION
----------------- ---------------------------------------- ---------------------------------------- ---------------
WEEKNIGHT_WINDOW  17-FEB-10 10.00.00.500000 PM +02:00      16-FEB-10 10.00.00.498709 PM +02:00      +000 08:00:00
WEEKEND_WINDOW    20-FEB-10 12.00.00.000000 AM +02:00      13-FEB-10 06.00.02.444867 AM +02:00      +002 00:00:00

WEEKNIGHT_WINDOW from 22:00 for 8 hours (so till 06:00) every working days, and WEEKEND_WINDOW from Saturday midnight for 2 days (so till Sunday midnight).

So finally deactivate it with:

EXECUTE DBMS_SCHEDULER.DISABLE('AUTO_SPACE_ADVISOR_JOB');
 
PL/SQL PROCEDURE successfully completed.

You can confirm it has been done with:

col owner FOR a10
col comments FOR a60
SET lines 130
SELECT OWNER,JOB_NAME,ENABLED,COMMENTS FROM DBA_SCHEDULER_JOBS;
 
OWNER      JOB_NAME                       ENABL COMMENTS
---------- ------------------------------ ----- ------------------------------------------------------------
SYS        PURGE_LOG                      TRUE  purge LOG job
SYS        FGR$AUTOPURGE_JOB              FALSE FILE GROUP auto-purge job
SYS        GATHER_STATS_JOB               TRUE  Oracle defined automatic optimizer STATISTICS collection job
SYS        AUTO_SPACE_ADVISOR_JOB         FALSE auto SPACE advisor maintenance job
ORACLE_OCM MGMT_CONFIG_JOB                TRUE  Configuration collection job.
ORACLE_OCM MGMT_STATS_CONFIG_JOB          TRUE  OCM STATISTICS collection job.
 
6 ROWS selected.

Your database should work better during the night…

Deactivate segment advisor with Oracle 11gR2

In Oracle 11gR2 things are a little bit different and a bit much simpler. All automatic tasks are manage outside the normal Oracle scheduler views. So to check if job is running (as well as other automatic tasks):

SQL> SELECT client_name, status FROM dba_autotask_client;
 
CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto SPACE advisor                                               ENABLED
SQL tuning advisor                                               ENABLED

To check how many jobs have been created by the task:

SQL> SET lines 200
SQL> SELECT window_name, jobs_created, jobs_started, jobs_completed
FROM DBA_AUTOTASK_CLIENT_HISTORY
WHERE client_name='auto space advisor'
AND window_start_time >= SYSDATE -7
ORDER BY window_start_time DESC;
 
WINDOW_NAME                                                       JOBS_CREATED JOBS_STARTED JOBS_COMPLETED
----------------------------------------------------------------- ------------ ------------ --------------
SUNDAY_WINDOW                                                                5            5              5
SATURDAY_WINDOW                                                              5            5              5
FRIDAY_WINDOW                                                                1            1              1
THURSDAY_WINDOW                                                              1            1              1
WEDNESDAY_WINDOW                                                             1            1              1
TUESDAY_WINDOW                                                               1            1              1
MONDAY_WINDOW                                                                1            1              1
 
7 ROWS selected.

Finally to get jobs’ duration:

SQL> col job_name FOR a30
SQL> col WINDOW_NAME FOR a30
SQL> SET pages 100
SQL> SELECT window_name,job_name, job_status, job_duration
FROM DBA_AUTOTASK_JOB_HISTORY
WHERE client_name='auto space advisor'
AND window_start_time >= SYSDATE -7
ORDER BY job_start_time DESC;
 
WINDOW_NAME                    JOB_NAME                       JOB_STATUS                     JOB_DURATION
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
SUNDAY_WINDOW                  ORA$AT_SA_SPC_SY_5057          SUCCEEDED                      +000 00:02:19
SUNDAY_WINDOW                  ORA$AT_SA_SPC_SY_5055          SUCCEEDED                      +000 00:02:38
SUNDAY_WINDOW                  ORA$AT_SA_SPC_SY_5053          SUCCEEDED                      +000 00:02:03
SUNDAY_WINDOW                  ORA$AT_SA_SPC_SY_5051          SUCCEEDED                      +000 00:01:22
SUNDAY_WINDOW                  ORA$AT_SA_SPC_SY_5048          SUCCEEDED                      +000 00:08:21
SATURDAY_WINDOW                ORA$AT_SA_SPC_SY_5046          SUCCEEDED                      +000 00:02:08
SATURDAY_WINDOW                ORA$AT_SA_SPC_SY_5044          SUCCEEDED                      +000 00:01:44
SATURDAY_WINDOW                ORA$AT_SA_SPC_SY_5042          SUCCEEDED                      +000 00:02:42
SATURDAY_WINDOW                ORA$AT_SA_SPC_SY_5040          SUCCEEDED                      +000 00:02:08
SATURDAY_WINDOW                ORA$AT_SA_SPC_SY_5037          SUCCEEDED                      +000 00:06:10
FRIDAY_WINDOW                  ORA$AT_SA_SPC_SY_5034          SUCCEEDED                      +000 00:13:54
THURSDAY_WINDOW                ORA$AT_SA_SPC_SY_5031          SUCCEEDED                      +000 00:23:06
WEDNESDAY_WINDOW               ORA$AT_SA_SPC_SY_5028          SUCCEEDED                      +000 00:12:03
TUESDAY_WINDOW                 ORA$AT_SA_SPC_SY_5008          SUCCEEDED                      +000 00:20:31
MONDAY_WINDOW                  ORA$AT_SA_SPC_SY_4999          SUCCEEDED                      +000 01:52:31
 
15 ROWS selected.

You can disable the automatic task with:

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT client_name, status FROM dba_autotask_client;
 
CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto SPACE advisor                                               DISABLED
SQL tuning advisor                                               ENABLED

If later on you want to re-enable it, you can specify a particular window where to re-activate it, use:

SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE (client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
 
PL/SQL PROCEDURE successfully completed.

One thought on “How to deactivate segment advisor…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>