cursor: pin S產生原理及解決方法

今天晚上在一個比較重要的庫上，CPU嚴重的衝了一下，導致DB響應變慢，大量應用連線timeout，緊接著LISTENER就掛了，連線數也滿了等一連串問題。

我們的監控抓取了當時系統的等待事件，ACTIVE SQL及SESSION_WAIT等待事件，所以問題比較容量定位，檢視下監控，馬上就發現出問題的時間點上出現大量的cusor:pin S,這個等待事件很常見。

再透過查詢持有的等待事件cursor: pin S的會話正在執行的SQL語句發現僅是一條簡單的SQL。通常記憶體中latch pin操作是相當快的，如果出現等待了，應該很可能就是該SQL執行的過於頻繁。latch在Oracle中是一種低階鎖，用於保護記憶體裡的資料結構，提 供的是序列訪問機制，而mutex是Oracle 10gR2引入的，也是為實現序列訪問控制的功能，並替換部分latch。

可以透過下面的SQL進行查詢:

--查詢sql
SELECT a.*, s.sql_text
  FROM v$sql s,
       (SELECT sid,
               event,
               wait_class,
               p1 cursor_hash_value,
               p2raw Mutex_value,
               TO_NUMBER (SUBSTR (p2raw, 1, 8), 'xxxxxxxx') hold_mutex_x_sid
          FROM v$session_wait
         WHERE event LIKE 'cursor%') a
 WHERE s.HASH_VALUE = a.p1

--Parameter說明
P1 Hash value of cursor

P2 Mutex value
64 bit platforms
8 bytes are used.
Top 4 bytes hold the session id (if the mutex is held X)
Bottom 4 bytes hold the ref count (if the mutex is held S).

32 bit platforms
4 bytes are used.
Top 2 bytes hold the session id (if the mutex is held X)
Bottom 2 bytes hold the ref count (if the mutex is held S).

P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps

在每個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。增加SQL的版本數來降低併發。如一個SQL：

select name from acct where acctno=:1

可以改為如下4個SQL，則併發的爭用可以下降4倍。

     select /*A*/ name from acct where acctno=:1
     select /*B*/ name from acct where acctno=:1
     select /*C*/ name from acct where acctno=:1
     select /*D*/ name from acct where acctno=:1

 

另外，我們還會經常碰到另外一個等待事件“cursor: pin S wait on X”，這個等待事件主要是由硬解析引起的,解釋如下：
“cursor: pin S wait on X” wait event is mostly related to mutex and hard parse.
- When a process hard parses the SQL statement, it should acquire exclusive
library cache pin for the corresponding LCO.
- This means that the process acquires the mutex in exclusive mode.
- Another process which also executes the same query needs to acquire the mutex
but it’s being blocked by preceding process. The wait event is “cursor: pin S wait on X”.

cursor: pin S，cursor: pin X，cursor: pin S wait on X這三個等待事件，實際上就是替代了cursor的library cache pin，pin S代表執行（share pin），pin X代表解析（exclusive pin），pin S wait on X代表執行正在等待解析操作。這裡需要強調一下，它們只是替換了訪問cursor的library cache pin，而對於訪問procedure這種實體物件，依然是傳統的library cache pin。

參考：

https://supporthtml.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=5051110464464000&id=1310764.1&_afrWindowMode=0&_adf.ctrl-state=fu77hl3v2_4

出處: