I have been facing performance issues with one of the production database ( Oracle 18.104.22.168).
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 ?