一、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.