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
 
SET LINESIZE 100;
SET PAGES 999;
 
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"
FROM SYS.X$KCBRBH
GROUP BY TRUNC(indx/250);

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

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.

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.