Oracle等待事件详细分析
SweetHarbour 2012-09-16
从今天起，木木同学要认真整理一下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<=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 3786 3
251 cache buffers chains 1997 2
578 cache buffers chains 3857 2
733 cache buffers chains 3554 2
280 cache buffers chains 4549 2
已选择20行。
看来837号cbc 子锁存器争用比较多，可以判定是比较热的块。
我们也可以通过v$latch_children视图，确定热块的cbc 锁存器的地址：SQL> 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<=20;
LATCH# CHILD# ADDR GETS SLEEPS
---------- ---------- -------- ---------- ----------
116 894 6CB7F7F0 554344 90
116 837 6CFFF70C 727379 80
116 951 6CB839D8 471117 74
116 56 6C783224 107910 60
116 341 6C797BAC 144056 47
116 458 6C7A02F4 37434 38
116 240 6C7906E4 99251 37
116 297 6C7948CC 98903 36
116 566 6C7A7FD4 94932 36
116 790 6CFFC0B4 107997 36
116 280 6C793524 87455 34
LATCH# CHILD# ADDR GETS SLEEPS
---------- ---------- -------- ---------- ----------
116 919 6CB814D8 49667 31
116 583 6C7A937C 85883 29
116 113 6C78740C 120322 28
116 627 6C7AC65C 91840 28
116 534 6C7A5AD4 112523 27
116 270 6C792994 50302 26
116 418 6C79D4B4 37697 26
116 680 6C7B03A4 104550 26
116 676 6C7AFF04 103297 26
已选择20行。
通过上面红色的锁存器地址，结合x$bh 视图，查看tch的次数，确定热快：
SQL> 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.我也不知咋回事。。
