Concurrent Refresh for On-Commit Materialized Views in Oracle 26ai: A Hands-On Test of enq: JI - contention

 


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”.

TL;DR
  • 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: JI and start waiting on the MV row lock, enq: TX - row lock contention, instead.
  • The hidden wait: in my 26ai sandbox, V$SESSION_EVENT did not list enq: JI - contention at 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

RunMV refreshWhat the two sessions insertMV rows they touch
ASerial (default)Both insert country = INDIAThe same MV row
BConcurrentBoth insert country = INDIAThe same MV row
CSerial (default)Session 1 INDIA, session 2 IRELANDDifferent MV rows
DConcurrentSession 1 INDIA, session 2 IRELANDDifferent 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



RunMV refreshMV rowsElapsed, test 1Elapsed, test 2AverageWait class Other (avg)Lock waits (avg)MV correct
ASerialsame41.3 s38.6 s40.0 s17.1 s (≈5,300–5,500 waits)noneYES, YES
BConcurrentsame37.8 s (−9%)32.8 s (−15%)35.3 s (−12%)4.4 senq: TX - row lock contention 3.8 sYES, YES
CSerialdifferent42.0 s43.0 s42.5 s18.2 s (≈5,000–5,500 waits)noneYES, YES
DConcurrentdifferent34.4 s (−18%)30.6 s (−29%)32.5 s (−24%)3.6 srow cache lock 2.9 sYES, 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).

Lesson for troubleshooting: on 26ai, do not conclude “no JI contention” from 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 - contention in 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.