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

適用的資料庫版本[Release 10.2 to 11.2]

事件：當執行DROP MATERIALIZED VIEW 時 會話hang住。

用下面命令生成跟蹤檔案（透過任意會話）：

我們可以在系統檔案中看到下面的等待事件：



當我們試圖刪除這個物化檢視時由於DBMS_JOB引起了這個等待事件，所以在刪除這個JOB後我們就可以刪除該物化檢視

原因：Due to the JI contention

解決辦法：
1.   killled正在執行的job，檢查Check DBMS_JOBS_RUNNING 檢視.

2.  重新整理job並離線，移除job

3.  完成刪除，看不到相關job和物化檢視。

附等待事件介紹：出處： />

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