buffer cache

2023-12-13 05:03:38

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 sgad?ynamicc?omponents;buffercache大小配置:altersystemsetdbc?aches?ize=200Mscope=memory;OLTP系统中,对于dbc?aches?ize的设置推介SGAM?AXS?IZE/2?SGAM?AXS?IZE?2/3。使用advice来确定buffercache的大小:selectsizef?ore?stimate"cachesize(MB)",sizef?actor,buffersf?ore?stimate"buffers",estdp?hysicalr?eadf?actorestr?eadf?actor,estdp?hysicalr?eadsestdp?hyr?ed,estdp?hysicalr?eadt?imeestp?hyr?edt?fromvdb_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;静态参数,需重启数据库

文章来源:https://blog.csdn.net/qq_33674508/article/details/134857722
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。