Monday, December 5, 2011

enq JI - contention

This enqueue used to prevent two sessions refreshing a materialized view at the same time. It is unavoidable if the situation allows multiple sessions to refresh at the same time.
Even when multiple sessions are trying to refresh the same materialized view, the refresh process come to a conclusion, unless one of the session that is holding the enqueue dies or killed without the immediate option.
Following test case shows two sessions trying to refresh the same materialized view resulting in high JI contention but both session ultimately finishing the refresh task.

1. Create two tables and the materialized view.
create table x (a number primary key, b number);
create table y (c number,d number, foreign key (c) references x(a));

begin
for i in 1 .. 10
loop
insert into x values (i, i + 10);
end loop;
end;
/

begin
for i in 1 .. 5
loop
insert into y values (i, i + 5);
end loop;
end;
/

create materialized view log on x with rowid;
create materialized view log on y with rowid;

CREATE MATERIALIZED VIEW mvtest (p,q,yid,xid) BUILD IMMEDIATE USING INDEX REFRESH FAST ON demand AS select y.d p,x.b q,y.rowid yid,x.rowid xid from y, x where x.a=y.c;
2. Open three sqlplus sessions for the test. Two (called session 2, 3 from now on) will be running the refresh and other session will be used to synchronize the starting of the to refresh sessions (called session 1 from now on).

3. On session 1 run the following pl/sql code block to create and explicitly lock an id
declare
    i pls_integer;
begin
    i := dbms_lock.request(10);
end;
/
4. On session 2 and 3 run the following code. Both session will hang unable to get the lock id 10 in shared mode (which is held in exclusive mode by session 1). However once they obtain the lock both will try to refresh (fast) the materialized view 1000 times
declare
    i pls_integer;
begin
    i := dbms_lock.request(10,DBMS_LOCK.S_MODE);

    for i in 1 .. 1000
    loop
     dbms_snapshot.refresh('mvtest','F'); 
    end loop;
end;
/
5. Release the lock on id 10 by running the following code which will start the refresh process
declare
    j pls_integer;
begin
    j := dbms_lock.release(10);
end;
/
6. Observer the JI contention on em console or APConsole

If the dba_waiters view was queried repeatedly while the refresh process is going on, it could be observed that sid of session 2 and session 3 alternating between holding session and waiting session.
Both session eventually will complete and return to sql prompt.
However as said earlier if the refresh sessions are serialized this will not result in contention and refresh could complete much quicker. (The last bit of CPU spike at the end of the graph above).

Some useful metalink notes.
What is JI enqueue used for? [ID 580001.1]
Materialized View Refresh is Hanging With JI Contention [ID 1358453.1]
Monitoring Locks During Materialized View Refreshes [ID 258258.1]
MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring [ID 258252.1]
Performance Degradation and JI Lock Contention Observed During Batch Load of Tables With On-Commit Materialized Views [ID 371869.1]