LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon