Oracle Resource Manager: Granular Control for Managing Idle Sessions

Oracle Resource Manager: Granular Control for Managing Idle Sessions

In database management, efficiently handling idle sessions is essential to maintaining performance and resource availability. Oracle Resource Manager is a powerful tool, often underutilized, that offers granular control over how sessions consume resources. This post explores how to leverage Oracle Resource Manager to monitor and automatically terminate idle sessions, complete with in-depth, practical examples.

Why Use Oracle Resource Manager?

Oracle Resource Manager allows DBAs to define and enforce resource limits on user sessions based on specific conditions, such as idle time, CPU usage, and session priority. This level of control can be particularly useful in environments with high session volumes, such as transactional systems or shared database infrastructures, where idle sessions can prevent other active sessions from connecting.

Setting Up Resource Manager for Idle Session Management

To illustrate, let's walk through a scenario where we have a transactional database with frequent user connections. Our goal is to manage idle sessions, terminating any session that remains idle for over an hour. 

Step 1: Creating a Resource Plan

A resource plan acts as a blueprint, defining how Oracle should handle session resources. We’ll create a resource plan named `Session_Management_Plan` to automatically switch idle sessions to a termination state.

SQL:

BEGIN

    DBMS_RESOURCE_MANAGER.create_pending_area();

    DBMS_RESOURCE_MANAGER.create_plan(

        plan    => 'Session_Management_Plan',

        comment => 'Resource Plan to handle idle sessions exceeding 1 hour'

    );

    DBMS_RESOURCE_MANAGER.submit_pending_area();

END;

/

Here, we set up a simple resource plan structure to act as a container for directives. The `create_pending_area` function places the configuration in a pending state until it is finalized and submitted.

Step 2: Defining Consumer Groups

In Oracle Resource Manager, consumer groups classify sessions based on their resource needs. Here, we create two consumer groups: `ACTIVE_SESSIONS` for sessions with standard activity and `IDLE_TERMINATION` for sessions that exceed the idle limit.

SQL:

BEGIN

    DBMS_RESOURCE_MANAGER.create_pending_area();

    DBMS_RESOURCE_MANAGER.create_consumer_group(

        consumer_group => 'ACTIVE_SESSIONS',

        comment        => 'Group for actively monitored sessions'

    );

    DBMS_RESOURCE_MANAGER.create_consumer_group(

        consumer_group => 'IDLE_TERMINATION',

        comment        => 'Group for sessions to be terminated if idle too long'

    );

    DBMS_RESOURCE_MANAGER.submit_pending_area();

END;

/

These consumer groups allow us to specify different rules and behaviors based on session status.

Step 3: Creating Plan Directives

Directives define the rules for each consumer group within a plan. We’ll set a directive to monitor sessions within `ACTIVE_SESSIONS`, moving idle sessions to the `IDLE_TERMINATION` group if they remain idle for over an hour.

SQL:

BEGIN

    DBMS_RESOURCE_MANAGER.create_pending_area();

    DBMS_RESOURCE_MANAGER.create_plan_directive(

        plan             => 'Session_Management_Plan',

        group_or_subplan => 'ACTIVE_SESSIONS',

        comment          => 'Standard sessions with monitoring for idle state',

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

        switch_group     => 'IDLE_TERMINATION'

    );

    DBMS_RESOURCE_MANAGER.create_plan_directive(

        plan             => 'Session_Management_Plan',

        group_or_subplan => 'IDLE_TERMINATION',

        comment          => 'Group for terminating sessions idle over 1 hour',

        cpu_p1           => 0, -- No CPU allocation as sessions are idle

        max_idle_blocker_time => 3600 -- Terminate after one hour idle

    );

    DBMS_RESOURCE_MANAGER.submit_pending_area();

END;

/

In this configuration:

- `switch_time` specifies the idle duration threshold (3600 seconds or 1 hour).

- `switch_group` moves sessions from `ACTIVE_SESSIONS` to `IDLE_TERMINATION` once they exceed the idle time.

- `cpu_p1` is set to zero for `IDLE_TERMINATION` to prevent idle sessions from consuming CPU.

- `max_idle_blocker_time` limits the maximum time for idle sessions in the `IDLE_TERMINATION` group, ensuring termination.

Step 4: Activating the Resource Plan

With the resource plan and directives set, we activate `Session_Management_Plan` to enforce these rules on the database.

SQL:

ALTER SYSTEM SET resource_manager_plan = 'Session_Management_Plan';

 

By activating this plan, Oracle will apply our specified rules for monitoring idle sessions and automatically terminate them after an hour of inactivity.

Step 5: Verifying and Monitoring Session Status

To ensure the plan is working as expected, monitor session activity by checking which sessions are in `ACTIVE_SESSIONS` versus `IDLE_TERMINATION`.

SQL:

SELECT username, 

       session_id, 

       status, 

       last_call_et AS idle_seconds, 

       consumer_group

FROM   v$session

WHERE  consumer_group IN ('ACTIVE_SESSIONS', 'IDLE_TERMINATION')

ORDER BY idle_seconds DESC;

The `last_call_et` column shows idle time in seconds, while the `consumer_group` column lets you see whether the session is active or set for termination. This query provides visibility into session status, letting you track how effectively idle sessions are managed.

Additional Tips for Using Oracle Resource Manager

1. Testing in Non-Production: Resource Manager settings should be tested in non-production environments first to ensure they align with your workload and that CPU or memory-intensive processes are unaffected by idle session policies.

2. Avoid Overly Aggressive Termination Policies: While terminating idle sessions frees up resources, be cautious with overly aggressive thresholds that could disrupt user sessions critical for business operations.

3. Regularly Review Session Activity: Use `DBA_HIST_ACTIVE_SESS_HISTORY` or `DBA_HIST_RESOURCE_PLAN_DIRECTIVE` views to analyze historical session activity and refine thresholds as needed.

Conclusion:

Oracle Resource Manager offers a structured, policy-based way to manage session resources, making it easier for DBAs to maintain optimal database performance. By automatically handling idle sessions, DBAs can prevent resource bottlenecks and maximize availability for active sessions. With careful setup and monitoring, Resource Manager becomes an invaluable tool in any Oracle DBA’s toolkit.

References: Oracle Database Administrator’s Guide, Oracle 19c Documentation, Oracle Database Resource Manager Concepts and Usage.