AWR報告分析之三：cursor: pin S 的原理與案例分析

在以下資料庫的AWR報告中，可以看到超高的Cursor: Pin S等待，這是一個由於頻繁執行SQL共享解析時產生的競爭。當一個會話嘗試以共享模式（S - Share）來獲得一個遊標時，需要修改相應的Mutex結構的引用計數（reference count），或者增加該計數，或者減少。修改引用技術的原子操作很快（其實和Latch的獲取釋放類似），但是在頻繁解析的情況下，仍然產生了競爭和等待，由此就產生了 cursor : pin S 的等待。

這通常是由於某些SQL以超高頻繁的頻率執行導致的，當然也可能與系統的CPU能力不足有關。

Mutex機制在Oracle 10g引入，用於替代Library cache pin操作，其效能更高，其原理為在每個Child Cursor上分配一個地址空間記錄Mutex，當該Cursor被共享執行時，通過將該位進行加一處理來實現。雖然是指遊標共享，但是更新Mutex結構的操作需要排他，當某一個SQL被頻繁共享執行時，可能就會出現Pin S的等待。

每個Library Cache物件都有一個reference count （引用計數），用來表明有多少其他物件目前正保留一個對它的引用（reference).  物件A 想要引用物件B, A 就把B 的 reference count 加 1。 當A 結束了對B 的引用， A 就把 B 的reference count 減 1.   當沒有任何物件再引用 B 時， B 的 reference count就減為0， B 就被清除（deallocated）, 記憶體就被釋放。清除B的時候， 被B所用的物件的 reference count 也可能減小， 也可能使它們被清除。

最簡單的解決方案是，將頻繁執行的SQL分割槽拆解，分散競爭，如以下SQL通過註釋將同一條SQL分解為2條，就分散了競爭：
    select /*SQL 1*/ a from t_a where id=?

    select /*SQL 2*/ a from t_a where id=?
這種做法在Ebay、Papal、支付寶等公司被廣泛採用。

在官方文件上這樣解釋：

A session waits for "cursor: pin S" when it wants a specific mutex in S (share) mode on a specific cursor and there is no concurrent X holder but it could not acquire that mutex immediately. This may seem a little strange as one might question why there should be any form of wait to get a mutex which has no session holding it in an incompatible mode. The reason for the wait is that in order to acquire the mutex in S mode (or release it) the session has to increment (or decrement) the mutex reference count and this requires an exclusive atomic update to the mutex structure itself. If there are concurrent sessions trying to make such an update to the mutex then only one session can actually increment (or decrement) the reference count at a time. A wait on "cursor: pin S" thus occurs if a session cannot make that atomic change immediately due to other concurrent requests. Mutexes are local to the current instance in RAC environments.

以下是這個使用者的AWR報告，Top 5事件：

可以看到在SQL解析部分，前3條SQL的執行頻率非常高（取樣為60分鐘間隔），這些頻繁的SQL執行是產生Pin S的源頭：

這幾條SQL的語句全文是：

select * from sw_PRODUCTS where ID=:ID

select * from sw_SERIES where ID=:ID

select f_sale_price from product_sale_price where f_product_id=:product_id and F_PRICE_BM='ECBJ'

典型的簡單SQL反覆執行，通過前面探討的SQL改寫方案將可以解決這裡面臨的Cursor: Pin S問題。

以下是整個AWR報告的全文，可供參考：

http://www.eygle.com/pdf/awrrpt_1_11577_11578.html

其他參考資源：

MOS：1310764.1 .
-------------------------------------------&gt>
轉載於:http://www.eygle.com/archives/2013/01/awr_cursor_pin_share_case.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1147548/，如需轉載，請註明出處，否則將追究法律責任。