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减少数据的访问.
阅读(2730) | 评论(0) | 转发(0) |
