New Feature 12c - temp_undo_enabled

Oracle 12c New Features - TEMP_UNDO_ENABLED

Oracle 12c introduces new parameter TEMP_UNDO_ENABLED which can be set at database and session level.  If this parameter is enabled, then undo for temporary objects (e.g global temporary tables) is written to the TEMP tablespace, compared to the default of writing to the UNDO tablespace.  This can help improve performance and reduce UNDO and REDO.

SQL> connect test/test@dondb1
Connected.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (id integer) ON COMMIT PRESERVE ROWS;

Table created.

SQL> show parameter temp_undo_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
temp_undo_enabled                    boolean     FALSE

Now run an insert to the global temporary table with the parameter set to FALSE. 

SQL> set autotrace on statistics
SQL> insert into my_temp_table values (1);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
          8  db block gets
          1  consistent gets
          0  physical reads
        312  redo size
        853  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

According to Auto trace statistics this generates redo of 312.

SQL> connect test/test@dondb1
Connected.
SQL> alter session set temp_undo_enabled=true;

Session altered.

Now run the insert again with the parameter set to TRUE.  

SQL> set autotrace on statistics
SQL> insert into my_temp_table values (1);

1 row created.


Statistics
----------------------------------------------------------
          3  recursive calls
         13  db block gets
          1  consistent gets
          0  physical reads
        280  redo size
        850  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

According to Auto trace statistics this generates redo of 280 (compared to 312).  Reduced UNDO and REDO from temporary transactions can help the performance of the database and reduce disk space for UNDO tablespaces, archivelogs, and backups. The Oracle documentation says "If database applications make use of temporary objects (using global temporary tables or temporary table transformations), it is advisable to set this parameter's value to TRUE

Statistics on TEMP UNDO are available via V$TEMPUNDOSTAT

SQL> desc V$TEMPUNDOSTAT
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 BEGIN_TIME                                         DATE
 END_TIME                                           DATE
 UNDOTSN                                            NUMBER
 TXNCOUNT                                           NUMBER
 MAXCONCURRENCY                                     NUMBER
 MAXQUERYLEN                                        NUMBER
 MAXQUERYID                                         VARCHAR2(13)
 UNDOBLKCNT                                         NUMBER
 EXTCNT                                             NUMBER
 USCOUNT                                            NUMBER
 SSOLDERRCNT                                        NUMBER
 NOSPACEERRCNT                                      NUMBER
 CON_ID                                             NUMBER

Interested in more details, click here :- http://docs.oracle.com/database/121/REFRN/refrn10326.htm#REFRN10326