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:
Post a Comment