Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
    
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:
 

D 60 0.5 7,455 9.88 613,380,871
D 72 0.6 8,946 1.51 93,521,075

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

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.