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]

Lbrary cache latch contention

Lbrary cache latch contention is typically caused by NOT using bind variables. It is due
to excessive parsing of statements.

One way to see if this might be the case in your situation is to run a script like:


create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(1000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2
/



The output of that last query will show you statements that are identical in the shared
pool after all numbers and character string constants have been removed. These
statements -- and more importantly their counts -- are the potential bottlenecks. In
addition to causing the contention, they will be HUGE cpu consumers.