ORACLE :11.2.0.3
row cache lock事件
--内存share pool分为library cache,dictionary cache;而row cache lock对象分布在dictionary cache中；
--每次看到类似的告警短信心理都比较发毛；这类属于latch类的资源竞争，相当耗CPU，如果并发量大的话，容易down机
先模拟一个场景：原理，建立一个没有cache的序列，频繁读取nextval,而数据库在读取的同时也要相应的频繁修改这些数据字典中的值
create sequence seq01 start with 1;
开两个会话分别执行：
declare
v_n number;
begin
for i in 1..100000000 loop
select seq04.nextval into v_n from dual;
end loop;
end;
/
首先看下CPU的消耗变化,idle都变为0，后果很严重
[root@11g3 ~]# vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 75952 14716 7184 735132 0 1 28 249 220 285 6 3 89 2 0
2 0 75952 14220 7188 735132 0 0 4 28 914 1810 2 0 98 0 0
0 0 75952 14344 7188 735248 0 0 0 216 990 1869 7 2 89 2 0
2 1 75952 14096 7188 735240 0 0 0 32 952 1806 3 0 97 0 0
1 0 75952 14352 7196 735232 0 0 0 160 984 1860 3 2 84 11 0
0 0 75952 14352 7196 735244 0 0 0 60 973 1817 6 1 93 0 0
0 0 75952 14352 7196 735244 0 0 0 0 912 1837 0 0 100 0 0
1 0 75952 14228 7196 735244 0 0 0 28 930 1794 2 1 97 0 0
1 0 75952 14236 7196 735252 0 0 0 80 1034 1914 6 2 91 1 0
2 1 75952 14236 7204 735348 0 0 112 6712 3211 6855 30 55 13 2 0
2 0 75952 14236 7208 735360 0 0 16 7924 3641 7799 37 63 0 0 0
2 1 75952 13988 7208 735380 0 0 12 8048 3480 7347 37 63 0 0 0
1 2 75952 13996 7208 735612 0 0 188 7404 3336 6571 44 56 0 0 0
5 1 75952 12012 7208 736776 0 0 960 6032 3126 4675 56 44 0 0 0
2 2 75952 12632 7216 736812 0 0 40 5736 2991 4767 60 40 0 0 0
3 1 75952 12632 7220 736820 0 0 20 6644 3274 5678 54 46 0 0 0
3 0 75952 12020 7228 736832 0 0 24 6728 3251 5598 55 45 0 0 0
两会话的等待事件：
USERNAME EVENT
------------------------------ ----------------------------------------------------------------
AIKI row cache lock
AIKI row cache lock
定位：
SQL> select cache#,cache_name,lock_mode,lock_request,saddr from v$rowcache_parent where lock_mode<>0
2 /
CACHE# CACHE_NAME LOCK_MODE LOCK_REQUEST SADDR
---------- ---------------------------------------------------------------- ---------- ------------ ----------------
13 dc_sequences 5 0 0000000077DB6F60
SQL> /
CACHE# CACHE_NAME LOCK_MODE LOCK_REQUEST SADDR
---------- ---------------------------------------------------------------- ---------- ------------ ----------------
13 dc_sequences 5 0 0000000077DE0A20
SQL> /
CACHE# CACHE_NAME LOCK_MODE LOCK_REQUEST SADDR
---------- ---------------------------------------------------------------- ---------- ------------ ----------------
13 dc_sequences 5 0 0000000077DB6F60
SQL> /
CACHE# CACHE_NAME LOCK_MODE LOCK_REQUEST SADDR
---------- ---------------------------------------------------------------- ---------- ------------ ----------------
13 dc_sequences 5 0 0000000077DB6F60
SQL> /
CACHE# CACHE_NAME LOCK_MODE LOCK_REQUEST SADDR
---------- ---------------------------------------------------------------- ---------- ------------ ----------------
13 dc_sequences 5 0 0000000077DE0A20
--根据saddr在v$session.saddr中去匹配找出会话，可以看两会话间断相互阻塞 （轮流阻塞，不是同时）
select sid,username,status,sql_id,blocking_instance,blocking_session from v$session where saddr in (
'0000000077DB6F60',
'0000000077DE0A20'
);
SID USERNAME STATUS SQL_ID BLOCKING_INSTANCE BLOCKING_SESSION
1 36 AIKI ACTIVE 0hhquh91uxqv3 1 50
2 50 AIKI ACTIVE 0hhquh91uxqv3
SID USERNAME STATUS SQL_ID BLOCKING_INSTANCE BLOCKING_SESSION
1 36 AIKI ACTIVE 0hhquh91uxqv3
2 50 AIKI ACTIVE 0hhquh91uxqv3 1 36
--看下SQL的内容
SQL> select sql_text from v$sqlarea where sql_id='0hhquh91uxqv3';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT SEQ04.NEXTVAL FROM DUAL
=》果断kill掉会话,先解决；
我们这里实验就不kill,接着验证确认：
SQL> select object_id from dba_objects where object_name='SEQ04' and owner='AIKI';
OBJECT_ID
----------
75883
SQL> select to_char(75883,'xxxxxxxxxxxxxx') from dual;
TO_CHAR(75883,'
---------------
1286b
SQL> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1));
SPID
------------------------
13364
SQL> alter system set events 'immediate trace name row_cache level 12'; --dump row_cache对象
System altered.
然后在vi oracle11gr3_ora_13364.trc匹配1286b，紧跟着data=后面；可以看到
这序列确实导致了这个问题；
BUCKET 39:
row cache parent object: address=0x726caf98 cid=13(dc_sequences)
hash=c2e42726 typ=9 transaction=0x761a1018 flags=0000012a
own=0x726cb060[0x715a0018,0x715a0018] wat=0x726cb070[0x78560a38,0x78560a38] mode=X
status=VALID/UPDATE/-/-/IO/-/-/-/-
data=
=》0001286b 0002000c 000f0002 00050001 000002c1 00000000 00000000 00000000
02c10000 00000000 00000000 00000000 00000000 646464ce 64646464 64646464
00646464 00800000 00000000 00000000 00000000 00000000 141808c4 00000063
00000000 00000000 2d2d0000 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d
2d2d2d2d 2d2d2d2d 00002d2d 00000000 00000000 c2e42726 726caf98 00000000
717ff0f8 00000000 751377a8 00000000
