MAX_IDLE_TIME and MAX_IDLE_BLOCKER_TIME - Kill Automatically

 

Implementation Summary

We recently introduced two Oracle Database parameters in our Production environment to optimize resource utilization:

• MAX_IDLE_TIME

  • Purpose: Automatically terminates sessions that remain idle beyond a defined threshold (e.g., 30 minutes).
  • Benefit: Prevents accumulation of idle sessions, reducing unnecessary CPU and memory consumption.

• MAX_IDLE_BLOCKER_TIME

  • Purpose: Terminates idle sessions that are blocking other sessions after a shorter threshold (e.g., 15 minutes).
  • Benefit: Improves concurrency and minimizes application hangs caused by blocking locks.

---

Objective

The primary goal of implementing these parameters was to reduce CPU and memory overhead by cleaning up idle and blocking sessions automatically, thereby improving overall database performance and stability.

---

Advantages

• Resource Efficiency: Frees up memory and process slots consumed by idle sessions.
• Improved Throughput: Eliminates blockers quickly, reducing wait times for active sessions.
• Operational Simplicity: Reduces manual intervention for session cleanup.
• Enhanced Stability: Helps avoid hitting session or process limits in high-load environments.

---

Here’s a detailed explanation of the two Oracle parameters you mentioned:

---

1. MAX_IDLE_TIME

• Definition:
  This parameter specifies the maximum number of minutes that a session can remain idle. After this time, the session is automatically terminated.

  • Default Value: 0 (means unlimited idle time)
  • Range: 0 to maximum integer value
  • Modifiable: Yes, dynamically using ALTER SYSTEM
  • Scope: Can be set at the CDB or PDB level
  • Oracle RAC: Different instances can have different values
  • Introduced: Available in Oracle Database 19c and later

• Behavior:
  Applies to all sessions, regardless of whether they are blocking other sessions or not. If a session is idle beyond the specified time, Oracle kills it.

• Advantages:

  • Prevents accumulation of idle sessions that consume resources like memory and process slots.
  • Helps maintain database performance and stability in environments with many client connections (e.g., web apps, connection pools).
  • Reduces risk of hitting SESSIONS or PROCESSES limits.

---

2. MAX_IDLE_BLOCKER_TIME

• Definition:
  Specifies the maximum number of minutes that a blocking session can remain idle. After this time, the blocking session is automatically terminated.

  • Default Value: 0 (means unlimited)
  • Range: 0 to maximum integer value
  • Modifiable: Yes, dynamically using ALTER SYSTEM
  • Scope: Can be set at CDB or PDB level
  • Introduced: Oracle Database 19c (backported) and officially in 21c

• What is a Blocking Session?
  A session that holds resources (like locks) needed by other sessions. For example:

  • Session A updates a row but does not commit or rollback.
  • Session B tries to update the same row and gets stuck waiting.

• Behavior:
  Applies only to blocking sessions. If such a session is idle beyond the specified time, Oracle terminates it automatically.
  Important: The value of MAX_IDLE_BLOCKER_TIME should be less than MAX_IDLE_TIME for it to be effective.

• Advantages:

  • Prevents application hangs caused by idle sessions holding locks.
  • Improves concurrency and throughput by freeing blocked sessions quickly.
  • Reduces DBA intervention for killing blocker sessions manually.
  • Useful in environments with frequent interactive tools (SQL Developer, Toad) where users leave transactions open.

---

Best Practices

• Set MAX_IDLE_TIME for general idle session cleanup (e.g., 30 minutes).
• Set MAX_IDLE_BLOCKER_TIME to a smaller value (e.g., 5–15 minutes) to quickly resolve blocking issues.
• Monitor application behavior before applying these limits to avoid unintended session terminations (especially for batch jobs or long-running queries).

Both are dynamic and can be modified using ALTER SYSTEM without an instance restart. 

For example:

 

Before:-

ALTER SYSTEM SET MAX_IDLE_TIME = 0 SCOPE=BOTH;
ALTER SYSTEM SET MAX_IDLE_BLOCKER_TIME = 0 SCOPE=BOTH;

 

