How to increase Processes, Sessions and Transactions Initialization Parameters

3 Comments

4.8
(11)

Demonstration on how to increase processes, sessions and transactions parameter in Oracle.


Whenever our processes utilized 100% then its NOT allowed any new sessions / connections to DB’s instances and we get below error :

ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
the ORA-20 errors.


Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.

Before start on solution of it, we understand what is processes, sessions & transactions:


PROCESSES: It specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from PROCESSES parameter. Therefore, if we change the value of PROCESSES, We should evaluate whether to adjust the values of those derived parameters.

SESSIONS: It specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. We should always set this parameter explicitly to a value equivalent to our 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.

TRANSACTIONS: It specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.


Lets move on solution and below is step by step demonstration:


Solution –


Connect database as sysdba –


sqlplus / as sysdba


Check current setting of parameters –


sql> show parameter processes
sql> show parameter sessions  
sql> show parameter transactions

We can also use below SQL code to find values of required parameters:

set lines 222
col RESOURCE_NAME for a15
col LIMIT_VALUE for a15


select resource_name,current_utilization, limit_value
from v$resource_limit
where resource_name in ('sessions','processes','transactions') order by resource_name;


OR

set lines 222
col NAME for a20
col VALUE for a20

select name, value FROM v$parameter
where name in (‘sessions’,’processes’,’transactions’) order by name;


  • If we are planning to increase “PROCESSES” parameter so, we  should also plan to increase the “SESSIONS” and “TRANSACTIONS” parameters. A basic formula for determining  these parameter values is as follows:
       
            processes=x
            sessions=x*1.1+5
            transactions=sessions*1.1


  • Before altering parameters we need to take a backup of pfile.

SQL> create pfile ='/u02/DBSGURU/initdbsguru.ora' from spfile;
SQL> alter system set processes=500 scope=spfile;
SQL> alter system set sessions=555 scope=spfile;
SQL> alter system set transactions=610 scope=spfile;


After resetting the parameters we need to bounce the database-


SQL>shutdown immediate;

SQL>startup;


After that will check the settings of parameters-


SQL> show parameter processes;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

aq_tm_processes                      integer    1

db_writer_processes                  integer     1

gcs_server_processes                 integer     0

global_txn_processes                 integer     1

job_queue_processes                  integer     4000

log_archive_max_processes            integer     4

processes                            integer     500


SQL> show parameter session;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

java_max_sessionspace_size           integer     0

java_soft_sessionspace_limit         integer     0

license_max_sessions                 integer     0

license_sessions_warning             integer     0

session_cached_cursors               integer     50

session_max_open_files               integer     10

sessions                             integer     784

shared_server_sessions               integer


Also we can use below SQL command to validate allocated and utilized value of changed parameters:

set lines 222
col RESOURCE_NAME for a15
col LIMIT_VALUE for a15

select resource_name,current_utilization, limit_value 
from v$resource_limit 
where resource_name in ('sessions','processes','transactions') order by resource_name;


NOTE – Here it increased the value of session automatically 784 but we wanted to set it 555 so, whenever we alter the processes it automatically calculate and increase the sessions . So again we need to set the transaction as per the current sessions .

SQL> show parameter transactions;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

transactions                         integer     610

transactions_per_rollback_segment    integer     5


We need to follow the below formula and set the transaction value-


transactions=sessions*1.1

SQL> alter system set transactions=862 scope=spfile;

Now again, need to bounce the database –


SQL>shutdown immediate;

SQL>startup;

Finally, we can execute this SQL to get values:

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

 1,488 total views,  1 views today

How useful was this post?

Click on a star to rate it!

Average rating 4.8 / 5. Vote count: 11

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the love

3 thoughts on “<a rel="bookmark" href="https://dbsguru.com/increase-processes-sessions-transactions-parameter/">How to increase Processes, Sessions and Transactions Initialization Parameters</a>”

  1. Thank you so much. Really a very nice and wonderful article related to Oracle core concepts. It’s very easy to understand and remember.

    Once again thank you so much team for your efforts it is really very much helpful for freshers like me.

    God bless you.

Leave a Reply

Your email address will not be published. Required fields are marked *

10 + 11 =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru