Oracle RAC 10g - maximum number of session exceeded

Ziad Abuqasem
Ziad Abuqasem used Ask the Experts™
Dear experts,
Good morning ,
I need your help please
I have oracle 10g RAC database installed on HP UNIX with 2 nodes
I have the following parameters values :
processes = 600 on node 1 & node 2
sessions = 665 on node 1 & node 2
and always reach the maximum number of sessions
I need to increase the number of sessions to 1000
what is the steps. And how I can confirm that my HP-UNIX server will handle 1000 session on each node
Thank you
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Here is a quite good "guide" about setting these parameters correctly:

Beware that sessions, transactions, processes and OS paramaters go hand in hand with each other ;-)

Setting them can be done via:
alter system set sessions=<number> scope=spfile;
alter system set processes=<number> scope=spfile;
alter system set transactions=<number> scope=spfile;

Open in new window

You need to bounce the database after you made your changes.
Most Valuable Expert 2012
Distinguished Expert 2018
Sessions and transactions are derived from processes.  I would unset them and let oracle use the derived values unless you have a good business reason to override what Oracle does.

>>And how I can confirm that my HP-UNIX server will handle 1000 session on each node

Look at the memory used by one session and multiply it by what you think the number of concurrent connections will be.

You will have to monitor CPU and disk usage over time to get a good idea about the workloads.
Most Valuable Expert 2011
Top Expert 2012
>> And how I can confirm that my HP-UNIX server will handle 1000 session on each node

After running for a few days with representative load, check the advisor views.

select * from dba_views where view_name like '%ADVICE'
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

johnsoneSenior Oracle DBA

You are going to have to look at the semaphore kernel parameters as well.  I didn't see that in the link that was posted.  Unless something has changed and Oracle does something special (like it does with memory segments), you are going to need enough semaphores to support 1000 sessions.  It is likely the parameters can already handle that, but you need to check.


Thanks alot experts for your comments
this is the semaphore kernel parameters:
semaem                            16384  Default
semmni                             4200  4200
semmns                             8400  8400
semmnu                             4196  (nproc-4)
semmsl                             2048  Default         Immed
semume                              100  Default
semvmx                            32767  Default
shlib_debug_enable                    0  Default         Immed
shmmax                       8589934592  8589934592      Immed
shmmni                              512  512             Immed
shmseg                              300  Default         Immed

can you check it.
I dont have experience in kernel parameters.
Also what is the best way to change the parameters
Change processes parameter only or change processes and sessions and transaction all.
Senior Oracle DBA
In terms of what Oracle tends to care about, the only semaphore parameters that you need to look at are semmsl, semmni and semmns.

The shlib_debug_enable, shmmax, shmmni and shmseg parameters are not semaphore related.

Assuming that you have one database running on the system and no other processes on the system use semaphores, then the settings of those parameters should be fine for a setting of 1000 processes.

Again, I don't know if Oracle has added logic like they have with memory segments that can grab multiple sets if the parameters are set too low.  I haven't tried it.  I try to set the parameters higher than I need them so there is only one set of semaphores and one memory segment per database.

As for how to set them, check with your system administrator.  They should know how to get that done.  I believe that changing semaphore parameters would require a reboot, but I'm not sure.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial