Consumption and simple optimization of the number of Oracle database connections

Posted by Sulman on Mon, 13 Jan 2020 12:49:36 +0100

Observe the indicators and check the connection consumption

--View session session: 
select * from v$session where username is not null
select username,count(username) from v$session where username is not null group by username

--View current connections
select count(*) from v$process;

--View maximum connections
select value from v$parameter where name ='processes';

--View the number of concurrent connections
Select count(*) from v$session where status='ACTIVE';

--Query: client device ID, client program oracle User name, number of connections consumed
select  b.MACHINE machine name, b.PROGRAM Program name, b.USERNAME User name, count(*) Connection number from v$process a, v$session b 
where a.ADDR = b.PADDR and  b.USERNAME is not null   
order by count(*) desc;

--Kill the specified process
alter system kill session 'sid,serial#' immediate;

Points that may need to be optimized

1. Release resources

        stay sqlnet.ora Set in file expire_time Parameters.
        //You can use the exit? Time parameter to intermittently check for abnormal session s and release the process.
        //Official note: Time
    Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
Limitations on using this terminated connection detection feature are:
    It is not allowed on bequeathed connections.
    Though very small, a probe packet generates additional traffic that may downgrade network performance.
   Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.

Recommended Value

//The setting here is 10 minutes. Every 10 minutes, Oracle will confirm whether all session client connections are normal. For abnormal sessions, Oracle will clean up the process.

2. Expand the number of connections, and the Session will automatically expand

alter system set processes = 500 scope = spfile;

3. After the above optimization operation is set, restart the database, and close all connections when shutting down (use immediate shutdown)

SQL>conn /as sysdba
SQL>shutdown immediate

If there are many connections or sessions, they may be closed slowly. If they cannot be closed, please refer to:

Basic knowledge of connection, process and session

Several connections, processes and sessions are mentioned above. Here is a brief supplement to the relationship between the two:

    Each sql login is called a connection, and each connection can generate one or more sessions. If the database is running in a dedicated server mode,
A session corresponds to a server process. If the database runs in shared server mode, a server process can serve multiple sessions.

    Connection is not built directly between the user process and the database instance. It's between the user process and the Server Process,
Therefore, if there is a Connection, there must be a user process and a server process, but there may not be a Session.
For example, if you need to transport things from A to B, Connection can be regarded as A "bridge", and the truck will transport things from A to B and return to A, which is Session.
Therefore, as long as you keep the connection open, you can create a session on the connection at any time.


Published 3 original articles, won praise 2, visited 6336
Private letter follow

Topics: Session Database SQL Oracle