Resolving Issues of "Library Cache Pin" or "Cursor Pin S wait on X" (Doc ID 1476663.1)

In this Document

APPLIES TO:

PURPOSE

TROUBLESHOOTING STEPS

 Brief Definition:

This note covers issues where the Guided Resolution Tool indicates contention as a result "library cache pins" or "Cursor Pin S wait on X".

In 10g and later, library cache pins are replaced by mutexes. So library cache pins and Pin S wait on X are similar waits but different names for different versions.

在这里 官方明确指出  library cache pin 被 cursor pin S wait on X  替换 。

Problem Confirmation:

• Library cache pin for pre 10g may be significant
• Cursor: Pin S wait on X may be significant on 10g and later
• High CPU with per above waits

Library Cache Pin

Library cache pin deals with concurrent wait for library cache.

When a pin is acquired, it causes the object’s heap to be loaded into memory if it is not already there.

A wait for a "library cache pin" implies some other session holds that PIN in an incompatible mode.

（比方说 2 shared与3 exclusive 模式不兼容，导致互相排斥，引起此等待事件）

For example, if an object structure is changed such as dropping a column, it will cause library cache pin wait and implies it is held in exclusive mode. 

So be careful when changing the structure of an object and try to do it during slow time of the day.

For more information on scenarios of library cache pin, review the following note:

Cursor Pin S wait on X

Mutex wait is similar to library cache pin wait but was introduced for 11g.  A session waits for "cursor: pin S wait on X" when it wants a mutex in S (share) mode on a specific cursor but some other session holds that same mutex in X (exclusive) mode. Hence the current session has to wait for the X mode holder to release the mutex.

Improving Performance of Library cache pin or Pin S wait on X

The library cache pin wait often happens when you are compiling or parsing a PL/SQL object or a view.  Recompilation may be due to invalidations.  Invalidations are usually due to ddls.  So check for ddls at the time of the issue.  For further information, review following note:

 Pin S wait on X is usually due to high parses.  So check for high parsing sqls or tune application to share cursors.  For further diagnosis, please review following note:

Measuring Success

Once you have applied the changes to resolve the issues you have found, compare the latest AWR to the AWR that led you here via Guided Resolution (that AWR becomes your baseline). Look at the percentage decrease total wait time for this event. If there are still issues, re- evaluate those and address them according to the specific symptom.

Known Issues