I have been facing performance issues with one of the production database ( Oracle 184.108.40.206).
When it was investigated using performance reports , one of the top wait event was library cache lock.
so after digging deep , i found some sessions whose event showed library cache lock ,And there was the culprit hiding in plain site , dbms_scheduler.
This scheduler belonged to auto optimizer task , i had to kill the session in question , since the issue rose during peak time.
After investigating below information was found.
CLIENT_NAME JOB_STATUS JOB_DURATION
auto optimizer stats collection STOPPED +000 19:59:57
auto optimizer stats collection STOPPED +000 19:59:58
auto optimizer stats collection STOPPED +000 03:59:56
auto optimizer stats collection STOPPED +000 13:26:15
Status of last four jobs show stopped while the jobs before that shows success , also there is a lot of difference in job duration ,
Job duration of last jobs shows as 20 hours and 4 hours , While the jobs before that shows random hours and no job showed its duration as 20 Hours.
I also know that default job duration is set to 20 hours and 4 hours.
Below is the required information about jobs,
select window_name, repeat_interval, duration from dba_scheduler_windows order by window_name;
|1||FRIDAY_WINDOW||freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0||+000 04:00:00|
|2||MONDAY_WINDOW||freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0||+000 04:00:00|
|3||SATURDAY_WINDOW||freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0||+000 20:00:00|
|4||SUNDAY_WINDOW||freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0||+000 20:00:00|
|5||THURSDAY_WINDOW||freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0||+000 04:00:00|
|6||TUESDAY_WINDOW||freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0||+000 04:00:00|
|7||WEDNESDAY_WINDOW||freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0||+000 04:00:00|
|9||WEEKNIGHT_WINDOW||freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0||+000 08:00:00|
I know there can be some confusion about above explanation , But please feel free to ask anything , I'll be happy to share whatever details required.
My question here is
1. job status shows as stopped , does it mean that the job has failed and when it would start , Auto tune job would start from 0 ?
Or will it start from where it has been left ?
2. Job previous to last four showed success and required 1-2 hours ,while these new jobs require 20 Hours , How do i find or investigate about this change ?
Thanks for reply.
Today when i queried dba_autotask_job_history , The same job succeeded and job duration was only 10 minutes.
And yes The job stopped due to window closure.
Seems autotune task started off from where it last stopped and completed the task.