12.5. Memory Tuning

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 Chapter 8, "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?

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.

PFILE

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.

SPFILE

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.

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.

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.

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:

alter system set db_cache_advice=ON;

System altered.

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:

select name, value, isdefault, ismodified from v$parameter
where name ='db_cache_advice' or name ='db_cache_size';

NAME                      VALUE      ISDEFAULT ISMODIFIED
------------------------- ---------- --------- ----------
db_cache_size             25165824   FALSE     FALSE
db_cache_advice           ON         TRUE      SYSTEM_MOD

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:

select size_for_estimate, estd_physical_reads
from v$db_cache_advice;

SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS
----------------- -------------------
                4             1158418
                8              213691
               12              100625
               16               44844
               20               37598
               24               35000
               28               34727
               32               34590
               36               34590
               40               34590
               44               34590
               48               34590
               52               34590
               56               34590
               60               34590
               64               34590
               68               34590
               72               34590
               76               34590
               80               34590

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.