SGA over allocation

[clinton]

I heard that over allocation of memory to the SGA can slow down the database. Can any one explain this?

[jm]

pls see, there are some discusion
http://www.dbasupport.com/forums/sho...threadid=16215

[sonofsita]

Since the purpose of the System Global Area (SGA) is to store data in memory for fast access, the SGA should always be contained in main memory. If pages of the SGA are swapped to disk, its data is no longer so quickly accessible. On most operating systems, the disadvantage of excessive paging significantly outweighs the advantage of a large SGA.

Although it is best to keep the entire SGA in memory, the contents of the SGA will be split logically between hot and cold parts. The hot parts will always be in memory because they are always being referenced. Some of the cold parts may be paged out, and a performance penalty may result from bringing them back in. A performance problem is very likely, however, if the hot part of the SGA cannot stay in memory.

Remember that data is swapped to disk because it is not being referenced. You can cause Oracle to read the entire SGA into memory when you start your instance by setting the value of the initialization parameter PRE_PAGE_SGA to YES. Operating system page table entries are then prebuilt for each page of the SGA. This setting may increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup. (Note that this setting does not prevent your operating system from paging or swapping the SGA after it is initially read into memory.)

I quoted from Oracle docs

further read :
Oracle8 Tuning
Release 8.0
A58246-01
Library
Product
Contents
Index

14
Tuning Memory Allocation