Buffer cache 的调整与优化(一)

雨点打透心脏的1/2处 2022-05-15 05:04 242阅读 0赞

\--==============================

\-- Buffer cache 的调整与优化(一)

\--==============================

Buffer Cache是SGA的重要组成部分，主要用于缓存数据块，其大小也直接影响系统的性能。当Buffer Cache过小的时候，将会造成更多的

free buffer waits事件。下面将具体描述Buffer Cache的作用，调整与优化。

一、SGA的所有组件

从动态视图v$sga\_dynamic\_components获取SGA的相关信息

SELECT component, current\_size, min\_size FROM v$sga\_dynamic\_components;

COMPONENT CURRENT\_SIZE MIN\_SIZE

\------------------------- ------------ ----------

shared pool 71303168 71303168

large pool 4194304 4194304

java pool 4194304 4194304

streams pool 4194304 4194304

DEFAULT buffer cache 113246208 113246208

KEEP buffer cache 0 0

RECYCLE buffer cache 0 0

DEFAULT 2K buffer cache 0 0

DEFAULT 4K buffer cache 0 0

DEFAULT 8K buffer cache 0 0

DEFAULT 16K buffer cache 0 0

DEFAULT 32K buffer cache 0 0

ASM Buffer Cache 0 0

二、Buffer cache介绍

1.Buffer cache的类型通常包括

default buffer cache

keep buffer cache

recycle buffer cache

nk buffer caches

与Buffer cache打交道的后台进程为DBWn，与之对应的数据文件通常位于system 表空间，sysaux表空间，undo表空间，datafile 等

Buffer cache是SGA的一部分，其内容是由用户进程从数据文件读取出来的数据块，并且所有的用户共享这些数据块。通常，服务

器进程为提高I/O性能，会一次性读多个数据块。对于Buffer cache中的脏数据则由DBWn进程写入到数据文件。同样为提高性能，DBWn

进程也会一次写多个数据块。Buffer cache会拥有一个数据块的多个副本，当前块的最新副本仅有一份，而该数据块老的或旧的副本可

能有多份，用于块的读一致性。Buffer cache采用LRU算法来淘汰掉过时的数据块。

Buffer cache中存在检查点队列以及LRU链表。

2.Buffer cache的几个相关参数

Buffer cache能有由多个独立的且具有不同block size的缓冲池(buffer pool)组成。

DB\_BLOCK\_SIZE：参数决定了数据库主块的块大小，该块的大小通常被系统表(system,sysaux)空间和主要的Buffer cache(recycle,keep,

default buffer cache)所使用

决定主要的Buffer cache大小的几个参数

db\_cache\_size

db\_keep\_cache\_size

db\_recycle\_cache\_size

3.Buffer cache中块的四种状态

pinned:意味着多个会话在相同的时段写同一个数据块，其他的会话等待访问块。

clean:优先要淘汰掉的数据块,即不是pinned状态,也不会被再次使用的块.该块可能和磁盘上的块处于同步状态,也可能是一个读一致性块

free/unused:即Buffer cache中的块处于空闲状态或未使用状态，通常是由于实例刚刚启动。

dirty：已发生变化的数据块，且没有进程再使用该块，则在aged out之前需要立即由DBWn 写入到数据文件。

服务器进程将数据块从数据文件填充到Buffer cache，当Buffer cache中不再需要使用到数据块的副本时，而DBWn进程则将脏数据

写入到数据文件，用于将数据块由 pinned 状态变为free 状态。

4.参数db\_block\_checksum

该参数设置为true，则一个指定的校验码被同时写入到数据块，用于防止磁盘，I/O系统损坏导致数据的丢失。

三、客户端服务器进程从Buffer cache获取数据的过程

1.服务器进程使用一个哈希函数来检查所需的数据块是否已经位于Buffer cache。如果在Buffer cache中找到所需的数据块，则该块根据使

用的频率放置到LRU队列中特定的位置。此时的读数据块为逻辑读，且不在需要执行后续步骤。如果不在Buffer cache中，则转到下一步。

2.服务器进程搜索LRU列表中是否存在可用的空闲空间存放新的数据块。在搜索LRU列表同时时，已经被修改的脏数据将被服务器进程放置到

检查点队列。

