出現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;

select * from v$latch_children where name='cache buffers chains' order by sleeps 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 v$latch_children

where name = 'cache buffers chains'

order by gets desc)

where rownum < 10))

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

等待模擬-cache buffer chain

相關文章