FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events

QUESTIONS AND ANSWERS

What are 'cursor: ' waits?

Any operation that manipulates or accesses cursors may be subject to waits for access to the structures that support them in the shared pool. In cases of extreme contention, these waits can become a significant bottleneck and can can constrain normal activity. From 10.2 some shared cursor operations started to be implemented by Oracle's Mutex feature and in 11g Librarycache and rowcache components were also implemented with Mutexes.

What are the most common waits?

The most commonly seen waits are :

Note that all these waits are very similar and may all be waited for in the course of an operation. eXclusive operations are those that need to change a particular structure while Share operations can make do without changes but need to briefly lock them for the duration of a change to stop them being changed by something else. The differentiation is not really relevant for diagnosis of problems other than certain waits may be more prevalent for certain problems.

What are the most common causes of waits?

Contention on these events is usually a symptom of another problem - an indicator that there is a problem elsewhere as opposed to a problem with the structure or the mechanism itself. To resolve the symptom, the root cause needs to be identified and addressed.

Waits for cursors are parse time waits and involve the loading of cursors into the shared pool or searching for those cursors. Most problems occur either because:

The number of versions of cursors in the shared pool becomes excessive
Excessive hard/soft parsing
Excessive Invalidations/reloads
Enormous objects are loaded
Inappropriately sized shared pool
The holder of the resource being scheduled Off CPU by the OS/Resource Manager
Operating system management of memory (e.g. Very large SGA on Linux x86-64 platform without the implementation of Hugepages)
Code defects

If the sharing of cursors is actively used and child cursors and versions are low in number, then it is unlikely that contention will occur.

How can I avoid these waits?

Generally, by adopting a sensible sharing strategy for cursors, using bind variables appropriately and making sure that there an not large numbers of versions, you should be able to avoid most issues of this nature. Useful articles include:

Note:62143.1 Understanding and Tuning the Shared Pool
Note:94036.1 Init.ora Parameter "CURSOR_SHARING" Reference Note

If you find that you have a large number of versions of cursors then the following articles can help:

Note:296377.1 Troubleshooting: High Version Count Issues
Note:438755.1 Formated V$SQL_SHARED_CURSOR Report by SQLID or Hash Value

Where can I find cause diagnostics and more information about these waits?

Common Diagnostics

Because these waits have similar causes, there are a number of common diagnostics that can be collected :
- History of the Issue
  Being aware of and focusing on changes that appear to trigger contention affects can help narrow down areas for examination and where deeper investigation may be merited.
  - What has recently changed?
    - Application, database, middle tier changes
    - OS change
    - Increased load
  - Common Patterns
    - Does something seem to trigger the problem?
    - Is there a specific period of time the issue is seen?
- Diagnostic Evidence
  Often a substantial quantity of diagnostics is required to pin-point an issue. This is because the waits are a symptom and the holding session may have nothing to do with the waiting session other than it happens to be blocking it.
  - AWR (or Statspack) and ASH reports (for the waiting sessions)
    These provide a system overview and also a focus on individual sessions where appropriate
  - Stack trace(s)
    Stack traces identify the code area where holders are working.
    - If the holder cannot be identified: Collect Systemstate with short stack dumps
      (Collecting information about every session should capture the holder)
    - If the holder cannot be identified: Collect Errorstacks of the waiter as this may also provide some useful information
  - Operating System statistics (e.g. OSWatcher)
    Operating system statistics are useful for various reasons such as identification of high CPU users, picking out peaks of activity and identifying 'build up' signs to help trigger early capture of data.
  Note that acquiring a systemstate on a busy system with lots of sessions can be expensive.
  If this is the case then different dumps can be produced but these will provide reduced information which may not be enough to make progress. So, from the most useful (and expensive) to the least useful please try to acquire these:
  - Systemstate(s) at level 266 (Systemstate with stacks)
  - Systemstate(s) at level 258 (less detailed Systemstate with stacks)
  - Hanganalyze output with stack traces
  As an example, if a level 266 systemstate is too expensive then you could try to use level 258. You could also combine the above. E.g., get one level 266 systemstate and multiple hanganalyze dumps. (The hanganalyze dumps should include short stack dumps).
- Data Collection Reference:
  Note:1363422.1 AWR Reports - Information Center
  Note:748642.1 How to Generate an AWR Report and Create Baselines [ID 748642.1]
  
  Note:1364257.1 How to Collect Errorstacks for use in Diagnosing Performance Issues.
  Note:452358.1 Database Hangs: What to collect for support.
  
  Note:301137.1 OS Watcher User Guide
  
  Note:1360119.1 FAQ: Database Performance Frequently Asked Questions

cursor: mutex X

Here a cursor is being parsed and is trying to get the cursor mutex in eXclusive mode

cursor: mutex S

Here a cursor is being parsed and is trying to get the cursor mutex in Share mode

Note:9591812.8 Bug 9591812 - Wrong wait events in 11.2 ("cursor: mutex S" instead of "cursor: mutex X")

cursor: pin X

Here a cursor is being parsed and is trying to get the cursor pin in eXclusive mode

cursor: pin S

Here a cursor is being parsed and is trying to get the cursor pin in Share mode. See:

Note:1310764.1 WAITEVENT: "cursor: pin S" Reference Note

If contention is seen on this event, it is better to look for other related waits and investigate them first, as it is likely that this is a symptom of those.

