高速缓存区db_cache的优化

调试高速缓存区：
SQL> desc v$db_cache_advice
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)
 BLOCK_SIZE                                         NUMBER
 ADVICE_STATUS                                      VARCHAR2(3)
 SIZE_FOR_ESTIMATE                                  NUMBER
 SIZE_FACTOR                                        NUMBER
 BUFFERS_FOR_ESTIMATE                               NUMBER
 ESTD_PHYSICAL_READ_FACTOR                          NUMBER
 ESTD_PHYSICAL_READS                                NUMBER
 ESTD_PHYSICAL_READ_TIME                            NUMBER
 ESTD_PCT_OF_DB_TIME_FOR_READS                      NUMBER
 ESTD_CLUSTER_READS                                 NUMBER
 ESTD_CLUSTER_READ_TIME                             NUMBER


statspack or awr 报告中Buffer Pool Advisory --->

P    Size for Est (M)    Size Factor    Buffers for Estimate    Est Phys Read Factor    Estimated Physical Reads
D     8     0.10     998     2.33     91,782
D     16     0.20     1,996     1.68     65,981
D     24     0.30     2,994     1.43     56,228
D     32     0.40     3,992     1.30     50,991
D     40     0.50     4,990     1.14     44,891
D     48     0.60     5,988     1.09     42,735
D     56     0.70     6,986     1.04     40,869
D     64     0.80     7,984     1.02     40,070
D     72     0.90     8,982     1.01     39,748
D     80     1.00     9,980     1.00     39,372
D     88     1.10     10,978     0.99     38,949
D     96     1.20     11,976     0.99     38,784
D     104     1.30     12,974     0.98     38,564
D     112     1.40     13,972     0.98     38,423
D     120     1.50     14,970     0.97     38,361
D     128     1.60     15,968     0.97     38,086
D     136     1.70     16,966     0.96     37,969  ----->物理因子降到最低时对应的db_cache_size大小是最合适的
D     144     1.80     17,964     0.96     37,929
D     152     1.90     18,962     0.96     37,898
D     160     2.00     19,960     0.96     37,726

 


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

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session logical reads                                               2078115
db block gets                                                        579267
consistent gets                                                     1498848
physical reads                                                        40521


SQL> select 579267+1498848 from dual;

579267+1498848
--------------
       2078115


physical reads  --->物理读i/o
就是从磁盘读取数据块数量 产生原因：
1. 在数据库高速缓存区不存在的块
2. 全表扫描
3. 磁盘排序