We are running 11.1.0.7.0 hosting on HP UX box. From last 2 days, we were made aware of the database slowness. Once, we tried to analyze, the GATHER_STALE_STATS_JOB was overrunning. earlier , it used to run in no time. we also noticed the session running the job experienced "library cache lock" wait events. we then stopped the job and database performance was back to normal. we also extracted the sql_id from AWR for the most time spent. Also, from the AWR , the wait , which spent most of the time waiting was "library cache lock". Just wondering , what caused this job to run crawl here. I also extracted the plan of the sql , which waits most of the time for "library cache lock" and it turn out pretty gigantic. will also paste that on board.

I will really appreciate your valuable inputs.

Note :- Had to omit the predicate part to the server the limit of maximum charecters.

This explain plan above is ver normal for this job. The job is doing what it is supposed to do . if there is more work to do so , it will more time. But it should be stopped after he maintenance time.
Can you check what is the maintenance window scheduled? If it is running after the maintenance window means you are hitting some bug.

What kind of memory management are you using? Oracle attempting to move chunks of memory around in the SGA can cause the Library Cache waits. There are numerous notes available at support related to this event. The first note below covers debugging while the second is about a specific problem.

Sorry for being late on my response. Also, would like to thank you for suggesting oracle notes. We using automatic memory management here. One thing, I am concerned here is that from last 2 days , it giving us the trouble and it got fixed automatically , once the GATHER_STALE_STATS_JOB is stopped. I am sure, this job had not caused the issue , but there must be something else, which might be making the slowness to occur.

Thanks for the inputs , Mate !. Earlier , it used to run in no time , less then a minute and from last 2 days , it giving us the trouble. Also, the explain , which you claim is normal. do you think , it's because of Gather Stale Stats or something else here.

The gather stats task should run in the overnight and weekend window so unless someone launched a gather stats task during the day it should not be your problem. That is unless you are seeing the issue at night and it goes away once the stats job terminated for the day.

You should be able to look at the memory management views to see if the note from Oracle about automatic memory managment granule migration being a potential cause of the waits applies. If it looks like you have issues in this area you could set reasonable minimum pool sizes to prevent Oracle from overdoing the memory migrations and see if that helps.

Naturally you should also look at the query plan and try to determine if it is the same plan the query would have been using last week. Check the tables/indexes to see when the statistics were last updated for the object and note if any were just changed while the others were not. Sometimes a change to one objects effects what the CBO expects to find in relation to how many rows in one object correspond to how many qualifying rows in another and you need the objects to have statistics collected at the same time.

A big question is, is the problem database wide or actually limited to a specific query or application process.