Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

 

 ”call

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.