I recently ran into an interesting problem that I thought I would share. A customer database was experiencing extremely high waits on “Enq: DX-contention” and “inactive transaction branch”. 

This script will list the Top 9 wait events over the past hour:

select * from (
SELECT
h.event “Wait Event”,
SUM(h.wait_time + h.time_waited)/1000000 “Total Wait Time”
FROM
v$active_session_history h,
v$event_name e
WHERE
h.sample_time < (select max(sample_time) from v$active_session_history)
and h.sample_time > (select max(sample_time)-1/24 from v$active_session_history)
AND h.event_id = e.event_id
AND e.wait_class <>’Idle’
GROUP BY h.event
ORDER BY 2 DESC)
where rownum <10;

As you can see, these two events are orders of magnitude greater than any of the others.  I ran this query a few times in a row and each time, the “Total Wait Time” value increased for the top two events. This meant that there was at least one process currently waiting. So my first thought was to check which SQL statements are responsible for such huge waits. I ran this script which looks over the past hour for the SQL statements that experienced these specific wait events.

SELECT
a.sql_text,
SUM(h.wait_time + h.time_waited)/1000000 “Total Wait Time”
FROM
v$active_session_history h,
v$event_name e,
v$sqlarea a
WHERE
h.sample_time < (select max(sample_time) from v$active_session_history)
and h.sample_time > (select max(sample_time)-1/24 from v$active_session_history)
AND h.event_id = e.event_id
and h.sql_id=a.sql_id
AND e.wait_class <> ’Idle’
and event in (’enq: DX - contention’,'inactive transaction branch’)
group by a.sql_text
ORDER BY 2 asc;

Surprisingly, this yielded 0 rows! This was confusing. so I looked at it by session, rather than by SQL:

SELECT
h.session_id,
SUM(h.wait_time + h.time_waited)/1000000 “Total Wait Time”
FROM
v$active_session_history h,
v$event_name e
WHERE
h.sample_time < (select max(sample_time) from v$active_session_history)
and h.sample_time > (select max(sample_time)-1/24 from v$active_session_history)
AND h.event_id = e.event_id
AND e.wait_class <> ’Idle’
and event in (’enq: DX - contention’,'inactive transaction branch’)
group by h.session_id
ORDER BY 2 asc;

This returned 3 rows. Another query showed some more details:

select prev_sql_id, sql_id, blocking_session
from v$session where sid in (184,199,174)

This showed that both prev_sql_id and sql_id were blank (no wonder the join to v$sqlarea failed) and that they were all being blocked by other sessions. A little more investigation revealed that the blocking sessions were long-running data extracts which were themselves waiting on “SQL*Net more data to client”.  At this point, I decided to check Oracle Support for any bugs that we may be hitting and sure enough, I found one that matched pretty closely to what I was seeing.  Bug 8552942 which causes processes to spin indefinitely waiting on “enq-DX Contention” and “inactive transaction branch”  is fixed in version 11.2.  Unfortunately, this database is version 10.2.0.4 so there is no fix currently available.