Sessions and Processes Parameters

Oracle has changed the way it derives sessions and processes parameters in database.

In 10g, oracle used to derive sessions parameter from processes parameter using following formula

(1.1 * PROCESSES) + 5

In 11g R1 onwards it changed to

(1.5 * PROCESSES) + 22

It has another rule though:

If we set lower value of sessions parameters than derived value, Oracle will automatically bump it to above derived value.
If we set higher value of sessions parameters than derived value, Oracle will consider our set value

We can see derived value (4500*1.5)+22=6776 is greater than set value of 5000 in spfile. So its taking derived value for this parameter

Lets change the value in spfile to 7000

Now you can see its taking the set value, because set value of more than derived value of 6776.





Additional information for reference in MAX xxx limit reach error:-

TO check the current processes,session and transactions utilization- 

SQL>select resource_name,current_utilization,max_utilization from v$resource_limit where resource_name in ('processes','sessions');


It helps - 

"1000 concurrent users" and "1000 simultaneously active users"

A connection pool of size 'n' lets 'n' people all be performing a database operation *right at that moment*.

From the user's perspective, they all have a connection to the database. But users are typically 99% idle, 1% busy, ie, they click a button, the database does some work, the results come back, and they are idle (reading the screen, etc etc).

Try this

select * from V$METRIC_HISTORY where metric_name like 'Average Active Sessions'; 

and you'll see on your database a figure that represents over time the number of concurrently active connections over time.