2 Replies Latest reply: Feb 7, 2014 12:34 AM by Nitin Chauhan RSS

    Library cache lock due to auto optimizer stats collection.

    Nitin Chauhan

      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