一、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