1 Oracle 12C 临时undo

在Oracle 12c R1之前，undo记录是由临时表产生并存储在undo表空间的。12c R1的到来，引入了临时undo功能，现在undo记录就存储在临时表中，而不用再存储到undo表空间中。这样以来，就不会产生redo日志了，从而undo表空间的开销得以减少，对数据库库的性能可以有所提升。

2 测试

点击(此处)折叠或打开

SQL> alter system set temp_undo_enabled=false;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 280
redo size 111840
SQL> create global temporary table temp_snoover as select * from dba_objects where 1=2;

Table created.

SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');

NAME VALUE
---------------------------------------------------------------- ----------
redo entries 384
redo size 145812

*.可以看出来做ddl操作，日志变化还是很明显

点击(此处)折叠或打开

SQL> insert into temp_snoover select * from dba_objects;
135216 rows created.
SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 4635
redo size 1130824

*.现在更加能够明显的看出来做dml操作日志变化是非常巨大的。

2.2 临时undo开启

点击(此处)折叠或打开

点击(此处)折叠或打开

SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 2
redo size 808

点击(此处)折叠或打开

SQL> insert into temp_snoover select * from dba_objects;
135216 rows created.
SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 6
redo size 1804

*.可以看出产生的redo日志是很小的，再次插入数据测试；

点击(此处)折叠或打开

SQL> insert into temp_snoover select * from dba_objects;
135216 rows created.
SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 6
redo size 1804

*.redo日志大小没有变化。

通过测试，在temp_undo_enabled参数为true后，redo生成的量就非常小了，这样更加的节存储空间且可以提高数据库性能。
注：此参数默认false。