Monday, July 4, 2011

Checking maximum number of sessions

If you want to know if your Oracle instance can support some amount of concurrent users, then you have to check the sessions and processes parameters:

SQL> set linesize 150
SQL> select count(*) from v$session;

COUNT(*)
----------
87

SQL> column name format a30
SQL> column value format a30
SQL> select name, value from v$parameter where NAME in ('sessions','processes');

NAME VALUE
------------------------------ ------------------------------
processes 600
sessions 665

The sessions parameter is derived by default from the processes parameter, but if not it might be better to check and set both the processes and sessions parameters. If you have configured shared servers for your instance, you can have a lot of sessions with few processes.

But how many processes you can have depends of the operating system's parameters, therefore it would be good to check also that parameters:

myserver> lsattr -El sys0 -a maxuproc
maxuproc 2048 Maximum number of PROCESSES allowed per user True
myserver> id oracle
uid=214(oracle) gid=203(dba) groups=1(staff)
myserver> ps axl|grep " 214"|wc -l
409
myserver> ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 5097151
nofiles(descriptors) unlimited

For this example, we checked the maximum number of processes per user in AIX, how many processes where running at that time, and just to be sure the oracle user limits. If you have more than one instance per user, then you have to take into account all the other instance's maximum sessions parameters to be sure your operating system's limits will not affect your instances.

More information:

SESSIONS
PROCESSES
HPUX: Kernel Tunable parameters
Getting AIX basic system info

No comments:

Post a Comment