2 Replies Latest reply: Feb 6, 2014 10:34 PM by Nitin Chauhan RSS

Library cache lock due to auto optimizer stats collection.

Nitin Chauhan Newbie
Currently Being Moderated

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;

1FRIDAY_WINDOWfreq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0+000 04:00:00
2MONDAY_WINDOWfreq=daily;byday=MON;byhour=22;byminute=0; bysecond=0+000 04:00:00
3SATURDAY_WINDOWfreq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0+000 20:00:00
4SUNDAY_WINDOWfreq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0+000 20:00:00
5THURSDAY_WINDOWfreq=daily;byday=THU;byhour=22;byminute=0; bysecond=0+000 04:00:00
6TUESDAY_WINDOWfreq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0+000 04:00:00
7WEDNESDAY_WINDOWfreq=daily;byday=WED;byhour=22;byminute=0; bysecond=0+000 04:00:00
8WEEKEND_WINDOWfreq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0+002 00:00:00
9WEEKNIGHT_WINDOWfreq=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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points