I am using Red Hat Enterprise Linux Server release 5.11 (Tikanga) as OS and rdbms is 11gR2 software. I have created a database with memory_target=3 GB and physical RAM=7 GB. My DB service is getting slow when I do huge operations. I need to change the SHMALL and SHMAX parameters to improve the performance. Can you please suggest me how much values I should give?
For 64-bit servers the optimal value you can use, is Half the RAM
While for 32-bit servers it is 3 gigabytes
You should make SHMALL smaller than free RAM to avoid paging.
From what we have on RedHat Documentations on Setting SHMAX:
This parameter defines the maximum size in bytes of a single shared memory segment that a Linux process can allocate in its virtual address space. For example, if you use the Red Hat Enterprise Linux 3 smp kernel on a 32 bit platform (x86), then the virtual address space for a user process is 3 GB. If you use the Red Hat Enterprise Linux 3 hugemem kernel on a 32 bit platform (x86), then the virtual address space for a user process is almost 4GB. Hence, setting SHMMAX to 4GB - 1 byte (4294967295 bytes) on a smp kernel on a 32 bit architecture will not increase the maximum size of a shared memory segment to 4 GB -1. Even setting SHMMAX to 4 GB - 1 byte using the hugemem kernel on a 32 bit architecture will not enable a process to get such a large shared memory segment. In fact, the upper limit for a shared memory segment for an Oracle 10g R1 SGA using the hugemem kernel is roughly 3.42 GB (~3.67 billion bytes) since virtual address space is also needed for other things like shared libraries. This means if you have three 2 GB shared memory segments on a 32 bit system, no process can attach to more than one shared memory segment at a time. Also note if you set SHMMAX to 4294967296 bytes (4*1024*1024*1024=4GB) on a 32 bit system, then SHMMAX will essentially bet set to 0 bytes since it wraps around the 4GB value. This means that SHMMAX should not exceed 4294967295 on a 32 bit system. On x86-64 platforms, SHMMAX can be much larger than 4GB since the virtual address space is not limited by 32 bits. Since the SGA is comprised of shared memory, SHMMAX can potentially limit the size of the SGA. SHMMAX should be slightly larger than the SGA size. If SHMMAX is too small, you can get error messages similar to this one:
ORA-27123: unable to attach to shared memory segment
From what we have on RedHat Documentation on Setting SHMALL:
This parameter sets the total amount of shared memory pages that can be used system wide. Hence, SHMALL should always be at least ceil(shmmax/PAGE_SIZE).
The default size for SHMALL in Red Hat Enterprise Linux 2.1, 3, 4 and 5 is 2097152 which is also Oracle's recommended minimum setting for 9i and 10g on x86 and x86-64 platforms. In most cases this setting should be sufficient since it means that the total amount of shared memory available on the system is 2097152*4096 bytes (shmall*PAGE_SIZE) which is 8 GB. PAGE_SIZE is usually 4096 bytes unless you use Chapter 14, Large Memory Optimization, Big Pages, and Huge Pages which supports the configuration of larger memory pages.
If you are not sure what the default PAGE_SIZE is on your Linux system, you can run the following command:
$ getconf PAGE_SIZE