[20130808]12c新特性Temporary undo.txt

[20130808]12c新特性Temporary undo.txt


12C提供特性临时表执行dml时生成的undo保存在临时表空间，这个特性叫Temporary Undo，由数据库参数temp_undo_enabled控制，
有两个优点：

    1.很明显减少redo在临时表做dml操作时。
    2.允许在physical standby database上对临时表执行dml操作。（没有环境无法测试)

测试：

SQL> @ver

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

1.测试1temp_undo_enabled=false:
SQL> create global temporary table gtt  (id number(8), name char(100));
Table created.

SQL> show parameter temp_undo_enabled

NAME               TYPE     VALUE

------------------ -------- ---------------------

temp_undo_enabled  boolean  FALSE

--可以发现缺省并没有打开这个特性。

SQL> select name, value from v$mystat ,v$statname where v$mystat.statistic#=v$statname.statistic# and (name = 'redo size' or name = 'redo entries');

NAME                      VALUE

-------------------- ----------

redo entries                 28

redo size                 10760

SQL> insert into gtt select level, 'test' from dual connect by level 

10000 rows created.

SQL> select name, value from v$mystat ,v$statname where v$mystat.statistic#=v$statname.statistic# and (name = 'redo size' or name = 'redo entries');

NAME                      VALUE

-------------------- ----------

redo entries                313

redo size                 71972

--可以发现redo entries增加 313-28=285,redo size增加71972 - 10760=61212，62K。

2.测试2temp_undo_enabled=true:

SQL> alter session set temp_undo_enabled = true;
Session altered.
--注意可以在session级设置

SQL> select name, value from v$mystat ,v$statname where v$mystat.statistic#=v$statname.statistic# and (name = 'redo size' or name = 'redo entries');

NAME                      VALUE

-------------------- ----------

redo entries                  2

redo size                   708

SQL> insert into gtt select level, 'test' from dual connect by level 

10000 rows created.

SQL> select name, value from v$mystat ,v$statname where v$mystat.statistic#=v$statname.statistic# and (name = 'redo size' or name = 'redo entries');

NAME                      VALUE

-------------------- ----------

redo entries                  3

redo size                   988

--可以发现redo entries增加 3-2=1,redo size增加988-708=280字节。