一、Server process和Buffer Cache：

SP是如何请求读取数据块的

1.SP使用hash函数检查所请求的数据块是否在buffer cache里：

a． 如果相应的buffer被找到，则该buffer被移到LRU列表的MRU（most recently used）端，这个叫做逻辑读（logical read），因为实际没有I/O产生；

b．如果相应的buffer未被找到，则SP必须从数据文件里读取所需数据块。

2.在执行b之前，SP会去扫描LRU列表寻找空闲的buffer块（free buffer），与此同时会将已经被SP修改的buffer块放到write list（或checkpoint quene）上，以便于在检查点发生时这些dirty buffer里面的数据能够被拷回磁盘。

3. 如果checkpoint quene超过了它的阀值（size threshold），或者如果SP经历了一次查找阀值（search threshold）还未找到空闲buffer块，SP就会通知DBWn将dirty buffer里面的数据flush掉（写到磁盘）；

4. 直到一个free buffer块被找到，SP就会将数据文件的数据块读取到该free buffer中，如果该数据块不是读一致性的（not read sonsistent），SP就会从前镜像信息（past image information）中构建一个读一致性拷贝。

At any given time, the buffer cache may hold multiple copies of a single database block.

Only one current copy of the block exists, but to satisfy queries server processes may need

to construct read-consistent copies from past image information(a CR block).

 

二、调优目的与工具

目的：减少物理I/O操作，提高buffer cache的命中率，减轻DB workload。

调优指标及工具：等待事件、直接命中率、缓冲池大小、查找空闲buffer的艰难度。

等待事件↘：v$system_event, v$session_event, v$session_wait.   

主要的几个wait events：Buffer Busy Waits, Free Buffer Inspected, Free Buffers Waits(参见另一篇).

 

SQL> SELECT event, total_waits

2  FROM   v$system_event

3  WHERE  event in

4    ('free buffer waits',

5     'buffer busy waits');

EVENT                  TOTAL_WAITS

---------------------- -----------

free buffer waits              337

buffer busy waits             3466

 

   Buffer cache尺寸：v$db_cache_advice

      (db_cache_size, db_keep_cache_size,db_recycle_cache_size)

   Cache hit ratio ↗:  v$sysstat (含该计算CHR的参数), v$sesstat,  Statspack.

Hit Ratio = 1 – [(physical reads – physical reads direct - physical reads direct (lob) )] /

session logical reads

SQL>  select name,value from v$sysstat

  2  where name in ('session logical reads',                     #逻辑读请求次数

  3                 'physical reads',                   #从磁盘读取的数据块个数

  4                 'physical reads direct',         #不需要Cache，直接读的个数

  5                 'physical reads direct (lob)');  #直接读大的二进制对象的次数

 

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

session logical reads                                                171695

physical reads                                                         7242

physical reads direct                                                    24

physical reads direct (lob)                                               0

 

SQL> SELECT 1 - (phy.value - lob.value - dir.value)

  2           / ses.value  "CACHE HIT RATIO"

  3    FROM   v$sysstat ses, v$sysstat lob,

  4           v$sysstat dir, v$sysstat phy

  5    WHERE ses.name = 'session logical reads'

  6    AND dir.name = 'physical reads direct'

  7    AND lob.name = 'physical reads direct (lob)'

  8    AND phy.name = 'physical reads';

 

CACHE HIT RATIO

---------------

     .890831495

 

Free Buffer Inspected

You should consider increasing the buffer cache size if there are high or increasing values

for the Free Buffer Inspected system statistic. This statistic is the number of buffers

skipped to find a free buffer. Buffers are skipped because they are dirty or pinned.

SQL> select name,value from v$sysstat

  2  where name = 'free buffer inspected';

 

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

free buffer inspected                                                     0

 

其他视图：v$buffer_pool_statistics（各个缓冲池信息）, v$buffer_pool（描述多缓冲池）, v$bh (描述buffer cache里面的块)

SQL> select name,physical_reads,db_block_gets,

  2  consistent_gets from v$buffer_pool_statistics;

 

NAME                 PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS

-------------------- -------------- ------------- ---------------

DEFAULT                        7220         40604          132453

 

调优方法：

1.sql语句：是否调优了？降低其对数据块的请求，即降低workload,杜绝无条件扫描大记录表，例：select *语句；

2.索引：是否有创建的必要来减少不必要的块阅读？

3.v$db_cache_advice：是有有必要修改buffer cache的大小？

4.多缓冲池技术（multiple buffer pool）：对不同类型的分流

5.常查的小表被固定到缓冲池中：cached

Technical Note

You need to consider the impact of operating system caching. For example, the Oracle

server may show a high rate of physical I/O that does not appear at the operating system

level. This could mean that Oracle blocks, aged out of the buffer cache, are kept in the

operating system cache and can be accessed very quickly. However, as a general rule it is

best to bypass the operating system cache, because:

* More memory may be required to maintain duplicate blocks in memory (one block in

the operating system cache and one in the database buffer cache).

* There is the CPU overhead of copying blocks from the operating system cache to the

database buffer cache.