12c NO REDO for UNDO for Global Temporary Tables(GTT) with TEMP_UNDO_ENABLED

Global Temporary Tables (GTT) hold temporary non persistent data. The data in GTTs are  either deleted after commit or kept until the session is connected depending of the definition of the GTT.(ON COMMIT PRESERVE OR DELETE ROWS ).

 However, data in GTTs are consistent. They can be part of a transaction and also support rollback and commit. For this to happen,  Oracle DB has to maintain some undo information for each transaction. And until now, any block change in UNDO also generates some REDO. Since the data is not persistent by definition of GTTs, it looks like a waste of precious cycles generating REDO.

In 12c, oracle can now write UNDO of GTT in temp tablespace instead of UNDO tablespace. This way you can now avoid generation of REDO for the UNDO, while still having the ability to rollback a transaction.

With this feature, some of the I/O contention is reduced for online redo logfiles. Since transaction throughput depends on how fast you can commit transactions to the sequential logfiles. Less load on these files in a busy server is certainly a bonus. With 12c consolidation , where you could have multiple Pluggable database in one Container Database, the PDBs share the online redolog files. So reducing load on redo logfile is a important strategy.

Another important functionality this will enable is run reports in a Active Dataguard (ADG)  Site.  Usually, reports on some large dataset will use some staging tables to temporarily hold data before producing some final report. Until now, such report could not be run on ADR site. Now since the standby database will not generate redo of its own, we do not have that issue.

So, How do you enable this feature ?

All you have to do is set  TEMP_UNDO_ENABLED=true;

Proof below.

SQL> @gtt
SQL> conn scott/tiger@pdb1
Connected.
SQL> set echo on
SQL> set autotrace traceonly statistics
SQL> alter system set temp_undo_enabled=false;

System altered.

SQL> drop   table my_temp_table  purge;

Table dropped.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    column1    NUMBER,
  3    column2    NUMBER
  4  )    ON COMMIT DELETE ROWS;

Table created.

SQL> 
SQL> 
SQL> insert into my_temp_table(column1,column2) select object_id,object_id from all_objects where rownum < 90001;

89791 rows created.


Statistics
----------------------------------------------------------
    139  recursive calls
   1271  db block gets
  59340  consistent gets
      0  physical reads
 260784  redo size
    857  bytes sent via SQL*Net to client
    907  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
   1466  sorts (memory)
      0  sorts (disk)
  89791  rows processed

SQL> update    my_temp_table set column1=column1+2;

89791 rows updated.


Statistics
----------------------------------------------------------
     14  recursive calls
  94538  db block gets
    472  consistent gets
      0  physical reads
5482356  redo size
    859  bytes sent via SQL*Net to client
    843  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      2  sorts (memory)
      0  sorts (disk)
      89791  rows processed

SQL> commit;

Commit complete.
  -- Now reconnect to enable emp_undo_enabled.
 --  This would not work without a reconnect due to session limitation

SQL>  conn scott/tiger@pdb1 
Connected.
SQL> alter system set temp_undo_enabled=TRUE;

System altered.

SQL> set autotrace traceonly statistics
SQL> insert    into my_temp_table(column1,column2) select object_id,object_id from all_objects where rownum < 90001;

89791 rows created.


Statistics
----------------------------------------------------------
    132  recursive calls
   1278  db block gets
  59330  consistent gets
      0  physical reads
    280  redo size
    854  bytes sent via SQL*Net to client
    908  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
   1465  sorts (memory)
      0  sorts (disk)
  89791  rows processed

SQL> update    my_temp_table set column1=column1+2;

89791 rows updated.


Statistics
----------------------------------------------------------
      5  recursive calls
  95985  db block gets
    411  consistent gets
      0  physical reads
      0  redo size
    858  bytes sent via SQL*Net to client
    843  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
  89791  rows processed

SQL> 
SQL> commit;

Commit complete.