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

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.