bufferpool oracle,Oracle调优之buffer pool相关

一个oracle block与data buffer中的一个buffer对应。 用户进程(server process)负责读取磁盘上的block到data buffer cache中，DEWn进程负责将脏块写到磁盘上。 一个buffer在data buffer中有4种状态 pined:多个进程想写这个块,但只有1个进程能获得锁。 claen :buffer中的数据和磁盘上的数据时一致的，这些块将优先被淘汰。 free/unused:没有被使用过，是空白内容。 dirty:不被某个进程使用使用，但buffer的内容和磁盘内容不一致 data buffer cache中用2个队列保存buffer,一个队列是LRU list，按照每个buffer被访问的时间排序，最近访问的buffer排在最前。一个队列是checkpoint queue,保存脏块，DBWn进负责将这些脏块写到磁盘中。 建议将参数db_block_checksum设为true,为每个block添加一个校验码，防止磁盘损坏引起数据丢失。 data buffer cache由多个相互独立的buffer pool组成，每个buffer对应一个block,不同的block size对应不同的buffer pool。 db_block_size决定了default,recycle,keep池中每个buffer的大小。 db_cache_size：default池的大小。 db_recycle_cache_size：recycle池的大小。 db_keep_cache_size：keep池的大小。 也可通过设置SGA_TARGET，实现自动管理。 关于各data buffer cache大小的建议 select a.name,a.SIZE_FOR_ESTIMATE,a.ESTD_PHYSICAL_READ_FACTOR,a.ESTD_PHYSICAL_READS from v$db_cache_advice a order by a.name,a.SIZE_FOR_ESTIMATE; 查看buffer cache的命中率 SELECT NAME,VALUE FROM V$SYSSTAT WHERE NAME IN ('db block gets from cache','consistent gets from cache','physical reads cache'); 并使用公式1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')计算 或者在sql语句中直接计算 select 1-(sum(decode(name,'physical reads',value,0))/ (sum(decode(name,'db block gets',0))+ (sum(decode(name,'consistent gets',0))))) from v$sysstat; 在多buffer pool情况下，分别统计不同buffer pool的命中率 select name,1-(physical_reads/(db_block_gets+consistent_gets)) from v$buffer_pool_statistics where db_block_gets+consistent_gets>0; 命中率需要和等待事件结合起来看性能情况。 大表的全扫描会降低buffer cache的命中率 查看段在buffer cache中的占用情况，利用V$BH 方式一：查看每个段有多少个块在buffer cache中 SELECT o.OBJECT_NAME,COUNT(*) NUMBER_OF_BLOCKS FROM DBA_OBJECTS o,V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS' GROUP BY o.OBJECT_NAME ORDER BY COUNT(*); 方式二：查看指定段占用buffer cache的比率 1.获取段的object_id SELECT DATA_OBJECT_ID,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME = UPPER('segment_name'); 2.获取指定段占用了多少buffer SELECT COUNT(*) BUFFERS FROM V$BH WHERE OBJD = data_object_id_value; 3.获取当前buffer cache中有多少个buffer SELECT NAME,BLOCK_SIZE,SUM(BUFFERS) FROM V$BUFFER_POOL GROUP BY NAME,BLOCK_SIZE HAVING SUM(BUFFERS) > 0; 4.利用第3步和第4步的结果就可以计算出该段在buffer cache中的占用情况。 对buffer cache调优的目标 server process能够在buffer cache中找到需要的数据 发生相应的等待事件的次数减少 调优的手段 降低sql语句对数据块的请求 增加buffer cache的容量 把不同访问模式的对方防到不同的pool中 将小表钉在内存中 直接读 主要事件 Free Buffer Inspected: server process为了在LRU链表上找到可用的内存数据块所所跳过的数据块的个数。 Free Buffer waits: server process通知DBWn写脏块的次数 Buffer busy waits: 找到buffer,但buffer被另一个进程占有，开始等待的次数。 000000000 查看Free Buffer Inspected产生的次数 select name,value from v$sysstat where name = 'free buffer inspected'; 查看Free Buffer waits和Buffer busy waits发生的次数及耗用时间 select EVENT,TOTAL_WAITS,TIME_WAITED from v$system_event where EVENT in('buffer busy waits','free buffer waits'); 多个buffer pool 当对大表进行全表扫描时，可能会使其他buffer(温块)移除buffer pool,将这些大表设置使用recycle pool。 recycle pool的容量应小于default pool,recycle pool中的buffer应尽快移出。 将温块设置为使用keep pool。keep pool的容量应大于default pool。 alter table me.t_option storage(buffer_pool keep); 1个segment只能放在1个pool中。 v$cache,需要执行@?/rdbms/admin/catclust.sql脚本。与v$bh类型 select a.username,NAME,count(BLOCK#) from dba_users a join v$cache b on a.USER_ID = b.owner# where a.username != 'SYS' group by OWNER#,a.username,NAME order by a.username; v$sess_io 记录了每个已连接session的读取情况的累计值 select b.USERNAME,a.BLOCK_GETS,a.CONSISTENT_GETS,a.PHYSICAL_READS from v$sess_io a full join v$session b on a.SID=b.SID where b.USERNAME is not null; cache table 当使用全表扫描的方式查询一张表时，会将该表相应的buffer放在LRU list的末尾，以便尽快淘汰，但有些小表需要全表扫描而不希望被淘汰掉，这时就需要使用cache table将表对应的buffer放在LRU list的开头。可以通过创建表，修改表，和sql提示实现cache table。建议被cache table的表不要太多，并放到keep pool中。cache table只能延长buffer在buffer pool中的存活时间，而不能将buffer pin在buffer pool中。 可以在user_tables或all_tables或dba_tables中查询cache字段查看表是否被cache table。 Y ：cache table N：没有被cache table 例：create table cache_test(id int) cache; alter table cache_test cache; 配置多个DBWn进程 配置参数db_writer_processes 在多cpu,异步IO的OS上能显著提高性能。 参数disk_asynch_io是是否启用异步IO的开关 true:打开 什么时候需要增加DBWn进程？如果等待事件Free Buffer waits占有比较高比例时

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。