Hi ,
I have been facing performance issues with one of the production database ( Oracle 11.2.0.2).
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.
<code>
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
</code>
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,
<code>
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 |
| 8 | WEEKEND_WINDOW | freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 | +002 00:00:00 |
| 9 | WEEKNIGHT_WINDOW | freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 | +000 08:00:00 |
</code>
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 ?
Regards,
Nitin Chauhan