…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. |
