Oracle Buffer Cache初步诊断调优

1 Buffer Cache调优目标

• Servers find data in memory
• No waits on the buffer cache

2 Buffer Cache诊断指标

• wait events
• cache hit ratio
• the v$db_cache_advice view

2.1 针对wait events的诊断和调优

3个主要指标：

• Free Buffer Inspected: Number of Buffer Cache buffers inspected by user Server Process before finding a free buffer.
• Free Buffer Waits: 找不到空闲块，必须等待DBWn将脏数据写回腾出新的块
• Buffer Busy Waits: 找到的块还有其他的进程在使用。多个进程同时准备修改一个块。

这三个指标的查询方法：

<span style="font-size:14px;">SQL> select name, value from v$sysstat where name='free buffer inspected';
SQL> select event, total_waits from v$system_event where event in ('free buffer waits', 'buffer busy waits');</span>

（如果事件发生，才会从v$sysstat中查找出；没有发生就找不到。）

Buffer Busy Waits Cause

未完待续。。。。。。。。

Free Buffer Waits Cause

原因基本是: DBWn may not be keeping up with writing dirty buffers in the following situations.

- The I/O system is slow.

solution: 文件放在不同的磁盘上；没效果就换磁盘

- The I/O is waiting for resources, such as latches.

solution: 文件放在不同的磁盘上；没效果就换磁盘

- The buffer cache is so small the DBWn spends most of its time cleaning out buffers for server processes.

solution: Increase the buffer cache size.

- The buffer cache is so large that one DBWn process cannot free enough buffers in the cache to satisfy requests.

soluition: Decrease the buffer cache size or initialize more database writer processes.

2.2 针对Cache Hit Ratio的诊断和调优

可以从awr中获取到Buffer Cache Hit Ratio

影响命中率的因素有如下几个：

• Full table scans
• Data or application design
• Large table with random access
• Uneven distribution of cache hits (热块和冷块)

Hit Ratio is not everything

联合这三个视图来检查瓶颈

v$session_wait/v$session_event/v$system_event

2.3 根据Dynamic Advisory Parameter来调整Buffer Cache大小

首先需要将DB_CACHE_ADVICE这一参数设置为on，然后再从V$DB_CACHE_ADVICE中查询核实的Buffer Cache大小。

查询SQL语句：

<span style="font-size:14px;">SQL> col name format a10;
SQL> select name, size_for_estimate, estd_physical_read_factor, estd_physical_reads from
v$db_cache_advice order by name, size_for_estimate;</span>

其中estd_physical_read_factor是估计物理读次数和实际物理读此数的比值,estd_physical_reads是估计物理读次数。当estd_physical_read_factor和estd_physical_reads不再明显降低时，取此时的buffer cache大小。

3 什么时候应该增大Buffer Cache Size？

Increase the cache size ratio under the following conditions:

• Any wait events have been tuned (三种wait event消失了)
• SQL statements have been tuned
• There is no undue page faulting ()
• The previous increase of the buffer cache was effective
• Low cache hit ratio.

As a general rule, investigate increasing the size of the cache if the cache hit ratio

is low and your application has been tuned to avoid performing full table scans.