Skip to content

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.

About Post Author

Share the knowledge!

Leave a Reply

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