Materialized View Concurrent Refresh feature in Oracle 23c

 The simultaneous update of Materialized Views on-commit refresh through different sessions can cause an event called enq: JI - contention because this type of MVs are updated serially by default and cannot be updated concurrently.

In the following section, we have inserted data into the MV reference table through two sessions at the same time, which caused us to see a large number of enq: JI - contention events:

SQL> create materialized view auther_contry_mv

refresh fast on commit

as

select country,count(*) from author_tbl group by country;

Materialized view created.

Session 1:

SQL> begin

for i in 10000 ..19999 loop

insert into author_tbl values(i,'Rahmat Rabbani','m','Oracle DBA','IRAN');

commit;

end loop;

end;

/

PL/SQL procedure successfully completed.

Elapsed: 00:03:00.18

Session 2:

begin

for i in 1 ..9999 loop

insert into author_tbl values(i,'Rahmat Rabbani','m','Oracle DBA','IRAN');

commit;

end loop;

end;

/

PL/SQL procedure successfully completed.

Elapsed: 00:03:00.25

In version 23c, Oracle has eliminated this limitation through the Materialized View Concurrent Refresh feature, and by using this feature, this type of MVs can be updated concurrently.

To use this feature, the statement enable concurrent refresh must be added to the command to create MV, then we will create an MV using this statement and re-execute the above PL/SQL block, we will see that the execution time will be from 3 seconds to 2 seconds has been reduced and there is no news of enq: JI – contention:

SQL> create materialized view auther_contry_mv

refresh fast on commit

enable concurrent refresh

as

select country,count(*) from author_tbl group by country;

Materialized view created.

--session 1:

SQL> begin

for i in 1 ..9999 loop

insert into author_tbl values(i,'Rahmat Rabbani','m','Oracle DBA','IRAN');

commit;

end loop;

end;

/

PL/SQL procedure successfully completed.

Elapsed: 00:02:23.02

Session 2:

SQL> begin

for i in 10000 ..19999 loop

insert into author_tbl values(i,'Rahmat Rabbani','m','Oracle DBA','IRAN');

commit;

end loop;

end;

/

PL/SQL procedure successfully completed.

Elapsed: 00:02:22.48

*********************************EOD*************************************