In this post, we'd like to talk about the following topics for you to manage SESSIONS parameter better.
Default Value of SESSIONS
SESSIONS is an initialization parameter of Oracle database, which limits the maximum number of sessions that can be reached in the instance.
In fact, the default value of SESSIONS is derived from PROCESSES, so we rarely change it.
| Release | SESSIONS Calculation |
|---|---|
| 9i | SESSIONS = PROCESSES * 1.1 + 5 |
| 10g | SESSIONS = PROCESSES * 1.1 + 5 |
| 11.1 | SESSIONS = PROCESSES * 1.1 + 5 |
| 11.2 | SESSIONS = PROCESSES * 1.5 + 22 |
| 12c | SESSIONS = PROCESSES * 1.5 + 22 |
| 18c | SESSIONS = PROCESSES * 1.5 + 22 |
| 19c | SESSIONS = PROCESSES * 1.5 + 22 |
| 21c | SESSIONS = PROCESSES * 1.5 + 22 |
Basically, the value of SESSIONS is to multiply PROCESSES by a factor and then add a constant. So the final number might look a little awkward, e.g. 2272 which we can see the example soon.
As you can see in the above list, there's a division from release 11.2, it starts to use a higher factor to make the value larger than before.
How to Check SESSIONS ?
First of all, we look up parameter PROCESSES and SESSIONS like this:
SQL> column name format a30;
SQL> column value format a30;
SQL> select name, value from v$parameter where name in ('processes', 'sessions') order by 1;
NAME VALUE
------------------------------ ------------------------------
processes 1500
sessions 2272
The value 2272 for SESSIONS is actually derived from PROCESSES. That is, 1500 * 1.5 + 22 = 2272. Someone doesn't know the story might raise questions about it.
How to Change SESSIONS ?
Although the default value of SESSIONS is derived from PROCESSES, it can be changed into a more meaningful value, say, 3000.
Most likely, you will need to raise SESSIONS only when your database applies Shared Server Architecture.
Please note that, any value less than the default one will be ignored at startup without any warning, the instance still take and use the default one. In other words, the derived value is the minimum one.
Backup SPFILE
First of all, we backup SPFILE in case that something goes wrong.
ALTER SYSTEM SET SESSIONS
Such parameter can only be changed within SCOPE=SPFILE.
SQL> alter system set sessions=3000 scope=spfile;
System altered.
The default behavior is to change all instances of the database, so you don't have to worry about the command if you're in a RAC database.
Restart Database
To make new value take effect, we have to restart the database to apply SPFILE from startup.
Check Result
Let's see the result.
SQL> select name, value from v$parameter where name in ('processes', 'sessions') order by 1;
NAME VALUE
------------------------------ ------------------------------
processes 1500
sessions 3000
Finally, we make SESSIONS become 2 times of PROCESSES.
At the state of computer technology, we usually deploy application servers to control incoming sessions in a connection pool, so tuning the value of SESSIONS is not as regular as the old days.