I am working in Oracle 9i Rel.2.
How do the kernel parameters like SHMMAX and SEMMNS affect the performance of the Database ?
How do I set these parameters and on what ratios.
My SGA is 538406552 then what must be the value set to SHMMAX and SEMMNS.
If I set the values based on SGA, can I expect the performance to be improved, if so, can u pl. explain the reasons.
SHMMAX is a system-wide parameter that affects the way the kernel allocates new memory regions for segments within the OS. Typically, when you request a new memory region using a shmget() call, SHMMAX is the maximum number of bytes you can use. Same goes with SEMMNS, but this is just for semaphores. It could affect the DB performance in a way that it will possible degrade it if you set it too low (Oracle processes not being able to allocate larger memory regions).
You set them depending on what Oracle says you should set them under your OS. In any case, SHMMAX should not be < SGA.
Thanks for the response. But what I wanted to understand is that the performance degrade, does it mean that the huge query when executed takes longer time to execute and if the parameters are set as Oracle says, will my performance problem be solved.
In what way these parameters are related to the DB performance?
You only have one option: increase it. If you lower it, I dont believe Oracle will let you ever start the DB. Your SGA upon startup will be equal to what you set SHMMAX. If you know and understand what SGA stands for, then you will understand how it affects Oracle performance. I suggest you read about SGA if you dont.
Your SGA upon startup will be equal to what you set SHMMAX.
Well, not exactly JMartinez : SHMMAX is the maximum SGA size. That is : if you set a SGA size greater than SHMMAX, then your instance will not start at all. SHMMAX is the maximum size of one shared memory. It only impacts performance through the fact that it restricts SGA size, but in fact this size is restricted by your total RAM, so the real limitating ressource is RAM . Oracle recommends to set SHMMAX to half your total RAM amount.
As for semaphores, I don't know the real impact, but I think allowing too few semaphores will limit your number of open sessions.
BTW, you can monitor shared memories and semaphores with the ipcs command. On my machine this is the return of ipcs (-a stands for "display all") :