cache buffers chains以及热块解决方案
今天是2013-10-10，今天下午我调休了，中午饭过后从14点一直睡到16点，这种感觉真爽。
之前学习过关于buffer cache的cache buffers lru chain的知识，其中说到了lru，dirty list，ws等等，详见：http://blog.csdn.net/xiaohai20102010/article/details/11490925
今天在看一下buffer bucket 与 cache buffers chain
为了快速找到查询中所需要的block是否在cache buffer中，oracle划分了bucket，根据每个数据块dba进行hash计算，然后存放到不同的bucket中区，bucket中存有cache buffers chain以及相关block的buffer header，举个例子，buffer相当于一个图书馆，bucket相当于每个抽屉，在这个抽屉中记录了是每个buffer中块的信息也就是buffer header，这个名片的信息（buffer header）通过cache buffers chain进行串联起来，每个抽屉都要有专门人进行管理，不然大家都翻卡片找自己的书那么不乱了嘛，这个管理这就是cache buffer chain latch当我们找书的时候，首先经过hash计算找到buffer位于哪个bucket中，然后通过获得cache buffers chain lath然后从cache buffers chain上 拿到每个buffer的名片信息，然后再去获得这个buffer，如果没有找到，那么就会从数据文件中去读这个所需信息。（通俗易懂的说发，呵呵)
ORACLE 中可以通过查看隐含参数可以知道有多个bucket，就像查看x$KCBWDS可以知道有多少个ws一样。
eg：
SQL> set verify off
SQL> @getsp.sql
Enter value for par: db_block_hash
KSPPINM KSPPSTVL KSPPDESC
-------------------------------------------------- -------------------- ------------------------------------------------------------
_db_block_hash_buckets 65536 Number of database block hash buckets
_db_block_hash_latches 2048 Number of database block hash latches
SQL>
可以看到有65536个bucket，但是latch的数量为：2048，也就是说每个cache buffers chain latch 管理32个bucket，65536/2048=32,在google找到了一个图，如下，可以更加明白：
首先我通过转储buffers然后找到一个chain如下：
EG:
ALTER SYSTEM SET EVENTS 'IMMEDAITE TRACE NAME BUFFERS LEVEL 10';
CHAIN: 35 LOC: 0x7bcf8bb0 HEAD: [0x733dc118,0x733dc118]
BH (0x733dc060) file#: 1 rdba: 0x004075b5 (1/30133) class: 1 ba: 0x730a0000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 475,28
dbwrid: 0 obj: 225 objn: 225 tsn: 0 afn: 1 hint: f
hash: [0x7bcf8bb0,0x7bcf8bb0] lru: [0x733dc288,0x733dc018]
lru-flags: debug_dump
ckptq: [NULL] fileq: [NULL] objq: [0x733dc790,0x733dbf08] objaq: [0x733dc7a0,0x733dbf18]
st: XCURRENT md: NULL fpin: 'kdswh05: kdsgrp' tch: 2
flags:
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
buffer tsn: 0 rdba: 0x004075b5 (1/30133)
scn: 0x0000.0002d9f1 seq: 0x01 flg: 0x06 tail: 0xd9f10601
frmt: 0x02 chkval: 0x1ca5 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
可以看到这是第35个chain，其中包含的是buffer header 信息，bh就是代表buffer header的，在其中还可以找到刚刚提及到的hash计算，hash: [0x7bcf8bb0,0x7bcf8bb0]代表下一个buffer header 的地址以及上一个buffer header地址，lru: [0x733dc288,0x733dc018]代表在lru中下一个buffer块的位和上一个lru中buffer块的位置。另外还有一个tch：2，这个代表这个快被两次访问，如果该值越到，那么这个块就越热手 ，大家都在找她，也就会出现热点块的概念，同时伴随着chache buffers chain的latch 竞争。
对于chain中的bh的信息，在oracle中还存有一个fixed table 那就是x$bh，对于该视图介绍，请看博客地址：
http://blog.csdn.net/xiaohai20102010/article/details/10285449
这里介绍几个常用的字段信息：
TS# NUMBER 8.X Tablespace number
DBARFIL NUMBER 8.X Relative file number of block
DBAFIL NUMBER 7.3 File number of block
DBABLK NUMBER Block number of block
NXT_HASH NUMBER Next buffer on this hash chain
PRV_HASH NUMBER Previous buffer on this hash chain
NXT_LRU NUMBER Next buffer on the LRU
PRV_LRU NUMBER Previous buffer on the LRU
HLADDR RAW(4) Hash Chain Latch Address
TCH NUMBER 8.1 Touch Count
next_hash以及PRV_HASH 对应于上文提到的hash: [0x7bcf8bb0,0x7bcf8bb0]，NXT_LRU以及PRV_LRU对应上文提到的lru: [0x733dc288,0x733dc018]，tch对应于上文提到的tch: 2。等等等等，还有很多信息反正bufferheader的信息都在x$bh中可以找到。但是注意oracle一直在变动这些表，具体字段还需依据系统版本来定。
select ts#,dbarfil,dbafil,dbablk,nxt_hash,prv_hash,nxt_lru,prv_lru,hladdr,tch from x$bh where nxt_hash='0x7bcf8bb0'；
一、
知道如上信息了，首先查看一下数据库中，最热手的数据块信息；
eg：
SQL> select * from (select ts#,file#,dbarfil,dbablk,state,tch,nxt_hash,prv_hash,nxt_repl,prv_repl from x$bh order by tch desc) where rownum<20;
TS# FILE# DBARFIL DBABLK STATE TCH NXT_HASH PRV_HASH NXT_REPL PRV_REPL
---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------- ----------------
0 1 1 2017 1 255 000000007BDC00B0 000000007BDC00B0 0000000073FF9DD8 0000000073FF65C8
0 1 1 2016 1 254 000000007BE481C0 000000007BE481C0 0000000071FDE370 0000000071FDD740
1 2 2 62997 1 235 000000007BE20D10 0000000070FD82F0 0000000073FF7BB8 000000006FFEADC0
1 2 2 39076 1 201 000000007BCFD820 000000007BCFD820 000000006FFF1690 000000006FFF12E8
1 2 2 36844 1 196 000000007BDC69E0 000000007BDC69E0 000000006FFEF6E0 000000006FFEF0C8
1 2 2 36839 1 150 000000007BDA68F0 00000000777F7CB8 00000000777FB500 00000000777FA660
1 2 2 39131 1 126 000000007BCFA8B0 000000007BCFA8B0 000000006FFF07F0 000000006FFF0580
1 2 2 39127 1 126 000000007BDB66B0 000000007BDB66B0 000000006FFF06B8 000000006FFF0448
1 2 2 60461 1 126 000000007BDB0550 000000007BDB0550 000000006FFF01D8 000000006FFEFF68
1 2 2 36826 1 126 000000007BD9A400 000000007BD9A400 000000006FFEFE30 000000006FFEFBC0
0 1 1 66493 1 126 00000000727E3618 000000007BD99600 000000006FFF0CD0 000000006FFF0A60
TS# FILE# DBARFIL DBABLK STATE TCH NXT_HASH PRV_HASH NXT_REPL PRV_REPL
---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------- ----------------
0 1 1 77698 1 126 000000007BD95EB0 000000006F3E4D40 000000006FFF0928 000000006FFF06B8
0 1 1 465 1 126 000000007BD81070 000000007BD81070 000000006FFF0B98 000000006FFF0928
1 2 2 39083 1 126 0000000072FE7578 000000007BD71D30 0000000073FFAA08 000000006FFF0CD0
1 2 2 60459 1 126 000000007BD5C130 000000007BD5C130 000000006FFF0448 000000006FFF01D8
1 2 2 36824 1 126 000000007BD45FE0 000000007BD45FE0 000000006FFEFBC0 000000006FFEF950
1 2 2 39073 1 126 0000000072FF72F8 000000007BD31B50 000000006FFF12E8 000000006FFF1558
0 1 1 497 1 126 000000007BD31A30 000000007BD31A30 000000006FFF0E08 000000006FFF0B98
1 2 2 60462 1 126 000000007BD28440 000000007BD28440 000000006FFF00A0 000000006FFEFE30
19 rows selected.
SQL>
可以看到在dbablk为：2017，它的nxt_hash与prv_hash都是一个，也就是说这个块自己在一个chain上，这个chain中在没有其他数据块。
知道了这个信息，我们可以知道这些热块存在于那个表空间，那个数据文件，那个数据块是热的等等信息。
二、
根据x$bh找到访问频繁的对象。可以与dba_extents关联
eg：
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name from dba_extents a,
2 (select * from
3 (select ts#,file#,dbarfil,dbablk,state,tch,nxt_hash,prv_hash,nxt_repl,prv_repl from x$bh order by tch desc)
4 where rownum<20) b
5 where a.file_id=b.file# and a.block_id <= b.dbablk and a.block_id+a.blocks >= b.dbablk and a.relative_fno=b.dbarfil;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------
SYS JOB$ TABLE SYSTEM
SYS JOB$ TABLE SYSTEM
SYS SCHEDULER$_CLASS TABLE SYSTEM
SYS SCHEDULER$_JOB TABLE SYSTEM
SYS SCHEDULER$_JOB TABLE SYSTEM
SYS SCHEDULER$_JOB TABLE SYSTEM
SYS SCHEDULER$_LIGHTWEIGHT_JOB TABLE SYSTEM
SYS C_USER# CLUSTER SYSTEM
SYS C_USER# CLUSTER SYSTEM
SYS I_USER# INDEX SYSTEM
SYS I_USER1 INDEX SYSTEM
SYS I_SYSAUTH1 INDEX SYSTEM
SYS I_SYSAUTH1 INDEX SYSTEM
SYS I_SYSAUTH1 INDEX SYSTEM
SYS I_SYSAUTH1 INDEX SYSTEM
SYS I_SYSAUTH1 INDEX SYSTEM
SYS I_PROFILE INDEX SYSTEM
SYS I_JOB_JOB INDEX SYSTEM
SYS I_JOB_NEXT INDEX SYSTEM
SYS SCHEDULER$_CLASS_PK INDEX SYSTEM
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------
SYS SCHEDULER$_CLASS_PK INDEX SYSTEM
SYS SCHEDULER$_LOBJ_UK INDEX SYSTEM
22 rows selected
SQL>
三、
根据x$bh 也可以找到对应于获得该cache buffers chain 的latch信息，注意不能与v$latch进行关联，因为该视图是个汇总，需要与v$latch_children视图进行关联：
eg：
SQL> select name,gets,misses,sleeps,immediate_gets,immediate_misses,spin_gets,wait_time
2 from v$latch_children a,
3 (select * from
4 (select ts#,file#,dbarfil,dbablk,state,tch,nxt_hash,hladdr,prv_hash,nxt_repl,prv_repl from x$bh order by tch desc)
5 where rownum<20) b
6 where a.ADDR=b.hladdr and a.name='cache buffers chains' order by gets desc;
NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- -------------- ---------------- ---------- ----------
cache buffers chains 94752 0 0 71 0 0 0
cache buffers chains 37077 0 0 60 0 0 0
cache buffers chains 29415 0 0 70 0 0 0
cache buffers chains 27973 0 0 67 0 0 0
cache buffers chains 21447 0 0 71 0 0 0
cache buffers chains 15893 0 0 64 0 0 0
cache buffers chains 10095 0 0 66 0 0 0
cache buffers chains 8585 0 0 146 0 0 0
cache buffers chains 3782 0 0 76 0 0 0
cache buffers chains 3744 0 0 67 0 0 0
cache buffers chains 3447 0 0 70 0 0 0
cache buffers chains 3386 0 0 65 0 0 0
cache buffers chains 2785 0 0 76 0 0 0
cache buffers chains 2782 0 0 71 0 0 0
cache buffers chains 2683 0 0 67 0 0 0
cache buffers chains 2488 0 0 69 0 0 0
cache buffers chains 2443 0 0 66 0 0 0
cache buffers chains 2300 0 0 62 0 0 0
cache buffers chains 1895 0 0 71 0 0 0
19 rows selected
SQL>
四、
如果存在cache buffers chains 可以通过statspack report查看得到信息，可以参考v$session_wait 视图找到free latch 等待事件，并且可以通过如下语句找到相应的latch 地址，然后我们在去组合找到对象，也是一种方法
eg：
查找属于cache buffers chains的latch信息，注意下面是演示，没有真正出现cache buffers chains latch竞争现象。
SQL> select addr,name,gets,misses,sleeps,immediate_gets,immediate_misses,spin_gets,wait_time
2 from v$latch_children where name='cache buffers chains' and rownum<20 order by sleeps desc
3 ;
ADDR NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS WAIT_TIME
---------------- ---------------------------------------------------------------- ---------- ---------- ---------- -------------- ---------------- ---------- ----------
000000007BE5B278 cache buffers chains 1243 0 0 72 0 0 0
000000007BE58468 cache buffers chains 915 0 0 71 0 0 0
000000007BE5B0E8 cache buffers chains 9720 0 0 69 0 0 0
000000007BE5B020 cache buffers chains 957 0 0 65 0 0 0
000000007BE5AF58 cache buffers chains 924 0 0 69 0 0 0
000000007BE5AE90 cache buffers chains 514 0 0 67 0 0 0
000000007BE5ADC8 cache buffers chains 1220 0 0 71 0 0 0
000000007BE5AD00 cache buffers chains 1120 0 0 66 0 0 0
000000007BE59C38 cache buffers chains 1041 0 0 139 0 0 0
000000007BE59B70 cache buffers chains 473 0 0 66 0 0 0
000000007BE59AA8 cache buffers chains 899 0 0 70 0 0 0
000000007BE599E0 cache buffers chains 851 0 0 66 0 0 0
000000007BE59918 cache buffers chains 3122 0 0 67 0 0 0
000000007BE59850 cache buffers chains 487 0 0 65 0 0 0
000000007BE59788 cache buffers chains 738 0 0 77 0 0 0
000000007BE596C0 cache buffers chains 1008 0 0 69 0 0 0
000000007BE585F8 cache buffers chains 756 0 0 69 0 0 0
000000007BE58530 cache buffers chains 1983 0 0 72 0 0 0
000000007BE5B1B0 cache buffers chains 539 0 0 63 0 0 0
19 rows selected
SQL>
既然找到了latch的addr，那么也就可以找到相应的对象信息：
eg：
SQL> select b.ts#,b.file#,b.dbarfil,b.dbablk,b.tch,a.gets,a.misses,a.sleeps,a.immediate_gets,a.immediate_misses from
2 (select * from
3 (select addr,ts#,file#,dbarfil,dbablk,hladdr,tch,nxt_hash,prv_hash,nxt_repl,prv_repl from x$bh
4 order by tch desc) where rownum<20 )b,
5 (select addr,name,gets,misses,sleeps,immediate_gets,immediate_misses,spin_gets,wait_time
6 from v$latch_children where name='cache buffers chains' order by misses desc) a
7 where a.addr=b.hladdr order by b.tch desc;
TS# FILE# DBARFIL DBABLK TCH GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------- ----------------
0 1 1 12498 175 9117 0 0 151 0
0 1 1 12584 172 4000 0 0 71 0
0 1 1 12496 172 4048 0 0 82 0
0 1 1 12481 172 3666 0 0 68 0
0 1 1 12489 172 2105 0 0 77 0
0 1 1 12497 170 3029 0 0 83 0
0 1 1 964 168 99290 0 0 76 0
0 1 1 2025 166 3789 0 0 72 0
0 1 1 963 166 38918 0 0 60 0
0 1 1 965 166 29381 0 0 71 0
1 2 2 36771 151 16726 0 0 68 0
0 1 1 77728 151 2506 0 0 68 0
1 2 2 38275 151 2897 0 0 70 0
0 1 1 417 111 5778 0 0 70 0
1 2 2 36847 89 2964 0 0 72 0
0 1 1 46461 77 7481 0 0 88 0
0 1 1 12097 76 2741 0 0 70 0
0 1 1 12145 76 3046 0 0 80 0
0 1 1 65330 76 12240 0 0 68 0
19 rows selected
SQL>
五、
如下是看到一个blog，在此学习一下。
热点块和热点对象我们都找到了，但是我们该怎么来解决这个问题呢？一般来说，热点块会导致cache buffers chains竞争等待，但并不是说cache buffer chains一定是因为热点块而起，在特别情况下有可能是因为latch数量的问题导致的，也就是一个latch管理的buffers数量太多而导致竞争激烈。但是latch数量我们一般是不会轻易去设置的，这是oracle的隐藏参数。
实际上最有效的办法，是从优化sql入手，不良的sql往往带来大量的不必要的访问，这是造成热点块的根源。比如本该通过全表扫描的查询却走了索引的range scan，这样将带来大量的对块的重复访问。从而形成热点问题。再或者比如不当地走了nested loops的表连接，也可能对非驱动表造成大量的重复访问。那么在这个时候，我们的目标就是找出这些sql来并尝试优化。在statspack报告中，根据报告中sql列表，我们如果是通过dba_extents确定的热点对象而不是通过dba_objects确定的，则可以通过查找出的热点segment转换为对应的表，对于非分区的索引，index_name就是segment_name,通过dba_indexes很容易的找到对应的table_name,对于分区表和分区索引也能通过和dba_tab_partition和dba_ind_partitions找到segment和table的对应关系。通过这些table到statspack报告中去找相关的sql。
select sql_text
from stats$sqltext a,
(select distinct a.owner,a.segment_name,a.segment_type from
dba_extents a,
(select dbarfil,dbablk
from (select dbarfil,dbablk
from x$bh order by tch desc) where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk) b
where a.sql_text like '%'||b.segment_name||'%' and b.segment_type = 'TABLE'
order by a.hash_value,a.address,a.piece;