【MOS】常见问题:'cursor:mutex ..'/ 'cursor:pin ..'/ 'library cache:mutex ..'类型的等待事件 (文档 ID 1525791.1)
文档内容
用途 |
问题和答案 |
什么是 'cursor: ' 等待事件? |
最常见的等待事件是什么? |
等待事件最常见的原因是什么? |
如何避免这些等待事件? |
可以在什么位置找到原因诊断以及关于这些等待事件的更多信息? |
有用参考 |
参考 |
Oracle Database - Enterprise Edition - 版本 10.1.0.2 和更高版本
Oracle Database - Standard Edition - 版本 10.1.0.2 和更高版本
Oracle Database - Personal Edition - 版本 10.1.0.2 和更高版本
本文档所含信息适用于所有平台
本文章针对与CURSOR(游标)管理活动相关的等待事件提供了一些核心要点信息。
处理或访问cursor的任何操作都可能需要等待,才能访问在 shared pool 中支持这些操作的结构。在极限争用的情况下,这些等待事件就会成为一个显著瓶颈,继而束缚正常活动。从版本 10.2 开始,一些共享cursor操作开始由 Oracle 的 Mutex 功能实施,在 11g 中 Librarycache 和 rowcache 组件也通过 Mutex 实施。
最常见的等待事件包括:
请注意,所有这些等待事件都非常相似,并且可能都是在一个操作过程中产生的等待。eXclusive(排他)操作是需要更改特定结构的那些操作;Share(共享)操作可以在不更改的情况下进行,只是需要在更改过程中暂时锁定这些操作,以防止其被其他项更改。这点区别实际对于问题的诊断并没有太多关联,只是特定等待事件可能在特定问题中更为常见。
基于这些事件的争用通常是另一个问题的症状表现 – 即问题是其它地方产生的,而不是 mutex 机制本身。如果需要解决 mutex 竞争这个问题表现,我们需要识别问题根本原因并加以处理。
Cursor 相关等待事件是 SQL 语句在 parse 时产生的,包括将cursor加载到 shared pool 中或在其中搜索那些cursor。出现这些问题可能的原因:
如果 cursor 共享的很好,并且 child cursor 和版本数量较低,那么一般不会产生这种类型的争用。
通常,通过采用合理的cursor共享策略,正确使用绑定变量并确保没有大量版本,应该能够避免大多数这类性质的问题。有用的文章包括:
Note:62143.1 Understanding and Tuning the Shared Pool
Note:94036.1 Init.ora Parameter "CURSOR_SHARING" Reference Note
如果您发现自己有大量 cursor 版本,参阅以下文章可能会有所帮助:
Note:296377.1 Troubleshooting: High Version Count Issues
Note:438755.1 Formated V$SQL_SHARED_CURSOR Report by SQLID or Hash Value
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:175006.1 Steps to generate HANGANALYZE trace files.
Note:301137.1 OS Watcher User Guide
Note:1360119.1 FAQ: Database Performance Frequently Asked Questions
Note:9591812.8 Bug 9591812 - Wrong wait events in 11.2 ("cursor: mutex S" instead of "cursor: mutex X")
Note:1310764.1 WAITEVENT: "cursor: pin S" Reference Note
Document 296377.1 Troubleshooting: High Version Count 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
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
Note:34579.1 WAITEVENT: "library cache pin" Reference Note
NOTE:1298015.1
- WAITEVENT: "cursor: pin S wait on X" Reference Note
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:786507.1
- How to Determine the Blocking Session for Event: 'cursor: pin S wait on X'
NOTE:94036.1
- Init.ora Parameter "CURSOR_SHARING" Reference Note
NOTE:758674.1
- " Library Cache: Mutex X " On Koka Cursors (LOBs) Non-Shared :
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:742599.1
- High 'cursor: pin S wait on X' and/or 'library cache lock' Waits. Cause: Shared Pool/Buffer Cache Resize Activity
NOTE:175006.1
- Steps to generate HANGANALYZE trace files (9i and below)
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: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
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:361468.1
- HugePages on Oracle Linux 64-bit
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:1360119.1
- * FAQ: Database Performance Frequently Asked Questions
NOTE:1363422.1
- Automatic Workload Repository (AWR) Reports - Start Point
NOTE:402027.1
- Bug:5653007; 5485914: SELF DEADLOCK PROCESS WAITS ON ''Cursor: Pin S Wait On X'' with SQL_TRACE enabled.
BUG:5653007
- SELF DEADLOCK PROCESS WAITS ON ''CURSOR: PIN S WAIT ON X''
NOTE:1364257.1
- How to Collect Errorstacks for use in Diagnosing Performance Issues.