配置和使用buffer cache

存放从磁盘读取的数据。排序和并行读不会使用buffer cache

可以从v$db_cache_advice或者buffer cache的命中率来检查buffer cache设置是否合理

查看v$db_cache_advice需要开启参数db_cache_advice

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

SQL> show parameter db_cache_advice   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_cache_advice                      string      ON SQL> SQL> SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads   2  FROM V$DB_CACHE_ADVICE   3  WHERE name = 'DEFAULT'   4  AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')   5  AND advice_status = 'ON';   SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS ----------------- -------------------- ------------------------- -------------------              2176               254898                    7.6197          4008798521              4352               509796                    2.5707          1352442576              6528               764694                    1.1147           586464253              8704              1019592                    1.0029           527644038             10880              1274490                         1           526108526    #当前buffer cache的大小(即size_for_estimate的值1088MB)，BUFFERS_FOR_ESTIMATE表示buffer数             13056              1529388                         1           526108526             15232              1784286                         1           526108526             17408              2039184                         1           526108526             19584              2294082                         1           526108526             21760              2548980                         1           526108526             23936              2803878                         1           526108526             26112              3058776                         1           526108526             28288              3313674                         1           526108526             30464              3568572                         1           526108526             32640              3823470                         1           526108526             34816              4078368                         1           526108526             36992              4333266                         1           526108526             39168              4588164                         1           526108526             41344              4843062                         1           526108526             43520              5097960                     .6183           325295281   20 rows selected.   SQL>

 

buffer cache的命中率=1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache'))
其中，
-consistent gets from cache：　　从buffer cache中获取数据块的一致性读次数
-db block gets from cache：　　  从buffer cache中读取当前块的次数
-physical reads cache：　　　　   从磁盘读入buffer cache的次数

?

1 2 3 4 5 6 7 8 9 10 11

SQL> SELECT NAME, VALUE   2  FROM V$SYSSTAT   3  WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');   NAME                                                                  VALUE ---------------------------------------------------------------- ---------- db block gets from cache                                           78868122 consistent gets from cache                                       5224871044 physical reads cache                                              526202271   SQL>

 

Buffer Pool命中率=1 - (physical_reads/(db_block_gets + consistent_gets))

?

1 2 3 4 5 6 7 8 9

SQL> select name, physical_reads, db_block_gets, consistent_gets,   2  1 - (physical_reads / (db_block_gets + consistent_gets)) "hit ratio"   3  from v$buffer_pool_statistics;   NAME                 PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS  Hit Ratio -------------------- -------------- ------------- --------------- ---------- DEFAULT                   526799914      78941788      5234506897 .900855368   SQL>