You Asked
In the Production Application we randomly notice a hiccup (slowness) in the database. The AWR shows as ‘Waiting for event "library cache lock" in wait class "Concurrency"’. The respective SQL statement is one of the frequently running statement (15,000 times per minute) in the application. The SQL is a simple READ on primary key from a table which is cached in a logical memory. The table holds just 40,000 records and doesn’t grow at runtime. During the regular season, the same SQL statement elapsed time would be in milliseconds. But during the hiccup (slowness) time, the same SQL statement takes minutes to return the results. The other important thing to note is that the issue initiates most of the time at either 0th minute, 30th minute or 45th minute of an hour and resolves its own in couple of minutes. However, sometime the issue lasts more than an hour before it resolves its own. We would appreciate if you can help on what could be the reason the SQL performs slower for few minutes randomly.
and Connor said...
A library cache lock means that some sessions are competing over a common resource in the library cache. Unfortunately, there is a whole raft of things that could be the cause, eg
- Excessive unshared SQL Due to Literals
- Shared memory pressure (hence SQL being aged out)
- Excessive Library cache object invalidations (DDL etc)
- Objects being compiled across sessions
- Excessive auditing options
- Excessive use of row level triggers
- Excessive Amount of Child Cursors (multiple plans, bugs etc)
MOS note 1952395.1 takes each in turn and talks about how to analyze, workaround and solve these.
Given the "predictability" of the timing, I'd be looking at scheduled tasks (either inside or outside the database) that are marking objects as invalid, or perhaps overly-aggressive calculating optimizer stats which would then potentially create parse storms
Since they last so long, v$active_session_history should be able to show you the session that *holds* the lock, and you can focus on what it is doing.
Is this answer out of date? If it is, please let us know via a Comment