Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


AMM and Oracle Instance Tuning

To fully understand AMM, the DBA needs to look at what happens at a detailed level within the buffers.  Figure 14.4 shows that a marginal increase in data buffer blocks is asymptotic to disk I/O. 


In databases with a very small db_cache_size, a large reduction in disk I/O is achieved with a small increase in the size of a small RAM buffer, as shown in the diagram.


Figure 14.4: Reduction in disk I/O from an increase to RAM data buffer.


This shows that a small increase in the size of db_cache_size results in a large reduction in actual disk I/O.  This happens because the cache is now larger and frequently referenced data blocks can now stay resident in the RAM data buffer.


However, the impressive reduction in disk I/O does not continue indefinitely.  As the total RAM size begins to approach the database size, the marginal reduction in disk I/O begins to decline as shown in Figure 14.5.


This low marginal cost is due to the fact that all databases have data that is accessed infrequently.  Infrequently accessed data does not normally have a bearing on the repeated reads performed by traditional OLTP applications, and this is why there is a marked decline in the marginal benefit as the database approaches full RAM caching.


Figure 14.5: Large buffers changes result in small I/O gains


As a general guideline, all memory available on the host should be tuned, and the db_cache_size should be allocating RAM resources up to the point of diminishing returns as shown in Figure 14.6. 


This is the point where additional buffer blocks do not significantly improve the buffer hit ratio.


Figure 14.6: The optimal size of the RAM data buffer


Taking the above into account, the thrifty DBA will apply this simple rule: db_cache_size should be increased if spare memory is available and marginal gains can be achieved by adding buffers.  Of course, increasing the buffer blocks increases the amount of RAM running on the database. 


Hence, the database management system may place more demands on the processor than it can handle.  The administrator must carefully juggle the amount of available memory with the limitations of the hardware in determining the optimal size of buffer blocks.


In Oracle9i and earlier, the DBA should prepare a strategy for enabling cache advice.  Setting the dba_cache_advice =on while the database is running will cause Oracle to grab RAM pages from the shared pool RAM area, with potentially disastrous consequences for the objects in the library cache. 


For complex databases that can benefit from Oracle’s sophistication, the DBA controls not only the size of each data buffer, but also the block size of each individual buffer.  For example, suppose the database tends to cluster records on a single database block, while the other data blocks remain small.  Realizing that the I/O for a 32K block is virtually the same as the I/O for a 4K block, the database designer might choose to make some of the buffers larger to minimize I/O contention.


With the cache advice utility, Oracle10g provides the DBA with another tool to streamline database performance by predicting the optimal size of the RAM buffer pools.


The following sections will plot the average DBHR for an Oracle database over different intervals.


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.