一 cursor: pin S

1.等待事件說明

1.1 官方文件解釋

A 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

1.2 cursor pin S原因

等待事件cursor:pin * 用於針對遊標的pin 操作。

Cursor Pin的mutex（不是cursor pin mutex）是在Library Cache Object 之內建立的動態物件。在競爭之下，透過x$mutex_sleep_history可以找到mutex的地址。

cursor: pin S 試圖以S 模式Pin 某個Cursor，但是該Cursor正在被Pining ，也就是“in flux”，必須等待這個過程完成，才能被共享Pin。

Mutexes were introduced in Oracle 10.2.
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.

系統本身CPU不足，許多cursor pin S有許多不同的p1。

2.等待事件模擬以及排查

2.1 建立測試表

create table t (id number);

2.2 測試

session1:

select sid from v$mystat where rownum<2;
declare
a number;
begin
for i in 1..100000 loop
execute immediate 'select count(*) from t where n=:v1' into a using i ;
end loop;
end;
/

 

session2：

select sid from v$mystat where rownum<2;
declare
a number;
begin
for i in 1..100000 loop
execute immediate 'select count(*) from t where n=:v1' into a using i ;
end loop;
end;
/

 

session3查詢：

col event for a30
col p1 for 999999999999999999999
col p2 for 999999999999999999999
col p3 for 999999999999999999999
col sid for 999
select event,p1,p1raw,p2,p2raw,p3,sid,blocking_session bs from v$session where sid in (45,40)
 
EVENT                                          P1 P1RAW                               P2 P2RAW                                P3  SID  BS
------------------------------ ------------------ ---------------- --------------------- ---------------- ---------------------- ---- ---
cursor: pin S                          1664038472 00000000632F3648          193273528320 0000002D00000000            12884901888   40
cursor: pin S                          1664038472 00000000632F3648          171798691841 0000002800000001            12884901888   45

 

可以看到兩個會話都在競爭相同的SQL子游標，可以根據p1查詢相關SQL，根據p2前2bytes找到併發競爭會話。

關於p1 p2 p3更詳細以及情況可以參考Mos文件：

WAITEVENT: "cursor: pin S" Reference Note (Doc ID 1310764.1)

 

 

P1:
select sql_text,sql_id from v$sqlarea where hash_value=1664038472;
 
P2:
SELECT decode(trunc(&&P2/4294967296),
           0,trunc(&&P2/65536),
            trunc(&&P2/4294967296)) SID_HOLDING_MUTEX
 FROM dual;
 
select to_number('2d','xx') from dual;
 
                    TO_NUMBER('2D','XX')
----------------------------------------
                                      45
 
select to_number('28','xx') from dual;
 
                    TO_NUMBER('28','XX')
----------------------------------------
                                      40
P3（32bit 10.2版本低位可能會溢位到高位，可能會計算出錯誤的location_id值）:
可以根據P3計算x$mutex_sleep中location_id：
SELECT decode(trunc(&P3/4294967296),
0,trunc(&P3/65536),
trunc(&P3/4294967296)) LOCATION_ID
FROM dual;
Use the LOCATION_ID returned above in this SQL:
SELECT MUTEX_TYPE, LOCATION
FROM x$mutex_sleep
WHERE mutex_type like 'Cursor Pin%'
and location_id=&LOCATION_ID;
 
MUTEX_TYPE                       LOCATION
-------------------------------- ----------------------------------------
Cursor Pin                       kksfbc [KKSCHLFSP2]
 
kkksfbc:kernel compile shared objects (cursor) find bound cursor

3.解決辦法

3.1 針對hot SQL使用sql hint

由於SQL的hash value是根據文字轉換為ASCII碼計算hash值，可以使用如下辦法生成多版本，降低單個mutex的高併發訪問造成的cursor pin S

For any identified "hot" SQLs one can reduce the concurrency on specific cursors by replacing the one SQL with some variants which are executed by different sessions.

eg: Consider "select <COLNAME1> from <TABLENAME> where <COLNAME2>=:1" is a very hot
    SQL statement then if clients can be put into groupings with: 
     some using "select /*A*/ <COLNAME1> from <TABLENAME> where <COLNAME2>=:1", 
     some using "select /*B*/ <COLNAME1> from <TABLENAME> where <COLNAME2>=:1", 
     some using "select /*C*/ <COLNAME1> from <TABLENAME> where <COLNAME2>=:1", 
    etc.. then the concurrency against any one of the SQLs can be reduced.