Requires Free Membership to View
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.
This was first published in March 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation