RE: How to set SGA size initially?

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Mon, 19 Jun 2000 08:56:37 -0700 (PDT)
Message-Id: <10533.109719@fatcity.com>

Vikas,

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.

• "Gurgaon, DBA (CAP, GECSI, CONTRACTOR)" <DBA.Gurgaon_at_geind.ge.com> wrote:
  > Your SGA size can be upto 1/3rd of your physical RAM.
  > keep (db_block_buffers*db_block_size) around 1/3rd of your
  > shared_pool_size.
  > Initially keep sort_area_size=1000000
  > log_buffers=4*db_block_size
  >
  > Vikas
  >
  > -----Original Message-----
  > Sent: Monday, June 19, 2000 2:56 PM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  > Hi,
  >
  > How do I select the size of SGA (DB Buffer & Shared pool
  > size)
  > during
  > the database design? Are there any guidelines?
  >
  > Thanx & Regards,
  > Ramamohan B N
  > --
  > Author: Ramamohan B N
  > INET: ramamohan.bn_at_tatainfotech.com
  >
  > 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
  > and in
  > the message BODY, include a line containing: UNSUB ORACLE-L
  > (or the name of mailing list you want to be removed from).
  > You may
  > also send the HELP command for other information (like
  > subscribing).
  > --
  > Author: Gurgaon, DBA (CAP, GECSI, CONTRACTOR)
  > INET: DBA.Gurgaon_at_geind.GE.com
  >
  > 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
  > and in
  > the message BODY, include a line containing: UNSUB ORACLE-L
  > (or the name of mailing list you want to be removed from).
  > You may
  > also send the HELP command for other information (like
  subscribing).

  ---

Gaja Krishna Vaidyanatha | gajav_at_yahoo.com 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