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
Post a Comment
Post a Comment