oracle wait event:cursor: pin S wait on X

oracle wait event:cursor: pin S wait on X

cursor: pin S wait on X等待事件的处理过程
http://database.ctocio.com.cn/tips/114/8263614_1.shtml

cursor: pin S wait on X等待！
http://www.itpub.net/viewthread.php?tid=1003340

解决cursor: pin S wait on X 有什么好办法:
http://www.itpub.net/thread-1163543-1-8.html

cursor: pin S wait on X:
http://space.itpub.net/756652/viewspace-348176

cursor: pin S:
http://yumianfeilong.com/html/2008/11/01/254.html

OTN的解释,

cursor: pin SA session waits on this event when it wants to update a shared mutex pin and another session is currently in the process of updating a shared mutex pin for the same cursor object. This wait event should rarely be seen because a shared mutex pin update is very fast.(Wait Time: Microseconds)
Parameter Description

• P1 Hash value of cursor
• P2 Mutex value (top 2 bytes contains 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

Oracle10g中引用的mutexes机制一定程度的替代了library cache pin，其结构更简单，get&set的原子操作更快捷。

它相当于，每个child cursor下面都有一个mutexes这样的简单内存结构，当有session要执行该SQL而需要pin cursor操作的时候，session只需要以shared模式set这个内存位+1，表示session获得该mutex的shared mode lock.可以有很多session同时具有这个mutex的shared mode lock；但在同一时间，只能有一个session在操作这个mutext +1或者-1。+1 -1的操作是排它性的原子操作。如果因为session并行太多，而导致某个session在等待其他session的mutext +1/-1操作,则该session要等待cursor: pin S等待事件。

当看到系统有很多session等待cursor: pin S事件的时候，要么是CPU不够快，要么是某个SQL的并行执行次数太多了而导致在child cursor上的mutex操作争用。如果是Capacity的问题，则可以升级硬件。如果是因为SQL的并行太多，则要么想办法降低该SQL执行次数，要么将该SQL复制成N个其它的SQL。

• select /*SQL 1*/object_name from t where object_id=?
• select /*SQL 2*/object_name from t where object_id=?
• select /*SQL …*/object_name from t where object_id=?
• select /*SQL N*/object_name from t where object_id=?

这样就有了N个SQL Cursor,N个Mutex内存结构，就将争用分散开来，类似partition的作用了。

实际测试效果很明显，当仅一个SQL Cursor的时候，并行执行等待cursor: pin S较高。

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
—————————————–
cursor: pin S 460,124 223 0 63.9
CPU time 121 34.6
latch free 173 5 29 1.5
db file sequential read 54 0 2 .0
control file parallel write 27 0 2 .0
——————————————

当分解为5个SQL再次测试同样的压力，cursor: pin S 等待大大减少。

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
——————————–
CPU time 75 60.4
cursor: pin S 115,159 33 0 26.2
latch free 175 16 90 12.7
cursor: pin S wait on X 25 1 29 .6
db file parallel write 38 0 2 .0
——————————–

如果使用SQLPLUS测试，则无上述效果。拆分SQL后仍然要等待很多cursor: pin S。因为sqlplus在返回纪录的时候默认调用BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;而导致在该SQL上的mutex 竞争。

如果配合上cursor_space_for_time，则效果更好。

Same work load, same parallel degree, cursor_space_for_time=TRUE and only 1 SQL statement

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

—————————————– ———— ———– —— ——

CPU time 62 75.5

latch free 171 9 54 11.4

cursor: pin S wait on X 162 4 23 4.6

db file sequential read 1,184 3 2 3.5

os thread startup 2 1 584 1.4

————————————————————-

Same work load, same parallel degree, cursor_space_for_time=TRUE and 5 different SQL statement

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

—————————————– ———— ———– —— ——

CPU time 58 99.4

latch free 145 0 2 .4

db file sequential read 11 0 4 .1

control file parallel write 20 0 2 .1

log file sync 1 0 9 .0

————————————————————-

可见mutex和cursor_space_for_time有互补性，在execution特别高的系统中或许值得考虑。

如上为10.2.0.3中的测试

<!-- Start of add bookmark buttons created by wp_addbookmarks. Plugin by http://www.thinkagain.cn/ -->