Sessions and Processes Parameters – Oracle 11g

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

So it always takes which ever is higher

SQL>select name, value from v$spparameter where name in ('sessions','processes');

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               5000

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

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               6776

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

SQL> alter system set sessions = 7000 scope=spfile;

System altered.

<< Bounce DB >>
SQL>select name, value from v$spparameter where name in ('sessions','processes');

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               7000

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

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               7000

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

Hope this helps !!

References:

Sessions parameter in 10.2 – http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams230.htm#REFRN10197

Sessions parameter in 11.1 – http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams191.htm#i1133629

Sessions parameter in 11.2 – http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams230.htm#i1133629

Advertisements

7 thoughts on “Sessions and Processes Parameters – Oracle 11g

  1. This is really informative Sir. When I had a debate with one of the Senior DBAs in my company about the session-process related issues, This article helped me to exemplify the way Oracle derives the settings.
    But I’ve noticed that this change is from 11.1 to 11.2 but not from 10.X to 11.X.
    11.1 –> http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams220.htm
    11.2 –> http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams230.htm#i1133629

    Regards,
    Chandra

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s