oracle 12c 新特性 Temporary UNDO

这个特性将对于临时表的UNDO信息分离出去，独立存储在临时表空间中，这就减少了对于UNDO段的使用。这个特性完全无损Oracle的事务一致性，但是却得到了空间缩减的好处。

下面用实验测试下效果：

SQL> show parameter temp_undo_enabled;
temp_undo_enabled                    boolean     FALSE
SQL> set autot traceonly statistics

SQL> create global temporary table test as select * from dba_tables where 1=0;

Table created.

SQL>  insert into test select * from dba_tables;

2108 rows created.

Statistics
----------------------------------------------------------
        782  recursive calls
        521  db block gets
      25061  consistent gets
          9  physical reads
      29384  redo size
        865  bytes sent via SQL*Net to client
        962  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         22  sorts (memory)
          0  sorts (disk)
       2108  rows processed

SQL>  update test set table_name=lower(table_name);

2108 rows updated.

Statistics
----------------------------------------------------------
         12  recursive calls
       2742  db block gets
        355  consistent gets
          0  physical reads
     299640  redo size
        865  bytes sent via SQL*Net to client
        965  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2108  rows processed

SQL> conn / as sysdba
Connected.
SQL> alter session set temp_undo_enabled=true;

Session altered.

SQL> set autotrace traceonly statistics;    
SQL>  insert into test select * from dba_tables;

2108 rows created.

Statistics
----------------------------------------------------------
         25  recursive calls
        528  db block gets
      11812  consistent gets
          0  physical reads
        272  redo size
        850  bytes sent via SQL*Net to client
        962  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       2108  rows processed

SQL>  update test set table_name=lower(table_name);

2108 rows updated.

Statistics
----------------------------------------------------------
          0  recursive calls
        129  db block gets
         97  consistent gets
          0  physical reads
          0  redo size
        857  bytes sent via SQL*Net to client
        965  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2108  rows processed