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:
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
- table data,
- index data, and
- clustered data.
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.
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.