3.检查点队列长度超出预设大小的阙值或服务器进程搜索空闲块操作预设的次数(由隐藏参数\_db\_block\_max\_scan\_pct所指定的值，表示已

经扫描的buffer header数量占整个LRU链表上的buffer header的总数量，在i中该限定值为40%)，则服务器进程通过DBWn将脏数据从

Buffer cache写出到数据文件。

4.当可用的空闲块被找到后，服务器进程从数据文件读入块到Database Buffer cache并放置到LRU队列中。如果所得到的块不是一致性读块

，则服务器进程从undo segment中重构一致性块。

Buffer cache与DBWn密切相关，下面给出DBWn触发的条件

脏缓冲列表达到指定的阙值大小

搜索LRU空闲队列达到预设的阙值次数

发生检查点事件

数据库关闭时

表空间实现热备份时

表空间离线

在段被删除时

更多有关体系结构请参考：[Oracle实例和Oracle数据库(Oracle体系结构)][Oracle_Oracle_Oracle]

四、对buffer cache调优，命中率等

1.调整buffer cache调优规则

调优的目标：尽可能在Buffer cache中找到数据，降低等待可用空闲块的时间

调试方法：

wait events

cache hit ration

v$db\_cache\_advice view

调整手段

降低SQL命令对数据块的请求，如避免使用select \* from 语句

增加缓冲池的大小

不同访问方式使用不同的缓冲池(buffer pools)

缓存常用的表到内存

并行读或排序操作不使用cache,直接从磁盘读入到PGA及内存

2.决定Buffer cache的几个指标

下面的查询中列出了涉及到buffer cache的几个重要指标数

SELECT NAME, VALUE

FROM v$sysstat

WHERE NAME IN ('session logical reads',

'physical reads',

'physical reads direct',

'physical reads direct (lob) ',

'consistent gets',

'db block gets',

'free buffer inspected',

'free buffer requested',

'dirty buffers inspected',

'pinned buffers inspected');

NAME VALUE

\------------------------------ -------------

session logical reads 139150060175

db block gets 274690511

consistent gets 139129962467

physical reads 21335058151

free buffer requested 21085155516

dirty buffers inspected 156801

pinned buffers inspected 432841

free buffer inspected 968639

physical reads direct 4995527

Session Logical Reads：所有的逻辑读的数据块的数量。

Free Buffer Inspected指标:

为寻找空闲buffer之前所检查块的总数量，即跳过块的数量。如果该值接近脏数据块的数量，则表明空闲块很少，该值应尽可能小

于脏块的数量。

Free Buffer Waits:

当session在LRU list上没有寻找到空闲可用数据块或者搜寻可用的内存数据块被暂停的时候，该发生该事件，此为等待DBWn将脏

块写入到数据文件的等待数。除此之外，会话在做一致性读时，需要构造数据块在某个时刻的前映像（image），此时需要申请内

存来存放这些新构造的数据块，如果内存中无法找到这样的内存块，也会发生这个等待事件。

Buffer Busy Waits:

用户服务器进程已找到所需的数据块，但该块正被其它进程使用或多个进程同时要修改该块，此时需要等待的时间。

当一个会话需要读取一个数据块，但这个数据块正在被另一个会话读取到内存中时，此时同样发生Buffer Busy Waits事件。

SELECT event

,total\_waits

FROM v$system\_event

WHERE event IN ('free buffer waits','buffer busy waits');

EVENT TOTAL\_WAITS

\------------------------- -----------

buffer busy waits 35216021

查询event事件名称

SELECT NAME

,parameter1

,parameter2

,parameter3

FROM v$event\_name

WHERE NAME='buffer busy waits';

NAME PARAMETER1 PARAMETER2 PARAMETER3

\------------------------------ -------------------- -------------------- ------------

buffer busy waits file\# block\# id

产生Buffer busy waits的几种情形

DATA BLOCK，数据块的竞争，该情形通常是基于表段和索引段上的竞争，下面的处理办法

尽可能缩小SQL语句的查询字段，查询范围，如不使用select \* 查询，将like子句改为直接赋值等

检查索引的合理性。如使用了sequence生成的索引，其索引键通常位于相同的块，因此可以使用反向索引避免此问题

使用自动段空间管理或增加空闲列表，以避免多个进程同时插入相同的块

查询视图v$session\_wait来获得热点块的文件ID，块ID，通过这些信息来获得对象ID，进一步对该对象进行调整

UNDO Header

基于UNDO段头部的竞争，如果未使用自动撤销段管理模式，则需要增加更多的回滚段

