Max SGA limit?

[watermelonyu]

This is Oracle 9.0.1.4 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?

[watermelonyu]

Hi Eric,



You can refer Metalink Note 50364.1 "SGA Sizing & OS Specifics Using tstshm" the tstshm program can be used to make sure that a user process can grab enough shared memory required to accomodate the SGA. It is also to ensure that the user process address space is sufficient to map it.

Without the '-t' argument to set the total size of SGA you require, tstshm will assume certain sizing information and use a default algorithm to present mapping statistics.

Therefore, if you are wishing to ensure that a certain size SGA is compatible with the OS (operating system) parameters and limits settings,then you need to use the

'-t' flag to specify the size of the SGA. For example:



For SGA size = 64MB = 67108864 (8192 8k pages)

N.B. Default page-size on Alpha is 8k.



On Alpha unix consider a value for shm-max = 16MB = 16777216



% setenv SHMMAX 16777216

% tstshm -t67108864



totsize = 67108864

Succeeded in attaching 4 segments with total shm of 67108864

This tells you that it needed to allocate 4 segments each of 16MB in order to meet the required 64MB SGA.

If tstshm fails, it will fail with an OS error.For example:

Consider SGA size = 1GB = 1073741824 OS settings as above.

% tstshm -t1073741824



totsize = 1073741824

Shmat error: Too many open files

shmctl: Not owner

In this case, the failure occurs because the OS parameter 'shm-seg' is set at 32 and we are trying to map more than 32 segments.



This can be seen by using the debug flag:

% tstshm -d -t1073741824



The important value can be seen by:

% tstshm -d -t1073741824 |& grep shmat |wc -l



33



It failed on the 33rd call to shmat().



Hint: '|&' pipes both stderr and stdout in 'csh'.



*Note: This program is provided as a guide for SGA sizing, it should not

be relied upon for definitive statistics on what shared memory

Oracle RDBMS allocates. Such details are undocumented.



The SHM_MAX can be set up to 4278190080 (4 GB less 16MB) as stated in Note 169706.