Memory Tuning - Oracle DBA

Some of the memory structures used by Oracle include the database buffer cache, the shared pool, and the redo log buffer cache, as shown below. (These memory structures were discussed in "Installing Oracle and Creating a Database.") While increasing the memory allocated for any of these structures will usually help, how much is enough? How much is too much?

Memory Tuning

You can adjust the amount of memory allocated to each of these areas by changing the value of a parameter in the parameter file used by Oracle, called a PFILE. A PFILE is a text file containing the parameters and their values for configuring the database and instance.


A text file containing the parameters and their values for configuring the database and instance at startup.

Oracle9i and Oracle 10g support a more flexible version of a PFILE called an SPFILE. An SPFILE is stored in a binary format. A change to a parameter in an SPFILE can be for the current running instance only, can take effect only after the next restart of the instance, or both.


A parameter file stored in a binary format that gives the DBA more flexibility when changing parameters. Parameters can be changed for the current instance only, can take effect only after the next restart of the instance, or both.

The sizing of the database buffer cache is usually the most problematic, since blocks from all tables read from and written to reside in this cache. A buffer cache that is too small will hurt performance by obtaining blocks from disk instead of from the buffer cache. A buffer cache that is too big will waste memory that can otherwise be used for other memory areas.

Buffer Cache Advisory A feature of the Oracle9i and Oracle 10g database that can assist the DBA in determining how large to make the buffer cache. This feature collects statistics on how often a requested database block is found in the buffer cache. The system initialization parameter DB_CACHE_ADVICE controls whether these statistics are collected, and the data dictionary view V$DB_CACHE_ ADVICE contains the estimated number of physical reads that would occur given a number of different cache sizes.

Both Oracle9i and Oracle 10g have a feature called the buffer cache advisory, which can help the DBA decide how big to make the buffer cache. The first step in monitoring the size of the buffer cache is to turn on the buffer cache advisory feature by setting the DB_CACHE_ADVICE parameter. You can do this either by editing the PFILE and restarting the database or by using an SPFILE and changing the value using the ALTER SYSTEM command.

Janice, the DBA at Scott's widget company, is determined to put off asking for a memory upgrade on the server until she makes the best use of what's already there. First, she will find out if the buffer cache needs to be larger. She changes the value of DB_CACHE_ADVICE, as follows:

To verify that the parameter is set correctly, she checks the value of that parameter in the V$PARAMETER dynamic performance view, along with the current value for the buffer cache size:

Memory Tuning

2 rows selected.

The value is set correctly, but Janice notices that ON is the default value for this parameter. After this tuning exercise is completed, Janice will remember to change this value back to OFF to eliminate any overhead generated by the monitoring process. It also looks like the value for DB_CACHE_SIZE is currently about 25MB.

After the system has been running for a day or two with the DB_CACHE_ADVICE parameter turned on, Janice reviews the dynamic performance view V$DB_CACHE_ADVICE:

Memory Tuning

20 rows selected.

The first column, SIZE_FOR_ESTIMATE, is the proposed size for the buffer pool in megabytes. The second column, ESTD_PHYSICAL_READS, is the number of reads from disk that would occur with the corresponding buffer cache size, given the recent activity level. From this report, Janice sees that her buffer cache of 25MB is sized optimally. Increasing the buffer cache size to 28MB, for example, would reduce the physical I/O only slightly, and it probably would not justify a memory upgrade at this time. At 32MB and higher, the additional memory allocated to the buffer cache would not reduce the reads from disk at all. It appears that Janice will not need a memory upgrade on the server for the foreseeable future.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle DBA Topics