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
Thanks for sharing this. Reading it reminded me of Charles Hooper blog — http://hoopercharles.wordpress.com/2012/02/08/sessions-derived-value-formula-doc-bug-explains/
Thanks Anand for stopping by !!
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
Thanks Chandra !!
Yes indeed. The change was done after 11.1
This is a very good tip especially to those new to the blogosphere. Brief but very accurate information… Thanks for sharing this one. A must read post
Reblogged this on RememberLINK.
Thank you for this clear explanation, I appreciate.