Prior to Oracle12c, there was lot of confusion about Global temporary tables and redo generation.  Some of us initially misinterpreted documentation and concluded that no redo is generated for DMLs against global temporary tables.  However in Oracle12c, the documentation is very clear about undo being generated for  DMLs against global temporary tables  because undo by default is stored in undo tablespace. This off course results in generation of redo because of the undo being generated in undo tablespace. Kind of chain effect.

Depending on  the type of DML, this can be significant.  For example,  the undo for insert statement is delete (based on rowid) and so undo will be minimal and therefore the redo too.  Please note the redo is generated for undo.

I have the  illustrated an example below where the redo for global temporary table  is 601200(600k) vs 12513404 (12M) from  permanent temporary table.

Therefore  redo is generated for DMLs into Global temporary table.

SQL> create  global temporary table dba_objects_gt as select * from dba_objects where 1=0;

Table created.

SQL>
SQL> set autotrace on

SQL> insert into dba_objects_gt select * from dba_objects;

90858 rows created.

Statistics
———————————————————-
70  recursive calls
8458  db block gets
6230  consistent gets
0  physical reads
601200  redo size
857  bytes sent via SQL*Net to client
852  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
3  sorts (memory)
0  sorts (disk)
90858  rows processed

SQL> create  table dba_objects_pm as select * from dba_objects where 1=0
2  ;

Table created.

SQL> insert into dba_objects_pm select * from dba_objects;

90859 rows created.

Statistics
———————————————————-
61  recursive calls
8781  db block gets
7746  consistent gets
0  physical reads
  12513404  redo size
859  bytes sent via SQL*Net to client
852  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
90859  rows processed

As we are aware  that undo records for temporary tables are stored in the undo tablespace and therefore logged in the redo. This is same way undo is managed for permanent tables too. With Oracle12c, this default bahavoiour can be changed with  initialization parameter TEMP_UNDO_ENABLED that separated undo for temporary tables from undo for persistent tables.  Temporary undo can be enabled at session level or system level.  By default ,Temporary undo is also enabled for physical standby databases (Active Dataguard)

Other than reduced redo generation, enabling temporary undo also results in less data stored in undo which in turn can help predict more accurate undo retentions.

I enabled temporary undo and ran the insert statement once again. What a difference … From 12M to 600K  to 228 bytes.

SQL> ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

System altered.

SQL>
SQL>  create  global temporary table dba_objects_gt as select * from dba_objects where 1=0;

Table created.

SQL>
SQL> insert into dba_objects_gt select * from dba_objects;

90860 rows created.

Statistics
———————————————————-
13  recursive calls
8456  db block gets
6153  consistent gets
0  physical reads
   228  redo size
860  bytes sent via SQL*Net to client
852  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
90860  rows processed

Tagged: global temporary table, global temporary table and redo generation, global temporary table and undo, Temporary Undo in Oracle12c, TEMP_UNDO_ENABLED, undo & redo