Managing and Terminating Idle Oracle Database Sessions Automatically

 

Managing and Terminating Idle Oracle Database Sessions Automatically

In database environments, maintaining optimal performance requires careful management of user sessions, especially idle ones. Idle sessions can consume resources and lead to unnecessary wait times, especially when the maximum number of sessions is reached. In this post, we’ll explore how to identify idle sessions, create a configuration to manage them, and set up an automatic process to terminate idle sessions that last longer than one hour.

Understanding Idle Sessions in Oracle Database

An idle session is a session where the user is connected but not actively executing SQL statements. While Oracle can handle many sessions simultaneously, excessive idle sessions can eventually strain resources. By managing and terminating these sessions, DBAs can optimize resource usage, particularly in environments where there’s a limit on concurrent connections.

Method 1: Using Oracle Profiles to Manage Idle Sessions

Oracle Profiles allows DBAs to control session-level resource usage, including limiting the amount of idle time.

Step 1: Create a Profile with an Idle Time Limit

To set an idle timeout of one hour (60 minutes), we’ll create a profile with a `IDLE_TIME` resource limit. 

SQL: 

CREATE PROFILE user_idle_limit_profile

LIMIT

    IDLE_TIME 60; -- 60 minutes

Here, `IDLE_TIME` represents the maximum allowed idle time for a session, in minutes. Once this threshold is exceeded, Oracle will automatically disconnect the session.

Step 2: Assign the Profile to Users

To enforce the idle timeout, assign the profile to users who require monitoring:

SQL: 

ALTER USER john_doe PROFILE user_idle_limit_profile;

ALTER USER jane_smith PROFILE user_idle_limit_profile;

All users assigned to `user_idle_limit_profile` will be disconnected automatically if idle for more than one hour. Keep in mind that an `IDLE_TIME` of zero means there is no idle limit.

 Note: Sessions terminated this way will receive an ORA-02396 warning message, indicating that they have been disconnected due to inactivity.

Step 3: Verifying Profile Application

You can verify if the profile was applied correctly by checking the `DBA_USERS` view:

SQL: 

SELECT username, profile 

FROM dba_users 

WHERE profile = 'USER_IDLE_LIMIT_PROFILE';

This confirms that the intended users are bound by the idle session limit.

Method 2: Using Oracle Resource Manager for More Granular Control

Oracle’s Resource Manager offers more control. It allows you to create rules that monitor and act on specific session conditions, including idle sessions.

Step 1: Create a Resource Plan

First, create a resource plan that specifies the action to take when a session remains idle for too long. Here’s an example:

SQL: 

BEGIN

    DBMS_RESOURCE_MANAGER.create_pending_area();

    DBMS_RESOURCE_MANAGER.create_plan(

        plan    => 'idle_session_plan',

        comment => 'Resource plan to terminate idle sessions'

    );

    DBMS_RESOURCE_MANAGER.create_plan_directive(

        plan             => 'idle_session_plan',

        group_or_subplan => 'OTHER_GROUPS',

        comment          => 'Terminate sessions idle for more than 60 minutes',

        switch_for_call  => 'TRUE',

        switch_time      => 3600, -- 3600 seconds (1 hour)

        switch_group     => 'KILL_SESSION'

    );

    DBMS_RESOURCE_MANAGER.submit_pending_area();

END;

/

In this example, the `switch_time` parameter is set to 3600 seconds (1 hour). If a session remains idle for this duration, Oracle will automatically move it to the `KILL_SESSION` group, where it’s terminated.

Step 2: Activate the Resource Plan

Once the resource plan is defined, activate it to enforce the rules:

SQL: 

ALTER SYSTEM SET resource_manager_plan = 'idle_session_plan';

With this plan active, sessions that remain idle for over an hour are automatically terminated, reducing unnecessary resource consumption.

Step 3: Verifying Active Resource Plan

To check which resource plan is active, you can use:

SQL: 

SELECT name, active

FROM v$rsrc_plan

WHERE is_top_plan = 'TRUE';

This will confirm that `idle_session_plan` is the active resource management plan.

Monitoring and Auditing Idle Sessions

Regularly reviewing the sessions terminated by the profile or resource manager helps ensure that active users are unaffected. You can monitor terminated sessions by querying Oracle’s session and resource views:

SQL: 

SELECT sid, serial#, username, status, last_call_et

FROM v$session

WHERE status = 'INACTIVE' 

  AND last_call_et > 3600; -- Sessions idle for more than 1 hour

The `last_call_et` column records the idle duration in seconds. This query allows you to keep track of all sessions that have been idle for over an hour.

Final Thoughts and Best Practices

1. Set Realistic Limits: Configure idle timeouts that reflect actual user behavior. For instance, if users typically require 30-minute breaks, a 60-minute idle timeout may be ideal.

2. Monitor Frequently: As with any automated process, regularly monitor your configuration to ensure that critical sessions are not mistakenly terminated.

3. Communicate with Users: Inform users about session policies, especially if implementing a more aggressive timeout.

By setting up automated processes to manage idle sessions, Oracle DBAs can improve resource utilization, ensure a smooth user experience, and optimize system performance. This proactive approach helps avoid potential bottlenecks, allowing DBAs to focus on higher-impact tasks.