Drop Materialized View Hangs with 'Enq: JI - Contention' (文档 ID 1126123.1)

APPLIES TO: 

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.

SYMPTOMS

When running a DROP MATERIALIZED VIEW the session hangs.

The hang has been reproduced and this time we have some trace files generated:

Connect to any session and run the following commands:

sqlplus /nolog
connect / as sysdba
REM The select below is to avoid problems on some releases
select * from dual; 
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266

In the system state we can see the following wait events.

*** 2010-05-11 13:07:10.310
WAIT #4: nam='enq: JI - contention' ela= 4961081 name|mode=1246298118 view object #=3893654 0=0 obj#=3012275 tim=30332617495564
WAIT #4: nam='enq: JI - contention' ela= 2929739 name|mode=1246298118 view object #=3893654 0=0 obj#=3012275 tim=30332620425505

This wait event was determined to be due to a DBMS_JOB entry to refresh the mview we are trying to drop.  So after removing this job we could then successfully drop the mview.

CAUSE

Due to the JI contention.

SOLUTION

1.  Temporarily break the dbms_job that is responsible for executing the refreshes, so that no more will be started until the issue is resolved.

-- Find the dbms_job:
col broken for a26
select job, broken, failures 
from dba_jobs
where upper(what) like '%%';  -- substitute with the correct name of the mview

-- Break the job temporarily:

exec dbms_job.broken(,true); -- replace with the number of the job found in the first query

 

2. Find the SID of the job holding the JI enqueue.

column owner format a15
column username format a15
column mview format a15
select o.owner, o.object_name mview, username, s.sid
from v$lock l, dba_objects o, v$session s
where o.object_id=l.id1 and
l.type='JI' and
l.lmode=6 and
s.sid=l.sid and
o.object_type='TABLE';


OWNER            MVIEW          USERNAME         SID
--------------- --------------- --------------- ----------
SCOTT           DEPT            SCOTT            16

 

 

3.  You now need to choose between letting the job holding the JI enqueue complete, or killing it.   If you kill it, it will rollback.   Use the following note to kill the currently running refresh:

Document 341842.1 How to kill a hung automated materialized view refresh job


4.  Run the query in step #2 again to make sure the enqueue is now held by the job that was previously waiting for the enqueue (value of SID should have changed).  Once that refresh has completed, you can unbreak the job, so it will automatically run again on its own.

-- Unbreak the job

exec dbms_job.broken(,false); -- replace with the number of the job you previously broke

-- Run the job once if the NEXT_DATE has been exceeded

exec dbms_job.run();