1 Cursor: pin S 等待事件描述
Oracle10g中引用的mutexes机制一定程度的替代了library cache pin，其结构更简单，get&set的原子操作更快捷。
它相当于，每个child cursor下面都有一个mutexes这样的简单内存结构，当有session要执行该SQL而需要pin cursor操作的时候，session只需要以shared模式set这个内存位+1，表示session获得该mutex的shared mode lock.可以有很多session同时具有这个mutex的shared mode lock；但在同一时间，只能有一个session在操作这个mutext +1或者-1。+1 -1的操作是排它性的原子操作。如果因为session并行太多，而导致某个session在等待其他session的mutext +1/-1操作,则该session要等待cursor: pin S等待事件。
在10.2.0.1.0版本中，默认没有启用mutexes。因此不会产出cursor: pin s的等待，只会产生library cache pin的等待事件。
参考OTN的解释：
cursor: pin SA 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
2 测试过程
2.1 设置_kks_use_mutex_pin隐藏参数
由于数据库版本为10.2.0.1.0，因此需要设置隐藏参数。
SQL*Plus: Release 10.2.0.1.0 - Production ON Mon May 13 09:08:41 2013
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.1.0 - Production
WITH the Partitioning, Oracle Label Security, OLAP AND DATA Mining Scoring Engine options
SYS@PROD> col name FOR a30
SYS@PROD> col VALUE FOR a10
SYS@PROD> SELECT nam.ksppinm NAME, val.ksppstvl VALUE FROM x$ksppi nam, x$ksppsv val WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%mutex%' ORDER BY 1;
NAME VALUE
------------------------------ ----------
_kks_use_mutex_pin FALSE
SYS@PROD> ALTER system SET "_kks_use_mutex_pin"=TRUE scope=spfile;
System altered.
SYS@PROD> shutdown immediate
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SYS@PROD> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed SIZE 1219184 bytes
Variable SIZE 92276112 bytes
DATABASE Buffers 218103808 bytes
Redo Buffers 2973696 bytes
DATABASE mounted.
DATABASE opened.
SYS@PROD> SELECT nam.ksppinm NAME, val.ksppstvl VALUE FROM x$ksppi nam, x$ksppsv val WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%mutex%' ORDER BY 1;
NAME VALUE
------------------------------ ----------
_kks_use_mutex_pin TRUE
2.2 创建测试用户
创建用于测试的test用户，并授予DBA权限。
USER created.
SYS@PROD> GRANT dba TO test;
GRANT succeeded.
SYS@PROD> conn test/test@PROD
Connected.
2.3 创建测试表和存储过程
使用test用户，创建测试表，并创建测试存储过程。
改存储过程模拟大量的相同SQL语句对测试表执行查询操作。
TABLE created.
TEST@PROD> CREATE OR REPLACE PROCEDURE cursor
AS
v_sql varchar2(100);
BEGIN
FOR i IN 1..5000000
loop
v_sql := 'select object_id from t_cursor';
EXECUTE immediate v_sql;
END loop;
END;
/ 2 3 4 5 6 7 8 9 10 11
PROCEDURE created.
2.4 开始测试
在测试开始之前，先检查此刻的等待事件，确认没有cursor: pin S。
TEST@PROD> SELECT event FROM v$session WHERE event LIKE '%cursor%';
no ROWS selected
TEST@PROD> 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') si_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;
no ROWS selected
再启动两个数据库连接，执行存储过程。
连接1：
SQL*Plus: Release 10.2.0.1.0 - Production ON Mon May 13 09:29:17 2013
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
Error accessing PRODUCT_USER_PROFILE
Warning: Product USER profile information NOT loaded!
You may need TO run PUPBLD.SQL AS SYSTEM
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.1.0 - Production
WITH the Partitioning, Oracle Label Security, OLAP AND DATA Mining Scoring Engine options
TEST@PROD> EXEC cursor
连接2：
SQL*Plus: Release 10.2.0.1.0 - Production ON Mon May 13 09:29:08 2013
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
Error accessing PRODUCT_USER_PROFILE
Warning: Product USER profile information NOT loaded!
You may need TO run PUPBLD.SQL AS SYSTEM
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.1.0 - Production
WITH the Partitioning, Oracle Label Security, OLAP AND DATA Mining Scoring Engine options
TEST@PROD> EXEC cursor
回到主连接中，查看等待事件。已经出现cursor: pin S或cursor: pin S wait on X等待事件。
EVENT
----------------------------------------------------------------
cursor: pin S
cursor: pin S
TEST@PROD> 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') si_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
-------- ------------------------ -------------- ----------- --------------
SI_HOLD_MUTEX_X SQL_TEXT SQL_TEXT
---------------------- --------------------- ------------------------------
306 cursor: pin S Other 2543761203 013D0001
317 SELECT object_id FROM t_cursor SELECT object_id FROM t_cursor
317 cursor: pin S wait ON X Concurrency 2543761203 01320000
306 SELECT object_id FROM t_cursor BEGIN cursor; END;
TEST@PROD> SELECT event FROM v$session WHERE event LIKE '%cursor%';
EVENT
----------------------------------------------------------------
cursor: pin S wait ON X
cursor: pin S wait ON X
TEST@PROD> 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') si_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
-------- ------------------------ -------------- ----------- --------------
SI_HOLD_MUTEX_X SQL_TEXT SQL_TEXT
---------------------- --------------------- ------------------------------
306 cursor: pin S wait ON X Concurrency 2543761203 013D0000
317 SELECT object_id FROM t_cursor SELECT object_id FROM t_cursor
317 cursor: pin S wait ON X Concurrency 2543761203 01320000
306 SELECT object_id FROM t_cursor SELECT object_id FROM t_cursor
3 解决方法
当看到系统有很多session等待cursor: pin S事件的时候，可能是CPU达到瓶颈，也可能是某个SQL的并行执行次数太多了而导致在child cursor上的mutex操作争用。如果是Capacity的问题，则可以升级硬件。如果是因为SQL的并行太多，则要么想办法降低该SQL执行次数，要么将该SQL复制成N个其它的SQL。
● select /*SQL 1*/object_name from t where object_id=?
● select /*SQL 2*/object_name from t where object_id=?
● select /*SQL …*/object_name from t where object_id=?
● select /*SQL N*/object_name from t where object_id=?
这样就有了N个SQL Cursor，N个Mutex内存结构，就将争用分散开来，类似partition的作用了。
Speak Your Mind