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
Database Buffer Cache
The database buffer cache holds copies of data blocks read from the data files.
The term data block is used to describe a block containing
The database buffer cache is a block that contains data.
All user processes concurrently connected to the instance share access to the database buffer cache.
The database buffer cache is logically segmented into multiple sets and this reduces contention on multiprocessor systems.
This area of the SGA contains only the buffers themselves and not their control structures.
For each buffer, there is a corresponding buffer header in the variable area of the (SGA) System Global Area..
The ability to compute the optimal size of the data buffers is a critical task for large databases.
As databases grow to billions of bytes, it becomes impractical to cache the entire database in RAM.
Therefore, IT professionals must find the point of diminishing marginal returns for the addition of RAM resources.
Being able to do this can save the company money in terms of physical RAM expenses.
- table data,
- index data, and
- clustered data.
Oracle9i introduces a new view, v$db_cache_advice, that can predict the benefit of additional data buffers in the data buffer cache.
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.