Timings to Set SESSIONS Explicitly

DBAs usually set PROCESSES when initializing a new database:
SQL> alter system set processes=3000 scope=both;

System altered.

Another important parameter SESSIONS will be derived from PROCESSES automatically in this manner:
SESSIONS = (1.5 * PROCESSES) + 22

In a dedicated server environment, the derived value of SESSIONS could be somewhat close to the facts of database. You can accept the value as a good starting point to tune, so you don't have to set SESSIONS value explicitly.

But in a shared-server dominant database, it's another case. You may have set PROCESSES to a low value (e.g. 30) to prevent the database from over commitment to server. The derived value of SESSIONS could be too limited or conservative to exploit the database. Therefore, it's the right timing to set SESSIONS explicitly to allow more connections.

According to Oracle documentation. Oracle suggests that you set SESSIONS explicitly as this:
SESSIONS = 1.1 * CONNECTIONS

The CONNECTIONS means the maximum number of concurrent connections that DBA allows users to connect the database. Please note that, CONNECTION is not an initialization parameter, but SESSIONS is.

For more information about configuring SESSIONS, please refer to Oracle documentation:
SESSIONS

Leave a Reply

Your email address will not be published. Required fields are marked *