存放从磁盘读取的数据。排序和并行读不会使用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>