Oracle session and processes settings

1. Sessions

Among the restrictions set by initialization parameters, the most well-known estimates are sessions and processes.

The sessions parameter specifies the number of concurrent users that can exist in an instance at the same time, or the number of concurrent users that can log on to the database at the same time. Generally, when setting this number, we need to consider how many concurrent users we may connect to the database at the same time, add the number of background processes, and multiply the number of processes with 1.1.

For example, it is estimated that 100 users may connect to the database in the system at least, so your session should be

(100 + 10) * 1.1 = 121

When the concurrent user connected to the database reaches this value and a new session is connected, an error is reported.

00018,000 00, "Maximum number of sessions exceeded"

// * Cause: All session State objects are in use.

// * Action: increase the value of the sessions initialization parameter.

 

2. Processes

The processes parameter is similar to sessions.

The processes parameter specifies the number of processes that the instance can run simultaneously at the OS level. Based on the same settings as sessions, when setting processes, we should also consider how many concurrent users we may have connected to the database at the same time, and add the number of background processes.

Of course, in the MTS (Shared Server) configuration, this value will indeed be different. It should be a common background process + max_shared_servers + max_dispatchers ).

In addition, since Oracle exists in the form of a single process on the window platform, the processes parameter is changed to a limit on the number of threads in the Oracle process.

When Oracle needs to start a new process and the process parameter has been reached, an error is returned:

00020,000 00, "Maximum number of processes (% s) Exceeded"

// * Cause: All process state objects are in use.

// * Action: increase the value of the processes initialization parameter.

1). Modify through sqlplus

The relationship between Oracle sessions and processes is

Sessions = 1.1 * processes + 5


Use sys to log on with sysdba permissions:

SQL> show parameter processes;

Name type value

--------------------------------------------------------------------------------------

Aq_tm_processes integer 1

Db_writer_processes integer 1

Job_queue_processes integer 10

Log_archive_max_processes integer 1

Processes integer 150

SQL> alter system set processes = 400 scope = spfile;

The system has been changed.

SQL> show parameter processes;

Name type value

----------------------------------------------------------------------------------------

Aq_tm_processes integer 1

Db_writer_processes integer 1

Job_queue_processes integer 10

Log_archive_max_processes integer 1

Processes integer 150

SQL> Create pfile from spfile;

The file has been created.



Restart the database. OK!


SQL> shutdown immediate;

The database has been closed.

The database has been detached.

The Oracle routine has been disabled.

SQL> startup

The Oracle routine has been started.


Total system global area 171966464 bytes

Fixed size 787988 bytes

Variable Size 145488364 bytes

Database buffers 25165824 bytes

Redo buffers 524288 bytes

The database has been loaded.

The database has been opened.

SQL> show parameter processes;


Name type value

---------------------------------------------------------------

Aq_tm_processes integer 0

Db_writer_processes integer 1

Gcs_server_processes integer 0

Job_queue_processes integer 10

Log_archive_max_processes integer 2

Processes integer 400

SQL> show parameter session;


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

Logmnr_max_persistent_sessions integer 1

Session_cached_cursors integer 0

Session_max_open_files integer 10

Sessions integer 445

Shared_server_sessions integer

SQL>

[Note: sessions is a derived value, determined by the processes value. formula sessions = 1.1 * process + 5]