OraclecursorpinSwaitonX等待事件说明

原 Oracle cursor pin S wait on X 等待事件 说明https://blog.csdn.net/tianlesoftware/article/details/6549018版权声明： https://blog.csdn.net/tianlesoftware/article/details/6549018
         这个等待事件也算一个常见的等待事件。 在warehouse blog和  itpub 上有相关的2个帖子。 连接如下：
  cursor: pin S wait on X等待事件模拟
http://warehouse.itpub.net/post/777/493962
    cursor: pin S wait on X
http://space.itpub.net/756652/viewspace-348176
    一.  Mutex 说明
       Oracle Mutex 机制 说明
       http://blog.csdn.net/tianlesoftware/archive/2011/05/30/6455517.aspx
         To improve cursor execution and also hard parsing, a new memory serialization mechanism has been created in 10gR2.
       For certain shared-cursor related operations, mutexes are used as a replacement for library cache latches and librarycache pins.
       -- mutexes 替代 library cache latches 和 librarycache pins。
         Using mutexes is faster, uses less CPU and also allows significantly improved concurrency over the existing latch mechanism.
       The use of mutexes for cursor pins can be enabled by setting the init.ora parameter _use_kks_mutex toTRUE.
              Btw, things get more fun in 10.2, you can pin cursors without getting library cache pin latch, using KGX mutexes. Mutexes are new thing in 10.2 and they enable shared access to objects in somewhat similar manner than shared latches, that every successful get of particular mutex will increment its value and release will decrement. When the count is zero, no-one has the mutex and it is safe to get it in exclusive mode too. However they are more fine grained than kgl latches and provide better waiting mechanism as far as I understand.

       So if your environment supports atomic compare and swap operation (as CMPXCHG on Intel), you might get away without cursor_space_for_time setting for ultrahigh execution rates. Otherwise the atomic mutex operations would be achieved using new KGX latches.

       At least on my laptop this feature isn’t enabled by default (from andOracleWorld’s paper I remember that it should become default in 10.2.0.2), but so far you can experiment with it if you set _kks_use_mutex_pin = true and bounce the instance (mutex structures will be stored in shared pool, so you might need to increase SP size).

       There are also x$mutex_sleep and x$mutex_sleep_history fixed tables that can show some interesting information if you generate some mutex waits into them.
         在Oracle 10.2中，对shared pool中的一些Serialization operation使用更轻量的 KGX mutexes (_use_kks_mutex) 取代library cache pin，从而降低CPU Usage, 是否使用这种muetx机制受到隐含参数_kks_use_mutex_pin的限制。
         从10.2.0.2开始该参数default为true,使用这种机制oracle是为了解决library cache bin latch的串行使用问题,但是mutex貌似还不是很稳定,在很多系统中会出现cursor: pin S wait on X等待事件,这个事件和mutex的使用有关,最近一客户受到cursor: pin S wait on X等待事件的困扰,出现cursor: pin S wait on X等待事件时通常等待比较严重,系统会出现hang。
  cursor: pin S wait on X
       A session waits for this event when it is requesting a shared mutex pin and another session is holding an exclusive mutex pin on the same cursor object.
  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
    这个事件的出现受到很多因素的影响，在高并发的情况下:
              （1）sga自动管理,sga的频繁扩展和收缩
              （2）过渡硬解析,造成library cache中的cursor object被频繁的reload
              （3）bug
         _kks_use_mutex_pin 是隐含参数，通过v$parameter 视图查不到，需要通过如下SQL 来查看。
  SELECT   i.ksppinm name,
           i.ksppdesc description,
           CV.ksppstvl VALUE,
           CV.ksppstdf isdefault,
           DECODE (BITAND (CV.ksppstvf, 7),
                   1, 'MODIFIED',
                   4, 'SYSTEM_MOD',
                   'FALSE')
              ismodified,
           DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted
    FROM   sys.x$ksppi i, sys.x$ksppcv CV
   WHERE       i.inst_id = USERENV ('Instance')
           AND CV.inst_id = USERENV ('Instance')
           AND i.indx = CV.indx
           AND i.ksppinm LIKE '/_%' ESCAPE '/'
           and i.ksppinm like '_kks%'
ORDER BY   REPLACE (i.ksppinm, '_', '');
    Oracle 参数分类 和 参数的查看方法
http://www.cndba.cn/Dave/article/1173
    二. 相关测试
SYS@anqing2(rac2)> select * from v$version where rownum<2;
  BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
  SESSION 1：
-------------------------
    --创建测试表
SYS@anqing2(rac2)> create table t as select * from dba_objects;
Table created.
  --查看session ID
SYS@anqing2(rac2)> select sid from v$mystat where rownum=1;
SID
----------
125
    SYS@anqing2(rac2)> declare
  2  v_string varchar2(100) := 'alter system flush shared_pool';
  3  msql varchar2(200);
  4  begin
  5  loop
  6  execute immediate v_string;
  7  for i in 1..100 loop
  8  msql:='select object_id from t where object_id='||i;
  9  execute immediate msql;
 10  end loop;
 11  end loop;
 12  end;
 13  /
  session 2：
-------------------------
--查看session ID
SYS@anqing2(rac2)> select sid from v$mystat where rownum=1;
SID
----------
130
  SYS@anqing2(rac2)> declare
  2  v_string varchar2(100) := 'alter system flush shared_pool';
  3  msql varchar2(200);
  4  begin
  5  loop
  6  execute immediate v_string;
  7  for i in 1..100 loop
  8  msql:='select object_id from t where object_id='||i;
  9  execute immediate msql;
 10  end loop;
 11  end loop;
 12  end;
 13  /
    session 3：
