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中的测试
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16158219/viewspace-594456/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16158219/viewspace-594456/