cursor: pin S，cursorpin

cursor: pin S，cursorpin

cursor: pin S

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

今天收集效能報告Top 5 

 

Top 5 Timed Foreground Events~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                                                           Avg                                                          wait   % DBEvent                                 Waits     Time(s)   (ms)   time Wait Class------------------------------ ------------ ----------- ------ ------ ----------DB CPU                                          205,412          99.8db file sequential read              68,063         487      7     .2 User I/Olog file sync                       139,316         233      2     .1 Commitcursor: pin S                     1,163,270         127      0     .1 ConcurrencSQL*Net message from dblink          97,575          46      0     .0 Network

Oracle10gR2中引用的mutexes機制一定程度的替代了library cache pin，其結構更簡單，相當於，每個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操作爭用。如果是前者，則可以升級硬體。如果是因為SQL的並行太多，則要麼想辦法降低該SQL執行次數，要麼將該SQL複製成N個其它的SQL。

    select /*SQL 1*/ename from t where deptno=?    select /*SQL 2*/ename from t where deptno=?    select /*SQL …*/ename from t where deptno=?    select /*SQL N*/ename from t where deptno=?

這樣就有了N個SQL Cursor,N個Mutex記憶體結構，就將爭用分散開來，類似partition的作用了。