DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Oracle AI Database 26ai(23.26.3) - Controlling PDB Redo Generation with REDO_GENERATION_KBPS_MAX

#oracle #ai #database #linux

In a container database environment, one PDB can sometimes generate a huge amount of redo in a short period of time. This excessive redo generation can consume shared resources and potentially affect the performance of other PDBs.

In other words, one PDB can become a resource bully, while other PDBs become the victims.

Oracle AI Database 26ai introduces REDO_GENERATION_KBPS_MAX, a parameter that allows us to control the target redo generation rate of an individual PDB.

When a PDB exceeds the configured target, Oracle can throttle its sessions to keep the redo generation rate close to the configured limit.

To demonstrate this behavior, I used a simple CTAS operation and compared its execution time with and without a redo-generation limit.

First, I ran the operation without any redo-generation limit.

SQL> create table tb2 as select * from tb1;
Table created
Executed in 18.315 seconds
Enter fullscreen mode Exit fullscreen mode

The operation completed in only 18.315 seconds. This session had generated more than 719,802 MB of redo:

SELECT round(value / 1024 / 1024) REDO_SIZE_KB
FROM v$sesstat st
JOIN v$statname sn
ON sn.statistic# = st.statistic#
WHERE st.sid = 221
AND sn.name = 'redo size';

REDO_SIZE_KB
 - - - - - - 
719802
Enter fullscreen mode Exit fullscreen mode

This gives us a baseline for the workload.

Limiting Redo Generation
To demonstrate this feature, I wanted to strictly limit the PDB so that the CTAS operation would take at least three minutes to complete. I configured the PDB with a target maximum redo generation rate of 4,000 KB/s:

SQL> ALTER SYSTEM SET redo_generation_kbps_max = 4000;
System altered
Enter fullscreen mode Exit fullscreen mode

I removed the table and ran exactly the same CTAS operation again:

SQL> drop table tb2;
Table dropped

SQL> create table tb2 as select * from tb1;
Table created
Executed in 190.017 seconds
Enter fullscreen mode Exit fullscreen mode

The result was very different. The same operation that previously required:

18.315 seconds
Enter fullscreen mode Exit fullscreen mode

now required:

190.017 seconds
Enter fullscreen mode Exit fullscreen mode

That’s more than 10 times slower.

While the second CTAS was running, I checked the session wait event:

SQL> select event from v$session_wait where sid=221;
EVENT
 - - - - - - - - - - - - -
resmgr: redo throttle
Enter fullscreen mode Exit fullscreen mode

This is the key observation in this experiment.

I I generated an AWR report, and the results were as follows:

84.7% of the database time was spent on resmgr: redo throttle.

This clearly shows that the session was spending most of its database time being throttled by Resource Manager because it exceeded the configured redo generation limit.

Top comments (0)