Oracle Buffer cache讲解.txt
5墨值下载
1、block、buffer 的概念
简述段区块的概念，引出块的概念
buffer 的概念
2、buffer cache 的意义
减少 IO
物理 IO：磁盘读
逻辑 IO：内存读
构造 cr 块：以后讲
undo 引出来：回滚未提交数据；构造 cr 块
只要未提交，就可以回滚
只要未提交，别的会话就看不见修改
3、buffer cache 的内存组织结构
CBC：cache buffer chain
根据 block 地址找 block 的时候，需要使用到 CBC chain
LRU：最近最少使用
LRU、MRU
LRUW
CHECKPOINT QUEUE：以后讲
4、DB_WRITER_PROCESSES
5、Buffer Cache 的重要参数配置
SELECT component,current_size,min_size FROM v$sga_dynamic_components;
Buffer Cache 的大小配置
alter system set db_cache_size=200M scope=both;
sga_max_size
sga_target
db_cache_size
在 OLTP 系统中，对于 DB_CACHE_SIZE 的设置，推荐配置是：
DB_CACHE_SIZE = SGA_MAX_SIZE/2～ SGA_MAX_SIZE*2/3
使用 advice 来确认 buffer cache 的大小
SELECT size_for_estimate "Cache Size (MB)",size_factor,buffers_for_estimate
"Buffers",
estd_physical_read_factor est_read_factor,
estd_physical_reads estd_phy_red,estd_physical_read_time est_phy_red_t
FROM v$db_cache_advice
WHERE NAME='DEFAULT' AND block_size=(SELECT VALUE FROM v$parameter WHERE
NAME='db_block_size');
6、Block 状态
x$bh
state:
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
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
一个对象占用 buffer 的具体情况
SQL>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;
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 = 61433;
对象使用 pool 的具体情况（考虑了各种池子的情况）
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 asc;
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',
of 10
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论