模拟cursor pin S wait on X

<pre name="code" class="sql">模拟cursor pin S wait on Xcreate table test tablespace users as select *from dba_objects;  BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'TEST',                                tabname          => 'TEST',                                estimate_percent => 30,                                method_opt       => 'for all columns size repeat',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;/测试开始:这是10.2版本提出的mutex(互斥)机制用来解决library cache bin latch争夺问题引入的新事件,是否使用这种机制受到隐含参数_kks_use_mutex_pin的限制,从10.2.0.2开始该参数default为true,使用这种机制oracle是为了解决library cache bin latch的串行使用问题,但是mutex貌似还不是很稳定,在很多系统中会出现cursor: pin S wait on X等待事件,这个事件和mutex的使用有关:SQL> SELECT nam.ksppinm NAME, val.ksppstvl VALUE  2  FROM x$ksppi nam, x$ksppsv val  3  WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%mutex%'  4  ORDER BY 1;NAME                                               VALUE-------------------------------------------------- ----------------------------------------_kks_use_mutex_pin                                 TRUESQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProdPL/SQL Release 10.2.0.4.0 - ProductionCORE10.2.0.4.0ProductionTNS for Linux: Version 10.2.0.4.0 - ProductionNLSRTL Version 10.2.0.4.0 - Production测试数据库版本:SQL> 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;  2    3    4    5  NAME     VALUE------------------------------------------------------------ ----------_kks_use_mutex_pin     TRUEselect SESSION_ID,ash.sample_time,       NAME,       P1,       P2,       P3,       WAIT_TIME,       CURRENT_OBJ#,       CURRENT_FILE#,       CURRENT_BLOCK#  from v$active_session_history ash, v$event_name enm where ash.event# = enm.event#        SESSION_ID   SANMPLE_TIME                 NAME                    P1            P21162908-7月 -14 01.24.43.480 上午cursor: pin S          87099441513276890-1002162908-7月 -14 01.24.42.480 上午cursor: pin S          87099441513276890-1003162608-7月 -14 01.24.41.480 上午cursor: pin S wait on X  9143406281067581443276860-1004162708-7月 -14 01.24.40.480 上午cursor: pin S          177405078015898250-1005162608-7月 -14 01.24.39.470 上午cursor: pin S wait on X  2029890821067581443276870-1006162408-7月 -14 01.24.38.470 上午cursor: pin S wait on X  1724083151067581443276850-1007162408-7月 -14 01.24.37.470 上午cursor: pin S          41075728881066270733276870-1008163408-7月 -14 01.24.37.470 上午latch: library cache  58958107221500-1009162408-7月 -14 01.24.36.470 上午cursor: pin S          41075728881066270733276870-10010162708-7月 -14 01.24.35.470 上午latch: library cache  58958107221500-10011163408-7月 -14 01.24.35.470 上午cursor: pin S wait on X  47183051066270723276880-100select SESSION_ID,ash.sample_time,       NAME,       P1,       P2,       P3,       WAIT_TIME,       CURRENT_OBJ#,       CURRENT_FILE#,       CURRENT_BLOCK#  from v$active_session_history ash, v$event_name enm where ash.event# = enm.event#1163108-7月 -14 01.14.57.196 上午cursor: pin S wait on X371930197603276810-100select 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                                                                 sid_hold_mutex_x11637cursor: pin S wait on XConcurrency4286791313065F00001631select object_id from test t where object_id=89declare   v_string varchar2(100) := 'alter system flush shared_pool';   msql     varchar2(200); begin   loop     for i in 1 .. 100 loop       msql := 'select object_id from test t where object_id=' || i;       execute immediate msql;     end loop;   end loop; end;                                                                    sid_hold_mutex_x 11627cursor: pin SOther868910173065D00011629select object_id from test t where object_id=3select object_id from test t where object_id=3                                                                                    sid_hold_mutex_x21631cursor: pin S wait on XConcurrency4107572888065B00001627select object_id from test t where object_id=100select object_id from test t where object_id=60