| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to set SGA size initially?
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.
Vikas
-----Original Message-----
From: Gaja Krishna Vaidyanatha [mailto:gajav_at_yahoo.com]
Sent: Monday, June 19, 2000 10:36 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to set SGA size initially?
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)
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.
"Opinions and views expressed are my own and not of Brio Technology"
-- Author: Gaja Krishna Vaidyanatha INET: gajav_at_yahoo.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-LReceived on Tue Jun 20 2000 - 01:03:12 CDT
 |
 |