Setting the SESSIONS Parameter
The SESSIONS parameter determines the maximum number of sessions that can be created in a system. So, it is basically the number of users that can be logged into the system at one time. BUT, it also needs to include the number of background processes running because they run in sessions, as well. Let’s see how to change this setting.
If you’re running out of sessions, check the parameters for SESSIONS, TRANSACTIONS and PROCESSES.
*NOTE: show parameter is always a LIKE search. Just pick out what you need and disregard the rest. ** added for emphasis.
SQL> show parameter sessions NAME TYPE VALUE ------------------------------------ ----------- ------ java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 248 ** SQL> show parameter transactions NAME TYPE VALUE ------------------------------------ ----------- ------- transactions integer 272 ** SQL> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------ aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 150 **
So we have the following:
Processes: 150 – the max number for background processes, etc. This number is used to figure the number of sessions. But what are we really running at? The processes setting is plenty for what we’re using.
SQL> select current_utilization from v$resource_limit where resource_name = 'processes'; CURRENT_UTILIZATION ------------------- 45 1 row selected.
Sessions: 248 – the default value: (1.1 * processes) + 5. We’re going above this to be able to test with 100 virtual users. NEW VALUE: 348.
Transactions: 272 – the default value: (1.1 * sessions). This has to change, too. NEW VALUE: 383
To make the changes, we need to restart the instance afterwards and set the scope to the spfile.
SQL> alter system set sessions=348 scope=spfile; System altered. SQL> alter system set transactions=383 scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1870647296 bytes Fixed Size 2254304 bytes Variable Size 536873504 bytes Database Buffers 1325400064 bytes Redo Buffers 6119424 bytes Database mounted. Database opened. SQL> show parameter sessions NAME TYPE VALUE ------------------------------------ ----------- ------ sessions integer 348 SQL> show parameter transactions NAME TYPE VALUE ------------------------------------ ----------- ------- transactions integer 383
The system is back up and my database parameters are changed. They are stored in the spfile, so those changes will persist.