Oracle enq: TM – Contention Troubleshooting and Fix

Introduction

Oracle enq: TM – Contention Troubleshooting is an important part of Oracle Database performance and locking investigations. When an application SQL statement runs for a long time, one possible cause is a database session holding a lock that another session needs to proceed.

During an incident, an Oracle DBA may identify a blocking session and execute ALTER SYSTEM KILL SESSION. However, the session can remain visible with a status of KILLED, and the application query may continue waiting.

In this article, we will discuss a real-world Oracle DBA troubleshooting scenario involving the enq: TM – contention wait event, a blocking session in KILLED status, and an application query that continued running for approximately 15 minutes.

We will cover how to:

1. Identify a long-running SQL query.

2. Investigate the Oracle enq: TM – contention wait event.

3. Identify the blocking session.

4. Check a killed Oracle session and its associated server process.

5. Investigate the operating system process ID.

6. Verify whether the blocking issue has been resolved.

Issue Description: Oracle enq: TM – Contention

During an application support bridge call, the application team reported that an SQL statement was taking longer than expected to complete.

The initial investigation identified stale table statistics. The DBA gathered the table statistics and also identified a blocking session.

The blocking session was marked for termination using the following command:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

After executing this command, the session appeared in KILLED status.

However, when the application team executed the SQL statement again, the query continued running for approximately 15 minutes.

The DBA joined the bridge call again to investigate the issue further.

During the investigation, the DBA identified the following symptoms:

1. The application query was running for approximately 15 minutes.

2. The session was waiting on enq: TM – contention.

3. Oracle reported a blocking session.

4. The blocking session was already displayed as KILLED.

5. The application query did not complete immediately after the normal session-termination command.

6. After the associated operating-system process was terminated through the approved procedure, the application query completed within seconds.

This scenario demonstrates why Oracle DBAs should investigate the actual wait event and blocking relationship instead of assuming that a session has completely terminated just because its status is KILLED.

Important: Stale table statistics and database locking are separate issues. Gathering statistics can improve query-plan selection, but it does not automatically release locks held by an active or terminating transaction.

Let's start the step-by-step Oracle enq: TM – Contention Troubleshooting and Fix

Step 1) Identify the Long-Running SQL Query

The first step in Oracle enq: TM – contention troubleshooting is to identify the application session and determine its current wait event.

Connect to the database using an appropriately privileged DBA account and execute:

set pagesize 999
set linesize 999
col logontime format a20
col username format a17
col terminal format a11
col program format a49
col osuser format a20
col sid format 9999
col runtime for a30
SELECT sid,serial#, to_char(logon_time,'DD/MM/YYYY HH24:MI:SS') logontime, username,terminal,osuser, SQL_HASH_VALUE, SQL_ID, ltrim(to_char(floor(LAST_CALL_ET/3600), '09')) ||':'|| ltrim(to_char(floor(mod(LAST_CALL_ET, 3600)/60), '09')) ||':'|| ltrim(to_char(mod(LAST_CALL_ET, 60), '09')) RUNTIME FROM v$session WHERE username IS NOT NULL and status ='ACTIVE' ORDER BY RUNTIME DESC;

Output

Step 2) Investigate the Oracle enq: TM – Contention Wait Event

After identifying the application session, check the wait event associated with it.

SET LINES 999 PAGES 999
col sql_id for a15
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
col status for a10
col machine for a20
COL PROGRAM FOR A25
COL OSUSER FOR A15
COL USERNAME FOR a20
col EVENT for a25
col STATE for a20
col Module for a25
SELECT sid,sql_id,serial#,STATUS, osuser,username,last_call_et, logon_time,state,event
FROM gv$session where STATUS='ACTIVE' and USERNAME not in ('PUBLIC') order by logon_time;

Output

Step 3) Identify the Oracle Blocking Session

Once the waiting session has been identified, determine which session Oracle reports as the blocker.

set lines 200 pages 200
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

Output

Step 4) Verify the Killed Oracle Session

A session marked KILLED is not necessarily the same as a server process that has completely terminated.

Oracle may mark a session for termination while additional cleanup or transaction rollback is still required.

Use the following query to inspect the blocking session and its associated Oracle process:

SET LINES 999 PAGES 999
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
col status for a10
col machine for a20
col username for a20
COL PROGRAM FOR A25
COL OSUSER FOR A15
COL EVENT for a20
SELECT sid,prev_sql_id,serial#,STATUS,osuser,MACHINE,username,event,sql_id,last_call_et,logon_time  FROM gv$session WHERE SID IN ('920') order by logon_time;

