【MW】Drop Materialized View Hangs with 'Enq: JI - Contention'

适用的数据库版本 [Release 10.2 to 11.2]

事件：当运行DROP MATERIALIZED VIEW 时 会话hang住。

用下面命令生成跟踪文件（通过任意会话）：

点击(此处)折叠或打开

1. sqlplus /nolog
   
2. connect / as sysdba
   
3. REM The select below is to avoid problems on some releases
   
4. select * from dual;
   
5. oradebug setmypid
   
6. oradebug unlimit
   
7. oradebug dump systemstate 266

我们可以在系统文件中看到下面的等待事件：

点击(此处)折叠或打开

1. WAIT #4: nam='enq: JI - contention' ela= 4961081 name|mode=1246298118 view object #=3893654 0=0 obj#=3012275 tim=30332617495564
   
2. WAIT #4: nam='enq: JI - contention' ela= 2929739 name|mode=1246298118 view object #=3893654 0=0 obj#=3012275 tim=30332620425505

当我们试图删除这个物化视图时由于DBMS_JOB引起了这个等待事件，所以在删除这个JOB后我们就可以删除该物化视图

原因：Due to the JI contention

解决办法：
1.   killled正在运行的job，检查Check DBMS_JOBS_RUNNING 视图.

2.  刷新job并离线，移除job

3.  完成删除，看不到相关job和物化视图。

附等待事件介绍：出处：http://www.askmaclean.com/archives/enq-ji-contention.html

Sessions waiting on this event are waiting on locks held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view.

Solutions

A materialized view cannot be fast refreshed more than once in a given period because it is serialized during the commit phase. Ensure that only one session at a time is performing the refreshes. If there is more than one session, the first session will work normally but the subsequent sessions will wait on “enq: JI – contention”.

Waits on this event can also be caused by on-commit time logic within the materialized view. Normally when a session updates record 1 and commits and then another session updates record 2 and commits, they do not have to wait for each other. However, when using an on commit-time fast refreshable materialized view on top of the table, we do have to wait when two sessions do totally unrelated transactions concurrently against the same table. This is not a problem when the table is modified infrequently or only by a single session, but it can be a big problem when applied to a table that performs a lot of modifications concurrently. Be sure to use on commit-time fast refreshable materialized views for implementing business rules only on tables that are not concurrently accessed or infrequently changed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29487349/viewspace-1789477/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29487349/viewspace-1789477/