Oracle session Active and Inactive status description

Oracle session Active and Inactive Status description original June 12, 2011 13:08:00

• Label:
• Session/
• Oracle/
• Database/
• Server/
• Middleware
• Resources

I. Session status Description

The status of the session can be viewed through the V$session view's State column. For the use of this view, refer to the online documentation:

V$session

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_3016.htm#REFRN30223

STATUS

VARCHAR2 (8)

Status of the session: Active-session currently executing SQL INACTIVE Killed-session marked to be killed Cached-session temporarily CACHED for use by Oracle*xa Sniped-session inactive, waiting on the client

Description of the Status:

(1) The active session in this state indicates that it is executing and is active.

Official Document Description:

Any session this is connected to the database and are waiting for a event that does does belong to the Idle wait class is C Onsidered as an active session.

(2) Killed session in this state, marked as deleted, indicates that an error has occurred and is rolling back.

Of course, it also occupies the 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 that is in this state is not executing

The state is in a wait operation (that is, waiting for the SQL statement to be executed), usually when the DML statement is complete. However, the connection is not released, this may not be released in the program, if the use of middleware to connect, it may be the configuration of the middleware or a bug caused.

Inactive has no effect on the database itself, but if the program does not commit in a timely manner, it can cause excessive session usage. It is easy for the DB session to reach the limit value.

Asked a few friends, their practice is not to deal with the inactive state of the session, if the maximum value of the session is reached, increase the processes and sessions parameters. If the kill inactive session is likely to have an impact on the middleware. Specific middleware This piece I also not very familiar, wait for later to make clear, in said.

Two. session that handles inactive status

In front of said do not deal with the inactive state of the session, but there is a way to solve. There are two ways.

2.1 Setting the Expire_time parameter in the Sqlnet.ora file

Official website for instructions on this parameter:

Http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm

Sqlnet. Expire_time

Purpose

Use parameter sqlnet. Expire_time to specify a of the time interval, in minutes, to send a probe to verify that client/server connections is Activ E. Setting a value greater than 0 ensures that connections is not left open indefinitely, due to an abnormal client Termi Nation. If The probe finds a terminated connection, or a connection that's no longer in use, it returns an error, causing the SER Ver process to exit. This parameter was primarily intended for the database server, which typically handles multiple connections at any one time .

Sqlnet.expire_time: Oracle server sends packet probing dead connection, closes the appropriate server process if the connection is closed or is no longer in use.

Limitations on using the This terminated Connection detection feature is:

(1) It is not allowed on bequeathed connections.

(2) Though very small, a probe packet generates additional traffic that may downgrade network performance.

(3) Depending on which operating system was in use, the server may need to perform additional processing to distinguish the Connection probing event from the other events, that occur. This can also the result in degraded network performance.

default:0

Minimum value:0

Recommended value:10

Example

Sqlnet. expire_time=10

2.2 Setting the Idle_time parameter for user profile

An article about profile that was previously organized:

Oracle User profile Properties

Http://blog.csdn.net/tianlesoftware/archive/2011/03/10/6238279.aspx

Note that to enable Idle_time, you first enable the Resource_limit parameter. This parameter is false by default. The official website is described below:

Resource_limit

Property	Description
Parameter type	Boolean
Default value	False
Modifiable	ALTER SYSTEM
Range of values	true | False

Resource_limit Determines whether RESOURCE limits is enforced in database profiles.

Values:

True:enables the enforcement of resource limits

False:disables the enforcement of resource limits

The following blog in this piece says more clearly and provides the relevant script:

Sqlnet.expire_time and Idle_time

http://space.itpub.net/10687595/viewspace-420407

Idle_time specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations is subject to this limit.

A valid database connection that's idle would respond to the probe packet causing no action on the part of the Server, WH Ereas the Resource_limit would snipe the session when Idle_time was exceeded. The ' sniped ' session would get disconnected when the user (or the user process) tries to communicate with the server again.

--Limit session idle time by Idle_time. The session idle exceeds the setup time and the status is sniped (v$session). However, the process under the OS is not released, and when the session (user process) communicates with the server process again, The appropriate server process will be closed.

What is does ' sniped ' status in V$session mean?

When Idle_time are set in the users ' profiles or the default profile. This would kill the sessions in the database (status in V$session now becomes sniped) and they would eventually disconnect. It does not always clean up the Unix session (Local=no sessions).

At this time all Oracle resources is released but the shadow processes remains and OS resources is not released. This shadow process is still counted towards the parameters of Init.ora.

This are killed and entry from V$session are released only if user again tries to do something. Another of forcing disconnect (if your users come in via Sql*net) are to put the file Sqlnet.ora on every client Machin E and include the parameter "Sqlnet. Expire_time "In it to force the close of the sql*net session

Sqlnet.expire_time

Sqlnet.expire_time actually works on a different principle and are used to detect dead connections as opposed to disconnect ing (actually ' sniping ') a session based on Idle_time which the profile accomplishes.

But again, as a mentioned, Expire_time works globally while idle_time profiles works for that user. You can use both of them to make sure, the client not only gets sniped but also gets disconnected if the user process Abnormally terminates.

To modify an example:

Sql>alter profile default limit idle_time 10;

-Requires a restart of Oracle

Query the number of connections applied to SQL:

/* Formatted on 2011/6/12 13:06:23 (QP5 v5.163.1008.3004) */

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;

Oracle session Active and Inactive status description