How to Clean Inactive Oracle Sessions Automatically?

 

Occasionally you will encounter situations where your database has ever increasing numbers of database sessions. In some cases these may be large numbers of inactive sessions. This article explains how you might manage large numbers of old or inactive database sessions.

Large numbers of long term inactive sessions are typically caused by problems with an application or an application server not handling its connections properly. 

The obvious thing to do is correct the problem at the source. If that is not possible, you can consider apply options presented below.

Inactive session is caused due to Dead Connection or IDLE Connection.

SQLNET.EXPIRE_TIME (Dead Connection Detection-DCD)

These are previously valid connections with the database but the connection between the client and server processes has terminated abnormally.

Examples of a dead connection:
– A user reboots/turns-off their machine without logging off or disconnecting from the database.
– A network problem prevents communication between the client and the server.

In these cases, the shadow process running on the server and the session in the database may not terminate.

DEAD Connection is implemented by;

* adding SQLNET.EXPIRE_TIME = [MINUTES] to the sqlnet.ora file 

With DCD is enabled, the Server-side process sends a small 10-byte packet to the client process after the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter.

If the client-side connection is still connected and responsive, the client discards the probe packet, ..and another packet will be sent when next interval expires (assuming no other activity on the connection).

If the client fails and client OS sends back an error in responding to the DCD probe packet

• the Server side process is marked as a dead connection and
• PMON performs the clean up of the database processes / resources
• The client OS processes are terminated

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

Purpose

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.

IDLE_TIME PROFILE PARAMETER (For Idle Connections)

The Idle Time Resource Usage setting limits the maximum idle time allowed in a session. Idle time is a continuous inactive period during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit. Setting an Idle Time Resource Usage limit helps prevent users from leaving applications open when they are away from their desks.

Implemented by:

• Setting RESOURCE_LIMIT = TRUE in the database startup parameter file (spfile or pfile)
• Creating or modifying existing user profiles (DBA_PROFILES) to have one or more resource limit
• Assigning a profile to a user whose resources are wished to be limited

!!! If you are to implement resource limits, may be advisable to create new profiles that be assigned to users and not to change the characteristics of DEFAULT !!!

When a resource limit is exceeded (for example IDLE_TIME) … PMON does the following

• Mark the V$SESSION as SNIPED (Older Versions) or KILLED (Newer Versions)
• Clean up the database resources for the session
• Remove the V$SESSION entry

When a resource limit is exceeded (for example IDLE_TIME) … 

PMON marks the session as SNIPED in V$SESSION. Then, AFTER the SNIPED session tries to execute any SQL statement, its database resources are cleaned up and its V$SESSION entry is removed.

PMON is responsible for sniping the sessions. 

It will do this periodically. 

However, a session will continue to show as idle even after the idle_time for that user, as specified in that user’s profile, has expired. 

When the user attempts to run a transaction against the database after the idle_time has expired, the database will disconnect the user by terminating the session. 

After this, the session will no longer show in v$session. So, even if the session appears to be idle for a duration slightly more then your x minutes — it is already “dead”, it just doesn’t show as dead yet. PMON will eventually snipe the session, marking it dead in V$session. 

HOW TO SET IDLE_TIME PROFILE PARAMETER?

1. CHECK RESOURCE PARAMETER VALUE

SQL> sho parameter resource_limit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE

2. UPDATE THE RESOURCE PARAMETER TO TRUE

SQL> alter system set resource_limit=TRUE scope=both sid='*';
System altered.

3. CHECK IDLE_TIME VALUE FOR DEFAULT OR OTHER PROFILE

SQL> SELECT PP.PROFILE, PP.RESOURCE_NAME, PP.LIMIT FROM DBA_PROFILES PP WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('IDLE_TIME');

PROFILE		RESOURCE_NAME		LIMIT
-------		-------------		-----
DEFAULT		IDLE_TIME		UNLIMITED

4. CHANGE THE IDLE_TIME TO DESIRED VALUE FOR DEFAULT OR OTHER PROFILE

SQL> ALTER PROFILE DEFAULT LIMIT IDLE_TIME 30;
Profile altered.

5. RE-CHECK IDLE_TIME VALUE FOR DEFAULT OR OTHER PROFILE

