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 ?
Table of contents
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.





