RelationalDBDesign RelationalDBDesign

Data Buffer Cache   «Prev  Next»
Lesson 5Examine the impact of adding data buffers
Objective Run the simulation utility.

Examine the Impact of Adding Data Buffers

Oracle provides a utility that predicts the performance improvement of adding more db_block_buffers, and the performance loss from subtracting db_block_buffers.
To estimate statistics, the following init.ora parameters must be set, and the database must be bounced for the parameters to take effect.

db_block_lru_statistics = true
db_block_lru_extended_statistics = #buffers

Note that "#buffers" is the number of buffers to add. Be aware that the (SGA) System Global Area will increase in size by this amount, such that a value of 10,000 would increase an SGA by 80MB (assuming an 8K block size).
You need to ensure that your host has enough memory before trying this. Also, note that performance will be degraded while these statistics are running. It is a good idea to choose a non-critical time for this test.
Oracle uses two system tables called SYS.X$KCBRBH (to track buffer hits) and SYS.X$KCBCBH (to track buffer misses). These are temporary tables and must be interrogated before stopping Oracle. Below is an SQL query that can be formulated against the X$KCBRBH structure to create a chart showing the size of the buffer pool and the expected buffer hits.

REM morebuff.sql - predicts benefit from added blocks to the buffer
COLUMN "Additional Cache Hits" format 999,999,999;
COLUMN "Interval"              format a20;
SELECT 250*TRUNC(indx/250)+1
                ||' to '||250*(TRUNC(indx/250)+1) "Interval",
                SUM(count) "Additional Cache Hits"
GROUP BY TRUNC(indx/250);

The Tooltip below shows you the output from this query.
The number of cache hits peaks at 232 with the additional 1,500 buffer blocks.

Additional Oracle Data Buffers

Adding buffers and full table Scans

The following sample is from a database that primarily performs reports that invoke full-table scans.

@morebuff command outputs 1) Interval 2) Additional Cache Hits
@morebuff command outputs 1) Interval 2) Additional Cache Hits

This is very typical of databases that read large tables front-to-back. Performing a full-table scan on a table that is larger than the buffer will cause the first table blocks to eventually page out as the last table rows are read. Consequently, there is no specific "optimal" setting for the db_block_buffers parameter.

Marginal Gains

As long as marginal gains can be achieved from adding buffers and you have the memory to spare, you should increase the value of db_block_buffers. Increases in buffer blocks increase the amount of required RAM memory for the database, and it is not always possible to use up all of the memory on a processor for the database management system. Therefore, a DBA should carefully review the amount of available memory and determine the optimal amount of buffer blocks.
If you over-allocate SGA memory on a UNIX system, such as with Oracle user's sign-on, the UNIX kernel will begin to swap out chunks of active memory in order to accommodate the new users and cause a huge performance problem.
In the next lesson, you will examine how to predict and maximize memory.