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.
Yannick Jaquier on LinkedinYannick Jaquier on RssYannick Jaquier on Twitter
Yannick Jaquier
Find more about me on social media.

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>