Oracle SESSIONS Parameter derived and explained.

Default parameter values are great, as long as it is possible to predict the default values, and the defaults are appropriate for the environment in which the defaults are present.  It is sometimes a challenge to remember all of the rules, and exceptions to those rules, that determine the defaults. There is an interesting formula which Oracle uses to determine the sessions parameter value which is derived from processes.

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
sessions                             integer     1522

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
processes                            integer     1000

The values shown above looks different from what I had set in the spfile i.e 1500. The reason for this is explained by Oracle Document on SESSIONS Parameter

                                        Default : Derived (1.1*PROCESSES)+5

“… You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.”

Wait, if my PROCESSES are set to 1000 then my sessions value should be 1.1*1000+5=1105, but its showing me 1522. Turns out the formula changes for 11.2.0  and the above formula works for 10.2.0 databases.

The new formula which comes closest to the session values I have seen is

                                         Sessions= (1.5 * PROCESSES) + 22

So any value set for Sessions lower than the formulated value is ignored by Oracle and the default value will be set based on the above formula.