cursor: pin S wait on X

Here a cursor is being parsed and has the cursor pin and is trying to get it in eXclusive mode. If you see 'cursor: pin S wait on X' prominent in the 'Top Waits' section of AWR, like here for example:

then it would be sensible to initially look at the number of versions of cursors that you have on the system:

and if they are anywhere remotely close to the exceptionally high numbers shown here, reduce them as a matter of priority. See:

Document 296377.1 Troubleshooting: High Version Count Issues

If this is not the case then you can use the following articles to help you debug issues:

Note:1349387.1 Troubleshooting 'cursor: pin S wait on X' waits
Note:1298015.1 WAITEVENT: "cursor: pin S wait on X" Reference Note
Note:786507.1 How to Determine the Blocking Session for Event: 'cursor: pin S wait on X'

Note:742599.1 High 'cursor: pin S wait on X' and/or 'library cache lock' Waits Generated by Frequent Shared Pool/Buffer Cache Resize Activity

Note:1268724.1 "Cursor: Pin S Wait On X" Contention Mutex Sleep Reason Primarily ' kkslce [KKSCHLPIN2]'
Note:402027.1 Bug:5653007; 5485914: SELF DEADLOCK PROCESS WAITS ON ''Cursor: Pin S Wait On X'' with SQL_TRACE enabled.
Note:9472669.8 Bug 9472669 - 'cursor: pin S wait on X' waits for invalid SQL over DB link

library cache: mutex X

Here a library cache operation is being performed and is trying to get the library cache mutex in eXclusive mode.

The "library cache: mutex X" symptom is very common and can be caused by a number of issues, so it is very important to fully understand the root cause in order to determine the right action. In numerous cases, the problem can be alleviated by a change to the application (preventing logon or logoff storms is an example). Also it is possible that a known Oracle bug is being encountered (this includes both mutex-related issues and non-mutex issues that had mutex waits as a symptom).

Note:1357946.1 Troubleshooting 'library cache: mutex X' waits.

Note:727400.1 WAITEVENT: "library cache: mutex X"
Note:758674.1 " Library Cache: Mutex X " On Koka Cursors (LOBs) Non-Shared :
Note:9530750.8 Bug 9530750 - High waits for 'library cache: mutex X' for cursor Build lock

library cache: mutex S

Here a library cache operation is being performed and is trying to get the library cache mutex in Share mode

Useful References

Note:34579.1 WAITEVENT: "library cache pin" Reference Note

REFERENCES

NOTE:742599.1 - High 'cursor: pin S wait on X' and/or 'library cache lock' Waits. Cause: Shared Pool/Buffer Cache Resize Activity
NOTE:748642.1 - How to Generate an AWR Report and Create Baselines
NOTE:62143.1 - Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
NOTE:786507.1 - How to Determine the Blocking Session for Event: 'cursor: pin S wait on X'
NOTE:361468.1 - HugePages on Oracle Linux 64-bit
NOTE:94036.1 - Init.ora Parameter "CURSOR_SHARING" Reference Note
NOTE:9472669.8 - Bug 9472669 - 'cursor: pin S wait on X' waits for invalid SQL over DB link
NOTE:727400.1 - WAITEVENT: "library cache: mutex X"
NOTE:758674.1 - " Library Cache: Mutex X " On Koka Cursors (LOBs) Non-Shared :
NOTE:1363422.1 - Automatic Workload Repository (AWR) Reports - Start Point
NOTE:296377.1 - Troubleshooting: High Version Count Issues
NOTE:301137.1 - OSWatcher (Includes: [Video])
NOTE:438755.1 - High SQL Version Counts - Script to determine reason(s)
NOTE:452358.1 - How to Collect Diagnostics for Database Hanging Issues
NOTE:9530750.8 - Bug 9530750 - High waits for 'library cache: mutex X' for cursor Build lock
NOTE:1360119.1 - * FAQ: Database Performance Frequently Asked Questions
NOTE:1364257.1 - How to Collect Errorstacks for use in Diagnosing Performance Issues.
NOTE:402027.1 - Bug:5653007; 5485914: SELF DEADLOCK PROCESS WAITS ON ''Cursor: Pin S Wait On X'' with SQL_TRACE enabled.

NOTE:9591812.8 - Bug 9591812 - Wrong wait events in 11.2 ("cursor: mutex S" instead of "cursor: mutex X")
NOTE:34579.1 - WAITEVENT: "library cache pin" Reference Note
BUG:5653007 - SELF DEADLOCK PROCESS WAITS ON ''CURSOR: PIN S WAIT ON X''

BUG:9591812 - INCORRECT WAIT EVENTS IN 11.2 ("CURSOR: MUTEX S" INSTEAD OF "CURSOR: MUTEX X")

NOTE:1268724.1 - "Cursor: Pin S Wait On X" Contention Mutex Sleep Reason Primarily ' kkslce [KKSCHLPIN2]'
NOTE:1298015.1 - WAITEVENT: "cursor: pin S wait on X" Reference Note
NOTE:1310764.1 - WAITEVENT: "cursor: pin S" Reference Note
NOTE:1349387.1 - Troubleshooting 'cursor: pin S wait on X' waits.
NOTE:1357946.1 - Troubleshooting 'library cache: mutex X' waits.

==================================================================

Note---> This informationmation taken from oracle metalink. all copy rights oracle only.

Oracle DBA Information at one place

Search This Blog