X$BH与Buffer Header
buffer header数据,可以从数据库的数据字典表中查询得到,这张字典表就是x$bh,x$bh中的bh就是
指buffer headers,每个buffer在x$bh中都存在一条记录
buffer header中存储每个buffer容纳的数据块的文件号,块地址,状态等重要信息,根据这些信息,
结合dba_extents视图,可以很容易地找到每个buffer对应的对象信息:
x$bh中还有一个重要的字段TCH,TCH为Touch的缩写,表示一个Buffer的访问次数,buffer被访问的次
数越多,说明该buffer就越抢手,也就可能存在热块竞争的问题
通过对下查询获得当前数据库最繁忙的buffer
SQL> select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<21; ADDR TS# FILE# DBARFIL DBABLK TCH ---------------- ---------- ---------- ---------- ---------- ---------- 9FFFFFFFBF5AACA8 7 12 12 638244 1722 9FFFFFFFBF5AACA8 7 13 13 598707 1636 9FFFFFFFBF5AAFF8 7 11 11 261996 1629 9FFFFFFFBF5AAFF8 6 8 8 135404 1614 9FFFFFFFBF5AAFF8 7 11 11 655501 1587 9FFFFFFFBF5AAFF8 7 11 11 269628 1568 9FFFFFFFBF5AAFF8 7 11 11 2742315 1568 9FFFFFFFBF5AAFF8 7 11 11 269612 1562 9FFFFFFFBF5AACA8 7 13 13 601755 1538 9FFFFFFFBF5AAFF8 7 13 13 599052 1514 9FFFFFFFBF5AACA8 7 13 13 254900 1506 9FFFFFFFBF5AACA8 7 12 12 261898 1504 9FFFFFFFBF5AAFF8 7 11 11 662797 1491 9FFFFFFFBF5AACA8 7 13 13 610957 1487 9FFFFFFFBF5AAFF8 6 8 8 715684 1465 9FFFFFFFBF5AAFF8 7 11 11 665204 1462 9FFFFFFFBF5AAFF8 7 11 11 132492 1461 9FFFFFFFBF5AACA8 6 8 8 1766500 1458 9FFFFFFFBF5AACA8 7 11 11 273549 1445 9FFFFFFFBF5AAFF8 7 11 11 266099 1441 20 rows selected
再结合dba_extents中的信息,可以查询得到这些热点buffer都来自哪些对象;
select a.owner,a.segment_name,a.segment_type from dba_extents a, (select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<21) b where a.relative_fno=b.dbarfil and a.block_id< =b.dbablk and a.block_id+a.blocks>b.dbablk;
除了查询x$bh之外,也可以从buffer cache的转储信息中,看到buffer header的具体内容
BH (0x25feb12c) file#: 1 rdba: 0x0040b894 (1/47252) class: 1 ba: 0x25cec000
set: 3 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 75
dbwrid: 0 obj: 181 objn: 183 tsn: 0 afn: 1
hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]
lru-flags:
ckptq: [NULL] fileq: [NULL] objq: [25feb124,25feb284]
st: XCURRENT md: NULL tch: 1
flags:
LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
buffer tsn: 0 rdba: 0x0040b894 (1/47252)
scn: 0x0000.00074869 seq: 0x01 flg: 0x04 tail: 0x48690601
frmt: 0x02 chkval: 0xa2b2 type: 0x06=trans data
在oracle10g以前,数据库的等待事件中,所有的latch等待都被归纳为latch free等待
在statspack的report中,如果在top5等待事件中看到latch free这一等待处于较高的位置
那么就要地行研究和解决了
由于latch free是一个汇总等待事件,我们需要从v$latch视图中获得具体的latch竞争主要
是由哪些latch引起的
如果需要具体确定热块对象,可以从v$latch_children中查询到具体的子latch信息
SQL> select * from ( 2 select addr,child#,gets,misses,sleeps,immediate_gets,immediate_misses, spin_gets 3 from v$latch_children 4 where name='cache buffers chains' 5 order by sleeps desc) 6 where rownum<6; ADDR CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS ---------------- ---------- ---------- ---------- ---------- -------------- ---------------- ---------- C00000001888BF70 14784 126455972 1469279 636 2832 1 1468752 C0000000187F9940 11786 48496473 345817 216 2948 0 345623 C000000018645EF0 2864 63718005 332144 211 2645 1 331967 C0000000187CEDA8 10911 48514435 329026 207 3791 0 328844 C00000001877CDF0 9232 63325754 341420 202 3223 0 341256
在x$bh中还存在另外一个关键字段HLADDR，即hash chain latch address,这个字段可以和v$latch_children.addr
进行关联,这样就可以把具体的latch竞争和数据块关联起来,再结合dba_extents视图,就可以找到具体的热块竞争
对象,找到具体热点对象后,可以结合v$sqlarea或v$sqltext,找到频繁操作这些对象的sql,然后对其进行优化,
就可以缓解或解决热块竞争的问题了
SQL> select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from ( 2 select * from ( 3 select addr,ts#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc) 4 where rownum<6) a, 5 (select addr,gets,misses,sleeps from v$latch_children 6 where name='cache buffers chains') b 7 where a.hladdr=b.addr 8 / ADDR TS# DBARFIL DBABLK TCH GETS MISSES SLEEPS ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- C00000001886F4D8 7 12 638244 2273 656301 13 0 C00000001876C900 7 11 261996 2320 2972787 89 0 C0000000186BC960 7 11 269628 2220 495549 10 0 C0000000186A9FB8 6 8 135404 2303 3257192 101 0 C000000018634678 7 11 655501 2257 3640151 30614 26
可以使用下面的语句来找到这些热点buffer的对象信息
select a.owner,a.segment_name,a.segment_type from dba_extents a, (select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<21) b where a.relative_fno=b.dbarfil and a.block_id< =b.dbablk and a.block_id+a.blocks>b.dbablk;
或
select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from ( select * from ( select addr,ts#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc) where rownum<6) a, (select addr,gets,misses,sleeps from v$latch_children where name='cache buffers chains') b, dba_extents c where a.hladdr=b.addr and c.relative_fno=a.dbarfil and c.block_id< =a.dbablk and c.block_id+c.blocks>a.dbablk
再与v$sqltext或v%sqlarea视图关联可以找到操作这些热块对象的相关sql
select hash_value,sql_fulltext from v$sqlarea where sql_id in( select a.sql_id from v$sqltext a, (select distinct b.owner,b.segment_name,b.segment_type from dba_extents b, (select dbarfil,dbablk from( select dbarfil,dbablk from x$bh order by tch desc) where rownum<11) c where b.relative_fno=c.dbarfil and b.block_id< =c.dbablk and b.block_id+b.blocks>c.dbablk) d where a.sql_text like '%'||d.segment_name||'%' and d.segment_type='TABLE')
找到这些sql语句后,就可以通过优化sql减少数据的访问,避免或优化某些容易引起的争用操作
来减少热块竞争