一、Cursor: pin S问题介绍
1、问题背景
数据库主机CPU严重的冲了一下100%，瞬间又回落，导致DB响应变慢，大量应用连接timeout。在AWR报告中，看到较高的Cursor: Pin S等待，这是一个由于频繁执行SQL共享解析时产生的竞争。
这通常是由于某些SQL以超高频繁的频率执行导致的，当然也可能与系统的CPU能力不足有关。
2、解决原理
Mutex机制在Oracle 10g引入，用于替代Library cache pin操作，其性能更高，其原理为在每个Child Cursor上分配一个地址空间记录Mutex，当该Cursor被共享执行时，通过将该位进行+1处理来实现。虽然是游标共享，但是更新Mutex结构的操作需要排他，当某一个SQL被频繁共享执行时，可能就会出现Pin S的等待。
在每个child cursor下面都有一个Mutex这样的简单内存结构，当有session要执行该SQL而需要pin cursor操作的时候，session只需要以shared模式set这个内存位+1，表示session获得该Mutex的shared mode lock.可以有很多session同时具有这个Mutex的shared mode lock；但在同一时间，只能有一个session在操作这个Mutex +1或者-1。+1/-1的操作是排它性的原子操作。如果因为session并行太多，而导致某个session在等待其他session的Mutex +1/-1操作,则该session要等待cursor: pin S等待事件。
最简单的解决方案是，将频繁执行的SQL进行拆解，分散Cursor竞争，如以下SQL通过注释将同一条SQL分解为10条，就分散了竞争。这种做法在Ebay、Papal、支付宝等公司被广泛采用。
select /*SQL 1*/ user from dual; select /*SQL 2*/ user from dual; select /*SQL 3*/ user from dual; select /*SQL 4*/ user from dual; select /*SQL 5*/ user from dual; select /*SQL 6*/ user from dual; select /*SQL 7*/ user from dual; select /*SQL 8*/ user from dual; select /*SQL 9*/ user from dual; select /*SQL 10*/ user from dual;
3、参考文档
MOS文档：WAITEVENT: “cursor: pin S” Reference Note [ID 1310764.1]
二、Cursor: pin S问题模拟和解决
1、开两个session循环执行’select upper(user) from dual’，模拟并行：
———————-
Session ID: 2201
———————-
SQL> select sid from v$mystat where rownum=1; SID ---------- 2201 SQL> declare 2 msql varchar2(200); 3 begin 4 loop 5 for i in 1 .. 100 loop 6 msql:='select upper(user) from dual'; 7 execute immediate msql; 8 end loop; 9 end loop; 10 end; 11 /
———————-
Session ID: 2268
———————-
SQL> select sid from v$mystat where rownum=1; SID ---------- 2268 SQL> declare 2 msql varchar2(200); 3 begin 4 loop 5 for i in 1 .. 100 loop 6 msql:='select upper(user) from dual'; 7 execute immediate msql; 8 end loop; 9 end loop; 10 end; 11 /
2、监控：
———————-
Session ID: 监控
———————-
（1）查看mutex状态：
SQL> select * from v$mutex_sleep; MUTEX_TYPE LOCATION SLEEPS WAIT_TIME -------------- -------------------------------- ------ ---------- Cursor Parent kkspsc0 [KKSPRTLOC26] 85 1300 Cursor Parent kkshdelq [KKSPRTLOC13] 5 2044 Cursor Parent kksLoadChild [KKSPRTLOC4] 12 2869 Cursor Parent kksfbc [KKSPRTLOC2] 52 199 Cursor Parent kksfbc [KKSPRTLOC1] 953 47746 Cursor Pin kksLockDelete [KKSCHLPIN6] 16272 623193 Cursor Pin kkslce [KKSCHLPIN2] 2459 15238073 Cursor Pin kksfbc [KKSCHLFSP2] 21464 556443 Cursor Pin kksfbc [KKSCHLCREA] 9 100664 hash table kkscsSearchChildList [KKSHBKLOC2] 1 1216 hash table kkshGetNextChild [KKSHBKLOC1] 5 1718
（2）查看等待事件，发现Cursor: pin S事件：（2个session出现相互等待，争用产生。）
select b.*, sq.sql_text
from v$session se,
v$sql sq,
(select a.*, s.sql_text
from v$sql s,
(select sid,
event,
wait_class,
p1,
p2raw,
to_number(substr(p2raw, 1, 4), 'xxxx') sid_hold_mutex_x
from v$session_wait
where event like 'cursor%') a
where s.hash_value = a.p1) b
where se.sid = b.sid
and se.sql_hash_value = sq.hash_value;
SID EVENT WAIT_CLASS P1 P2RAW SID_HOLD_MUTEX_X SQL_TEXT SQL_TEXT
---- ------------- ----------- ---------- ----- ---------------- ---------------------------- ----------------------------
2201 cursor: pin S Other 2917066271 00 0 select upper(user) from dual select upper(user) from dual
2268 cursor: pin S Other 2917066271 00 0 select upper(user) from dual select upper(user) from dual
（3）查看CPU状态（2个session出现在Top session里）
SQL> select spid from v$process where addr in (select paddr from v$session where sid in (2201,2268)); SPID ------ 24389 24432
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 24389 otzj11g 25 0 10.1g 50m 46m R 99.9 0.0 5:56.33 oracle 24432 otzj11g 25 0 10.1g 41m 37m R 99.9 0.0 5:54.61 oracle
（4）2个session执行一段时间后，中断，再查看mutex状态，发现Cursor Pin增加，明确为mutex争用。
SQL> select * from v$mutex_sleep; MUTEX_TYPE LOCATION SLEEPS WAIT_TIME -------------- -------------------------------- ------ ---------- Cursor Parent kkspsc0 [KKSPRTLOC26] 85 1300 Cursor Parent kkshdelq [KKSPRTLOC13] 5 2044 Cursor Parent kksLoadChild [KKSPRTLOC4] 12 2869 Cursor Parent kksfbc [KKSPRTLOC2] 52 199 Cursor Parent kksfbc [KKSPRTLOC1] 953 47746 Cursor Pin kksLockDelete [KKSCHLPIN6] 267792 987937 Cursor Pin kkslce [KKSCHLPIN2] 2459 15238073 Cursor Pin kksfbc [KKSCHLFSP2] 1083668 2083310 Cursor Pin kksfbc [KKSCHLCREA] 9 100664 hash table kkscsSearchChildList [KKSHBKLOC2] 1 1216 hash table kkshGetNextChild [KKSHBKLOC1] 5 1718
3、简单修改一下，加上不同的hint关键字，以牺牲version_count为代价，再次进行以下测试，Cursor Pin S的等待和mutex的争用将不再存在。
declare msql varchar2(200); msid number; begin select sid into msid from v$mystat where rownum=1; loop for i in 1 .. 100 loop msql:='select /*'||msid||i||'*/ upper(user) from dual'; execute immediate msql; end loop; end loop; end; /
no comment untill now