Oracle Automatic Storage Memory Management

Now that Oracle10g is being used for mainstream databases we are starting to get feedback on the Oracle 10g ASMM mechanism for automatically adjusting the sizes of the shared pool and data buffers.

The documentation shows that Oracle uses the memory advisories from Oracle9i and applies heuristics (rules of thumb) to determine the best shift in RAM pool sizes.  These heuristics consist of hypothesis testing with "what if" scenarios, computing the ratio of the marginal reduction in physical disk reads, and choosing the size with the greatest overall marginal benefit.

Click here for more on Oracle Automatic SGA storage.

Consider the actual sample below of v$db_cache_advice from an Oracle 10g database running ASMM.

Note the odd shape of the 1/x curve below for plotting the marginal reductions in disk I/O for various db_cache_size values and the fact that this database had a 100% data buffer cache hit ratio during the observed AWR snapshot period.

This is the drop-off that does not make sense, where a change from 60 to 72 meg would result in 6x reduction in disk reads:
 

When you remove the values below 60% of the current cache size, we see a more normal 1/x curve.  It might be that ASMM has never actually sampled values this small and their is a "break" in the algorithm:

Buffer Pool Statistics  DB/Inst: PROD/prod  Snaps: 2622-2623 -> Standard block size Pools  D: default,  K: keep,  R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k                                                               Free Writ     Buffer      Number of Pool         Buffer     Physical    Physical Buff Comp       Busy P      Buffers Hit%           Gets        Reads      Writes Wait Wait      Waits --- ---------- ---- -------------- ------------ ----------- ---- ---- ---------- D       15,407  100      1,687,283        3,751      10,148    0    0          9           -------------------------------------------------------------   Instance Recovery Stats  DB/Inst: PROD/prod  Snaps: 2622-2623 -> B: Begin snapshot,  E: End snapshot     Targt  Estd                                  Log File Log Ckpt     Log Ckpt   MTTR   MTTR   Recovery  Actual    Target       Size    Timeout     Interval    (s)    (s)   Estd IOs Redo Blks Redo Blks  Redo Blks Redo Blks   Redo Blks - ----- ----- ---------- --------- --------- ---------- --------- ------------ B     0    29        637      5329     18432      18432     39225 E     0    30       1592      8748     18432      18432     28281           -------------------------------------------------------------   Buffer Pool Advisory  DB/Inst: PROD/prod  Snap: 2623 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate           Size for  Size      Buffers for  Est Physical          Estimated P   Estimate (M) Factr         Estimate   Read Factor     Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D             12    .1            1,491         15.43        957,944,373 D             24    .2            2,982         11.87        737,153,531 D             36    .3            4,473         10.86        674,394,135 D             48    .4            5,964         10.23        634,999,591 D             60    .5            7,455          9.88        613,380,871 D             72    .6            8,946          1.51         93,521,075 D             84    .7           10,437          1.32         81,918,820 D             96    .8           11,928          1.20         74,410,986 D            108    .9           13,419          1.09         67,947,571 D            120   1.0           14,910          1.02         63,492,383 D            124   1.0           15,407          1.00         62,099,971 D            132   1.1           16,401          0.96         59,409,414 D            144   1.2           17,892          0.88         54,933,082 D            156   1.3           19,383          0.84         52,023,158 D            168   1.4           20,874          0.79         49,136,139 D            180   1.5           22,365          0.75         46,499,323 D            192   1.5           23,856          0.71         44,201,690 D            204   1.6           25,347          0.69         42,760,823 D            216   1.7           26,838          0.67         41,373,698 D            228   1.8           28,329          0.64         39,743,418 D            240   1.9           29,820          0.62         38,473,464