How to Efficiently Manage Inactive (IDLE) Oracle Database Sessions

In busy database environments, inactive or dead connections can accumulate, consuming resources and potentially causing performance issues. This article explores how to manage inactive Oracle sessions using Dead Connection Detection (DCD) and resource parameters like IDLE_TIME, to keep database performance optimal.

Understanding Inactive Sessions

Inactive sessions typically arise from applications or systems that fail to close connections properly. These sessions might be due to users closing their devices without logging out or network disruptions. Over time, unmanaged idle connections can lead to issues, including resource exhaustion.

Detecting and Handling Dead Connections With SQLNET.EXPIRE_TIME

Oracle’s Dead Connection Detection (DCD) feature helps identify inactive connections between the client and server. Set in the sqlnet.ora configuration file, SQLNET.EXPIRE_TIME specifies a time interval (in minutes) to send a probe to check if connections are still active.

• Configuration: Add SQLNET.EXPIRE_TIME = [minutes] to sqlnet.ora.
• Functionality: If a connection is inactive, DCD flags it, allowing the database’s Process Monitor (PMON) to release database resources linked to that session.

Benefits: DCD is effective for connections terminated due to network or client issues, freeing server-side resources.

Limitations: It does not work for bequeathed connections and may slightly impact network performance due to additional traffic.

Using IDLE_TIME Profiles for Idle Connections

Oracle provides the IDLE_TIME parameter within user profiles to manage idle sessions, setting the maximum duration of inactivity allowed per session.

1. Enable Resource LimitsFirst, and ensure RESOURCE_LIMIT is set to TRUE.

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE SCOPE=BOTH;

2. Set the IDLE_TIME. Apply an idle time limit in minutes to relevant profiles.

ALTER PROFILE at_profile LIMIT IDLE_TIME 30;

Setting IDLE_TIME IN PROFILE PARAMETER

1. CHECK RESOURCE PARAMETER VALUE

SQL> show 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

3. Monitor the idle session. Once the IDLE_TIME When the threshold is reached, PMON marks the session as "sniped." The session will remain visible in V$SESSION until it attempts a new operation, at which point Oracle cleans it up.

Example: Suppose a user at_user is assigned to profile at_profile, which has an idle time limit of 30 minutes. If at_user the session is inactive beyond this limit, Oracle will mark it as "sniped" and clean up when the user attempts a new action.

Note: It’s best to create custom profiles for users who require resource limits instead of altering default settings.

Leveraging Resource Manager for Advanced Control

Oracle’s Resource Manager provides further control over managing inactive sessions. By configuring a Resource Manager plan, you can specify the MAX_IDLE_TIME for sessions, which will automatically kill sessions exceeding the idle threshold.

For more detailed implementation, refer to Oracle documentation or trusted resources. Resource Manager is suitable for environments where detailed control over session management is necessary.

Best Practices for Cleaning Inactive Sessions

To maintain optimal database performance, consider using profiles to combine DCD with resource limits. This dual approach targets both idle and dead connections:

• DCD handles dead connections by removing terminated sessions.
• Resource limits clean-up sessions based on idle time, effectively managing active but unused connections.

Caution: While DCD and Resource Manager can manage many inactive sessions, they won’t eliminate all OS-level processes. Occasionally, manual cleanup of OS processes may still be necessary to free system resources completely.

Summary

Automating the cleanup of inactive sessions in the Oracle Database is vital for maintaining resource efficiency and avoiding performance issues. By leveraging DCD, idle time settings, and Resource Manager, DBAs can ensure their databases remain responsive, even under heavy load. Regularly monitor inactive sessions and apply best practices to keep your database environment streamlined and resource-efficient.