How to increase PROCESSES,SESSIONS and TRANSACTIONS initialization parameter in Oracle Database

Sessions : Sessions specify the number of connections that can served by oracle database at a time. This is equivalent to the number of concurrent users of the application. If your application has a lot of concurrent users then you need to increase the sessions in the oracle database.

Processes : Processes run in the background of Oracle database to maximize performance and accommodate many users.

Transaction : A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database

Steps:

1.    Login as sysdba
    sqlplus / as sysdba
   
2. Check Current Setting of Parameters
    sql> show parameter sessions
    sql> show parameter processes
    sql> show parameter transactions

3.    If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
    A basic formula for determining  these parameter values is as follows:
   
        processes= x (OS Dependent)
        sessions= (1.5 * X) + 22
        transactions= sessions*1.1


Example 
X=300
processes = 300
sessions = 472
transactions=519


4.    These parameters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.

    sql> alter system set processes=300 scope=spfile;
    sql> alter system set sessions=472 scope=spfile;
    sql> alter system set transactions=519 scope=spfile;
    sql> shutdown immediate
    sql> startup






If you like please follow and comment