||Oracle Tips by Burleson
Oracle SGA Issues
As discussed previously, in UNIX, the parameters controlling shared
memory usage are the limiting factor. In any case, before you create
the database, serious thought has to be given to how much you expect
the SGA to grow over the next year. Overspecifying the shared memory
parameters on a UNIX platform will not do any harm and may save you
and the system administrator some headaches.
The size of the SGA is controlled by buffer sizes, and the buffer
sizes are controlled by the database block size, which is specified
at database creation and cannot be changed without rebuilding the
database in Oracle8i but can vary on Oracle9i. This usually defaults
to 8 KB. I usually suggest at least 8 KB, although in some cases 16
KB works best. If in doubt, set the block size to the largest
supported on your system.
The five major components of the SGA are the database buffers, log
buffers, LARGE POOL, Java pool, and the shared pool. The SGA also
contains the redo log buffers. The ideal situation would be to size
the SGA to hold the entire database in memory. For small systems,
this may be a real situation; for most, it is not feasible. However,
with the new 64-bit architectures 16 exabytes of storage can be
directly addressed; only a few exabytes can hold all of the world’s
printed, videotaped, and recorded data, so in the future entire
databases will reside easily in memory. Therefore, you must decide
how much to allocate.
In many cases, especially for development databases, this will be a
rough SWAG (scientific wild-assed guess). For systems already
designed with detailed data storage estimates, it may be better
defined. A general rule of thumb for a pure Oracle system (no other
applications) is 50 to 60 percent of available RAM for your SGA.
Note that for small databases this may be overkill. In general, I
have found that sizing the SGA data block buffers (the product of
DB_BLOCK_SIZE and DB_BLOCK_BUFFERS) to 1/50 to 1/100 of the total
physical size of the database is a good starting point. Under
Oracle8i and Oracle9i, the new default sizes in the supplied sample
initialization file are more realistic, but in general will still be
too small for most production databases, so use them with caution.
Oracle provides tools to analyze buffer performance. Unfortunately,
they can only be used once a system is operating and running under a
normal load; so for our discussion of installation, they are
If you have no idea whatsoever, make the buffer area at least 60 to
100 MB or so (you will usually outgrow the Oracle default rather
quickly) for a database that is near 1 gigabyte in total physical
size, and up to 400 MB for one that is around 20 gigabytes in size.
For databases smaller than 1 gigabyte physical size, the Oracle
defaults may be usable. Make the shared pool at least 50 to 100 MB.
We will discuss the actual parameters in the INIT.ORA file that
control SGA size when we get to the section on tuning. What you need
to know right now is that the default initialization file provided
by Oracle has three default ranges: way-too-small, too-small, and
small. Unless you are creating a database that will be less than 1
gigabyte in physical size, even the large parameters in the example
initialization file are woefully inadequate.
One thing to remember: If you overspecify the shared memory size on
UNIX, you may get into a situation known as swapping. This is where
all or part of your application is swapped out to disk because
physical memory just isn’t large enough to hold it all. Needless to
say, this has a very negative impact on performance. Usually,
overspecification of the SGA on UNIX will lead to not being able to
start the database.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.