UNDO BLOCK

基于UNDO段块的竞争，如果未使用自动撤销段管理模式，则需为回滚段分配更大的尺寸

产生Free Buffer waits的几种情形

DBWn进程来不及将数据写入到数据文件，导致需要等待被释放的空间

I/O系统速度过于缓慢

确保数据库文件是否分布在不同的磁盘上，或增加更高性能的磁盘

资源等待造成I/O系统过慢，如latch等待

确保数据库文件是否分布在不同的磁盘上，或增加更高性能的磁盘

Buffer cache太小，导致DBWn来不及将脏数据写入到数据文件

需要增大buffer cache的尺寸

Buffer cache太大，而单一的DBWn进程需要多次才能将数据写入到文件

减少buffer cache的尺寸，或增加更多的DBWn进程

3.评估Cache的命中率

计算命中率的思想

1\-(物理读的次数\-总的请求次数)

**计算命中率**

SELECT ROUND(1 \- ((physical.value \- direct.value \- lobs.value) / logical.value),3) \*100 ||'%'

"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';

physical reads

从Oracle级别来理解，从磁盘读数据块的次数，一次可以读多块,由参数db\_file\_multiblock\_read\_count来控制。此种读方式使用

了db cache.

形象示意：db\_file ==> db\_cache ==> pga

physical reads direct

有些数据块不会先从硬盘读入内存再从内存读入PGA再传给用户，而是绕过SGA直接从硬盘读入PGA。比如并行查询以及从临时表空

间读取数据。这部分数据块由于不缓存使得hit ratio不会被提高。其在计算hit ratio时应当被扣除。

形象示意：db\_file ==> pga

通常，disk sort / hash , exp direct=Y ,都会有physical reads direct

scott@ORCL> select name from v$statname where statistic\# in (54,55,56);

NAME

\----------------------------------------------------------------

physical reads

physical reads cache \--使用buffer cache

physical reads direct \--不使用buffer cache

physical reads direct (lob)

对于大值对象，如LOB数据类型以及LOB段，Oracle可以绕过buffer cache而直接使用PGA，其原理等同于physical reads direct

使用physical reads direct，physical reads direct (lob)的优点：

对于一个大操作，需要请求大量数据块，假设又使用并行执行，且执行次数就那么一次，这个时候就适合使用direct方式，

如果还是走buffer cache则需要把buffer cache里已缓存的数据库都清空

注意physical write /direct 同理

session logical reads

发出的总的请求次数，此处是指从database buffer cache中请求块。对于一致性读，则这些缓冲包含来自回滚段的数据

下面是另一种不同的**计算命中率**的方法，通常用在10g和11g之中

SELECT NAME,

physical\_reads,

db\_block\_gets,

consistent\_gets,

ROUND((1 \- (physical\_reads / (db\_block\_gets \+ consistent\_gets))) \* 100) || '%' ratio

FROM V$BUFFER\_POOL\_STATISTICS

WHERE NAME = 'DEFAULT';

SELECT (1 \- (SUM(decode(NAME, 'physical reads', VALUE, 0)) /

(SUM(DECODE(NAME, 'db block gets', VALUE, 0)) \+

SUM(DECODE(NAME, 'consistent gets', VALUE, 0))))) \* 100 "Hit Ratio"

FROM v$sysstat;

consistent gets from cache

在回滚段Buffer中的数据构造一致性读数据块的总次数。其产生原因是由于其他会话对当前数据块进行操作，如update操作，

但是由于我们的查询是在这些修改之前调用的，所以需要使用回滚段中的数据块的前映像进行查询，来保证数据的一致性。

这样就产生了一致性读。

db block gets

在操作中提取的块数目，而不是在一致性读的情况下而产生的块数。

当前块(current,相对于cosistent读而言，current总是最新的块)，从buffer cache中请求的次数。当前块意思就是在操作

中正好提取的块数目，而不是在一致性读的情况下而产生的块数。通常的情况下，一个查询提取的块是在查询开始的那个时

间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。

physical reads cache

从磁盘读入到buffer cache中的总次数。

产生的主要原因是：在数据库高速缓存中不存在这些块,全表扫描,磁盘排序等

**db\_block\_gets \+ consistent\_gets****两者之和作为总的请求次数，在与physical\_reads相比进而得到命中率**

此方法与前面命中率计算的方法不一样，更简单直观。