------------------------
用如下SQL 进行监控，在sqlplus 里看起来格式有点乱，我放到Toad执行了。
  /* Formatted on 2011/6/16 16:06:44 (QP5 v5.163.1008.3004) */
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;
           通过监控发现两个session在执行相同的sql,他们在相同的cursor object上交互请求a shared mutex pin或者 an exclusive mutex pin 从而造成等待。
  --监视sql reae区的cursor object reload情况
SYS@anqing2(rac2)>  select namespace ,reloads from v$librarycache;
  NAMESPACE          RELOADS
--------------- ----------
SQL AREA            790805
TABLE/PROCEDURE     103713
BODY                    59
TRIGGER                 27
INDEX                94280
CLUSTER                 11
OBJECT                   0
PIPE                     0
JAVA SOURCE              0
JAVA RESOURCE            0
JAVA DATA                0
  11 rows selected.
  --监视parse情况
SYS@anqing2(rac2)> col name format a40
SYS@anqing2(rac2)> select s.sid, s.serial#,b.name,a.value
  2   from v$sesstat a, v$statname b, v$session s
  3   where a.statistic# = b.statistic# and s.sid=a.sid
  4   and b.name like '%parse%'
  5   and s.sid in (130,125);
  sid    serial# name                         value
---------- ---------- ---------------------------------------- ----------
125      41915 parse time cpu                115260
125      41915 parse time elapsed             146605
125      41915 parse count (total)             633792
125      41915 parse count (hard)             602732
125      41915 parse count (failures)           4
130       6074 parse time cpu                69559
130       6074 parse time elapsed              99149
130       6074 parse count (total)              394689
130       6074 parse count (hard)               365538
130       6074 parse count (failures)             0
  从这里看出，硬解析很多，library cache中的cursor object被频繁的reload。
    三. 几个与mutex 相关的视图
       在第一部分，提到了x$mutex_sleep 和x$mutex_sleep_history。我们在联机文档里看不到相关的说明。
         不过可以查看到v$mutex_sleep  和v$mutex_sleep_history的说明。 但是v$ 比x$ 字典显示的列要少。
  select * from x$mutex_sleep;
  select * from v$mutex_sleep;
    SYS@anqing2(rac2)> desc x$mutex_sleep_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(4)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 MUTEX_ADDR                                         RAW(4)
 MUTEX_IDENTIFIER                                   NUMBER
 SLEEP_TIMESTAMP                                    TIMESTAMP(6)
 MUTEX_TYPE                                         VARCHAR2(32)
 MUTEX_TYPE_ID                                      NUMBER
 GETS                                               NUMBER
 SLEEPS                                             NUMBER
 REQUESTING_SESSION                                 NUMBER
 BLOCKING_SESSION                                   NUMBER
 LOCATION_ID                                        NUMBER
 LOCATION                                           VARCHAR2(40)
 MUTEX_VALUE                                        RAW(4)
 P1                                                 NUMBER
 P1RAW                                              RAW(4)
 P2                                                 NUMBER
 P3                                                 NUMBER
 P4                                                 NUMBER
 P5                                                 VARCHAR2(64)
  SYS@anqing2(rac2)> desc v$mutex_sleep_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MUTEX_IDENTIFIER                                   NUMBER
 SLEEP_TIMESTAMP                                    TIMESTAMP(6)
 MUTEX_TYPE                                         VARCHAR2(32)
 GETS                                               NUMBER
 SLEEPS                                             NUMBER
 REQUESTING_SESSION                                 NUMBER
 BLOCKING_SESSION                                   NUMBER
 LOCATION                                           VARCHAR2(40)
 MUTEX_VALUE                                        RAW(4)
 P1                                                 NUMBER
 P1RAW                                              RAW(4)
 P2                                                 NUMBER
 P3                                                 NUMBER
 P4                                                 NUMBER
 P5                                                 VARCHAR2(64)
            -------------------------------------------------------------------------------------------------------
QQ:492913789
Email:ahdba@qq.com
Blog: http://www.cndba.cn/dave

DBA1 群：62697716(满);   DBA2 群：62697977(满)   DBA3 群：62697850(满)  
DBA 超级群：63306533(满);  DBA4 群： 83829929  DBA5群： 142216823   
DBA6 群：158654907  聊天 群：40132017   聊天2群：69087192
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请https://img-blog.csdnimg.cn/20190217105710569.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEwNzgxNDE=,size_16,color_FFFFFF,t_70《算法导论 第三版英文版》_高清中文版.pdf
https://pan.baidu.com/s/17D1kXU6dLdU0YwHM2cvNMw
《深度学习入门：基于Python的理论与实现》_高清中文版.pdf
https://pan.baidu.com/s/1IeVs35f3gX5r6eAdiRQw4A
《深入浅出数据分析》_高清中文版.pdf
https://pan.baidu.com/s/1GV-QNbtmjZqumDkk8s7z5w
《Python编程：从入门到实践》_高清中文版.pdf
https://pan.baidu.com/s/1GUNSg4mdpeOf1LC_MjXunQ
《Python科学计算》_高清中文版.pdf
https://pan.baidu.com/s/1-hDKhK-7rDDFll_UFpKmpw