1、buffer cache相关概念
segment:一个表就是一个段；
extend：物理上连续的多个块，给段分配空间的单位；
block：IO的最小单位，数据文件中的单位，oracle实例创建时指定标准块大小db_block_size，需要根据应用设置
buffer：db buffer cache中的一个内存 块。
buffer Head:buffer的头，包含LRU链，hash链，指向buffer的指针
DB_WRITER_PROCESSES：定义DBWR进程个数
alter system set db_writer_processes = 2 scope = spfile; #cup/8 静态参数
2、buffer cache的意义
缓存数据文件，减少物理IO
物理IO：磁盘读
逻辑IO：内存读
与UNDO一同构造cr块：
一致性读要求，只要未提交修改别的会话无法查看。
cr块构造过程：A会话修改表的数据，未提交，修改前数据行的内容会写入数据块对应的undo块里，B会话登陆查询A修改的数
据，sp申请新的数据块，取原块中未修改的数据，和undo块中修改前的数据，构造CR块。B读取CR块中的数据，实现读一致性。
3、buffer cache的内存组织结构
CBC：cache buffer chain latch
hash chain list:配合hash bucket快速定位block在内存中得位置
Oracle将buffer cache中所有的buffer通过一个内部的Hash算法运算之后，将这些buffer放到不同的 Hash Bucket中。
每一个Hash Bucket中都有一个Hash Chain List，通过这个list，将这个Bucket中的buffer串联起来。一个CBC latch
(x$bh.hladdr) 可以保护多个Hash Bucket，也就是说，如果我要访问某个buffer，我首先要获得这个latch，一个Hash
Bucket对应一个Hash Chain List，而这个Hash Chain List挂载了一个或者多个Buffer Header。BH指向具体的buffer。
当一个用户进程想要访问某个块如Block(569,411)：对该Block运用Hash算法，得到Hash值。获得cache buffers chains latch
到相应的Hash Bucket中搜寻相应Buffer Header，如果找到相应的Buffer Header，然后判断该Buffer的状态，看是否需要构造CR
Block，或者Buffer处于pin的状态，最后读取。如果找不到，就从磁盘读入到Buffer Cache中。
LRU：最近最少使用：找可用的buffer时使用，挂载free,pinned buffer，未被移动到write list的dirty buffer;
LRU、MRU
LRUW：最近最少脏:DBWR写入磁盘时使用，从冷端开始写入，挂载dirty block信息也叫writ list
如果一个用户进程发现某个block不在Buffer Cache中，那么用户进程就会从磁盘上将这个block读入Buffer Cache。
在将block读入到Buffer Cache之前，首先要在LRU list上寻找可用的buffer，在寻找过程中，如果发现了Dirty Buffer就
将其移动到 LRU Write List上。如果Dirty Queue超过了阀值25%(如下面查询所示)，那么DBWn就会将Dirty Buffer写入到磁盘中。
当某个用户进程扫描LRU list超过40%都还没找到可用 Buffer，那么这个时候用户进程将停止扫描LRU list，同时通知
DBWn将Dirty Buffer写入磁盘，用户进程也将记录一个free buffer wait等待事件。如果经常看到free buffer wait等待
事件，应该考虑加大Buffer Cache。
查询检索阀值
select kvittag,kvitval,kvitdsc from x$kvit where kvittag in('kcbldq','kcbfsp');
Cache Buffers LRU chain Latch
当用户进程要将Block从磁盘读入到Buffer Cache中，或者是DBWR扫描LRU Main List的时候要获得Cache Buffers LRU chain
Latch。从oracle 8i开始引入了辅助的list，用于提高管理效率，当数据库初始化时，buffer首先存放在lru的辅助list上，当
被使用后移动到lru 的主list上，这样当用户进程搜索free buffer时，首先从辅助list上搜索，当dbwr搜索dirty buffer时 会
首先从lru 的主list上进行搜索，提高了搜索效率和数据库性能。
CHECKPOINT QUEUE：第一次脏的时间点链起来的：也是链的脏块，
CKPT进程会周期行的检查checkpoint queue并缩短队列，更新控制文件和数据文件头部的检查点信息，缩短实例恢复时间。
4.db buffer cache性能相关
发现buffer cache性能问题
过小的Buffer Cache导致大量的磁盘I/O，大量全表扫描，扫描没有选择性的索引等引发Cache Buffers LRU chain Latch竞争。
可以通过AWR报告等查看buffer cache的性能情况（闩锁等待，命中率，物理读的块数*块大小 算出每秒数据库的物理读大小等）。
DB cache性能：
优化db cache的核心是IO与内存之间的平衡，命中率只是性能情况的一个反应，加大db cache会增加CPU和内存消耗，减轻IO压力
内存紧张时考虑keep pool;
awr报告DB cache相关闩锁，
cache buffer handles
cache buffers chains latch --9i开始可以只读共享，及多个进程可以只读共享某块，某进程要独占时产生该等待事件。
cache buffers lru chain
multiblock read objects
buffer cache性能问题调优方法
多缓冲技术：减少操作间相互影响，提高缓冲池效率，
select COMPONENT,CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;
defaul pool:通常默认使用的缓冲池；
recycle pool:临时统计使用缓冲池；
keep pool ：热点数据缓冲池；减少热点数据对default pool的影响，启动会减小buffer cache的大小
KEEP热对象
通过业务和x$bh表的tch字段，对象ID关联dba_objects,dba_extents和AWR报告确定热对象；
alter system set db_keep_cache_size=10M scope =both; --keep pool不受ASMM影响需要手动设置
alert table|index ... storage (buffer_pool keep|[default]); --keep具体对象
select * from dba_segments where BUFFER_POOL='KEEP';
NK缓冲：
为不同块大小的表空间提供独立缓冲，配合独立块大小表空间使用，热块冲突严重可以使用小块，数据行长度
大的数据使用大块配合使用NK表空间.
参考资料：
http://blog.csdn.net/robinson1988/article/details/5982996
5、Buffer Cache的大小设置
alter system set db_cache_size=200M scope=both;
如果启用ASMM时调大先调整sga_max_size，sga_target两参数
alter system set sga_max_size=200M scope=spfile;
alter system set sga_target=200M scope=spfile;
在OLTP系统中，对于DB_CACHE_SIZE的设置，推荐配置是：DB_CACHE_SIZE = SGA_MAX_SIZE/2～ SGA_MAX_SIZE*2/3
使用advice来确认buffer cache的大小
SELECT size_for_estimate "预估缓存值(MB)",
size_factor "预估值与真实值比较",
buffers_for_estimate "预估buffer数",
estd_physical_read_factor "预估物理读/实际",
estd_physical_reads "预估物理读次数",
estd_physical_read_time "预估物理读时间"
FROM v$db_cache_advice
WHERE NAME='DEFAULT'
AND block_size=(SELECT VALUE FROM v$parameter WHERE NAME='db_block_size');
6、内存中buffer状态（x$bh.state）
每一buffer都是x$bh表中的一行记录。
select distinct status from x$bh;
0, FREE, no valid block image
1, XCUR, a current mode block, exclusive to this instance 单实例环境
2, SCUR, a current mode block, shared with other instances 多实例RAC环境
3, CR, a consistent read (stale) block image
4, READ, buffer is reserved for a block being read from disk
--恢复相关，介质恢复和实例恢复
5, MREC, a block in media recovery mode
6, IREC, a block in instance (crash) recovery mode
7, WRITE
x$bh表里有对象ID字段，关联对象表可以查询对象缓存情况
select object_name,DBARFIL,DBABLK from x$bh a,dba_objects b
where a.obj=b.object_id and object_name='T2';
具体块块在内存中的状态
select class, flag, state, lru_flag from x$bh where dbarfil = 1 and dbablk = 61354;
7、dbms_rowid包
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) --通过rowid得到块号
select dbms_rowid.rowid_block_number(rowid), id from t;
ROWID 在磁盘上需要10 个字节的存储空间并使用18 个字符来显示它包含下列组件：
数据对象编号：每个数据对象如表或索引在创建时分配，并且此编号在数据库中是唯一的；
相关文件编号：此编号对于一个表空间中的每个文件是唯一的；
块编号：表示包含此行的块在文件中的位置；
行编号：标识块头中行目录位置的位置；
AAAH7J AAL AAAAAU AAA
AAAH7J 是数据对象编号
AAL 是相关文件编号
AAAAAU 是块编号
AAA 是行编号
可以据此来判断数据在数据块中分布情况
工具函数，通过ROWID找到具体的对象及存储信息
create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||
'Relative_fno is :'||to_char(relative_fno)||chr(10)||
'Block number is :'||to_char(block_number)||chr(10)||
'Row number is :'||to_char(row_number);
return ls_my_rowid ;
end;
/
select get_rowid('AAAM13AABAAAO/6AAA') row_id from dual;
ROW_ID
Object# is :52599
Relative_fno is :1
Block number is :61434
Row number is :0
8、关于buffer cache的各种SQL语句
缓冲区命中率：
select (1-(sum(decode(name, 'physical reads',value,0))/(sum(decode(name, 'db block gets',value,0))
+sum(decode(name,'consistent gets',value,0))))) * 100 "Hit Ratio"
from v$sysstat;
SELECT (P1.VALUE + P2.VALUE - P3.VALUE) / (P1.VALUE + P2.VALUE)
FROM v$sysstat P1, v$sysstat P2, v$sysstat P3
WHERE P1.name = 'db block gets'
AND P2.name = 'consistent gets'
AND P3.name = 'physical reads'
具体会话的buffer cache命中率
SELECT (P1.VALUE + P2.VALUE - P3.VALUE) / (P1.VALUE + P2.VALUE)
FROM v$sesstat P1, v$statname N1, v$sesstat P2, v$statname N2, v$sesstat P3, v$statname N3
WHERE N1.name = 'db block gets' AND P1.statistic# = N1.statistic# AND P1.sid =139
AND N2.name = 'consistent gets' AND P2.statistic# = N2.statistic# AND P2.sid = P1.sid
AND N3.name = 'physical reads' AND P3.statistic# = N3.statistic# AND P3.sid = P1.sid;
9、清空buffer cache
alter system flush buffer_cache;
10、相关查询SQL语句：
x$bh与dba_objects表结合：
一个对象占用buffer cache的具体情况
select o.object_name, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',
6,'irec',7,'write',8,'pi') state, count(*) blocks from x$bh b, dba_objects o
where b.obj = o.data_object_id and o.object_name = 'T2'
group by o.object_name, state order by blocks desc;
查找占用buffer最多的对象
select o.object_name, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec', 6,'irec',7,
'write',8,'pi') state, count(*) blocks
from x$bh b, dba_objects o where b.obj = o.data_object_id and state <> 0
group by o.object_name, state order by blocks desc;
寻找热块，由块的tch值判断冷热，TCH为touch的缩写，表示一个buffer的访问次数
SELECT obj object, dbarfil file#, dbablk block#, tch touches
FROM x$bh WHERE tch > 10 ORDER BY tch asc;
查找热块的对象,关联v$sqlarea找到操作对象的SQL语句
select object_name,DBARFIL,DBABLK from x$bh a,dba_objects b
where a.obj=b.object_id and DBARFIL=1 and DBABLK=1658;
select event,p1,p2,p3 from v$session_wait where event like 'buffer busy wait%';
select owner,segment_name from dba_extents where file_id=&p1 and block_id<=&p2 and (block_id+blocks)>=&p2;
热块与latch的关联查询
select * from x$bh where hladdr in (select addr from v$latch_children);
找到前5个latch
select * from (select hladdr,count(*) from x$bh group by hladdr order by 2 desc) where rownum<=5;
查询计算cache buffers chains latch的数量
select count(*) from v$latch_children a where a.name='cache buffers chains';
查找链上buffer信息,buffer被写入dbf后，x$bh表里的块数会减少
select hladdr,obj,dbarfil,dbablk, nxt_hash,prv_hash from x$bh where hladdr='C000000469F08828' order by obj;
查询查出Top 5 的争用的latch address。
select * from( select CHILD#,ADDR,GETS ,MISSES,SLEEPS from v$latch_children where
name = 'cache buffers chains' and misses>0 and sleeps>0 order by 5 desc, 1, 2, 3) where rownum<6;
查询找出链上的Hot block。
select /*+ RULE */ e.owner ||'.'|| e.segment_name segment_name, e.extent_id extent#,
x.dbablk - e.block_id + 1 block#, x.tch, /* sometimes tch=0,we need to see tim */x.tim ,l.child#
from v$latch_children l, x$bh x, dba_extents e where x.hladdr = '&ADDR' and e.file_id = x.file#
and x.hladdr = l.addr and x.dbablk between e.block_id and e.block_id + e.blocks -1 order by x.tch desc;
buffer cache的空闲空间的比例，最好控制在10%以内
select decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state)
"BLOCK STATUS", count(*) from x$bh group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE'
,'BEING USED'),3, 'BEING USED', state);
查找不同大小的内存使用情况
select decode(wbpd.bp_id, 1,'keep', 2,'recycle', 3,'default', 4,'2k pool', 5,'4k pool', 6,'8k pool',
7,'16k pool', 8,'32k pool', 'unknown') pool, bh.owner, bh.object_name object_name, count(1) numOfBuffers
from x$kcbwds wds, x$kcbwbpd wbpd, (select set_ds, x.addr, o.name object_name, u.name owner
from sys.obj$ o, sys.user$ u, x$bh x where o.owner# = u.user# and o.dataobj# = x.obj and x.state != 0
and o.owner# != 0 ) bh where wds.set_id >= wbpd.bp_lo_sid and wds.set_id <= wbpd.bp_hi_sid
and wbpd.bp_size != 0 and wds.addr = bh.set_ds and object_name='T2'
group by decode(wbpd.bp_id, 1,'keep', 2,'recycle', 3,'default', 4,'2k pool', 5,'4k pool', 6,'8k pool',
7,'16k pool', 8,'32k pool', 'unknown'), bh.owner, bh.object_name order by 1, 4, 3, 2;
整个数据库所有文件中block的总和
select sum(blocks) from dba_data_files;
最浪费内存的前10个语句访问对象占所有语句的比例，建议控制在5%以内
select sum(pct_bufgets) "Percent" from (select rank() over ( order by buffer_gets desc ) as
rank_bufgets,to_char(100 * ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sqlarea )
where rank_bufgets < 11;
找出消耗物理IO资源最大的的SQL语句，内存读消耗CPU资源，物理读消耗IO
select disk_reads, substr(sql_text,1,4000) from v$sqlarea order by disk_reads asc;
查询sql语句物理读和逻辑读信
SELECT executions,buffer_gets,disk_reads,first_load_time,sql_text FROM v$sqlarea ORDER BY disk_reads;
查询各个DBF文件的IO情况
SELECT A.file_name, B.phyrds 次数, B.phyblkrd 块数 FROM SYS.dba_data_files A, v$filestat B
WHERE B.file# = A.file_id ORDER BY A.file_id
查询系统的隐藏参数_db_block_hash_buckets，_db_block_hash_latches
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = USERENV ('Instance') AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx AND x.ksppinm LIKE '%_db_block_hash%';
补充SQL：
-- buffer cache具体使用情况
SELECT /*+ ORDERED USE_HASH(o u) MERGE */
DECODE(obj#,
NULL,
to_char(bh.obj),
u.name || '.' || o.name) name,
COUNT(*) total,
SUM(DECODE((DECODE(lru_flag, 8, 1, 0) + DECODE(SIGN(tch - 2), 1, 1, 0)),
2,
1,
1,
1,
0)) hot,
SUM(DECODE(DECODE(SIGN(lru_flag - 8), 1, 0, 0, 0, 1) +
DECODE(tch, 2, 1, 1, 1, 0, 1, 0),
2,
1,
1,
0,
0)) cold,
SUM(DECODE(BITAND(flag, POWER(2, 19)), 0, 0, 1)) fts,
SUM(tch) total_tch,
ROUND(AVG(tch), 2) avg_tch,
MAX(tch) max_tch,
MIN(tch) min_tch
FROM x$bh bh, sys.obj$ o, sys.user$ u
WHERE
bh.obj <> 4294967295
AND bh.state in (1, 2, 3)
AND bh.obj = o.dataobj#(+)
AND bh.inst_id = USERENV('INSTANCE')
AND o.owner# = u.user#(+)
-- AND o.owner# > 5
AND u.name NOT like 'AURORA$%'
GROUP BY DECODE(obj#,
NULL,
to_char(bh.obj),
u.name || '.' || o.name)
ORDER BY total desc;
buffer cache中每一个对象的使用情况
SELECT t.name AS tablespace_name,
o.object_name,
SUM(DECODE(bh.status, 'free', 1, 0)) AS free,
SUM(DECODE(bh.status, 'xcur', 1, 0)) AS xcur,
SUM(DECODE(bh.status, 'scur', 1, 0)) AS scur,
SUM(DECODE(bh.status, 'cr', 1, 0)) AS cr,
SUM(DECODE(bh.status, 'read', 1, 0)) AS read,
SUM(DECODE(bh.status, 'mrec', 1, 0)) AS mrec,
SUM(DECODE(bh.status, 'irec', 1, 0)) AS irec
FROM v$bh bh
JOIN dba_objects o ON o.data_object_id = bh.objd
JOIN v$tablespace t ON t.ts# = bh.ts#
GROUP BY t.name, o.object_name
order by xcur desc;
REM dbbuffer
select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds;
with bh_lc as
(select /*+ ORDERED */
lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets,
lc.immediate_misses, lc.spin_gets, lc.sleeps,
bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class,
bh.state, bh.obj
from
x$kslld ld,
v$session_wait sw,
v$latch_children lc,
x$bh bh
where lc.addr =sw.p1raw
and sw.p2= ld.indx
and ld.kslldnam='cache buffers chains'
and lower(sw.event) like '%latch%'
and sw.state='WAITING'
and bh.hladdr=lc.addr
)
select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
bh_lc.child#, bh_lc.gets,
bh_lc.misses, bh_lc.immediate_gets,
bh_lc.immediate_misses, spin_gets, sleeps
from
bh_lc,
dba_objects o
where bh_lc.obj = o.object_id(+)
union
select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets,
bh_lc.immediate_misses, spin_gets, sleeps
from
bh_lc,
dba_objects o
where bh_lc.obj = o.data_object_id(+)
order by 1,2 desc;
扫一扫
788
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
