Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle SGA Issues

Oracle Database Tips by Donald Burleson

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. Over specifying 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 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.

One thing to remember: If you over specify 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, over specification of the SGA on UNIX will lead to not being able to start the database.

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.