Oracle: sizing the processes and sessions parameters
Check Oracle process and session usage and compute the processes, sessions and transactions parameters.
Oracle limits the number of server processes and sessions through the processes, sessions and transactions parameters. This note shows how to check their usage and how to size them.
Check current usage
select resource_name,current_utilization,max_utilization
from v$resource_limit
where resource_name in ('processes','sessions');
Average active sessions:
select *
from V$METRIC_HISTORY
where metric_name like 'Average Active Sessions';
Other useful queries
show parameter processes
select count(*) from v$process;
select program, process,count(*) from v$session group by program,process;
select name, value from v$spparameter where name in ('sessions','processes','transactions');
select name, value from v$parameter where name in ('sessions','processes','transactions');
Sizing formulas
| Parameter | Formula |
|---|---|
processes |
x (chosen value) |
sessions |
(1.5 * processes) + 22 |
transactions |
sessions * 1.1 |
parallel_max_servers |
number of CPU cores of the DB machine * 10 |
Example with processes = 300: sessions = (1.5 * 300) + 22 = 472.
Change the parameters
The change is written to the spfile and requires a restart:
alter system set processes=1000 scope=spfile;
alter system set sessions=1248 scope=spfile;
shutdown immediate
startup
Notes
- Reference: Oracle parameter processes and sessions (SAP blogs).