Tuning Database Buffer Cache

Buffer pool or buffer cache is a memory structure within Oracle SGA that is used for caching data blocks in the memory. Accessing data from the memory is significantly faster than accessing data from disk. The goal of block buffer tuning is to efficiently cache frequently used data blocks in the buffer cache (SGA) and provide faster access to data.

Tuning block buffer is a key task in any Oracle tuning initiative and is a part of an ongoing tuning and monitoring of production databases. The task of database performance optimization by allocating appropriate memory structure within SGA is a very interesting and challenging task for a DBA. Generally, it takes a few iterations before you can reach to a satisfactory memory allocation. This article presents a tuning approach that greatly simplifies the tuning effort. The methodology discussed here will help DBAs in creating a proactive database tuning and monitoring strategy for their production databases. If you are a DBA responsible for tuning a database without enough physical memory on the server, you will find this article very informative and useful. With the help of an established methodology and tuning strategy, you can easily identify the problem areas and tune the SGA memory structure.

  • Identify the amount of memory that you can allocate to block buffers. Create buffer pool without inducing significant paging or swapping.
  •  Identify how frequently data blocks are accessed from the buffer cache (a. k. a Block Buffer Hit Ratio).
  • Identify frequently used and rarely used data blocks. Cache frequently used blocks and discard rarely used blocks.
  • Assign tables to KEEP / RECYCLE pool. Identify buffer hit ratio for KEEP, RECYCLE, and DEFAULT pool. Adjust the initialization parameters for optimum performance.
  • Identify the amount of memory needed to maintain required performance.

Using this methodical approach, a DBA can easily identify the problem areas, and tune the database block buffers. The DBA can create the following buffer pool to efficiently cache data blocks in SGA:


Cache tables that are very critical for system performance. Typically, lookup tables are very good candidates for the KEEP pool. The DBA should create the KEEP pool large enough to maintain 99% buffer hit ratio on this pool.


Cache tables that are not critical for system performance. Typically, a table containing historical information that is either rarely queried or used by batch process is a good candidate for the RECYCLE pool. The DBA should create the RECYCLE pool large enough to finish the current transaction.


Cache tables that do not belong to either KEEP or RECYCLE pool.

The DBA can setup OEM jobs, Oracle statspack, or custom monitoring scripts to monitor your production database block buffer efficiency, and to identify and tune the problem area.

Draft Article

Source / Reference Links:


Don’t Let Referential Integrity Degrade Your Database Performance

Heap Size Exceeds Notification Threshold

Memory Notification: Library Cache Object loaded into SGA
Heap size ** K exceeds notification threshold (8192K)

These are warning messages only which should not cause any problems. These warning messages appear due to new event messaging mechanism and memory manager in 10g Release 2.

In 10g, there is a new undocumented parameter that sets the KGL heap size warning threshold. This parameter was not present in 10gR1. Warnings are written if heap size exceeds this threshold.

Set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

To find out the hidden ‘_kgl_large_heap_warning_threshold’ parameter value in the database:

SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%kgl_large_heap%';
Parameter Session Value Instance Value
--------------------------------- ------- -------
_kgl_large_heap_warning_threshold 2097152 2097152

If you want to set this to 8192 (8192 * 1024) and are using an spfile:

(logged in as “/ as sysdba”)

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
SQL> shutdown immediate
SQL> startup
If using an "old-style" init parameter,edit the init parameter file and add

The default threshold in is 2M. So these messages could show up frequently in some application environments. In, the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value.

Check your product version:

select   BANNER
from     sys.v_$version;

Source / Reference: