3

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?

  • What are your current values, RedHat sets that quite high by default. Do your server have 7GB of memory or is that the limit placed on Oracle? What is the page size on that machine (4k, 8k)? – grochmal Jul 26 '16 at 12:50
  • hi,yes server is having 7 GB RAM.and page size is kernel.shmmni = 4096 – Mannaji Jul 26 '16 at 12:58
  • # Controls the maximum number of shared memory segments, in pages kernel.shmall = 4294967296 fs.suid_dumpable = 1 fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmmni = 4096 # semaphores: semmsl, semmns, semopm, semmni kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default=262144 net.core.rmem_max=4194304 net.core.wmem_default=262144 net.core.wmem_max=1048586 – Mannaji Jul 26 '16 at 12:59
  • $free -m total used free shared buffers cached Mem: 6987 6942 45 0 11 3620 -/+ buffers/cache: 3310 3677 Swap: 15359 2922 12436 – Mannaji Jul 26 '16 at 13:02
  • If you have kernel.shmall = 4294967296 (and SHMMAX is not below it) this is already way above 7GBs (4294967296*4096 > 7*1024^3). You will need to optimise your queries, kernel tweaking will not give you more shared memory. – grochmal Jul 26 '16 at 13:21
1

SHMAX

For 64-bit servers the optimal value you can use, is Half the RAM

While for 32-bit servers it is 3 gigabytes

SHMALL

You should make SHMALL smaller than free RAM to avoid paging.

SHMAX

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

SHMALL

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

  • we are using linux 5.11, if default size of shmall is 8 GB recommend by oracle then my server is having only 7 GB physical memory.How you suggest for this, do i need to reduce the size of shmall – Mannaji Jul 28 '16 at 12:01
  • i have given 3 GB MEMORY_MAX_SIZE to Database and i can put shmmax= 3.4 GB is it enough? how you suggest for this – Mannaji Jul 28 '16 at 12:03
  • I recommend you improve your physical memory first, and then change the values. BTW, it depends on what your DB is about, is it only yours and for test, or is it holding important data? The choice is yours, consider what your work is, and what you need. But I think you need to improve your physical memory, at least, that's what I would do if I were in your shoes. – FarazX Jul 29 '16 at 9:54
  • but we have blade server on that we built the several virtual machines – Mannaji Aug 3 '16 at 9:16
  • and shmmax>= SGA as mentioned in docs but my question is shmmax>=SGA or shmmax>= memory_target or memory_max_size – Mannaji Aug 3 '16 at 9:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.