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?

click to expand

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 supports 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.

Oracle9i has 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 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 only reduce the physical I/O 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.




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net