4.影响命中率的因素

全表扫描(小标尚可，对于大表而言I/O性能更差。而且全表扫描总是被置于LRU的最尾端，随时被aged out)

不同数据定义和应用程序设计影响命中率

大表的随机访问(非顺序)

不均衡的cache hit

命中率需要考虑的问题

a.对相同的大表和索引的重复扫描容易造成命中率很高的假象。定期检查频繁使用且返回结果集很大的SQL语句，确保这些SQL语

句使用了最优的执行计划。

b.避免返回查询相同的数据，尽可能将获得的结果集缓存的客户端程序或中间件。

c.大表的全表扫描问题，直接将其放置到LRU的尾端，容易aged out。

(小表通常指全表扫描时占用buffer cache 20%或拥有个数据块)。

d.对较大OLTP系统而言，表中的很多行仅仅被访问次或很少的次数，基于此，这些块不易长时间占住buffer cache。

f.对于并行查询或排序等，持续增加buffer cache的大小并无实际意义，优化效果并明显。

5.下列情形可以考虑增加buffer cache

一些等待事件已经被优化

不良的SQL语句已经被优化

操作系统级别无不良的内存页面置换

上次增加的buffer cache有效

基于上面的情形，且命中率很低，此时可以增加buffer cache

6.增加buffer cache 的步骤

首先将db\_cache\_advice置于ON状态

检查动态性能视图v$db\_cache\_advice(需要考虑增加后不影响操作系统级别过多的内存页面置换)

动态增加db\_cache\_size的值(生产数据库不建议关闭系统而使用动态调整alter system set db\_cache\_size=nM;)

查看当前buffer cache的大小

SELECT NAME,current\_size,buffers FROM v$buffer\_pool;

NAME CURRENT\_SIZE BUFFERS

\------------------------------ ------------ ----------

DEFAULT 3456 428760

7.减少buffer cache 的情形

在命中率很高的情形下，查询视图v$db\_cache\_advice，来权衡适度降低buffer cache size是否会使得系统I/O显剧增加，如不是，且

降低buffer cache size不会影响性能的情况下，则可以适度降低buffer cache size的大小。

使用alter system set db\_cache\_size来调整

8.使用advisor来调整buffer cache

buffer cache advisor 可以启用或禁用通过收集统计信息来预估buffer cache的大小，然后根据预估的大小以及工作负荷来调整buffer

cache的大小。buffer cache advisor功能通过设置参数db\_cache\_advice 开启用或禁用，该参数仅能支持系统级别的修改。

其参数值为:OFF,ON,READY

OFF：禁用buffer cache advisor特性，且不为advisor分配内存

READY：不收集数据,但是收集数据的内存已经预先分配好了.通过把参数值从off设置为ready,然后再设置为on，以避免出现错误。

ALTER SYSTEM SET db\_cache\_advice = ON | READY | OFF ;

该参数设置的前提条件为STATISTICS\_LEVEL参数必须要先设置为TYPICAL或者ALL

通过设置启用buffer cache advisor 功能后，可以查看视图v$db\_cache\_advice来获得不同负荷下的advisor

以下是该视图的几个重要列

SIZE\_FOR\_ESTIMATE

BUFFERS\_FOR\_ESTIMATE

ESTD\_PHYSICAL\_READS

**查看缺省的buffer cache的advice**

SELECT size\_for\_estimate "Cache Size (MB)",

size\_factor,

buffers\_for\_estimate "Buffers",

estd\_physical\_read\_factor est\_read\_factor,

estd\_physical\_reads estd\_phy\_red

\-- ,estd\_physical\_read\_time est\_phy\_red\_t --此参数在i 中不可用

FROM v$db\_cache\_advice

WHERE NAME='DEFAULT'

AND block\_size=(

SELECT VALUE

FROM v$parameter

WHERE NAME='db\_block\_size'

AND advice\_status='ON');

Cache Size (MB) SIZE\_FACTOR Buffers EST\_READ\_FACTOR ESTD\_PHY\_RED

\--------------- ----------- ---------- --------------- ------------

352 .1019 43670 15.8455 6.1906E+10

704 .2037 87340 58.5913 2.2891E+11

1056 .3056 131010 31.6149 1.2351E+11

1408 .4074 174680 21.5397 8.4152E+10

1760 .5093 218350 14.6721 5.7321E+10

