library cache hash chains 3
peplm 3
library cache lock 3
library cache lock allocation 3
library cache 3
Shared B-Tree 2
session idle bit 2
parallel query stats 2
longop free list parent 2
NAME CT
---------------------------------------- ----------
latch wait list 2
ksfv messages 2
enqueue hash chains 2client/application info 2
channel handle pool latch 1
granule operation 1
logminer context allocation 1
session queue latch 1
sim partition latch 1
msg queue latch 1
done queue latch 1
已选择55行。
如此说来，oracle10g中有55个有名字的latch，拥有量最大的就是我们的cbc latch，正好1024个。
我通过构建测试环境，创建了表，加上索引。并且创建一个全表扫描的过程：SQL> create table cbc_test(id number, name char(100));SQL> insert into cbc_test(id,name) select object_id, object_name from dba_objects;SQL> cretate index cbc_test_idx on cbc_test(id);
好了，下面进行不必要的广泛扫描索引：创建一个过程：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;
反复执行此过程2000次：var job_no number;
begin
for idx in 1..2000 loop
dbms_job.submit(:job_no,'cbc_do_select;');
commit;
end loop;
end;
查看一下cbc 锁存器对应的CHILD#,GETS, SLEEPS判断子锁存器上使用的次数和争用是否集中：
select * from
2 (select child#,name,gets,sleeps from v$latch_children
3 where name='cache buffers chains'
4 order by sleeps desc
5 )where rownum<=20;
CHILD# NAME GETS SLEEPS
---------- ------------------------------ ---------- ----------
837 cache buffers chains 34466 28
67 cache buffers chains 23994 15
684 cache buffers chains 6288 14
238 cache buffers chains 3823 12
898 cache buffers chains 4868 12
908 cache buffers chains 32807 10
288 cache buffers chains 3956 8
737 cache buffers chains 3865 8
412 cache buffers chains 1671 8
968 cache buffers chains 2706 7
420 cache buffers chains 2998 6
CHILD# NAME GETS SLEEPS
---------- ------------------------------ ---------- ----------
460 cache buffers chains 3912 6
1 cache buffers chains 2564 4
839 cache buffers chains 1119 4
951 cache buffers chains 21741 4
33 cache buffers chains