library cache lock mutex row cache

Library Cache Lock, Mutex and Data Dictionary Cache

UADTAO had high library cache lock and cursor: pin S wait on X contention. Let's find out detail.

Top activity shows library cache lock and cursor: pin S wait on X are on top two spots.

There are a couple of SQLs with high wait counts.

No session has dominant waits.

For "cursor: pin S wait on X", use the context menu to find out mutex holder.

Session 1442 is the mutex holder.

Session 1442 is waiting on library cache lock for 25 seconds. Use context menu to find the lock holder. Note this wait time is not very long. Frequently when use SQLPLUS for troubleshooting, the time we type or copy/paste SQL scripts, the lock will disappear and we then have no idea

what happens.

This is the lock matrix. They are plenty of sessions requesting the same library cache lock, for table TAO_LOGFILE_DELTA. It is held by session 23 on node 3 in Exclusive mode. The holder is busy, but had "latch: row cache objects" waits which frequently refers to data dictionary cache contention.

(This screenshot was taken a little bit too late.) Session 23 on node 3 now waits on  library cache lock, too. We can use the same method to find out the lock

holder. In this case, it is session 592 on the same node, which is busy and has waited on "latch: row cache objects"

Session 592 is working on a stat gathering job.

Session 23 is also working on a stat gathering job.

Let's look at shared pool for node 3. KQR L PO is part of data dictionary cache.  It is using 7881MB, or 51% of

total shared pool memory.

The top data dictionary cache is dc_histogram_defs, at 7.771M entries. Note each KQR L PO entry will use at least 1024 bytes (1KB). So dc_histogram_defs is the

reason for shared pool memory usage. dc_histogram_def is related to table column stats -  histograms. This also explains why we see "latch: row cache objects" wait

events from the stat gather jobs when those jobs need to update row cache.

A snapshot taken on another day. “row cache lock” is data dictionary cache related. Clicking

the row will show the cache name.

"row cache lock" is related to column histogram entries, too.

Data dictionary cache uses more than 4GB memory.

Column stats histogram is the major consumer.