[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;

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;

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位元組。