1.基本概念

BUFFER缓冲区是至关重要的内存区域之一，设置的不合理会导致缓冲页命中率低，磁盘 IO 频繁，IO的速度瓶颈会导致SQL执行性能变差，建议设置为可用物理内存的 60%～80%。
--设置方式（单位MB），设置后重启生效，DM8中已经弃用MAX_BUFFER
call SF_SET_SYSTEM_PARA_VALUE ('BUFFER',XXXX,1,2);

2.测试验证

• 以下我们进行简单的测试，设置不合理的数据缓存区，测试出对数据库造成的影响。
  为了方便测试，首先调整内存大小。
  --调整参数

call SF_SET_SYSTEM_PARA_VALUE ('BUFFER',500,1,2);
call SF_SET_SYSTEM_PARA_VALUE ('BUFFER_POOLS',5,1,2);

• 通过v$bufferpool获取实际内存

select 'BUFFER' || rownum POOL,
       N_PAGES * 8 / 1024 "总大小(MB)",
       N_LOGIC_READS "READ 命中的次数",
       N_DISCARD "淘汰的页数",
       N_PHY_READS "READ 未命中的次数",
       to_char(trunc(RAT_HIT * 100, 4)) ||'%' "命中率"
  from v$bufferpool
 where NAME = 'NORMAL'
union all
select 'SUM',
       sum(N_PAGES) * 8 / 1024,
       sum(N_LOGIC_READS),
       sum(N_DISCARD),
       sum(N_PHY_READS),
       to_char(trunc(sum(N_LOGIC_READS) * 100.0 /
             (sum(N_LOGIC_READS) + sum(N_PHY_READS)),
             4)) ||'%' 
  from v$bufferpool
 where NAME = 'NORMAL';::: hljs-left

--虽然分配了500M内存，但实际可以使用内存会少一点

• 创建两张大表，总大小大于设置的BUFFER大小

select segment_name,bytes/1024/1024 
from dba_segments where segment_name in ('TS_BUFFER_TAB1','TS_BUFFER_TAB2') and owner='SYSDBA';

• 重启数据库服务后查看系统的逻辑读和物理读次数

select name,stat_val from v$sysstat 
where name in ('physical read count','logic read count');

• 查询TS_BUFFER_tab1表，并记录使用资源情况

select name from TS_BUFFER_TAB1 where id=1;

第一次对表进行查询，需要先扫描数据块读入内存，故耗时较长-3.4S
--查看SQL的逻辑读和物理读

select a.sql_id,sql_txt,logic_read_cnt,phy_read_cnt,io_wait_time
from v$sql_stat_history a,
(select sql_id  from v$sql_history where  exec_id=500 ) b 
where  a.sql_id=b.sql_id;

物理读42457块*8K/1024=332M，与整个表占磁盘大小340M相符，走的全表扫描。
--查看系统的逻辑读和物理读次数

select name,stat_val from v$sysstat where name in ('physical read count','logic read count');

只有这一个会话，物理读的块数和上述完全一致

• 再次查询TAB1

select name from TS_BUFFER_TAB1 where id=1;

由于直接从内存中读取，比第一次快了很多。再次查看SQL的逻辑读和物理读

select sql_txt,logic_read_cnt,phy_read_cnt,io_wait_time
from v$sql_stat_history  where sql_id=7

不存在物理读的情况下，在内存中的全表扫描速度也很快，IO_WAIT_TIME也为0.
--再次查看系统的逻辑读和物理读次数

select name,stat_val from v$sysstat where name in ('physical read count','logic read count');

物理读这里也显示没有增加，增加了大量的内存逻辑读。

• 查询TS_BUFFER_tab2后，再查询TS_BUFFER_tab1

select name from TS_BUFFER_tab2 where id=1;

select name from TS_BUFFER_TAB1 where id=1;

又变回首次查询的执行耗时

--查看SQL的逻辑读和物理读

select a.sql_id,sql_txt,logic_read_cnt,phy_read_cnt,io_wait_time
from v$sql_stat_history a,(select sql_id  from v$sql_history where  exec_id=502 ) b
where  a.sql_id=b.sql_id;

• 可得知TS_BUFFER_tab1被淘汰出内存，再次进行了IO读入内存的过程
  通过上述的测试，我们可以清晰的看到BUFFER缓存区对SQL性能的提升，以及设置不合理时所导致的问题，甚至在第二次的写入内存过程中还需要参与淘汰逻辑所产生的额外消耗，可能会导致性能更差。