2112 .6111 262020 8.3646 3.2679E+10

2464 .713 305690 3.8134 1.4898E+10

2816 .8148 349360 1.9615 7663197796

3168 .9167 393030 1.2403 4845534681

3456 1 428760 1 3906820053

3520 1.0185 436700 .9675 3779821439

3872 1.1204 480370 .8359 3265831355

4224 1.2222 524040 .7512 2934896434

4576 1.3241 567710 .6957 2717876216

4928 1.4259 611380 .6565 2564852647

5280 1.5278 655050 .6272 2450487527

5632 1.6296 698720 .6045 2361720470

5984 1.7315 742390 .5859 2289177246

6336 1.8333 786060 .5726 2236962972

6688 1.9352 829730 .5619 2195121418

7040 2.037 873400 .5521 2156817317

9.优化Buffer cache的常用参数及视图

db\_cache\_size

db\_cache\_advice

db\_keep\_cache\_size

db\_recycle\_cache\_size

db\_file\_multiblock\_read\_count

statspace report

v$db\_cache\_advice(view)

视图

v$buffer\_pool\_statistics

v$buffer\_pool

v$db\_cache\_advice

v$sysstat

v$sesstat

v$system\_event

v$session\_wait

v$bh

v$cache

五、更多参考

有关闪回特性请参考

[Oracle 闪回特性(FLASHBACK DATABASE)][Oracle _FLASHBACK DATABASE]

[Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)][Oracle _FLASHBACK DROP _ RECYCLEBIN]

[Oracle 闪回特性(Flashback Query、Flashback Table)][Oracle _Flashback Query_Flashback Table]

[Oracle 闪回特性(Flashback Version、Flashback Transaction)][Oracle _Flashback Version_Flashback Transaction]

有关基于用户管理的备份和备份恢复的概念请参考：

[Oracle 冷备份][Oracle]

[Oracle 热备份][Oracle 1]

[Oracle 备份恢复概念][Oracle 2]

[Oracle 实例恢复][Oracle 3]

[Oracle 基于用户管理恢复的处理][Oracle 4](详细描述了介质恢复及其处理)

有关RMAN的恢复与管理请参考：

[RMAN 概述及其体系结构][RMAN]

[RMAN 配置、监控与管理][RMAN 1]

[RMAN 备份详解][RMAN 2]

[RMAN 还原与恢复][RMAN 3]

有关Oracle体系结构请参考：

[Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle_Oracle_Oracle]

[Oracle 表空间与数据文件][Oracle 5]

[Oracle 密码文件][Oracle 6]

[Oracle 参数文件][Oracle 7]

[Oracle 数据库实例启动关闭过程][Oracle 8]

[Oracle 联机重做日志文件(ONLINE LOG FILE)][Oracle _ONLINE LOG FILE]

[Oracle 控制文件(CONTROLFILE)][Oracle _CONTROLFILE]

[Oracle 归档日志][Oracle 9]

[Oracle_Oracle_Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529239.aspx
[Oracle _FLASHBACK DATABASE]: http://blog.csdn.net/robinson_0612/archive/2010/12/27/6100429.aspx
[Oracle _FLASHBACK DROP _ RECYCLEBIN]: http://blog.csdn.net/robinson_0612/archive/2010/12/29/6105327.aspx
[Oracle _Flashback Query_Flashback Table]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112607.aspx
[Oracle _Flashback Version_Flashback Transaction]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112981.aspx
[Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791597.aspx
[Oracle 1]: http://blog.csdn.net/robinson_0612/archive/2010/08/16/5816012.aspx
[Oracle 2]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791585.aspx
[Oracle 3]: http://blog.csdn.net/robinson_0612/archive/2010/07/27/5768233.aspx
[Oracle 4]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041217.aspx
[RMAN]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032525.aspx
[RMAN 1]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032597.aspx
[RMAN 2]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032739.aspx
[RMAN 3]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041247.aspx
[Oracle 5]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611738.aspx
[Oracle 6]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611672.aspx
[Oracle 7]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559174.aspx
[Oracle 8]: http://blog.csdn.net/robinson_0612/archive/2010/04/29/5542983.aspx
[Oracle _ONLINE LOG FILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/20/5749556.aspx
[Oracle _CONTROLFILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/15/5737527.aspx
[Oracle 9]: http://blog.csdn.net/robinson_0612/archive/2010/08/03/5784713.aspx