oracle sqlcode -4092,X$BH和Buffer Header(深入浅出Oracle笔记)

Buffer Header数据，可以从数据库的字典表中查询得到，这张字典表是X$BH，X$BH中的BH就是Buffer Headers，每个Buffer在X$BH中都存在一条记录

X$BH中还有一个重要字段TCH，TCH为Touch的缩写，表示一个Buffers 的访问次数，Buffer被访问的次数越多，说明该Buffer越"抢手"，也就可能存在热点块竞争问题。

下面查询可获得数据库最繁忙的Buffer

SQL> select * from

2  (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh

3  order by tch desc)

4  where rownum<11;

ADDR            TS#      FILE#    DBARFIL     DBABLK        TCH

-------- ---------- ---------- ---------- ---------- ----------

11357E60          0          1          1      11248         96

11357E60          2          3          3        192         96

126A1CCC          0          1          1      11336         96

11357E60          0          1          1      11249         95

126A1CCC          0          1          1       1953         94

126A1CCC          0          1          1       1952         94

126A1F60          0          1          1      11250         94

11357E60          0          1          1      11233         92

11357E60          2          3          3        240         92

11357E60          2          3          3        144         92

已选择10行。

在结合dba_extents中的信息，可以查询得到这些热点Buffer都来自哪些对象:

SQL> select e.owner,e.segment_name,e.segment_type

2  from dba_extents e,

3  (select * from(select addr,ts#,file#,dbarfil,dbablk,tch

4  from x$bh order by tch desc)

5  where rownum<11)b

6  where e.relative_fno=b.dbarfil

7  and e.block_id<=b.dbablk

8  and e.block_id+e.blocks>b.dbablk;

OWNER      SEGMENT_NAME                   SEGMENT_TYPE

---------- ------------------------------ ------------------------------

SYS        JOB$                           TABLE

SYS        JOB$                           TABLE

SYS        SCHEDULER$_JOB                 TABLE

SYS        SCHEDULER$_LIGHTWEIGHT_JOB     TABLE

SYS        _SYSSMU5_1235568195$           TYPE2 UNDO

SYS        SCHEDULER$_JOB                 TABLE

SYS        SCHEDULER$_JOB                 TABLE

SYS        _SYSSMU2_1235568195$           TYPE2 UNDO

SYS        _SYSSMU8_1235568195$           TYPE2 UNDO

SYS        SCHEDULER$_CLASS               TABLE

已选择10行。

热点块与竞争的解决

可以从v$latch_children中查询具体的子Latch信息

SQL> select * from(select addr,child#,gets,misses,sleeps,immediate_gets igets,

2  immediate_misses imiss,spin_gets sgets

3  from v$latch_children

4  where name='cache buffers chains'

5  order by sleeps desc)

6  where rownum<11;

ADDR     CHILD#  GETS MISSES     SLEEPS      IGETS      IMISS      SGETS

-------- ------ ----- ------ ---------- ---------- ---------- ----------

32151190   3284  2995      1          1         29          0          0

3219C790   4096   409      0          0         25          0          0

3219C710   4095   635      0          0         22          0          0

3219C690   4094   666      0          0         23          0          0

3219C610   4093   584      0          0         26          0          0

3219C590   4092  4724      0          0         23          0          0

3219C510   4091   678      0          0         32          0          0

3219C490   4090   390      0          0         23          0          0

3219C410   4089   453      0          0         26          0          0

3219C390   4088  1748      0          0        155          0          0

X$BH中还存在另外一个关键字段HLADDR，即Hash Chain Latch Address，这个字段可以和v$latch_child.addr进行连接，这样就可以把具体的Latch竞争和数据块关联起来，在结合dba_extents视图，就可以找到具体的热点竞争对象，找到具体热点竞争对象之后，可以结合v$sqlarea或者v$sqltext，找到频繁操作这些对象的SQL，然后对其进行优化，即可缓解或解决热点块竞争的问题。

可以通过如下查询获取当前持有最热点数据块的Latch及Buffer信息:

1  select b.addr,a.ts#,a.dbarfil,a.tch,b.gets,b.misses,b.sleeps from

2  (select * from (

3  select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from x$bh order by

4  tch desc) where rownum<11) a,

5  (select addr,gets,misses,sleeps from v$latch_children where

6  name='cache buffers chains')b

7  where a.hladdr=b.addr

SQL> /

ADDR            TS#    DBARFIL        TCH   GETS MISSES     SLEEPS

-------- ---------- ---------- ---------- ------ ------ ----------

327EB254          0          1        153  12335      0          0

320B2510          0          1        153  16504      0          0

3211C810          2          3        131   4644      0          0

320F5E90          0          1        129   4630      0          0

3218A590          0          1        129   6176      0          0

32064010          0          1        127   2705      0          0

32151190          0          1        127   3152      1          1

3219C590          2          3        125   4935      0          0

3208E490          0          1        124   1191      0          0

32079210          0          1        123   1179      0          0

已选择10行。

根据如下的sql找到这些热点buffer的对象的信息

SQL> select e.owner,e.segment_name,e.segment_type from dba_extents e,

2  (select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh

3  order by tch desc)

4  where rownum<11) b

5  where e.relative_fno=b.dbarfil and e.block_id<=b.dbablk

6  and e.block_id+e.blocks>b.dbablk;

OWNER      SEGMENT_NAME                   SEGMENT_TYPE

---------- ------------------------------ ------------------------------

SYS        JOB$                           TABLE

SYS        JOB$                           TABLE

SYS        _SYSSMU5_1235568195$           TYPE2 UNDO

SYS        SCHEDULER$_JOB                 TABLE

SYS        SCHEDULER$_LIGHTWEIGHT_JOB     TABLE

SYS        SCHEDULER$_JOB                 TABLE

SYS        SCHEDULER$_JOB                 TABLE

SYS        _SYSSMU2_1235568195$           TYPE2 UNDO

SYS        SCHEDULER$_CLASS               TABLE

SYS        C_USER#                        CLUSTER

已选择10行。

结合v$sqltext或v$sqlarea，可以找到操作这些对象的相关SQL，继续查询:

SQL> break on hash_value skip 1

SQL> select hash_value,sql_text

2  from v$sqltext

3  where (hash_value,address) in (

4  select a.hash_value,a.address from v$sqltext a,

5  (select distinct a.owner,a.segment_name,a.segment_type

6  from dba_extents a,

7  (select dbarfil,dbablk from (

8  select dbarfil,dbablk from x$bh order by tch desc)

9  where rownum<11) b

10  where a.relative_fno=b.dbarfil

11  and a.block_id<=b.dbablk

12  and a.block_id+a.blocks>b.dbablk) b

13  where a.sql_text like '%'||b.segment_name||'%' and b.segment_type='TABLE'

14  )order by hash_value,address,piece;

找到sql之后，就可以通过优化SQL减少数据的访问.

阅读(1511) | 评论(0) | 转发(0) |