Oracle 11g SGA recommendations

PaperTiger used Ask the Experts™
I am currently running an Oracle 11g database on a Windows 2008 Server 64 bit with 20GB memory. What would be your recommendation on memory settings for all the SGA components? This is a database supporting about 100 users, not very transaction heavy. The actual database size in datafile is about 20GB.

I can add another 8G memory to this server.

Automatic Shared Memory Management      Enabled      
Total SGA Size (MB)      5376      
SGA Component      Current Allocation (MB)
Shared Pool       2112
Buffer Cache       2752
Large Pool       64
Java Pool       256
Other       192
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chakravarthi AyyalaDatabase Administrator

Please give sga_max_Size to be 12GB.
There is no unique way to set the SGA which suits all applications.  Hence, based on the hit ratios in the AWR reports, you can adjust sga_target dynamically.


How do I adjust sga_max_size?
Chakravarthi AyyalaDatabase Administrator

sga_max_size can be adjusted only in spfile or init.ora file and it needs a db restart.
whereas, sga_target can be adjusted dynamically when the DB is up and running.
Top Expert 2009
My recommendation is to leave it alone. Your database isn't that big and with automatic memory management enabled, Oracle is going to perform fairly well in most situations. Unless you observe slow performance or have complaints from users, there is no reason to tune the SGA from its current sizing.

Only tune when you have a problem, otherwise if you tweak things that don't need tweaking, you could cause problems for yourself.

Instead, spend time monitoring queries for missing indexes, etc. as you'll usually gain more benefit from that sort of tuning more often.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial