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 "@morebuff" command is deprecated in Oracle Database 12c and is not recommended for use. While it may still function in some cases, it is not officially supported and may not work as expected in future versions of Oracle Database.
There are several reasons why "@morebuff" is deprecated:

• Security risks: The "@morebuff" command can be used to bypass certain security features in Oracle Database, making it a potential security risk.
• Unpredictable behavior: The behavior of "@morebuff" can be unpredictable and may not work as expected in certain situations.
• Lack of support: Oracle no longer officially supports "@morebuff" and it is not included in the Oracle documentation.

Instead of using "@morebuff", Oracle recommends using the following alternatives:

• DBMS_OUTPUT.PUT_LINE: This procedure is a more secure and reliable way to display text output.
• HETEROGENEOUS SERVICES: This feature provides a more robust and flexible way to interact with external data sources.
• UTL_FILE: This package provides a set of procedures for working with files.

If you are using "@morebuff" in your existing Oracle Database 12c applications, it is recommended that you migrate to one of these alternatives as soon as possible. This will ensure that your applications are compatible with future versions of Oracle Database and are not exposed to potential security risks.

The valid values or value ranges of some initialization parameters depend upon the host operating system. For example, the parameter DB_BLOCK_BUFFERS indicates the number of data buffers in main memory, and its maximum value depends on the operating system. The size of those buffers, set by DB_BLOCK_SIZE, has an operating system-dependent default value.
The variable initialization parameters offer the most potential for improving system performance. Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its eleventh cursor receives an error. Other variable parameters affect performance but do not impose absolute limits. For example, reducing the value of DB_BLOCK_BUFFERS does not prevent work even though it may slow down performance. Increasing the values of variable parameters may improve your system's performance, but increasing most parameters also increases the system global area (SGA) size. A larger SGA can improve database performance up to a point. In virtual memory operating systems, an SGA that is too large can degrade performance if it is swapped in and out of memory. Operating system parameters that control virtual memory working areas should be set with the SGA size in mind. The operating system configuration can also limit the maximum size of the SGA.

Before data stored in the database can be queried or modified, it must be read from a disk and stored in the buffer cache. All user processes connected to the database share access to the buffer cache. For optimal performance, the buffer cache should be large enough to avoid frequent disk I/O operations.

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.