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: Gaja Krishna Vaidyanatha <>
Date: Mon, 19 Jun 2000 08:56:37 -0700 (PDT)
Message-Id: <>


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" Received on Mon Jun 19 2000 - 10:56:37 CDT

Original text of this message