ITPub博客

首页 > Linux操作系统 > Linux操作系统 > cursor: pin S

cursor: pin S

原创 Linux操作系统 作者:v_fantasy 时间:2009-05-07 20:13:49 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Mutexes in Oracle10g
请登录后发表评论 登录
全部评论

注册时间:2008-10-07

  • 博文量
    98
  • 访问量
    179016