| |
 |
|
Donald K. Burleson
Oracle Tips |
Oracle SGA Issues
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 Oracle, 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
useless.
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.
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.
|
