I heard that over allocation of memory to the SGA can slow down the database. Can any one explain this?
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 :
Tuning Memory Allocation
Click Here to Expand Forum to Full Width