Output

Step 5) Retry the Normal Oracle Session Termination

The standard command for terminating an Oracle session is:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

For Oracle RAC, use the appropriate instance-qualified syntax supported by your Oracle Database version:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#,@INST_ID' IMMEDIATE;

Output

If the session is already marked KILLED, repeating the command may not immediately terminate its server process or release the resource needed by the waiting session.

At this stage, verify the current session status and investigate whether Oracle is still performing transaction rollback or cleanup.

Do not assume that a session is safe to terminate at the OS level simply because its status is KILLED. Confirm the blocking relationship and process identity before proceeding.

Step 6) Investigate the Oracle Server Process at the OS Level

If the killed session continues to be associated with the blocking problem, the DBA may need to investigate the operating-system process.

Command to find the SPID from SID.

set lines 200
col sid format 99999
col username format a15
col osuser format a15
select a.sid, a.serial#, a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;

Output

Precautions before terminating the process

1. Obtain the required approval for the production environment.

2. Verify the session-to-process mapping immediately before termination.

3. Confirm that the process is not an Oracle background process.

4. Follow the organization's production runbook and Oracle support guidance.

5. Consider the impact of transaction recovery, rollback, and other sessions.

If the approved procedure permits a normal OS-level termination, the command will be:

kill -9 <OS_PID>

Output

Warning: Never run kill -9 blindly against an Oracle process. It prevents the process from performing a normal shutdown and cleanup. Oracle may need to recover the affected transaction and clean up the associated resources. Shared-server configurations require particular caution because one process can serve multiple sessions.

Step 7) Verify That Oracle Blocking Has Cleared

After the process-termination action, check the application session again.

SET LINES 999 PAGES 999
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
col status for a10
col machine for a20
col username for a20
COL PROGRAM FOR A25
COL OSUSER FOR A15
COL EVENT for a20
SELECT sid,prev_sql_id,serial#,STATUS,osuser,MACHINE,username,event,sql_id,last_call_et,logon_time  FROM gv$session WHERE SID IN ('920') order by logon_time;

Check whether:

1. The original blocking relationship has cleared.

2. The application session has resumed execution.

3. The application SQL statement has completed.

4. Another session has become the blocker.

5. Any other sessions remain waiting on the same resource.

If the original blocking session has disappeared, that does not by itself confirm that every transaction or application issue has been resolved. Verify the waiting session and application result.

If the query is still running, investigate its current SQL ID, wait event, execution plan, and any new blocking relationship.

Step 8) Confirm Application Query Completion

After the blocking issue was resolved in this incident, the application query completed successfully within seconds.

The DBA should confirm the result with the application team and verify the database session state.

If the query completes, record the resolution details, including:

1. The application session SID and SQL ID.

2. The wait event observed.

3. The blocking session's SID and SERIAL#.

4. The operating-system PID, if applicable.

5. The action taken and its approval.

6. The final status confirmed by the application team.

This information helps the DBA team understand the incident and supports future Oracle blocking-session troubleshooting.

Root Cause Analysis

The investigation highlighted the difference between marking a session for termination and the complete termination and cleanup of its associated server process.

The observations in this incident were:

1. Stale statistics had been identified, and table statistics were gathered.

2. A blocking session was identified.

3. The blocking session was marked for termination.

4. The session remained visible with KILLED status.

5. The application continued to experience a long-running SQL query.

6. Further investigation identified the associated operating-system process.

7. Following the approved process-termination action and Oracle cleanup, the application query completed within seconds.

The immediate issue was consistent with a blocking resource that had not yet been released. The exact underlying cause should be confirmed using the available session, lock, and transaction evidence.

Stale statistics can affect query plans and performance, but they should not be reported as the proven cause of Oracle enq: TM – Contention without supporting evidence.

Conclusion

Oracle enq: TM – Contention Troubleshooting requires more than identifying a wait event or executing a session-kill command. The DBA must determine which session is waiting, identify the actual blocker, verify the state of the killed session, and investigate the associated server process when necessary.

In this real-world incident, the blocking session remained visible in KILLED status while the application query continued running. Further process-level investigation helped resolve the issue, and the application query completed successfully within seconds.

The key lesson is to verify the blocking relationship, process identity, and final application result instead of assuming that ALTER SYSTEM KILL SESSION has immediately released every resource held by the session.