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减少数据的访问,避免或优化某些容易引起的争用操作

来减少热块竞争

ITPub博客

X$BH与Buffer Header

最新文章