Database Buffer Cache

The database buffer cache, also called the buffer cache, is the memory area that stores copies of data blocks read from data files. A buffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data block. All users concurrently connected to a database instance share access to the buffer cache.

database buffer cache小名buffer cache，它存储从数据文件中读取到的数据块的copies。Buffer是内存中的一个地址，buffer manager会临时的把currently or recently used数据块cache到那里（注意buffer与cache的区别）。同时连接到数据库实例的所有用户都能够访问buffer cache。

使用buffer cache有两个目的：

1、Optimize physical I/O（优化物理 I/O）

The database updates data blocks in the cache and stores metadata about the changes in the redo log buffer. After a COMMIT, the database writes the redo buffers to disk but does not immediately write data blocks to disk. Instead, database writer (DBWn) performs lazy writes in the background.

数据库在cache中更新数据块，并且在redo log buffer中存储改动的元数据。在COMMIT后，数据库把redo buffer写到disk，但是不立即把数据库写到disk。相反，这个工作由DBWn进行再后台执行lazy写。

2、Keep frequently accessed blocks in the buffer cache and write infrequently accessed blocks to disk（把经常访问的块保留在buffer cache中，把不经常访问的块写到磁盘上）

When Database Smart Flash Cache (flash cache) is enabled, part of the buffer cache can reside in the flash cache. This buffer cache extension is stored on a flash disk device, which is a solid state storage device that uses flash memory. The database can improve performance by caching buffers in flash memory instead of reading from magnetic disk.

要是Smart Flash Cache开启的话，buffer cache的一部分就会存在于flash cache中。这个buffer cache extension存储在一个闪盘设备上，它是一个固态存储设备，使用闪存。数据库可以通过把buffers cache到闪存而不是从磁盘读取来提高性能

那么，我们应该给database buffer cache多大的值才能获得最好的性能呢？

测量Database Buffer Cache的性能

通过下面sql先对buffer cache做一个简单测试

select 1 - ((physical.value - direct.value - lobs.value) / logical.value) "Buffer Cache Hit Ratio"
 from v$sysstat physical,
 v$sysstat direct,
 v$sysstat lobs,
 v$sysstat logical
 where physical.name = 'physical reads'
 and direct.name = 'physical reads direct'
 and lobs.name = 'physical reads direct (lob)'
 and logical.name = 'session logical reads';

这里结果当>90%实说明调整充分的。命中率是高的，如果低于90%可以往下看：

oracle统计顾问

使用oracle推荐的统计顾问，在正常操作数据库半小时之后

alter system set db_cache_advice=on;

得到对于高速缓冲区推荐的大小

select name,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READS from v$db_cache_advice where block_size='8192' and advice_status='ON';

其他相关命令

1、查看哪些对象正缓存在Buffer Cache中，正使用了多少个Buffer Cache缓冲区

select obj.owner,obj.object_name,obj.object_type,count(distinct bh.BLOCK#) "NUM. Buffers"
from dba_objects obj,v$bh bh
where obj.object_id=bh.OBJD
and owner != 'SYS'
group by obj.owner,obj.object_name,obj.object_type；

2、查看缓冲池分配情况

select owner,segment_type,segment_name,buffer_pool

from dba_segments

where buffer_pool != 'DEFAULT';

3、查看每个Buffer Pool的大小

select name,block_size,current_size from v$buffer_pool;

4、查看每个Buffer Pool的命中率 KEEP越大越好，Recycle越小越好

select name "Buffer Pool",1 - (physical_reads / (db_block_gets + consistent_gets)) "Buffer Pool Hit Ratio"

from v$buffer_pool_statistics order by name;

关于database buffer cache方面的内容就介绍到这了，这里大家可以考虑下建立多个缓冲池，分别设置保持区和回收区大小，如果某个表操作比较频繁可以单独分配给保持区。

后面会分享更多DBA方面的内容，感兴趣的朋友可以关注下！！