Number of Oracle connection processes set

Sql> Select COUNT (*) from v$session  #连接数
Sql> Select Count (*) from v$session where status= ' ACTIVE ' #并发连接数
Sql> Show parameter processes  

Process: This parameter limits the number of operating system processes that can connect to the SGA (or the number of threads in the Windows system), which must be large enough to be applied to the background process and all dedicated server processes, in addition, The number of shared server processes and scheduling processes is also counted. Therefore, in a dedicated server environment, this is a way to limit the number of concurrent connections.

Sessions: is applied to the Oracle hierarchy, not the operating system hierarchy. This parameter limits the number of concurrent logons to a specified instance without considering logging on through a dedicated server or a shared server.

<context path= "" docbase= "zsxxw/" debug= "0" reloadable= "true"/> Tomcat specifies a single home directory (temporarily not considering consolidating Apache HTTP with Tomcat, Implementation of dynamic page separation)
Problem Description: Client Connection Database error

Ora-12516:tns: Listener cannot find a handle available for matching protocol stack

Resolution process:
1. View the current number of sessions, processes and sessions values, and find that the values of the session number and 2 parameters are already very close

Sql*plus:release 10.2.0.1.0-production on Monday October 9 15:50:21 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Sql> Conn/as SYSDBA
is connected.

Sql> Select COUNT (*) from v$session;

COUNT (*)
----------
45


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 50
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
Logmnr_max_persistent_sessions Integer 1
Sessions Integer 60
Shared_server_sessions integer


2. modifying processes and Sessions values

Sql> alter system set PROCESSES=300 Scope=spfile;

The system has changed.

Sql> alter system set SESSIONS=300 Scope=spfile;

The system has changed.


3. View the processes and sessions parameters, but the changes do not take effect

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 50
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
Logmnr_max_persistent_sessions Integer 1
Sessions Integer 60
Shared_server_sessions integer


4. Restart the database for the changes to take effect

sql> shutdown Immediate



Sql> Startup



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 300
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
Logmnr_max_persistent_sessions Integer 1
Sessions Integer 335
Shared_server_sessions integer

Last Test increase the number of connections to 50,100 did not report ORA-12516 error.


Sometimes we need to adjust the maximum number of links in the Oracle database, and the number of links is adjusted to the DBS under Oacle
Recorded in the Init.ora file.
The number of connections to Oracle (sessions) is related to the number of processes in its parameter file (process), and their relationships are as follows:
Sessions= (1.1*process+5)
However, when we increase the number of process, the database is not started. That's because we're missing a Unix system parameter:
It is the semmns in the core parameter, which is the semaphore parameter of the UNIX system. Each process consumes a semaphore. Semmns
After tuning, the UNIX operating system needs to be restarted for the parameters to take effect. However, its size is subject to hardware memory or Oracle
Sga. Range can vary from 200--2000.

However, the processes modification should not only adjust the parameters in the Init.ora file, but also adjust the OS kernel
Parameters, like Aix,hpux,solaris,sco,unixware, the OS tuning is required to reboot, and this parameter
Settings can not be simple according to how many terminals to connect to this server, depending on
The key is to consider how many simultaneous sessions (usually not too big when using some shared-connected middleware),
Of course, there are some Oracle background processes to consider, and some system maintenance work requires more connections.

My atmp large front machine on Oracle tuning, it uses the UnixWare operating system, in making the link number adjustment
, you need to adjust the core parameters first.

The key related parameters are adjusted as follows:

SHMMAX 1000000000

Shmmin 1

Shmmni 200

Shmseg 15

Semmni 1000

SEMMSL 300

Semmns 230

SEMOPM 20

of which SEMMNI,SEMMNS,SEMMSL to increase, at least greater than processes 18;

Semmni (10,10000;150): Specifies the number of signal recognition in the core. This is the only thing that can be activated at any given time
Set the number of signals. The default value is 150. The maximum value is generated by automatic system adjustment.

SEMMSL (25,300;150): Specifies the maximum number of semaphores in each signal recognition. The default value is 25.
Semmns the number of processes+10* instances of processes and +2* maximum db for all DB except maximum db. such as 3 instances into a
The number of processes is 100, 100, 200, then = (100+100) +2*200+10*3=630

SEMOPM (10,20;10): Specifies the maximum number of signal operations that can be performed in each system call SEMOP.
The default value is 10.

SHMMAX (131072,1073741824;524288): Specifies the maximum value of the shared memory portion size.
equals 0.5x of physical memory bytes

Shmmni (10,1000;100): Specifies the maximum value of the system-wide shared memory identity.

Shmseg (6,15;6): Specifies the number of shared memory blocks (or identities) associated with each process. The default value is 6. With each
The maximum number of shared memory blocks associated with a process is related to the unused space owned by the process. Therefore, although a process has less than
Shmseg a shared memory block of values, it is also possible because of its limited space and cannot be associated with other processes.

The Init.ora is adjusted to:

processes = # SMALL

#processes = # MEDIUM

# processes = $ # LARGE
The processes is the number of maximum connections to be adjusted, so we just need to adjust this parameter, and other parameters can be
To keep the default values.