Showing results for 
Search instead for 
Did you mean: 

Max SGA limit?

Go to solution
Regular Advisor

Max SGA limit?

This is Oracle on HP Tru64 5.1B. The physical memory is 10GB. How to know the max SGA limit? Is there a solution to avoid the limit?


Eric Antunes
Honored Contributor

Re: Max SGA limit?

Hi Eric,

In your situation, the SGA limit is the RAM you have on your system (10Gb) but you should consider lower values.

Best Regards,

Each and every day is a good day to learn.
Joseph Loo
Honored Contributor

Re: Max SGA limit?

hi eric,

for 9.0.1, the maximum SGA recommended by Oracle is to set the kernel settings:

SHM_MAX 4278190080 (4 GB less 16MB)

to check current SGA:

SQL> show sga

what you do not see does not mean you should not believe
Indira Aramandla
Honored Contributor

Re: Max SGA limit?

Hi Eric,

Until oracle 8i the System Global Area (SGA) has been static. Since Oracle9i it allows the DBA to modify the SGA size dynamically. This provides an SGA that will grow and shrink in response to a DBA command. SGA_MAX_SIZE specifies the maximum size of SGA for the lifetime of the instance.

This feature implements an infrastructure to allow the Oracle server to change the SGA configuration without shutting down the instance. The Oracle Server can modify the physical address space use to respond to the operating system's use of physical memory.

Begining with Oracle9i, the dynamic SGA infrastructure will allow for the sizing of the Buffer Cache, Shared Pool and the Large Pool without having to shutdown the database. The dynamic SGA infrastructure also allows the limits to be set at run time on how much physical memory will be used for the SGA. The instance will be started under-configured and will use as much memory as the operating system gives it.

For more details refer to Note:148495.1 in Metalink. And also Note:69119.1 describes the components of SGA.

Indira A
Never give up, Keep Trying