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

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