Tuning the Oracle data buffer cache to reduce disk I/O
In the previous module, we examined tuning the shared pool.
This module discusses tuning the Oracle data buffer cache.
The Oracle data buffers are one of the most important areas of SGA tuning, since the presence of an Oracle data block in memory can greatly reduce disk I/O.
Disk I/O is one of the most time-consuming operations that Oracle will perform.
Our focus in this module will be to understand the dynamics of the Oracle data buffers and examine techniques that provide the greatest performance gains.
By the time you complete this module, you should be able to:
- Describe the components of the Oracle data buffer
- Calculate the buffer hit ratio
- Run the simulation utility
- Predict the amount of spare memory
- Define multiple data buffers
- Configure multiple buffer pools
- Run a script to display data buffer hit ratio
- Issue the cache directive on a table
About the Database Buffer Cache
For many types of operations, Oracle Database uses the buffer cache to store data blocks read from disk. Oracle Database bypasses the buffer cache for particular
operations, such as sorting and parallel reads.
To use the database buffer cache effectively, tune SQL statements for the application to avoid unnecessary resource consumption.
To meet this goal, verify that frequently executed SQL statements and SQL statements that perform many buffer gets are well-tuned.
When using parallel query, consider configuring the database to use the database buffer cache instead of performing direct reads into the Program Global Area (PGA).
This configuration may be appropriate when the system has a large amount of memory.
V$DB_CACHE_ADVICE contains rows that predict the number of physical reads for the cache size corresponding to each row.
The rows also compute a "physical read factor," which is the ratio of the number of estimated reads to the number of reads actually performed by the real buffer cache during the measurement interval.
In the next lesson, we will examine the Oracle data buffer cache.
diminishing marginal returns:
The law of diminishing marginal returns is a law of economics that states an increasing number of new employees causes the marginal product of another employee to be smaller
than the marginal product of the previous employee at some point.