配置和使用buffer cache

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

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

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

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的次数

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))

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>