Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to set SGA size initially?

RE: How to set SGA size initially?

From: Gurgaon, DBA (CAP, GECSI, CONTRACTOR) <>
Date: Tue, 20 Jun 2000 11:33:12 +0530
Message-Id: <>

Dear Gaja
What you are saying is right ,your sga can be more than 50% of your ram, provided that you are running no other things on your server. Actually the things I'd written in my previous mail are recommended by oracle.
More over I've not seen a single database where shared pool is smaller than data buffer cache.


-----Original Message-----
From: Gaja Krishna Vaidyanatha [] Sent: Monday, June 19, 2000 10:36 PM
To: Multiple recipients of list ORACLE-L Subject: RE: How to set SGA size initially?


I am not very sure whether a recommendation to "size the database buffer cache" at "1/3rd of the shared_pool_size" is very optimal. I am curious to know how you arrived at that number. In most environments, the size of the shared pool tends to be much smaller than the size of the database buffer cache. Sizing the database buffer cache to a fraction of the shared pool may not be optimal at all.

Further, the default size of the redo log buffer is
(4*db_block_size), and that is definitely too small. For most
environments the size of the redo log buffer varies from about 256K - 1M, where the average size is at 512K and 1M being the exception and not the rule.

The "initial sizing" of the SGA can utilize upto 50% of available RAM. Out of the 50% chunk of system memory, the various components of the Oracle SGA can be further allocated in the following fashion. These are good starting points and the individual components may require further tweaking, based on application nature, data access patterns, mix of batch jobs vs. OLTP transactions etc. The final percentages/number will be determined on system and Oracle monitoring. So here it goes:

Oracle SGA Component Allocated % of memory (out of the 50%)

--------------------    --------------------------------------
Database Buffer Cache	~80%
Shared Pool Area	~19%
Fixed Size + Misc.	~1%
Redo Log Buffer		~0.1%

"~" indicates approximate value.

So for example, if you are trying to perform memory allocation for Oracle on a system with say 2 GB of memory, it can be done in the following fashion:

System Component	Allocated memory(In Mb.)
----------------        ------------------------
Oracle SGA Components	~1024
Operating System +
Related Components	~306
User Memory		~694  (PGAs come from this pool)

(Sort Area and Session Cached Cursor configuration should be
done keeping this in mind)

The 1 Gb. of memory for the Oracle SGA can then be allocated in the following fashion:

Oracle SGA Component Allocated memory(In Mb.)

--------------------    ------------------------
Database Buffer Cache	~800
Shared Pool Area	~128 - 191
Fixed Size + Misc.	~8
Redo Log Buffer		~1

I have made the assumption of 1 instance in my example. If you have multiple instances, then the percentages can still relevant for the "sum total of all Oracle instances running on a given machine". I have utilized this with great success at scores of customer sites for initial configuration of the Oracle SGA and the optimal configuration for the PGAs of user sessions.

Best Regards,


Gaja Krishna Vaidyanatha | Brio Technology | (972)-304-1170

"Opinions and views expressed are my own and not of Brio Technology"

Do You Yahoo!?
Send instant messages with Yahoo! Messenger.
Author: Gaja Krishna Vaidyanatha

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
Received on Tue Jun 20 2000 - 01:03:12 CDT

Original text of this message