What are Sessions in Oracle database : 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.
What are processes in Oracle database : Processes run in the background of Oracle database to maximize performance and accommodate many users.
What are transactions in Oracle database : 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
What is the relation between Sessions, transactions & processes : By default, the following formula is followed in oracle database.
Processes (x) , Sessions(s), Transactions(t)
s = x * 1.1 + 5
t = s * 1.1
For example, if processes are 100, then sessions will be 115 and transactions will be 127.
To alter the sessions,transactions, processes follow this procedure -
1. At command line, type sqlplus
2. For username give / as sysdba
3. Give the following commands –
alter system set sessions=400 scope=spfile;
alter system set processes=350 scope=spfile;
alter system set transactions=440 scope=spfile;
Give the values according to the requirement
4. After running these commands, you need to restart the Oracel instance, follow these commands
5. After startup is complete run the following commands to see the updated values
show parameters processes;
show parameters sessions;
show parameters transactions;