Big Savings on Oracle REDO Via Temp UNDO
When Oracle introduced version 12.1 of the RDBMS, an interesting new parameter was included: “temp_undo_enabled.” By default, “temp_undo_enabled “is set to FALSE. The purpose of this parameter is to reduce REDO that is generated from UNDO. Longtime Oracle DBAs will recognize this. Here’s a brief explanation for everyone else.
When a data block is changed in Oracle, corresponding UNDO and REDO are generated.
The UNDO (once known as rollback segments) is the vector used to reverse a change, and to provide a read consistent view in other sessions.
If a row is updated, and then a ROLLBACK issued, the UNDO data is used to reconstruct the block to the way it was.
The UNDO is also used by other sessions to provide a read consistent view, so that changes made in other sessions do not change results in your current session.
See Managing Undo for more information on that.
The REDO data is also a change vector, but with a different purpose. REDO is used to reconstruct blocks in the event of instance or database recovery.
When an instance crashes, the REDO in the current REDO LOGS is used to recover to the point of the crash.
That REDO is also written to Archive Logs, which are then used to complete recovery when a database is restored from a backup.
See Managing the Redo Log for more information.
UNDO on temp segments
Imagine you are making use of Global Temporary Tables (it’s easy if you try) in your application.
Each session has its own view of the data in a GTT. CREATE GLOBAL TEMPORARY TABLE
Many GTTs may be used for in-app transformation of transient data. By their very nature, GTTs normally do not need to be recovered either for instance recovery, nor for database recovery; therefore, no REDO is generated for DML performed on a GTT.
And yet, every transaction on a GTT is by default generating, UNDO.
When UNDO is generated, those UNDO blocks are in turn protected by REDO, which fills up the REDO LOGS, and then gets written out as Archive Logs.
All this UNDO, and the accompanying REDO, are not usually required for database recovery, as they are TEMP segments, which do not get recovered.
Oracle introduced the temp_undo_enabled parameter to help with that.
If temp_undo_enabled is set to TRUE, then Oracle no longer generates UNDO for changes to GTTs. And if there is no UNDO, then no REDO is being generated for the UNDO.
How much difference might that make?
An experiment is called for.
Reducing Redo
Using a version of Sqlrun that was modified for this testing, 10 sessions were run simultaneously for 5 minutes.
During that time, three SQL statements were run by each session.
insert into sqlrun_gtt select * from all_objects
update sqlrun_gtt set object_id = object_id + 1
merge into txcount tx
using (select sys_context('userenv','sid') sid from dual ) s
on (s.sid = tx.sid)
when matched then
update set tx.txacts = tx.txacts + 1
when not matched then
insert (sid, txacts)
values(sys_context('userenv','sid'),1)
The MERGE statement is used to keep track of the number of transactions that were executed for each test
The results
There difference in REDO generation was quite substantial
| temp_undo_enabled | redo size | undo size | undo per tx | transactions |
| FALSE | 816,580,156 | 488,315,540 | 194,238 | 2,514 |
| TRUE | 2,265,596 | 641,919,004 | 245,664 | 2,613 |
| Diff | -814,314,560 | 153,603,464 | 51,425 | 99 |
With temp_undo_enabled=FALSE, approximately 816M of REDO was generated.
With temp_undo_enabled=TRUE, only 2.54M of REDO was generated.
If an application is making heavy use of GTTs, a substantial reduction in REDO generation can be realized by simply setting this one parameter.
A by-product of this reduction in overhead is that 99 more transactions were able to run when the extra REDO was not being generated.
You may have noticed that the amount of UNDO per transaction increased. Wait a minute, wasn’t a the whole purpose of this to eliminate UNDO, so that no REDO would be generated?
The difference is that the UNDO is now Temporary UNDO, for which no REDO is generated.
The pattern of permanent and temporary UNDO usage can be seen by joining v$undostat and v$tempundostat, as seen in allseg.sql.
SQL# @allseg
PERM TEMP
UNDO PERM TEMP UNDO
BEGIN_TIME END_TIME BLKS UNDO UNDO BLKS
------------------- ------------------- ------- ------- ------- -------
07/05/2022 12:46:20 07/05/2022 12:56:20 98309 3618
07/05/2022 12:56:20 07/05/2022 13:06:20 67406 2476
07/05/2022 13:06:20 07/05/2022 13:16:20 57562 1899
07/05/2022 13:16:20 07/05/2022 13:26:20 1459 234
07/05/2022 13:26:20 07/05/2022 13:36:20 689 266
07/05/2022 13:36:20 07/05/2022 13:46:20 45881 1570
07/05/2022 13:46:20 07/05/2022 13:56:20 59245 2029
07/05/2022 13:56:20 07/05/2022 14:06:20 7 88
07/05/2022 14:06:20 07/05/2022 14:16:20 3607 170
07/05/2022 14:16:20 07/05/2022 14:26:20 86646 3027
07/05/2022 14:26:20 07/05/2022 14:36:20 44228 1563
07/05/2022 14:36:20 07/05/2022 14:46:20 87402 3035
07/05/2022 14:46:20 07/05/2022 14:56:20 5992 580
07/05/2022 14:56:20 07/05/2022 15:06:20 64 3410 3156 109760
07/05/2022 15:06:20 07/05/2022 15:16:20 3 9 0 0
07/05/2022 15:16:20 07/05/2022 15:26:20 6 74
07/05/2022 15:26:20 07/05/2022 15:36:20 7 517 154 5360
07/05/2022 15:36:20 07/05/2022 15:46:20 0 5 0 0
07/05/2022 15:46:20 07/05/2022 15:56:20 4 118 26 905
07/05/2022 15:56:20 07/05/2022 16:06:20 102 4959 2330 80995
07/05/2022 16:06:20 07/05/2022 16:16:20 54313 4484 333 11622
07/05/2022 16:16:20 07/05/2022 16:26:20 23349 1772 0 0
07/05/2022 16:26:20 07/05/2022 16:36:20 3 104
07/05/2022 16:36:20 07/05/2022 16:46:20 0 1
24 rows selected.
The data in these V$ views is updated at 10 minute intervals, so there is some overlap between the various tests. That is, we can see both permanent and temporary UNDO segments being used in the same 10 minute slots.
If I could find a way to include the REDO generation for each, I would include that in the SQL as well. In any case, the temporary UNDO blocks do not generate any REDO.
The nitty gritty
If you want to see details about how these tests were performed, then keep reading.
If not, perhaps you would like to investigate and see if you have any databases that can benefit from reducing REDO on GTT TEMP segments.
As mentioned previously, Sqlrun was used to run multiple SQL statements simultaneously.
The sqlrun.pl Perl script in this branch of Sqlrun was modified to do the following.
- Drop, create and populate the stats_begin table for use in tracking redo and undo during testing
- Drop and create the stats_end table for tracking redo and undo
The changes made to sqlrun.pl are a bit of a kludge, with quite a bit of hard-coding. Should any of this make it into the feature list, it will be done properly. For now the testing was the most important thing.
The tests are being run under the UNDOTEST account in a 19.9 PDB.
SQL# @showpriv undotest
"Roles/Privileges for which user or role? - "
( Wildcards OK )
PRIV
GRANTEE TYPE PRIV NAME OWNER TABLE_NAME GRANTABLE
---------- --------------------- ---------------------- ---------- --------------------------- ---------
UNDOTEST ROLE CONNECT NO
RESOURCE NO
SYSPRIV ALTER SESSION NO
TABPRIV SELECT SYS V_$INSTANCE NO
4 rows selected.
As the UNDOTEST user, the create/create.sql script was run initially:
$ cat create/create.sql drop table sqlrun_gtt purge; create global temporary table sqlrun_gtt on commit delete rows as select * from all_objects where 1=0; drop table txcount purge; create table txcount ( sid number, txacts number);
The file SQL/Oracle/sqlfile.conf has three active SQL scripts:
$ grep -vE '^\s*$|^\s*#' SQL/Oracle/sqlfile.conf | grep sql 1,temp-insert.sql, 1,temp-update.sql, 1,txcount-update.sql,
These are the SQL files that are executed repeatedly by each Oracle session.
The txcount-update.sql script updates the number of transactions during the testing. It is actually responsible for a fair part of the UNDO that is generated when temp_undo_enabled = TRUE/.
Running a test
Once everything is set, running the test is fairly simple
The contents of sqlrun.sh
./sqlrun.pl \ --exe-mode sequential \ --connect-mode flood \ --tx-behavior commit \ --max-sessions 10 \ --exe-delay 0.25 \ --db p2 \ --username undotest \ --password XXXX \ --runtime 300 \ --sqldir $(pwd)/SQL
Before running sqlrun.sh, the job_queue_processes parameter was set to 0 so that no system jobs would run during testing.
The temp_undo_enabled parameter was set TRUE|FALSE as appropriate, and checked before each testing session.
SQL# @set-temp-undo-off
System altered.
V$PARAMETER for temp_undo_enabled report for
Instance: cdb1
Date/Time: 07/05/2022 18:42:25
DEF SESS SYS
NAME INST VALUE VAL? MOD? MOD?
-------------------------------------------------- ----- -------------------------------------------------- ---- ---- ----
temp_undo_enabled 1 FALSE Y Y I
temp_undo_enabled 2 FALSE Y Y I
Though this is a RAC database, all testing was performed on instance 1.
Now to run sqlrun.sh
$ ./sqlrun.sh
driver config file:.../sqlrun/SQL/Oracle/driver-config.json
sqlFile: /home/jkstill/oracle/dba/undo_size/temp-undo/sqlrun/SQL/Oracle/sqlfile.conf
SQL PARSER:
DEBUG: 0
sqlParmFileFQN: .../sqlrun/SQL/Oracle/sqlfile.conf
exeMode: sequential
$sqlParms: $VAR1 = {
'txcount-update.sql' => '1',
'temp-insert.sql' => '1',
'temp-update.sql' => '1'
};
Connect Mode: flood
Truncating undotest.txcount
PID: 15152
Waiting on child 15152...
PID: 0
PID: 15154
Waiting on child 15154...
PID: 0
PID: 15156
Waiting on child 15156...
PID: 0
PID: 15158
Waiting on child 15158...
PID: 0
PID: 15160
Waiting on child 15160...
PID: 0
PID: 15162
Waiting on child 15162...
PID: 0
PID: 15164
Waiting on child 15164...
PID: 0
PID: 15166
Waiting on child 15166...
PID: 0
PID: 15168
Waiting on child 15168...
Timer Check: 300
PID: 0
Timer Check: 300
Timer Check: 300
Timer Check: 300
PID: 15170
Waiting on child 15170...
PID: 0
Timer Check: 300
Timer Check: 300
Timer Check: 300
Timer Check: 300
deleting stats_begin
Timer Check: 300
Timer Check: 300
Results of dropping stats_begin: 0E0
Results of creating stats_begin: 20
~/sqlrun $
$ ps
PID TTY TIME CMD
15153 pts/4 00:00:00 perl
15155 pts/4 00:00:00 perl
15157 pts/4 00:00:00 perl
15159 pts/4 00:00:00 perl
15161 pts/4 00:00:00 perl
15163 pts/4 00:00:00 perl
15165 pts/4 00:00:00 perl
15167 pts/4 00:00:00 perl
15169 pts/4 00:00:00 perl
15171 pts/4 00:00:00 perl
15281 pts/4 00:00:00 ps
18634 pts/4 00:00:00 bash
While the test is running in one SSH session, another is busy running this monitor script in a loop:
#!/usr/bin/env bash
USERNAME=somedba
PASSWORD=XXX
DB=p2
while :
do
sqlplus -S -L $USERNAME/$PASSWORD@$DB <<-EOF
set pause off verify off feed on term on
@@stats-end
@@stats-report
exit
EOF
sleep 5
done
This monitor allows you to see the change in UNDO and REDO statistics while the test is running. It also shows the number of transactions completed by the test.
When the number of transactions is no longer increasing, the current iteration of sqlrun.sh has completed.
Note: this test is not necessarily the same test run that the values in the body of the article are based on.
NAME VALUE TXCOUNT ---------------------------------------- ---------------- ---------- redo size 984,328,224 2407 undo change vector size 588,628,380 2407 2 rows selected. 20 rows updated. Commit complete. NAME VALUE TXCOUNT ---------------------------------------- ---------------- ---------- redo size 1,002,735,604 2452 undo change vector size 599,633,832 2452 2 rows selected. 20 rows updated. Commit complete. NAME VALUE TXCOUNT ---------------------------------------- ---------------- ---------- redo size 1,021,414,600 2500 undo change vector size 610,805,356 2500 2 rows selected. 2 rows updated. Commit complete. NAME VALUE TXCOUNT ---------------------------------------- ---------------- ---------- redo size 919,021,668 2514 undo change vector size 549,578,792 2514 2 rows selected. 2 rows updated. Commit complete. NAME VALUE TXCOUNT ---------------------------------------- ---------------- ---------- redo size 919,021,668 2514 undo change vector size 549,578,792 2514
Put it to use
This is just one of the benefits of enabling the temp_undo_enabled parameter, though it is a compelling one.
If you’re looking for opportunities to reduce REDO generation in your Oracle 12c+ database, be sure to test the use of this parameter.
I hope you found this post helpful. Feel free to drop any questions or share your thoughts in the comments, and make sure to sign up for updates.
