A materialized view with REFRESH FAST ON COMMIT is always up to date: every transaction that changes the base table also updates the MV before its commit completes. The price is that these refreshes are serialized. When several sessions commit at the same time, they queue behind each other on the JI enqueue, and you see enq: JI - contention in your wait events.
Oracle Database 23ai added a fix: ENABLE CONCURRENT REFRESH, which lets on-commit refreshes from different sessions run at the same time. I first wrote about this feature in October 2023. This post replaces that one with my own test on Oracle AI Database 26ai, and the results were more interesting than “it is faster”.
- Setup: 2 sessions, 5,000 single-row inserts each, commit after every row, on-commit fast refresh MV with
COUNT(*) ... GROUP BY country. Oracle AI Database 26ai Enterprise Edition 23.26.1. - Different MV rows (each session inserts a different country): concurrent refresh cut elapsed time by about 24% (18% and 29% in two full runs) and the JI-class wait by about 80%.
- Same MV row (both sessions insert the same country): only about 12% faster (9% and 15%). The sessions stop waiting on
enq: JIand start waiting on the MV row lock,enq: TX - row lock contention, instead. - The hidden wait: in my 26ai sandbox,
V$SESSION_EVENTdid not listenq: JI - contentionat all. It was counted under “events in waitclass Other”. ASH showed the real event name. - Correctness: in all 8 runs (4 scenarios, tested twice) the MV matched the base table exactly.
Why on-commit refresh serializes
With REFRESH FAST ON COMMIT, the commit of every transaction that touched the base table also applies its changes (from the materialized view log) to the MV. By default, only one transaction at a time may refresh a given on-commit MV. Oracle protects this with the JI enqueue, so a second committing session waits on enq: JI - contention until the first has finished its refresh. With many small transactions, which is typical for OLTP, that queue becomes the bottleneck.
The animation shows the three cases I tested: the default serialized refresh, concurrent refresh with sessions updating different MV rows, and concurrent refresh with both sessions updating the same MV row.
ENABLE CONCURRENT REFRESH removes that one-at-a-time rule. Two transactions can now refresh the MV at the same time, as long as they do not need to change the same MV rows. That condition turned out to matter.
The test setup
I ran everything in a LiveLabs sandbox: Oracle AI Database 26ai Enterprise Edition, release 23.26.1, in a pluggable database, as a dedicated lab user. The full script is at the end of the post.
1. Base table, materialized view log and MV
Fast refresh needs a materialized view log on the base table. For an aggregate MV like this one, the log needs ROWID, SEQUENCE, the grouping column and INCLUDING NEW VALUES:
CREATE TABLE author_tbl (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
gender CHAR(1),
job VARCHAR2(30),
country VARCHAR2(30));
CREATE MATERIALIZED VIEW LOG ON author_tbl
WITH ROWID, SEQUENCE (country) INCLUDING NEW VALUES;
-- default: serialized on-commit refresh
CREATE MATERIALIZED VIEW author_country_mv
REFRESH FAST ON COMMIT
AS SELECT country, COUNT(*) AS cnt FROM author_tbl GROUP BY country;
-- 23ai / 26ai: concurrent on-commit refresh
CREATE MATERIALIZED VIEW author_country_mv
REFRESH FAST ON COMMIT
ENABLE CONCURRENT REFRESH
AS SELECT country, COUNT(*) AS cnt FROM author_tbl GROUP BY country;
The data dictionary has a new column for it. You can check the setting with:
SELECT mview_name, refresh_mode, refresh_method, concurrent_refresh_enabled
FROM user_mviews;
Not every MV qualifies. If the shape of your on-commit MV does not support concurrent refresh, the CREATE fails with ORA-32384.
2. Two sessions that really start together
To make the test repeatable, I did not use two terminal windows. A small driver procedure creates two DBMS_SCHEDULER jobs and enables them in the same call, so both sessions start within milliseconds of each other. Each job runs this worker, which inserts one row and commits, 5,000 times, then records its elapsed time and its own wait events from V$SESSION_EVENT:
CREATE OR REPLACE PROCEDURE load_rows (p_run IN VARCHAR2, p_session IN NUMBER,
p_from IN NUMBER, p_to IN NUMBER,
p_country IN VARCHAR2) AS
t0 TIMESTAMP := SYSTIMESTAMP;
BEGIN
FOR i IN p_from .. p_to LOOP
EXECUTE IMMEDIATE 'INSERT INTO author_tbl VALUES (:1, :2, ''M'', ''Oracle DBA'', :3)'
USING i, 'Author ' || i, p_country;
COMMIT; -- every commit refreshes the MV
END LOOP;
-- save elapsed time and this session's non-idle waits
INSERT INTO lab_waits
SELECT p_run, p_session, event, total_waits, ROUND(time_waited_micro/1e6, 2)
FROM v$session_event
WHERE sid = SYS_CONTEXT('USERENV', 'SID') AND wait_class <> 'Idle';
COMMIT;
END;
/
3. Four runs
| Run | MV refresh | What the two sessions insert | MV rows they touch |
|---|---|---|---|
| A | Serial (default) | Both insert country = INDIA | The same MV row |
| B | Concurrent | Both insert country = INDIA | The same MV row |
| C | Serial (default) | Session 1 INDIA, session 2 IRELAND | Different MV rows |
| D | Concurrent | Session 1 INDIA, session 2 IRELAND | Different MV rows |
Every run starts from a freshly created table, MV log and MV, and ends with a check that the MV matches SELECT country, COUNT(*) FROM author_tbl GROUP BY country exactly.
Results
| Run | MV refresh | MV rows | Elapsed, test 1 | Elapsed, test 2 | Average | Wait class Other (avg) | Lock waits (avg) | MV correct |
|---|---|---|---|---|---|---|---|---|
| A | Serial | same | 41.3 s | 38.6 s | 40.0 s | 17.1 s (≈5,300–5,500 waits) | none | YES, YES |
| B | Concurrent | same | 37.8 s (−9%) | 32.8 s (−15%) | 35.3 s (−12%) | 4.4 s | enq: TX - row lock contention 3.8 s | YES, YES |
| C | Serial | different | 42.0 s | 43.0 s | 42.5 s | 18.2 s (≈5,000–5,500 waits) | none | YES, YES |
| D | Concurrent | different | 34.4 s (−18%) | 30.6 s (−29%) | 32.5 s (−24%) | 3.6 s | row cache lock 2.9 s | YES, YES |
I ran the whole test twice, about 30 minutes apart. Each time is the average of the two sessions (they always finished within 0.1 s of each other); the wait columns average both sessions over both tests. The exact percentages moved between the two tests, which is normal on a small shared sandbox, but the pattern was the same both times.
Finding 1: the JI wait was there, under another name
My first surprise was that V$SESSION_EVENT showed zero enq: JI - contention waits, even in the serial runs. What it did show was one line called “events in waitclass Other”, with about one wait per commit and 17–18 seconds of wait time per session: over 40% of the elapsed time. enq: JI - contention belongs to wait class Other, so I checked Active Session History during a serial run:
SELECT NVL(event, 'ON CPU') AS event, COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time > SYSTIMESTAMP - INTERVAL '90' SECOND
AND user_id = (SELECT user_id FROM dba_users WHERE username = 'MVLAB')
GROUP BY event
ORDER BY 2 DESC;
-- serial run (A) -- concurrent run (D)
EVENT SAMPLES EVENT SAMPLES
--------------------- ------- --------------------- -------
ON CPU 45 ON CPU 65
enq: JI - contention 39 row cache lock 6
enq: JI - contention 5
enq: JI - MV setup 4
ASH names the real event: in the serial run, the sessions spent almost half their time on enq: JI - contention. With concurrent refresh, that dropped to a handful of samples (plus enq: JI - MV setup when the MV was created at the start of the run).
V$SESSION_EVENT or V$SYSTEM_EVENT alone. If “events in waitclass Other” is high, look at ASH (Diagnostics Pack) or a SQL trace to see which event it really is.Finding 2: different MV rows, a real gain
When the two sessions updated different MV rows (run D), the JI-class wait fell from about 18 s to about 3.6 s per session (−80%) and the elapsed time dropped by 18% in the first test and 29% in the second. A smaller new wait appeared, row cache lock (about 2.9 s per session on average), but the net effect was clearly positive.
Finding 3: the same MV row is still a queue
When both sessions inserted the same country (run B), they both had to update the same row of the MV. Concurrent refresh lets them start at the same time, but the second one then waits for the first one's row lock: about 2,300 to 2,700 enq: TX - row lock contention waits per session. The JI queue was replaced by a row-lock queue, and the gain shrank to 9% and 15% in the two tests.
That is the key point for real systems: concurrent refresh helps when concurrent transactions change different groups of the MV. A “hot” aggregate row (one total, one status count, one popular key) is still a single point of serialization.
When to use it
- Good fit: on-commit aggregate MVs where concurrent transactions usually touch different groups (different customers, branches, products, dates), and you see
enq: JI - contentionin ASH or AWR. - Limited help: MVs where most transactions update the same few rows. Expect the wait to move to
enq: TX - row lock contention. - Not a fix for the design: every commit still does the refresh work. For high-rate OLTP, also ask whether the MV needs to be refreshed on commit at all, or whether an on-demand fast refresh every few seconds or minutes would do.
- Test first: my test used 2 sessions on a small sandbox, and the gain varied between my two test runs. Your gain depends on session count, MV shape and how your keys are spread. Run something like the script below with your own MV before changing production.
The full lab script
It does not touch your existing data: it creates one lab user with its own tables, and you can drop that user afterwards. It needs Oracle Database 23ai / 26ai or later. Part 0 runs as SYS in a PDB; Part 1 runs as the lab user; Part 2 prints the results.
mv_concurrent_lab.sql (click to expand)
-- =====================================================================
-- mv_concurrent_lab.sql
-- Lab: on-commit fast refresh MV, serial vs ENABLE CONCURRENT REFRESH
-- Needs Oracle Database 23ai / 26ai or later. Tested on Oracle AI Database 26ai
-- Enterprise Edition 23.26.1 (LiveLabs sandbox).
-- Lab database only. No client data. No passwords stored in this file.
--
-- PART 0: run ONCE as SYS in the PDB (e.g. sqlplus / as sysdba, then
-- ALTER SESSION SET CONTAINER = <your_pdb>;)
-- PART 1: run as MVLAB (connect mvlab@<your_pdb_service>), it takes
-- a few minutes; PART 2 prints the results.
-- =====================================================================
-- ======================= PART 0 (as SYS) =============================
-- SQL*Plus asks "Enter value for mvlab_pw:" - type a password (letters and digits)
CREATE USER mvlab IDENTIFIED BY "&mvlab_pw" DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE MATERIALIZED VIEW,
CREATE PROCEDURE, CREATE JOB TO mvlab;
GRANT SELECT ON sys.v_$session_event TO mvlab;
GRANT SELECT ON sys.v_$version TO mvlab;
UNDEFINE mvlab_pw
-- now: CONNECT mvlab@<host>:<port>/<pdb_service> (it will prompt for the password)
-- ======================= PART 1 (as MVLAB) ===========================
SET SERVEROUTPUT ON LINESIZE 220 PAGESIZE 200 FEEDBACK ON
-- lab tables (safe to re-run: existing tables are emptied, not recreated)
DECLARE
PROCEDURE make (p_name IN VARCHAR2, p_ddl IN VARCHAR2) AS
n NUMBER;
BEGIN
SELECT COUNT(*) INTO n FROM user_tables WHERE table_name = p_name;
IF n = 0 THEN EXECUTE IMMEDIATE p_ddl;
ELSIF p_name <> 'AUTHOR_TBL' THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_name;
END IF;
END;
BEGIN
make('LAB_RESULTS', 'CREATE TABLE lab_results (run_id VARCHAR2(1), session_no NUMBER, rows_loaded NUMBER, '
|| 'start_ts TIMESTAMP, end_ts TIMESTAMP, ji_waits NUMBER, ji_secs NUMBER)');
make('LAB_WAITS', 'CREATE TABLE lab_waits (run_id VARCHAR2(1), session_no NUMBER, event VARCHAR2(64), '
|| 'total_waits NUMBER, secs_waited NUMBER)');
make('LAB_RUNS', 'CREATE TABLE lab_runs (run_id VARCHAR2(1), mv_mode VARCHAR2(20), country_mode VARCHAR2(20), '
|| 'rows_per_session NUMBER, mv_ok VARCHAR2(3))');
-- the base table must exist before load_rows is compiled (setup_mv rebuilds it per run)
make('AUTHOR_TBL', 'CREATE TABLE author_tbl (id NUMBER PRIMARY KEY, name VARCHAR2(50), gender CHAR(1), '
|| 'job VARCHAR2(30), country VARCHAR2(30))');
END;
/
-- Rebuilds base table + MV log + MV, with or without concurrent refresh
CREATE OR REPLACE PROCEDURE setup_mv (p_concurrent IN VARCHAR2) AS
BEGIN
BEGIN EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW author_country_mv';
EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -12003 THEN RAISE; END IF; END;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE author_tbl PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -942 THEN RAISE; END IF; END;
EXECUTE IMMEDIATE 'CREATE TABLE author_tbl (id NUMBER PRIMARY KEY, name VARCHAR2(50), '
|| 'gender CHAR(1), job VARCHAR2(30), country VARCHAR2(30))';
-- fast refresh needs an MV log on the base table
EXECUTE IMMEDIATE 'CREATE MATERIALIZED VIEW LOG ON author_tbl '
|| 'WITH ROWID, SEQUENCE (country) INCLUDING NEW VALUES';
EXECUTE IMMEDIATE 'CREATE MATERIALIZED VIEW author_country_mv '
|| 'REFRESH FAST ON COMMIT '
|| CASE WHEN p_concurrent = 'Y' THEN 'ENABLE CONCURRENT REFRESH ' END
|| 'AS SELECT country, COUNT(*) AS cnt FROM author_tbl GROUP BY country';
END;
/
-- One worker: insert + commit row by row (each commit refreshes the MV)
CREATE OR REPLACE PROCEDURE load_rows (p_run IN VARCHAR2, p_session IN NUMBER,
p_from IN NUMBER, p_to IN NUMBER,
p_country IN VARCHAR2) AS
t0 TIMESTAMP := SYSTIMESTAMP;
v_waits NUMBER;
v_us NUMBER;
BEGIN
FOR i IN p_from .. p_to LOOP
EXECUTE IMMEDIATE 'INSERT INTO author_tbl VALUES (:1, :2, ''M'', ''Oracle DBA'', :3)'
USING i, 'Author ' || i, p_country;
COMMIT;
END LOOP;
SELECT NVL(SUM(total_waits), 0), NVL(SUM(time_waited_micro), 0)
INTO v_waits, v_us
FROM v$session_event
WHERE sid = SYS_CONTEXT('USERENV', 'SID')
AND event = 'enq: JI - contention';
INSERT INTO lab_results
VALUES (p_run, p_session, p_to - p_from + 1, t0, SYSTIMESTAMP, v_waits, ROUND(v_us/1e6, 2));
INSERT INTO lab_waits
SELECT p_run, p_session, event, total_waits, ROUND(time_waited_micro/1e6, 2)
FROM v$session_event
WHERE sid = SYS_CONTEXT('USERENV', 'SID')
AND wait_class <> 'Idle';
COMMIT;
END;
/
-- One test run: rebuild MV, start 2 scheduler jobs at the same moment, wait
CREATE OR REPLACE PROCEDURE run_test (p_run IN VARCHAR2, p_concurrent IN VARCHAR2,
p_same_country IN VARCHAR2, p_rows IN NUMBER) AS
v_done NUMBER;
v_fail NUMBER;
v_bad NUMBER;
v_country VARCHAR2(30);
BEGIN
DELETE lab_results WHERE run_id = p_run;
DELETE lab_waits WHERE run_id = p_run;
DELETE lab_runs WHERE run_id = p_run;
COMMIT;
setup_mv(p_concurrent);
FOR s IN 1 .. 2 LOOP
v_country := CASE WHEN p_same_country = 'Y' OR s = 1 THEN 'INDIA' ELSE 'IRELAND' END;
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'MVLAB_' || p_run || '_S' || s,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN load_rows(''' || p_run || ''', ' || s || ', '
|| ((s - 1) * p_rows + 1) || ', ' || (s * p_rows) || ', '''
|| v_country || '''); END;',
enabled => FALSE,
auto_drop => TRUE);
END LOOP;
DBMS_SCHEDULER.ENABLE('MVLAB_' || p_run || '_S1, MVLAB_' || p_run || '_S2');
LOOP
DBMS_SESSION.SLEEP(5);
SELECT COUNT(*) INTO v_done FROM lab_results WHERE run_id = p_run;
EXIT WHEN v_done = 2;
SELECT COUNT(*) INTO v_fail
FROM user_scheduler_job_run_details
WHERE job_name IN ('MVLAB_' || p_run || '_S1', 'MVLAB_' || p_run || '_S2')
AND status <> 'SUCCEEDED';
IF v_fail > 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Run ' || p_run || ': a job failed, see USER_SCHEDULER_JOB_RUN_DETAILS');
END IF;
END LOOP;
-- correctness: the MV must match the base table exactly
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ((SELECT country, COUNT(*) cnt FROM author_tbl GROUP BY country '
|| ' MINUS SELECT country, cnt FROM author_country_mv) UNION ALL '
|| '(SELECT country, cnt FROM author_country_mv MINUS '
|| ' SELECT country, COUNT(*) FROM author_tbl GROUP BY country))' INTO v_bad;
INSERT INTO lab_runs VALUES (p_run,
CASE p_concurrent WHEN 'Y' THEN 'CONCURRENT' ELSE 'SERIAL (default)' END,
CASE p_same_country WHEN 'Y' THEN 'same country' ELSE 'different country' END,
p_rows, CASE v_bad WHEN 0 THEN 'YES' ELSE 'NO' END);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Run ' || p_run || ' finished, MV consistent: ' || CASE v_bad WHEN 0 THEN 'YES' ELSE 'NO' END);
END;
/
-- ---- the 4 runs (5,000 rows per session; lower it if your lab is slow)
EXEC run_test('A', 'N', 'Y', 5000)
EXEC run_test('B', 'Y', 'Y', 5000)
EXEC run_test('C', 'N', 'N', 5000)
EXEC run_test('D', 'Y', 'N', 5000)
-- ======================= PART 2: results ==============================
SELECT banner_full FROM v$version;
-- does the dictionary show the concurrent setting? (column name varies)
SELECT column_name FROM all_tab_columns
WHERE owner = 'SYS' AND table_name = 'USER_MVIEWS' AND column_name LIKE '%CONCURR%';
SELECT mview_name, refresh_mode, refresh_method, fast_refreshable, staleness,
concurrent_refresh_enabled
FROM user_mviews;
COLUMN mv_mode FORMAT a18
COLUMN country_mode FORMAT a18
SELECT u.run_id, u.mv_mode, u.country_mode, r.session_no, r.rows_loaded,
ROUND(EXTRACT(HOUR FROM (r.end_ts - r.start_ts)) * 3600
+ EXTRACT(MINUTE FROM (r.end_ts - r.start_ts)) * 60
+ EXTRACT(SECOND FROM (r.end_ts - r.start_ts)), 1) AS elapsed_secs,
r.ji_waits, r.ji_secs, u.mv_ok
FROM lab_results r JOIN lab_runs u ON u.run_id = r.run_id
ORDER BY r.run_id, r.session_no;
COLUMN event FORMAT a40
SELECT run_id, session_no, event, total_waits, secs_waited
FROM (SELECT w.*, ROW_NUMBER() OVER (PARTITION BY run_id, session_no ORDER BY secs_waited DESC) rn
FROM lab_waits w)
WHERE rn <= 5
ORDER BY run_id, session_no, secs_waited DESC;
-- ======================= CLEAN UP (optional, as SYS) =================
-- DROP USER mvlab CASCADE;
Summary
ENABLE CONCURRENT REFRESH does what it promises: on-commit refreshes no longer queue on the JI enqueue. In my tests that removed about 80% of the JI-class wait and made the load about 24% faster (18% and 29%) when sessions touched different MV rows, with the MV correct every time. When sessions hit the same MV row, the wait simply moves to a row lock and the gain is small. And if you look for enq: JI - contention on 26ai, check ASH: in my sandbox, the session-level wait statistics listed it only as “events in waitclass Other”.
Tested by me (two full test runs) on Oracle AI Database 26ai Enterprise Edition 23.26.1 in an Oracle LiveLabs sandbox. Names and data are generic test data. Test in a non-production database before use.
Tamilselvan Perumal is an Oracle ACE Associate and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.