SQL> SELECT PP.PROFILE, PP.RESOURCE_NAME, PP.LIMIT FROM DBA_PROFILES PP WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('IDLE_TIME');

PROFILE		RESOURCE_NAME		LIMIT
-------		-------------		-----
DEFAULT		IDLE_TIME		30

RESOURCE MANAGER (For Idle Connections)

Resource manager plans can be created to kill inactive sessions with high idle time. Refer to How To Automatic Kill Inactive Sessions using Resource Manager (Doc ID 1935739.1). This document contains the details with an example. You can customize the plan directives as per your requirement.

In this case, once the inactive time goes beyond the specified MAX_IDLE_TIME, PMON marks the session as KILLED. When this KILLED later tries to execute any SQL statement, its database resources are cleaned up and its V$SESSION entry is removed.

GENERAL NOTES

It is strongly recommended that both DCD and Resource Limits with Profiles be implemented in order to clean up resources at both the database and OS level.

This combination will not clean up IDLE / ABANDONED / INACTIVE connections (OS processes) as these sessions still have active clients

For this case we will see that :

• PMON has cleaned up the V$SESSION entries .. but both the OS processes and the V$PROCESS entries will still exist
• SQLNET will continue to be able to send the 10 byte packet successfully until the session is logged off

This condition can be a major problem as

• The database exhausts PROCESSES and gives ORA-20 maximum number of processes [num] exceeded
• The OS can become exhausted due to the un needed resources consumed by the abandoned processes

The SYMPTOMS of this condition are:

• The database view V$PROCESS will have no corresponding V$SESSION entry
• An OS process / thread still exists for the SNIPED session

The solutions to this scenario can are to clean up the OS processes … after which the V$PROCESS entries should be removed automatically.

SUMMARY

1) DCD initiates clean up of OS and database processes that have disconnected/terminated abnormally.

2) DCD will not initiate clean up sessions that are still connected … but are idle / abandoned / inactive.

3) Database Resource Limits + Profile Parameter clean up database resources for user sessions that exceed resource limits.

4) Database Resource Limits + Profile Parameter will not clean up OS processes.

5) If DCD and Database Resource Limits + Profile Parameter are used in combination .. Dead Connections OS and Database Resources will be cleaned up.

6) IDLE / ABANDONED / INACTIVE sessions OS processes will not be cleaned up even if DCD and Database Resource Limits + Profile Parameter are used in combination … these must be cleaned up manually!!!

7) Resource Manager can be used to kill an inactive session, which exceeds the idle time mentioned in the plan directives.

SR RESULTS

I created SR for this issue. Answers are like below.

1. Permanent Solution

All codes should be examined in detail by software developers and connections that are not closed should be closed. In this work, we, as the DBA team, can provide software developers with the SQLs that cause inactive sessions with the following queries.

/* INACTIVE SESSIONS WITH SQLTEXTS FOR RAC INSTANCE */
SELECT SESS.INST_ID,
SESS.SID,
SESS.SERIAL#,
SESS.USERNAME,
SESS.TERMINAL,
SESS.PROGRAM,
SQLAREA.SQL_ID,
SQLAREA.SQL_TEXT
FROM GV$SQLAREA SQLAREA, GV$SESSION SESS
WHERE SESS.PREV_HASH_VALUE = SQLAREA.HASH_VALUE
AND SESS.PREV_SQL_ADDR = SQLAREA.ADDRESS
AND SESS.USERNAME IS NOT NULL
AND SESS.STATUS = 'INACTIVE';

/* INACTIVE SESSIONS WITH SQLTEXTS FOR SINGLE INSTANCE */
SELECT SESS.SID,
SESS.SERIAL#,
SESS.USERNAME,
SESS.TERMINAL,
SESS.PROGRAM,
SQLAREA.SQL_ID,
SQLAREA.SQL_TEXT
FROM V$SQLAREA SQLAREA, V$SESSION SESS
WHERE SESS.PREV_HASH_VALUE = SQLAREA.HASH_VALUE
AND SESS.PREV_SQL_ADDR = SQLAREA.ADDRESS
AND SESS.USERNAME IS NOT NULL
AND SESS.STATUS = 'INACTIVE';

2. Workaround

Inactive sessions can be cleaned with the methods described in A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes ( Doc ID 601605.1 ) article.