We help IT Professionals succeed at work.

What should be the Size of SGA

dba1234
dba1234 asked
on
Hi,
I want to set up new instance. I have 16GB RAM on AIX machine. What should be the size of SGA for 10G.
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
We cannot answer this for you.

There are way to many variables.

Look at current and future load on the system and give Oracle all you can without swapping to disk.

Also, I assume 64Bit OS?
One factor that has a big impact on dividing memory between SGA and PGA is whether you are setting up a transactional database or an analytical database (such as a data warehouse). I a transactional DB you would typically want a large SGA because you want a large buffer cache to avoid physical reads. In an analytical database you want to have a very large PGA so that you avoid using temp space for large sorting and hashing operations.

But a bit more detail would be helpful regarding your system...
Top Expert 2011
Commented:
- i suggest that you set the AMM, (Automatic Memory Management) for auto management of your SGA memory area by specifying sga_target more than 0. AMM will auto-manage based on the sga_target specified. read further the following doc on understand and setting up AMM. the sga_target value can be increase should the need arise :

http://docs.oracle.com/cd/B19306_01/server.102/b14220/memory.htm
http://oracleflash.com/8/Automatic-Shared-Memory-Management-in-Oracle-10g.html

Author

Commented:
This is a transactional database on AIX 6.0  64 bit  with 12 cpu and 16GB RAM. Database size would be approx 2 TB. Can you suggest me something to set up SGA_MAX_SIZE and SGA_TARGET.
Top Expert 2011
Commented:
- what i can tell is to specify SGA_TARGET to be less that SGA_MAX_SIZE. this is because you can alter the SGA_TARGET when the need arise within the SGA_MAX_SIZE. for a start, i can only suggest that you allocate few hundred MB for a start however this might be too large or too low, as we don't really know your system and database environment. look into the document i shared earlier to calculate what is the ideal SGA_TARGET for your system.
If nothing else is running on our server you could start e.g. with SGA_MAX_SIZE 6 GB and SGA_TARGET 5 GB. That leaves quite enough for the OS and other Oracle memory areas. Keep an eye on buffer cache hit ratios and temp space usage to see if your SGA or PGA need to be resized.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You will also need to figure out an estimate for PGA_AGGREGATE_TARGET.

>>Can you suggest me something to set up SGA_MAX_SIZE and SGA_TARGET.

The 6 Gig starting point sounds reasonable.  Again, you need to have some idea what is already running on the machine and future capacity and what apps will be used.

For example if you will have 1000 java processes connecting on the same server, they will use memory themselves.  You need to account for this to ensure you NEVER swap memory to disk.
Indeed, you need to set PGA_AGGREGATE TARGET (maybe 1 GB?) and keep an eye (in addition to buffer cache hit ratio and temp space usage) also on OS level memory usage to make sure you don't run out and start swapping.