X$BH与Buffer Header

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

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.