<pre name="code" class="sql">模拟cursor pin S wait on X
create 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 TRUE
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL 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 TRUE
select 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 P2
1 1629 08-7月 -14 01.24.43.480 上午 cursor: pin S 870994415 1 327689 0 -1 0 0
2 1629 08-7月 -14 01.24.42.480 上午 cursor: pin S 870994415 1 327689 0 -1 0 0
3 1626 08-7月 -14 01.24.41.480 上午 cursor: pin S wait on X 914340628 106758144 327686 0 -1 0 0
4 1627 08-7月 -14 01.24.40.480 上午 cursor: pin S 1774050780 1 589825 0 -1 0 0
5 1626 08-7月 -14 01.24.39.470 上午 cursor: pin S wait on X 202989082 106758144 327687 0 -1 0 0
6 1624 08-7月 -14 01.24.38.470 上午 cursor: pin S wait on X 172408315 106758144 327685 0 -1 0 0
7 1624 08-7月 -14 01.24.37.470 上午 cursor: pin S 4107572888 106627073 327687 0 -1 0 0
8 1634 08-7月 -14 01.24.37.470 上午 latch: library cache 589581072 215 0 0 -1 0 0
9 1624 08-7月 -14 01.24.36.470 上午 cursor: pin S 4107572888 106627073 327687 0 -1 0 0
10 1627 08-7月 -14 01.24.35.470 上午 latch: library cache 589581072 215 0 0 -1 0 0
11 1634 08-7月 -14 01.24.35.470 上午 cursor: pin S wait on X 4718305 106627072 327688 0 -1 0 0
select 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#
1 1631 08-7月 -14 01.14.57.196 上午 cursor: pin S wait on X 3719301976 0 327681 0 -1 0 0
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 sid_hold_mutex_x
1 1637 cursor: pin S wait on X Concurrency 4286791313 065F0000 1631 select object_id from test t where object_id=89 declare 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
1 1627 cursor: pin S Other 868910173 065D0001 1629 select object_id from test t where object_id=3 select object_id from test t where object_id=3
sid_hold_mutex_x
2 1631 cursor: pin S wait on X Concurrency 4107572888 065B0000 1627 select object_id from test t where object_id=100 select object_id from
test t where object_id=60