RE: How to set SGA size initially?

Gaja / Vikas !

I agree with Gaja except for the log buffer size. Bigger buffer(s) size is/are always NOT better (atleast in) LOG_BUFFERS.

The ideal size for log buffer will be 3-4 times of your average redo size (redo bytes/ redo writes).

And increasing log buffers beyond 512 k will not yield any significant benefit and will be wasted (lgwr will start writing to log files once the buffer is 33% full.. right?) and some times it MAY increase the commit confirmation delay.

YMMV K Gopalakrishnan
Bangalore, INDIA
http://www.geocities.com/kgkrish

At 09:06 AM 6/19/00 -0800, Gaja Krishna Vaidyanatha wrote:
>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"
>
>__________________________________________________
>Do You Yahoo!?
>Send instant messages with Yahoo! Messenger.
>http://im.yahoo.com/
>--
>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-L
Received on Thu Jun 22 2000 - 14:43:51 CDT