Understanding and Resolving “enq: JI – contention” in Materialized Views
Recently, I ran into an interesting (and slightly frustrating) contention issue while trying to drop a materialized view in Oracle Database 19c. The operation was hanging longer than expected, which is always a red flag. After digging into the session waits, I noticed a wait event I hadn’t encountered before:
enq: JI - contention
This turned into a good learning moment, so I’m documenting the root cause, how I diagnosed it, and how I resolved it.
What is enq: JI – contention?
The enq: JI – contention wait event in Oracle is related to materialized view (MV) refresh serialization.
In simple terms:
- Oracle uses a JI enqueue (lock) to ensure that only one session can refresh a materialized view at a time
- If multiple sessions attempt to refresh the same MV concurrently, they get serialized
- The sessions waiting for the lock will show this wait event
This is expected behavior designed to maintain data consistency.
Common Causes
Here are the most common scenarios where you’ll see this wait:
1. Concurrent Refreshes
Multiple sessions trying to run:
DBMS_MVIEW.REFRESH
on the same materialized view at the same time.
2. FAST REFRESH ON COMMIT
If your MV is configured as:
REFRESH FAST ON COMMIT
Then:
- Every commit on the base table triggers a refresh
- High DML activity = multiple concurrent refresh attempts
- This can quickly lead to contention
3. Administrative Operations
Operations like:
- ALTER MATERIALIZED VIEW
- DROP MATERIALIZED VIEW
also require the same lock and can get blocked by active refresh sessions.
How I Diagnosed the Issue
While trying to drop the MV, I noticed it was hanging. Checking active sessions revealed the wait event:
enq: JI – contention
This immediately pointed to a materialized view refresh conflict.
To confirm, I identified the locking session using:
SELECT s.sid, s.serial#, s.username, s.status, l.type
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
WHERE l.type = 'JI' AND l.lmode = 6;
Key insight:
- LMODE = 6 → session holding the lock (exclusive)
- REQUEST = 6 → session waiting for the lock
The Fix: Stopping the Blocking Refresh
Once I identified the session performing the refresh, I terminated it:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
Important Note:
- If the refresh was large, the session may remain in KILLED state while rolling back
- You must wait for rollback to complete before the lock is released
Dropping the Materialized View
After the lock was released, the drop worked immediately:
DROP MATERIALIZED VIEW your_mv_name;
Preventing This in the Future
This issue is avoidable with some design and operational adjustments.
1. Avoid Overlapping Refresh Jobs
If using scheduled jobs:
- Ensure refresh jobs don’t overlap
- Check both DBMS_SCHEDULER and legacy DBMS_JOB
2. Reconsider ON COMMIT Refresh
If your system has heavy DML:
- Avoid FAST REFRESH ON COMMIT
- Switch to:
REFRESH ON DEMAND
Then schedule controlled refreshes instead.
3. Monitor Long-Running Refreshes
Keep an eye on:
- Long refresh durations
- Sessions holding JI locks
4. Investigate Internal Tables (Advanced)
If contention is frequent, review:
- SNAP$
- SNAP_REFTIME$
These track MV refresh metadata and timing.
Pro Tip
Even after killing the session, if your DROP MATERIALIZED VIEW still hangs:
- Check for locks on base tables
- The drop operation may be waiting to unregister the MV from logs
Final Thoughts
The enq: JI – contention wait isn’t a bug—it’s Oracle doing its job to enforce consistency. But in high-concurrency environments, especially with ON COMMIT refreshes, it can become a bottleneck.
In my case, identifying and killing the refresh session resolved the issue quickly, but the real takeaway was architectural:
Be intentional about how and when materialized views refresh.
If you’ve run into similar MV contention issues or have different strategies for handling refreshes at scale, I’d be interested to hear how you approach it.