Cursor pin S问题

一、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;
/
Trackback

no comment untill now

Add your comment now

切换到手机版