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減少資料的訪問,避免或最佳化某些容易引起的爭用操作

來減少熱塊競爭

X$BH與Buffer Header

相關文章