How to increase Processes, Sessions and Transactions Initialization Parameters in Oracle

September 8, 2020

DATABASE

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

Whenever DB processes are utilized 100% then it’s NOT allowed any new sessions/connections to DB’s instances and we get the 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 starting on the solution, 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 the 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.

Let’s move on to the solution and below is the step by step demonstration:

Solution:-

Connect database as sysdba-

sqlplus / as sysdba

Check the 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 the 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;

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 the 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 calculates and increases 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 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.

DATABASE, ORA-ERRORS, ORACLE

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

Related Articles