Saturday, 7 June 2014

Oracle database is hanging or slow in night due to Auto job

Oracle database is hanging or slow in night

Then that could be because of default jobs running in maintenance window time in the night.

Disabling the default Maintenance job in week days(Monday,Tuesday,Wednesday and Friday) and run that in week end(Saturday and Sunday).

++ Checking the current status of Maintenance window
select client_name,status from DBA_AUTOTASK_TASK;
select client_name,status from DBA_AUTOTASK_CLIENT;

++ Sub jobs in details with days
set line 1000
set pages 1000
SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS'),sql_tune_advisor, optimizer_stats, segment_advisor
FROM DBA_AUTOTASK_WINDOW_CLIENTS;


col window_name for a20
col window_next_time for a25
select  window_name,to_char(cast(window_next_time as date),'DD/MM/YYYY HH24:MI:SS') window_next_time,
window_active,autotask_status, optimizer_stats,segment_advisor,sql_tune_advisor, health_monitor
from DBA_AUTOTASK_WINDOW_CLIENTS;

++ Disabling in week days for auto optimizer stats collection
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto optimizer stats collection',OPERATION => NULL,WINDOW_NAME => 'MONDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME =>'auto optimizer stats collection',OPERATION => NULL,WINDOW_NAME => 'TUESDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME =>'auto optimizer stats collection',OPERATION => NULL,WINDOW_NAME => 'WEDNESDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto optimizer stats collection',OPERATION => NULL,WINDOW_NAME => 'THURSDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto optimizer stats collection',OPERATION => NULL,WINDOW_NAME => 'FRIDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto optimizer stats collection',OPERATION => NULL,WINDOW_NAME => 'SATURDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto optimizer stats collection',OPERATION => NULL,WINDOW_NAME => 'SUNDAY_WINDOW');
END;
/

++ Disabling in week days for  sql tuning advisor
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'sql tuning advisor',OPERATION => NULL,WINDOW_NAME => 'MONDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME =>'sql tuning advisor',OPERATION => NULL,WINDOW_NAME => 'TUESDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME =>'sql tuning advisor',OPERATION => NULL,WINDOW_NAME => 'WEDNESDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'sql tuning advisor',OPERATION => NULL,WINDOW_NAME => 'THURSDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'sql tuning advisor',OPERATION => NULL,WINDOW_NAME => 'FRIDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'sql tuning advisor',OPERATION => NULL,WINDOW_NAME => 'SATURDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'sql tuning advisor',OPERATION => NULL,WINDOW_NAME => 'SUNDAY_WINDOW');
END;
/
++ Disabling in week days for auto space advisor
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto space advisor',OPERATION => NULL,WINDOW_NAME => 'MONDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME =>'auto space advisor',OPERATION => NULL,WINDOW_NAME => 'TUESDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME =>'auto space advisor',OPERATION => NULL,WINDOW_NAME => 'WEDNESDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto space advisor',OPERATION => NULL,WINDOW_NAME => 'THURSDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto space advisor',OPERATION => NULL,WINDOW_NAME => 'FRIDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto space advisor',OPERATION => NULL,WINDOW_NAME => 'SATURDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto space advisor',OPERATION => NULL,WINDOW_NAME => 'SUNDAY_WINDOW');
END;
/

++ Sub jobs in details with days
set line 1000
set pages 1000
SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS'),sql_tune_advisor, optimizer_stats, segment_advisor
FROM DBA_AUTOTASK_WINDOW_CLIENTS;

col window_name for a20
col window_next_time for a25
select  window_name,to_char(cast(window_next_time as date),'DD/MM/YYYY HH24:MI:SS') window_next_time,
window_active,autotask_status, optimizer_stats,segment_advisor,sql_tune_advisor, health_monitor
from DBA_AUTOTASK_WINDOW_CLIENTS;


++Note1: If you want to disable in all the days of particular job

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
END;
/

++ To enable it again

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
END;
/

++ To check
select client_name,status from DBA_AUTOTASK_TASK;
select client_name,status from DBA_AUTOTASK_CLIENT;

++Note2

To disable complete maintance window tasks.

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE;

To enable

EXEC DBMS_AUTO_TASK_ADMIN.ENABLE;

++ Other notes
If you are having issue on Auto jobs please follow below oracle metalink.
Troubleshooting Scheduler Auto tasks issues (Doc ID 1561498.1)


++ Note3:

Follow the below link for further default to disable.

http://oracletechdba.blogspot.com/2015/07/how-to-disable-defaultmaintenanceplan.html

http://oracletechdba.blogspot.com/2015/07/how-to-make-status-column-to-false-in.html

No comments: