buffer cache的意义：
1）减少IO
2）物理IO
3）逻辑IO
4）构造CR块
undo:回滚未提交的数据，构造CR块
只要未提交就可以回滚
只要未提交，别的会话就看不见修改
buffer cache的内存组织结构
CBC
LRU
LRUW
CHECKPOINT QUEUE
DB_WRITER_PROCESSES
buffer cache的参数配置：
select component,current_size,min_size from v
s
g
a
d
y
n
a
m
i
c
c
o
m
p
o
n
e
n
t
s
;
b
u
f
f
e
r
c
a
c
h
e
大小配置：
a
l
t
e
r
s
y
s
t
e
m
s
e
t
d
b
c
a
c
h
e
s
i
z
e
=
200
M
s
c
o
p
e
=
m
e
m
o
r
y
;
在
O
L
T
P
系统中，对于
d
b
c
a
c
h
e
s
i
z
e
的设置推介
S
G
A
M
A
X
S
I
Z
E
/
2
−
S
G
A
M
A
X
S
I
Z
E
∗
2
/
3
。使用
a
d
v
i
c
e
来确定
b
u
f
f
e
r
c
a
c
h
e
的大小：
s
e
l
e
c
t
s
i
z
e
f
o
r
e
s
t
i
m
a
t
e
"
c
a
c
h
e
s
i
z
e
(
M
B
)
"
,
s
i
z
e
f
a
c
t
o
r
,
b
u
f
f
e
r
s
f
o
r
e
s
t
i
m
a
t
e
"
b
u
f
f
e
r
s
"
,
e
s
t
d
p
h
y
s
i
c
a
l
r
e
a
d
f
a
c
t
o
r
e
s
t
r
e
a
d
f
a
c
t
o
r
,
e
s
t
d
p
h
y
s
i
c
a
l
r
e
a
d
s
e
s
t
d
p
h
y
r
e
d
,
e
s
t
d
p
h
y
s
i
c
a
l
r
e
a
d
t
i
m
e
e
s
t
p
h
y
r
e
d
t
f
r
o
m
v
sga_dynamic_components; buffer cache大小配置： alter system set db_cache_size =200M scope = memory; 在OLTP系统中，对于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
sgadynamiccomponents;buffercache大小配置：altersystemsetdbcachesize=200Mscope=memory;在OLTP系统中，对于dbcachesize的设置推介SGAMAXSIZE/2−SGAMAXSIZE∗2/3。使用advice来确定buffercache的大小：selectsizeforestimate"cachesize(MB)",sizefactor,buffersforestimate"buffers",estdphysicalreadfactorestreadfactor,estdphysicalreadsestdphyred,estdphysicalreadtimeestphyredtfromvdb_cache_advice
where name =‘DEFAULT’ and block_size =(select value from v$parameter where name =‘db_block_size’);
buffer状态
x$bh
一个对象占用buffer的具体情况(x$bh还可以看见每个块被访问的次数)
free - not currently in use
xcur - exclusive current,表示该数据块处于排外模式,正在被当前Instance占用；
scur - shared current,在RAC环境中表示该数据库正在和其他实例共享数据。
cr - consistent read,一致性读。
read - being read from disk
mrec - in media recovery mode,表示数据块处于介质恢复模式；
irec - in instance recovery mode ,表示数据块处于实例恢复模式；
write - 表示数据库正在往磁盘写入数据
select B.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 v$bh a,dba_objects b where a.objD =b.object_id and object_name ='CS' group by b.object_name,state order by blocks desc;
查看buffer cache的命中率（数据缓存）根据经验，一个良好性能的系统，这一值一般保持在 95%左右
select 1 - (sum(decode(name, ‘physical reads’, value, 0)) /
(sum(decode(name, ‘db block gets’, value, 0)) +
(sum(decode(name, ‘consistent gets’, value, 0))))) “Buffer Hit Ratio”
from v$sysstat;
查看buffer cache的命中率（数据缓存）根据经验，一个良好性能的系统，这一值一般保持在 95%左右
select a.value + b.value logical_reads,
c.value phys_reads,
round(100*(1-c.value/(a.value+b.value)),4) hit_ratio
from v
s
y
s
s
t
a
t
a
,
v
sysstat a,v
sysstata,vsysstat b,v$sysstat c
where a.name=‘db block gets’
and b.name=‘consistent gets’
and c.name=‘physical reads’ ;
修改db_block_buffers
alter system set db_block_buffers=16384 scope=spfile;静态参数，需重启数据库
637
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
