Oracle性能优化：SQL优化思路之五——解析与缓冲优化

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

buffer cache调优方法：

wait events

cache hit ration

v$db_cache_advice view

调优思路：

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

增加缓冲池的大小

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

缓存常用的表到内存

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

决定Buffer cache的几个指标：

SQL> 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 7468866

db block gets 2999122

consistent gets 4469744

physical reads         69585

physical reads direct     1531

free buffer requested     161297

dirty buffers inspected 8553

pinned buffers inspected         51

free buffer inspected 77232

9 rows selected.

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

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

Free Buffer Waits: 当session在LRU list上没有寻找到空闲可用数据块或者搜寻可用的内存数据块被暂停的时候，该发生该事件，此为等待DBWn将脏块写入到数据文件的等待数。除此之外，会话在做一致性读时，需要构造数据块在某个时刻的前映像（image），此时需要申请内存来存放这些新构造的数据块，如果内存中无法找到这样的内存块，也会发生这个等待事件。

Buffer Busy Waits: 用户服务器进程已找到所需的数据块，但该块正被其它进程使用或多个进程同时要修改该块，此时需要等待的时间。当一个会话需要读取一个数据块，但这个数据块正在被另一个会话读取到内存中时，此时同样发生Buffer Busy Waits事件。

评估Cache的命中率：

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

Buffer Cache Hit Ratio

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

99.1%

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

    一些等待事件已经被优化

    不良的SQL语句已经被优化

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

    上次增加的buffer cache有效

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

增加buffer cache 的步骤:

    首先将db_cache_advice置于ON 状态

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

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

查看当前buffer cache的大小：

SQL> select name,current_size,buffers from v$buffer_pool;

下列情形可以考虑减少buffer cache：

在命中率很高的情形下，查询视图v$db_cache_advice，来权衡适度降低buffer cache size是否会使得系统I/O显剧增加，如不是，且降低buffer cache size不会影响性能的情况下，则可以适度降低buffer cache size的大小。使用alter system set db_cache_size来调整

使用advisor来调整buffer cache：

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

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

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

 ALTER SYSTEM SET db_cache_advice = ON | READY | OFF ;

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

通常情况下，SQL语句的执行过程如下：

    a. SQL代码的语法(语法的正确性)及语义检查(对象的存在性与权限)。

    b. 将SQL代码的文本进行哈希得到哈希值。

    c. 如果共享池中存在相同的哈希值，则对这个命令进一步判断是否进行软解析，否则到e步骤。

    d. 对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。这些比较包括大小写，字符串是否一致，空格，注释、对象/schema等，如果一致，则对其进行软解析，转到步骤f。否则到f步骤。

    e. 硬解析，生成执行计划。

    f. 执行SQL代码，返回结果。

解析优化让第二次执行更快：

SQL> set autotrace traceonly;

SQL> set timing on

SQL> select count(*) from t;

153个物理读physical reads

执行第二遍：

SQL>

物理读physical reads变为0