Problem solve Get help with specific problems with your technologies, process and projects.

Calculating optimal size of shared pool and data buffer cache

We have installed Oracle9i on the server with 512 MB RAM. The default size of shared pool created is 128 MB. Now...

we have to increase the capacity of RAM from 512 MB to 1 GB. What's the criteria for calcultaing optimal size of shared pool and data buffer cache on the basis of primary memory? Personally, I have always hated the rule of thumb that Oracle Corp puts in their documents which says something like "the total SGA size should be XX% of your server's physical memory." If I followed that line of reasoning, then when I double my server's physical memory, I have to double my total SGA size. That couldn't be farther from the truth.

Is your database running fine now with its current memory allocations? If so, then when you add more memory, don't do a thing. If not, then investigate why your database is running slow and act appropriately. It may be that you do need to allocate more memory to the SGA components. Or it may be that something else needs to be done and allocating more memory to the SGA is a waste of resources.

To determine if your shared pool needs more memory, run the following query:

SELECT bytes FROM v$sgastat
WHERE pool='shared pool' AND name='free memory';

If the number of bytes returned is low (or zero), then increase the shared pool size. If the number of bytes is around a few megabytes, then don't do anything. If the number of bytes is in the ten's of megabytes, then decrease the shared pool size. The free memory in the Shared Pool is constantly changing so run this query every so often to gauge where a settling point is.

For your buffer cache, make sure that you have the DB_CACHE_ADVICE parameter set to ON and query the V$DB_CACHE_ADVICE view to see if the estimated physical reads will change significantly when you increase or decrease the buffer cache size.

Dig Deeper on Oracle database design and architecture