Donald K. 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
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 Oracle. 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.
This is an
excerpt by Mike Ault’s book “Oracle
Administration & Management”. If you want more current Oracle tips
by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or
Ault’s Oracle Scripts Download.
Copyright © 1996 - 2014 by
Burleson. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.