Hello,

during trying to learn about oracle i came across the following question:

Lets say i have a rather small database, with all datafiles together not larger than 1GB. And lets assume further that this database should run on a server with 8GB of ram. So, the OS will take some part of the 8GB, but there will be a lot of space left.

Is there any reason for not making the SGA very big, like lets say 1GB ? I think i remember to have read about "lots of logical reads" as a reason for not "oversizing" the SGA at some place on the internet, but as i'm a beginner i may have got that wrong...

"Making SGA as big as possible always a good idea ?"

No, it can be a very bad idea. The SGA should be only as large as it needs to be.

Also the physical size of the database has very little relation to how large the SGA should be size. It is the user load and DML activity levels that demand SGA and PGA. I have seen sites force paging and swaping at the OS level because of over-allocation of the SGA where more space was need for PGA. With only a 1G database and 8G of physical memory the odds are you will not have that problem but the principal remains: size the SGA, PGA, etc... as appropriate.

HTH -- Mark D Powell --

Hans,

In general, for versions frmo 8i onwards, the size of the buffer cache shouldn't make any difference to the time it takes to determine the blocks that need to be written as Oracle introduced the "checkpoint queue" in 8i. When a block first becomes dirty it is appended to the queue, so the database writer only has to walk the queue in order to find blocks that need to be written.

One of the threats comes from consistent read copies - if you have an excessively large buffer you may find extreme numbers of copies of the same block building up in the cache (despite the notional limit of 6 set by the _db_block_max_cr_dba parameter). Since all the copies will be covered by the same cache buffers chains latch, processes may start to hold the latch for long periods of time as they search the chain for the correct copy.

Another threat comes from dropping or truncating objects - Oracle needs to search the buffer for CLEAN blocks from the object and, prior to 10g (possibly 10.2), there was no really quick way to perform this search. 10g maintains an "object queue" for each object in the cache, and that is supposed to make this task more efficient.

In the case of the original poster - a 1GB database with 8GB of RAM - it's almost guaranteed that a fully cached database will be beneficial i.e. 1GB of db_cache_size - but keeping an eye open for long waits on the cache buffers chains latch.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk