热点块引发的Cache buffer Chains latch:
SQL语句即便适当进行了调优,有时也无法解决cache buffers cahins latch，若在编写SQL语句时的SQL工作方式,
只是持续扫描少数特定块,则在多个会话同时执行此SQL语句时,就会发生Hot Block引起的cache buffers chains latch争用。
测试方案如下:
1) 创建与测试相同的表和索引。
2) 即便有效扫描索引,多个会话也会反复扫描相同的块,因此发生Hot Block引起的cache buffers chains latch争用。
SQL> create or replace Procedure cbc_do_select(p_from in NUMBER,
2 p_to IN NUMBER) is
3 begin
4 -- 反复,集中扫描特定块
5 for idx in 1 .. 3000 loop
6 for x in (select id from cbc_test where id between p_from and p_to) loop
7 null;
8 end loop;
9 end loop;
10 end;
11 /
Procedure created
---同时执行20个读取工作
var job_no number;
begin
for idx in 1 .. 20 loop
dbms_job.submit(:job_no,'cbc_do_select(1000,1010);');
commit;
end loop;
end;
select sid,event,p1,p2,p3,p1raw from v$session where event='latch: cache buffers chains'
p1 p2 p3 p1raw
1 56 latch: cache buffers chains 758400976 150 0 000000002D3447D0
引发问题的SQL语句时select * from cbc_test where id between p_from and p_to,此SQL语句本身已经是优化的状态,因为通过cbc_test_idx索引,
只扫描必要范围并获取相应值。通过v$latch_children视图确认特定子latch使用是否偏多,从此可以间接判断是否是Hot Block引起的锁存器争用。
或者也可以捕捉v$session_wait 视图的PARAW列值,将反复被观察的值为锁存器地址利用。
SQL> SQL> SQL> SQL> select *
from (select addr, child#, gets, sleeps
from v$latch_children
where name = 'cache buffers chains'
order by sleeps desc)
where rownum <= 20 2 3 4 5 6 ;
ADDR CHILD# GETS SLEEPS
-------- ---------- ---------- ----------
2D3447D0 632 22491 74
2D351280 908 1843173 15
2D341E34 575 21975 6
2D33F498 518 15291 3
2D34FA08 874 4050 1
2D331E28 226 16410 1
2D33296C 241 12922 1
2D33A2D4 407 12470 1
2D346438 670 2590 1
2D3567B0 1024 2836 0
2D3560D8 1014 2830 0
ADDR CHILD# GETS SLEEPS
-------- ---------- ---------- ----------
2D35605C 1013 3634 0
2D355EE0 1012 1187 0
2D355E64 1011 1806 0
2D355DE8 1010 1455 0
2D355D6C 1009 2011 0
2D355BF0 1008 2615 0
2D355B74 1007 3166 0
2D355AF8 1006 8798 0
2D355A7C 1005 2829 0
已选择20行。
已选择20行。
SLEEPS :
SLEEPS NUMBER Number of times a willing-to-wait latch request resulted in a session sleeping while waiting for the latch
从结果来看child# 632发生了锁存器争用,利用x$bh视图可以确认哪些块是Hot Block
SQL> 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
from x$bh x
where hladdr in ('2D3447D0','2D341E34','2D351280')
order by hladdr, obj
2 3 4 5 6 7 8 9 10 11 12 13 14 ;
HLADDR OBJ OBJECT_NAME DBARFIL DBABLK TCH
-------- ---------- -------------------- ---------- ---------- ----------
2D341E34 37 I_OBJ2 1 32383 9
2D341E34 288 I_JOB_JOB 1 2017 43
2D341E34 288 I_JOB_JOB 1 2017 1
2D341E34 288 I_JOB_JOB 1 2017 1
2D341E34 288 I_JOB_JOB 1 2017 1
2D341E34 288 I_JOB_JOB 1 2017 1
2D341E34 444 STATS_TARGET$ 2 852 0
2D3447D0 37 I_OBJ2 1 32375 9
2D3447D0 236 I_IDL_CHAR1 1 63207 0
2D3447D0 267 SMON_SCN_TO_TIME_AUX 2 61809 0
2D3447D0 287 JOB$ 1 2009 1
HLADDR OBJ OBJECT_NAME DBARFIL DBABLK TCH
-------- ---------- -------------------- ---------- ---------- ----------
2D3447D0 287 JOB$ 1 2009 2
2D3447D0 287 JOB$ 1 2009 2
2D3447D0 287 JOB$ 1 2009 22
2D3447D0 287 JOB$ 1 2009 1
2D351280 2 ICOL$ 1 83980 25
2D351280 37 I_OBJ2 1 44472 10
2D351280 40 I_OBJ5 1 14339 1
2D351280 68 SYN$ 1 40600 14
2D351280 159 TRIGGER$ 1 83747 1
2D351280 6213 WRH$_SQL_PLAN 2 4265 0
2D351280 75405 WRH$_SQLSTAT_INDEX 2 4498 0
HLADDR OBJ OBJECT_NAME DBARFIL DBABLK TCH
-------- ---------- -------------------- ---------- ---------- ----------
2D351280 75500 CBC_TEST_IDX 7 291882 80
已选择23行。