Skip to Main Content
  • Questions

  • Library cache lock in wait class Concurrency

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 13, 2022 - 3:58 pm UTC

Last updated: January 18, 2022 - 1:58 am UTC

Version: 19C

Viewed 10K+ times! This question is

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.





We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database