« Oracle Mutex 等待事件之: cursor pin X | Blog首页 | Oracle Mutex 等待事件之: cursor mutex X »
Oracle Mutex 等待事件之: cursor: mutex S
链接：https://www.eygle.com/archives/2017/07/oracle_mutex_cursor_mutex_s.html
Cursor: Mutex S 等待事件是指，一个会话以共享模式请求一个Mutex，而其他会话以排他模式正在持有Cursor 上的 Mutex。
从文档说明可以看到，此处的Mutex是位于 Cursor 对象上的固有 Mutex，也就是针对 Parent Cursor 的。这个等待的第一个参数会披露出 SQL 的 Hash Value。
A session waits on this event when it is requesting a mutex in shared mode, when another session is currently holding a this mutex in exclusive mode on the same cursor object.
| Parameter | Description |
|---|---|
|
P1 |
Hash value of cursor |
|
P2 |
Mutex value (top 2 bytes contain SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0) |
|
P3 |
Mutex where (an internal code locator) OR'd with Mutex Sleeps |
通常 Mutex S 是一种很少见的等待事件，在 BUG 16175381 中提供了一种可能性：
Bug 16175381 Process spin in kkscsSearchChildList() -> kkshGetNextChild() with fix for bug 14613900 present
这个BUG 影响 12.2 以下的主要版本，包括 11.2.0.2 和 11.2.0.4。
出现错误时，其主要堆栈是：
kgxRelease()<-kkshGetNextChild()<-kkscsSearchChildList()<-kksfbc()
这其中的几个函数作用如下：
kksfbc - 内核编译共享对象（游标）查找绑定游标 （kernel compile shared objects (cursor) find bound cursor）
kkscsSearchChildList - 用于扫描子游标列表（Search Cursor Children List）.
kkshGetNextChild - 获取下一个子游标，扫描过程的跳转；
kgxRelease(.,AOL) - 用于释放 Mutex （release the mutex）.
其他常见的还有：
kgxExclusive (.,mutex, AOL) -get the mutex in X mode.
kgxShared(.,mutex, AOL) -get the mutex in Shared mode.
kgxSharedExamine(. . . )
kgxExclusive2Shared (. . . ) -downgrade X mode to S.
kgxIncrement(. . . ) -increment S mode counter.
kgxDecrement(. . . ) -decrement S mode counter
这个BUG的主要描述如下，可以作为参考：
Description
A process may enter into a spin/hang state while executing shared cursors code
if the fix for bug 14613900 is present, either as an interim patch or as a PSU
or bundle release that includes that fix.
Rediscovery Notes
Process spinning with method kkshGetNextChild() and kkscsSearchChildList() in
the stack could indicate this problem.
eg: kgxRelease()<-kkshGetNextChild()<-kkscsSearchChildList()<-kksfbc()
Importantly, this problem only occurs if the fix of bug 14613900 is present.
Other sessions may show as waiting for "cursor: mutex S" with P1=0 (ie: idn=0)
Workaround
Flushing the shared pool can alleviate the problem in some cases.
确认的影响版本是：
11.2.0.3.5 Database Patch Set Update
11.2.0.3.4 Database Patch Set Update
11.2.0.3 Bundle Patch 11 for Exadata Database
11.2.0.2.9 Database Patch Set Update
11.2.0.2.8 Database Patch Set Update
11.2.0.2 Bundle Patch 18 for Exadata Database
11.2.0.3 Patch 11 on Windows Platforms
11.2.0.2 Patch 22 on Windows Platforms
确认的修复版本是：
12.2.0.1 (Base Release)
12.1.0.2 (Server Patch Set)
11.2.0.4 (Server Patch Set)
11.2.0.3.6 Database Patch Set Update
11.2.0.3 Bundle Patch 16 for Exadata Database
11.2.0.2.10 Database Patch Set Update
11.2.0.2 Bundle Patch 20 for Exadata Database
11.2.0.3 Patch 18 on Windows Platforms
11.2.0.2 Patch 25 on Windows Platforms
但是这个问题确信在 12.1.0.2 上仍然存在。
在SQL解析时，对于父游标的检测同样会使用 Cursor mutex S:
Parent examination
-
When finding a cursor to execute, the parent must be examined. The examination of the parent is performed using the mutex, cursor: mutex S.
-
When the parent cursor has many child cursors involved, this waits will come as the server process has to traverse the entire list of child cursors under the parent to find a match.
-
Mutex is in the parent cursor.
在 SQL 解析时 kgxSharedExamine 就是在检测共享游标。
历史上的今天...
>> 2011-07-06文章:
>> 2010-07-06文章:
>> 2009-07-06文章:
>> 2007-07-06文章:
>> 2006-07-06文章:
>> 2005-07-06文章:
By eygle on 2017-07-06 17:05 | Comments (0) | FAQ | 3258 |