查看 buffer cache 命中率

SQL> select name,value from v$sysstat where name in('db block gets','consistent gets','physical reads');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                           932
consistent gets                                                       50535
physical reads                                                         9145

命中率计算公式

Hit Radio=1-physical reads/(db block gets+consistent gets)

 

SQL> select (1-9145/(50535+932)) from dual;

(1-9145/(50535+932))
--------------------
          .822313327
SQL> show parameter db_block_buffers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0

由于10G中引入了SGA自动管理，所以show parameter 查出来的db_block_buffers为 0

查看当前buffer cache size

SQL> select name ,bytes/1024/1024 Mb from v$sgainfo where name ='Buffer Cache Size';

NAME                                     MB
-------------------------------- ----------
Buffer Cache Size                       288    这个buffer cache size 指的是 default + keep +recycle 的和

SQL> select current_size from v$buffer_pool;

CURRENT_SIZE
------------
         288         这里 我没有配置 keep pool ,也没有配置 recycle pool

如果一个长期运行的database 长期的 Hit Radio<90%,那么就应该考虑多分配点内存给buffer cache ,同时增加物理内存，或者SQL调优

理想的hit radio 应该在 95%以上