Oracle number of connections too many release mechanism

Oracle Number of connections too many release mechanism

  sqlplus/nolog 
  open sqlplus  
  
  
 connect/ As sysdba  
  Use DBA authority to login oracle  
   
  
  Show parameter resource_limit 
  shows whether resource qualification is on, value is true is on, false is off   
 
  Alter system set resource_limit=true 
  If not turned on, use this command to turn on resource-limited functionality   
  
   &NBSP
 create profile profilename limit connect_time idle_time 30 
  Create ProfileName any start, Connect_time set the number of minutes after the connection is forced to release, Idle_time set the number of consecutive inactive sessions more than a few minutes after the forced release   
 
 alter User Oracleuser profile profilename 
  profiles for specified users  

Since last week, the server Oracle database problems, in less than half a day, will be reported maxsession (150) problem, it is certain that the database session exceeded the maximum number.

Since the server is running file transfer applications, the requests and sessions must be very large, so the number of users has reached the maximum number of sessions for Oracle.

There are two ways to deal with it: to increase the maximum number of sessions for Oracle and to clear inactive session, and, of course, from database connection pooling and program bugs.

Some statements that view the current session are collected from the rest of the page, recording:

1.select Count (*) from v$session;

Select COUNT (*) from v$process;

View the current total number of sessions and the number of processes, both of which are important views related to the session and process, and the information is taken from there.

2. Query the number of connections for those applications at this time?

Select B.machine, B.program, COUNT (*) from v$process A, v$session b where a.addr = B.paddr and b.username are NOT null Group BY B.machine, B.program ORDER by COUNT (*) desc;

3. Find out if there is a deadlock

SELECT * from V$locked_object;

If the query result is no rows selected, there is no deadlock in the database. Otherwise, there is a deadlock in the database.

Next, explain the status of the session:

1.active a session in this State, indicating that it is executing, is active.

The 2.killed session in this state indicates that an error has occurred, is rolling back, and, of course, is taking up system resources. Another point is that the state of the killed will generally last longer, and using the Windows Tools PL/SQL developer to kill, is not useful, to use the command: Alter system kill session ' sid,serial# ';

3.inactive a session in this state is not executing, such as the SELECT statement is complete. I thought at first, as long as the inactive state of the session, is the killing, why not release it. In fact, inactive has no effect on the database itself, but if the program does not commit in a timely manner, it can result in excessive session usage. The best way to solve inactive is to set the timeout in Oracle directly, there are two ways, the difference is not clear at the moment:

1. Modify Sqlnet.ora file, add expire_time=x (unit is minutes)

My Sqlnet.ora position is in d:/oracle/ora92/network/admin.

2. By Alter profile DEFAULT LIMIT Idle_time 10; Command changes, remember to restart Oracle.

Modify session and process in Oracle

The relationship between session sessions and process pocesses
A process can have 0, one or more sessions, a session can also exist in a number of process, parallel is also a session corresponding to a process, the main session is coordinator session, Each parallel process also corresponds to a separate session in the database. This can be verified from v$px_session and v$session.
Connection connects, session sessions and process pocesses relationships

Each SQL login is called a connection (connection), and each connection can produce one or more sessions, if the database is running in a dedicated server way, a session corresponds to a server process (process), if the database is running in a shared server mode, A server process can serve multiple sessions.

The number of Oracle sessions and processes is: sessions=1.1 * processes + 5

Here we modify the maximum value of the process in two ways
I. Modifying in the graphical manager via Oracle Enterprise Manager Console
Log in as System administrator, go to the interface database routines-configuration-general information-all initialization parameters, modify the value of processes

Ii. Modification in Sqlplus
Log in with DBA authority to modify the value of the process (the value of the session will be changed); Create a pfile; restart the database. The SQL command entered below, the ECHO message omitted
Sql> Connect Sys/sys as Sysdba
Sql> alter system set PROCESSES=400 scope = SPFile;
Sql> create Pfile from SPFile;
sql> shutdown immediate;
Sql> Startup

Oracle number of connections too many release mechanism