从今天起，木木同学要认真整理一下Oracle中常见的等待事件，通过这部分的学习，希望自己能对oracle内部的结构能有一个更清晰的认识，有兴趣的童鞋一起来哇。
1、latch：cache buffers chains从oracle 9i开始，以只读为目的的查询chains时，可以将cache buffers chains锁存器以shared模式共享，因此有助于减少争用。（我们需要注意，若能共享cache buffer chains 锁存器，理论上理论上不应该发生同时执行select 操作引起cbc锁存器的争用，但实际的测试结果表明，同时执行select依然会发生cbc锁存器争用，其理由是与buffer lock相关：为了读取工作，以shared模式已经获得锁存器，但是读取实际缓冲区过程中，还要以shared 模式获取buffer lock，在此过程真呢过需要部分修改缓冲区头信息。因此在获取buffer lock过程中，需要将cbc锁存器修改为exclusive 模式，在释放buffer lock期间也需要exclusive模式获取cbc锁存器，在此过程中会发生争用。）
发生cache buffers chains 锁存器争用代表性的情况如下：低效的SQL 和 hot block（热块）
低效SQL引起的cbc争用先介绍视图：v$latch_children 数据库中有些类别的latches拥有多个。v$latch中提供了每个类别的总计信息。如果想查看单个latch，可以通过查询本视图：查询数据库中所有latch的名字和个数:SQL> select name,count(*) ct from v$latch_children group by name order by ct desc;
NAME CT
---------------------------------------- ----------
cache buffers chains 1024
SQL memory manager workarea list latch 67
channel operations parent latch 65
global tx hash mapping 47
message pool operations parent latch 34
name-service namespace bucket 32
simulator hash latch 32
row cache objects 29
redo allocation 20
In memory undo latch 18
checkpoint queue latch 16
NAME CT
---------------------------------------- ----------
msg queue 15
JS queue access latch 13
commit callback allocation 11
transaction allocation 11
buffer pool 8
cursor bind value capture 8
simulator lru latch 8
object queue header operation 8
object queue header heap 8
cache buffers lru chain 8
business card 8
NAME CT
---------------------------------------- ----------
shared pool 7
flashback copy 6
virtual circuit queues 6
post/wait queue 5
slave class 5
JS slv state obj latch 4
redo copy 4
session switching 4
parallel query alloc buffer 4
job workq parent latch 3
undo global data 3
NAME CT
--------------------------------------- -----------
library cache pin allocation 3
library cache pin 3
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 select * from
2 (select latch#,child#,addr,gets,sleeps from v$latch_children
3 where name='cache buffers chains'
4 order by sleeps desc
5 )where rownum select hladdr,obj,(select object_name from dba_objects
2 where (data_object_id is null and object_id=x.obj)
3 or data_object_id=x.obj
4 and rownum=1 )as object_name,dbarfil,dbablk,tch
5 from x$bh x
6 where hladdr in('6CB7F7F0','6CFFF70C')
7 order by hladdr,obj;
HLADDR OBJ OBJECT_NAME DBARFIL DBABLK TCH
-------- ---------- ------------- ---------- ---------- ----------
6CB7F7F0 18 OBJ$ 1 47810 0
6CB7F7F0 109 I_SYSAUTH1 1 827 15
6CB7F7F0 57855 CBC_TEST 1 58171 0
6CB7F7F0 57855 CBC_TEST 1 58655 0
6CFFF70C 2 ICOL$ 1 42272 17
6CFFF70C 18 OBJ$ 1 47811 0
6CFFF70C 109 I_SYSAUTH1 1 828 15
6CFFF70C 57855 CBC_TEST 1 58172 0
6CFFF70C 57855 CBC_TEST 1 58656 0
但是很不幸，我这里也没有出现热块的迹象，因为tch竟然都是0.我也不知咋回事。。