【Best Practices】cursor: pin S wait on X等待事件模拟




  • 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,因此需要设置隐藏参数。

    [oracle@secdb1 oracle]$ sqlplus / AS sysdba

    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权限。

    SYS@PROD> CREATE USER test IDENTIFIED BY test;

    USER created.

    SYS@PROD> GRANT dba TO test;

    GRANT succeeded.

    SYS@PROD> conn test/test@PROD

    Connected.

    2.3  创建测试表和存储过程

    使用test用户,创建测试表,并创建测试存储过程。
    改存储过程模拟大量的相同SQL语句对测试表执行查询操作。

    TEST@PROD> CREATE TABLE t_cursor AS SELECT * FROM dba_objects;

    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> SET pages 500
    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:

    [oracle@secdb1 ~]$ sqlplus test/test@PROD

    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:

    [oracle@secdb1 ~]$ sqlplus test/test@PROD

    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等待事件。

    TEST@PROD> SELECT event FROM v$session WHERE event LIKE '%cursor%';

    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

    *