Oracle 12c announce new feature call Temporary Undo for Temporary Table for
-Reduce the amount of UNDO in the UNDO tablespace
-Better for retention periods for “real” data
-Reduce the size of the redo generated
-Allows for DML on temporary tables in Active Data Guard
It very simple step to using temporary undo you can do that by
alter system/session set temp_undo_enabled=true|false;
let me show demonstrate how temp undo reduce redo generate record (This demo got from T.Kyte Presentation Oracle Open World 2012)
###Create global temp demo###
c##tkyte%CDB1> create global temporary table gtt
2 on commit delete rows
3 as
4 select * from stage where 1=0;
Table created.
###Running with default undo###
c##tkyte%CDB1> alter session set temp_undo_enabled = false;
Session altered.
c##tkyte%CDB1> insert into gtt
2 select *
3 from stage;
87310 rows created.
Statistics
----------------------------------------------------------
…
566304 redo size
…
c##tkyte%CDB1> update gtt
2 set object_name = lower(object_name);
87310 rows updated.
Statistics
----------------------------------------------------------
…
8243680 redo size
###Running with Temporary Undo###
c##tkyte%CDB1> alter session set temp_undo_enabled = true;
Session altered.
c##tkyte%CDB1> insert into gtt
2 select *
3 from stage;
87310 rows created.
Statistics
----------------------------------------------------------
…
280 redo size
…
c##tkyte%CDB1> update gtt
2 set object_name = lower(object_name);
87310 rows updated.
Statistics
----------------------------------------------------------
…
0 redo size
Wow !! 99% redo generate reduce from temporary undo !! , now imagine to your database performance ha ha ha.
Credit : Tom Kyte
Hope to Help !
Chatchai Komrangded
