Oracle AI Database 26ai introduces Auto Redo Prioritization, a new capability available from Release Update 23.26.3 that helps protect OLTP workloads during periods of heavy redo activity.
When redo buffer pressure becomes critical, Oracle can identify sessions generating large amounts of redo with longer-running calls as batch-like workloads. These sessions may wait on redo prioritization for log write lag, slowing their redo generation and giving latency-sensitive OLTP transactions more room to progress.
The goal is simple: protect OLTP responsiveness by automatically controlling batch-like redo generation when redo pressure builds.
REDO PRESSURE
│
▼
┌─────────────────────┐
│ Redo buffer pressure │
└──────────┬──────────┘
│
▼
Auto Redo Prioritization
│
┌────────┴────────┐
│ │
▼ ▼
OLTP-behaving Batch-behaving
sessions sessions
│ │
▼ ▼
Continue normally Redo generation
is reduced/throttled
1. Enabling Auto Redo Prioritization
The feature can be enabled using the dynamic initialization parameter:
SQL> alter system set log_redo_prioritization=true;
System altered.
The parameter is dynamic, so a database restart is not required. In an Oracle RAC environment, it can also be configured independently on individual instances.
2. Designing the Test
The test uses five tables.
The first two represent the OLTP workload:
--OLTP
SQL> CREATE TABLE vahid.tb1 (
id NUMBER PRIMARY KEY,
name VARCHAR2(1000),
amount NUMBER
);
Table created.
SQL> CREATE TABLE vahid.tb2 (
id NUMBER PRIMARY KEY,
name VARCHAR2(1000),
amount NUMBER
);
Table created.
Each table was populated with 100,000 rows:
SQL> INSERT INTO vahid.tb1
SELECT
level,
'Vahid Yousefzadeh',
level * 10
FROM dual
CONNECT BY level <= 100000;
100000 rows created.
SQL>COMMIT;
Commit complete.
SQL>INSERT INTO vahid.tb2
SELECT
level,
'Vahid Yousefzadeh',
level * 10
FROM dual
CONNECT BY level <= 100000;
100000 rows created.
SQL>COMMIT;
Commit complete.
For the batch workload, three additional tables were created:
-- TB3,TB4,TB5 = Batch
SQL> CREATE TABLE vahid.tb3
(
id NUMBER PRIMARY KEY,
c1 VARCHAR2(4000),
c2 VARCHAR2(4000),
c3 VARCHAR2(4000),
c4 VARCHAR2(4000)
);
Table created.
SQL> CREATE TABLE vahid.tb4
(
id NUMBER PRIMARY KEY,
c1 VARCHAR2(4000),
c2 VARCHAR2(4000),
c3 VARCHAR2(4000),
c4 VARCHAR2(4000)
);
Table created.
SQL> CREATE TABLE vahid.tb5
(
id NUMBER PRIMARY KEY,
c1 VARCHAR2(4000),
c2 VARCHAR2(4000),
c3 VARCHAR2(4000),
c4 VARCHAR2(4000)
);
Table created.
The batch tables deliberately contain substantially more data per row, creating a significantly more redo-intensive workload.
3. Running the Workloads Concurrently
The most important part of this test is that all five sessions are executed concurrently.
The workload consists of:
Session 1 → OLTP on TB1
Session 2 → OLTP on TB2
Session 3 → Batch on TB3
Session 4 → Batch on TB4
Session 5 → Batch on TB5
In other words:
2 OLTP sessions + 3 batch sessions = 5 concurrent redo-generating sessions
Session 1 — OLTP
SQL> BEGIN
FOR i IN 1..3000000 LOOP
UPDATE vahid.tb1
SET amount = amount + 1
WHERE id = MOD(i - 1, 100000) + 1;
COMMIT;
END LOOP;
END;
/
Session 2 — OLTP
SQL> BEGIN
FOR i IN 1..3000000 LOOP
UPDATE vahid.tb2
SET amount = amount + 1
WHERE id = MOD(i - 1, 100000) + 1;
COMMIT;
END LOOP;
END;
/
Session 3 — Batch
SQL> INSERT INTO vahid.tb3
SELECT
level,
RPAD('A',4000,'A'),
RPAD('B',4000,'B'),
RPAD('C',4000,'C'),
RPAD('D',4000,'D')
FROM dual
CONNECT BY level <= 70000;
Session 4 — Batch
SQL> INSERT INTO vahid.tb4
SELECT
level,
RPAD('A',4000,'A'),
RPAD('B',4000,'B'),
RPAD('C',4000,'C'),
RPAD('D',4000,'D')
FROM dual
CONNECT BY level <= 70000;
Session 5 — Batch
SQL> INSERT INTO vahid.tb5
SELECT
level,
RPAD('A',4000,'A'),
RPAD('B',4000,'B'),
RPAD('C',4000,'C'),
RPAD('D',4000,'D')
FROM dual
CONNECT BY level <= 70000;
The five sessions above are started concurrently, intentionally creating contention between OLTP and batch workloads for redo generation and redo log buffer resources.
execution time for each session:
--session 1:
PL/SQL procedure successfully completed.
Elapsed: 00:08:53.76
--session 2:
PL/SQL procedure successfully completed.
Elapsed: 00:08:47.98
--session 3:
70000 rows created.
Elapsed: 00:07:48.15
--session 4:
70000 rows created.
Elapsed: 00:07:45.00
--session 5:
70000 rows created.
Elapsed: 00:07:44.48
After the concurrent workload completed, the following query was used to check two important wait events:
SELECT event,
total_waits,
ROUND(time_waited / 100, 2) AS time_waited_sec,
ROUND(
time_waited / NULLIF(total_waits, 0) * 10,
4
) AS avg_wait_ms
FROM v$system_event
WHERE event IN (
'log buffer space',
'redo prioritization for log write lag'
)
ORDER BY event;
EVENT TOTAL_WAITS TIME_WAITED_SEC AVG_WAIT_MS
log buffer space 23497 197 8
redo prioritization for log write lag 1058 134 127
In addition, the AWR report for the test showed the following results:
The most interesting observation is the appearance of:
redo prioritization for log write lag
This provides visibility into sessions being delayed by the redo prioritization mechanism.
In this test:
- 23,497 log buffer space waits
- 1,058 redo prioritization for log write lag waits
- Average log buffer space wait: *8.4143 ms *
- Average redo prioritization for log write lag wait: 127.3629 ms
Moreover, The *batch-workload session trace * provides direct evidence of repeated redo prioritization for log write lag waits. Each wait lasted approximately 105–110 ms, as indicated by the ela value in microseconds.
WAIT #140202184698400: nam='redo prioritization for log write lag' ela=109507 p1=0 p2=0 p3=0 obj#=4294967295 tim=20022112908
WAIT #140202184698400: nam='redo prioritization for log write lag' ela=106961 p1=0 p2=0 p3=0 obj#=4294967295 tim=20022223931
WAIT #140202184698400: nam='redo prioritization for log write lag' ela=108165 p1=0 p2=0 p3=0 obj#=4294967295 tim=20022336260
WAIT #140202184698400: nam='redo prioritization for log write lag' ela=104621 p1=0 p2=0 p3=0 obj#=4294967295 tim=20022445083
These events represent different aspects of the workload: log buffer space reflects redo buffer pressure, while redo prioritization for log write lag reflects sessions being delayed by redo prioritization.
4. Disabling Redo Prioritization
For comparison, the feature was disabled:
SQL> alter system set log_redo_prioritization=false;
System altered.
The same queries were then executed to compare the results.
The output was:
EVENT TOTAL_WAITS TIME_WAITED_SEC AVG_WAIT_MS
log buffer space 30408 304 10
The AWR report:
With redo prioritization enabled, log buffer space waits were lower, decreasing from 30,408 to 23,497, while total wait time dropped from 304 to 197 seconds and average wait time from 10 ms to 8 ms.
At the same time,** 1,058** redo prioritization for log write lag waits were recorded, with an average wait of** 127 ms**, indicating that redo prioritization was actively delaying affected sessions.
These results show that redo prioritization was actively engaged, reducing log buffer space waits while introducing controlled delays for batch-like sessions. This demonstrates how Oracle can protect **OLTP **activity under heavy redo pressure.
Top comments (0)