After:-

ALTER SYSTEM SET MAX_IDLE_TIME=30 SCOPE=BOTH sid='*';
ALTER SYSTEM SET MAX_IDLE_BLOCKER_TIME=15 SCOPE=BOTH sid='*';

---

Alert log file details for Killing the sessions :-
KILL SESSION for sid=(2743, 39563):
  Reason = max_idle_time parameter
  Mode = KILL HARD SAFE -/-/NO_REPLAY
  Requestor = PMON (orapid = 2, ospid = 41773, inst = 1)
  Owner = Idle Virtual Circuit
  Result = ORA-0
2025-11-20T04:45:15.159483+01:00
KILL SESSION for sid=(1960, 6001):
  Reason = max_idle_time parameter
  Mode = KILL HARD SAFE -/-/NO_REPLAY
  Requestor = PMON (orapid = 2, ospid = 41773, inst = 1)
  Owner = Idle Virtual Circuit
  Result = ORA-0
KILL SESSION for sid=(2458, 64982):
  Reason = max_idle_time parameter
  Mode = KILL HARD SAFE -/-/NO_REPLAY
  Requestor = PMON (orapid = 2, ospid = 41773, inst = 1)
  Owner = Idle Virtual Circuit
  Result = ORA-0
KILL SESSION for sid=(2709, 36680):
  Reason = max_idle_time parameter
  Mode = KILL HARD SAFE -/-/NO_REPLAY
  Requestor = PMON (orapid = 2, ospid = 41773, inst = 1)
  Owner = Idle Virtual Circuit
  Result = ORA-0
2025-11-20T04:46:18.196496+01:00
KILL SESSION for sid=(2117, 64732):
  Reason = max_idle_time parameter
  Mode = KILL HARD SAFE -/-/NO_REPLAY
  Requestor = PMON (orapid = 2, ospid = 41773, inst = 1)
  Owner = Idle Virtual Circuit
  Result = ORA-0
KILL SESSION for sid=(2122, 14405):
  Reason = max_idle_time parameter
  Mode = KILL HARD SAFE -/-/NO_REPLAY
  Requestor = PMON (orapid = 2, ospid = 41773, inst = 1)
  Owner = Idle Virtual Circuit
  Result = ORA-0

The log entries you shared indicate that Oracle's PMON (Process Monitor) automatically killed several sessions because they exceeded the MAX_IDLE_TIME limit you configured. Here’s what each part means:

---

Breakdown of the Log

• KILL SESSION for sid=(2743, 39563):
  The session identified by SID and Serial# was terminated.

• Reason = max_idle_time parameter:
  The termination was triggered because the session was idle longer than the MAX_IDLE_TIME value (e.g., 30 minutes in your example).

• Mode = KILL HARD SAFE -/-/NO_REPLAY:

  • KILL HARD: The session is forcibly terminated without waiting for cleanup.
  • SAFE: Ensures minimal impact on database consistency.
  • NO_REPLAY: Indicates that the session cannot be replayed (not part of Application Continuity).

• Requestor = PMON (orapid = 2, ospid = 41773, inst = 1):
  PMON is the background process responsible for cleaning up failed or idle sessions.

• Owner = Idle Virtual Circuit:
  The session was idle and had no active work.

• Result = ORA-0:
  Successful termination (ORA-0 means no error).

---

Why This Happens

When MAX_IDLE_TIME is set (e.g., 30 minutes), Oracle monitors sessions. If a session remains idle beyond that threshold:

• PMON kills the session automatically.
• This prevents resource leakage and keeps the database healthy.

---

Advantages of This Behavior

• Automatic Cleanup: No need for manual DBA intervention.
• Resource Optimization: Frees up memory, process slots, and network resources.
• Improved Stability: Reduces risk of hitting SESSIONS or PROCESSES limits.
• Better Performance: Avoids idle sessions hogging resources in connection pools.

---