出现CACHE BUFFER CHAIN 后首先查看V$SESSION_WAIT 找到P1raw的值,此P1对应了v$latch_children addr和 x$bh 的HLADDR
然后查看v$latch_children 找到GET最高的LATCH ADDR
select * from v$latch_children where name='cache buffers chains' order by gets desc;
最后和X$BH连接找到对象
查看当前等待的,注意本语句只能查询本实例，RAC需要每个实例都进行。
select hladdr,
obj,
b.sid,
b.sql_id,
(select object_name
from dba_objects
where (data_object_id is null and object_id = x.obj)
or data_object_id = x.obj
and rownum = 1) as object_name,
dbarfil,
dbablk,
tch,
TIM
from x$bh x,
(select P1RAW,sql_id,sid from v$session where wait_class'Idle' and event='latch: cache buffers chains') b
where HLADDR in (select P1RAW from v$session where wait_class'Idle' and event='latch: cache buffers chains')
and x.hladdr=b.P1RAW(+)
order by tch desc;
查看系统中最为严重的
select hladdr,
obj,
(select object_name
from dba_objects
where (data_object_id is null and object_id = x.obj)
or data_object_id = x.obj
and rownum = 1) as object_name,
dbarfil,
dbablk,
tch,
TIM
from x$bh x
where HLADDR in (select addr
from (select *
from (select *
from v$latch_children
where name = 'cache buffers chains'
order by gets desc)
where rownum
order by tch desc;
测试：
1、建立测试表
create table cbc_test(id number,name char(100));
2、插入数据
insert into cbc_test(id,name)
select rownum,object_name from dba_objects;
insert into cbc_test
select * from cbc_test;
多执行几次
SQL> select count(*) from cbc_test;
COUNT(*)
----------
401336
3、创建索引
create index cbc_test_idx on cbc_test(id);
4、建立存储过程进行大量的扫描
create or replace procedure cbc_do_select is
begin
for x in (select /*+ INDEX(cbc_test cbc_test_idx) */ * from cbc_test where id >=0) loop
null;
end loop;
end;
5、模拟足够多的JOB
var job_no number;
begin
sys.dbms_job.submit(job => :job,
what => 'cbc_do_select;',
next_date => to_date('13-06-2013 10:16:04', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1/2880');
commit;
end;
6、观察
HLADDR OBJ SID SQL_ID OBJECT_NAME DBARFIL DBABLK TCH TIM
---------------- ---------- ---------- ------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
000007FF1C0FCBC0 53216 CBC_TEST 1 72322 534 1371092084
000007FF1C7D72E8 53216 CBC_TEST 1 71538 529 1371092084
000007FF1C784708 53216 138 3uakn4vk92jf8 CBC_TEST 1 75337 517 1371092080
000007FF1C0FCBC0 53216 CBC_TEST 1 72089 491 1371092080
000007FF1C7D72E8 53216 CBC_TEST 1 71305 487 1371092080
000007FF1C0FCBC0 53216 CBC_TEST 1 76660 469 1371092080
000007FF1C7D72E8 53216 CBC_TEST 1 75876 466 1371092080
000007FF1CBDB268 53216 CBC_TEST 1 74975 429 1371092082
000007FF1C784708 53216 138 3uakn4vk92jf8 CBC_TEST 1 70999 422 1371092082
000007FF1C784708 53216 138 3uakn4vk92jf8 CBC_TEST 1 75570 421 1371092082
000007FF1C784708 4294967295 138 3uakn4vk92jf8 2 193 26 1371091585
000007FF1C784708 9091 138 3uakn4vk92jf8 WRH$_SGA_TARGET_ADVICE 3 3599 6 1371088807
000007FF1CBDB268 8999 WRH$_SQL_SUMMARY 3 3004 4 1371045615
000007FF1C784708 4226 138 3uakn4vk92jf8 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 3 34198 4 1371045618
000007FF1C7D72E8 8995 WRH$_SQLTEXT 3 34504 3 1371045618
000007FF1C7D72E8 181 C_TOID_VERSION# 1 58291 3 1371086393
000007FF1C784708 93 138 3uakn4vk92jf8 ACCESS$ 1 27386 3 1371014071
000007FF1C0FCBC0 181 C_TOID_VERSION# 1 50399 3 1371086393
000007FF1C784708 181 138 3uakn4vk92jf8 C_TOID_VERSION# 1 14372 3 1371086393
000007FF1CBDB268 101 I_SYN1 1 763 2 1371045607
HLADDR OBJ SID SQL_ID OBJECT_NAME DBARFIL DBABLK TCH TIM
---------------- ---------- ---------- ------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
000007FF1CBDB268 53100 WRH$_SERVICE_STAT_PK 3 37941 2 1371045619
000007FF1C784708 53090 138 3uakn4vk92jf8 WRH$_PARAMETER 3 38536 2 1371045620
000007FF1C7D72E8 4294967295 2 499 1 1371089114
000007FF1C0FCBC0 52856 WRH$_SQLSTAT_INDEX 3 35288 1 1371045613
000007FF1C0FCBC0 73 IDL_UB1$ 1 11124 1 1371014072
000007FF1C7D72E8 75 IDL_UB2$ 1 6002 1 1370999208
26 rows selected
