一、 简介及作用
在12c之前,临时表产生的undo信息写在undo表空间,虽然对临时表的DML不产生redo,但undo表空间中的信息会写入redo。这可能会产生两个问题——占用undo多、产生redo及归档多。
12c开始推出了Temporary Undo Segments,通过将全局临时表产生的undo信息从undo表空间挪到了临时表空间避免以上两个问题(主要是为减少redo和归档量)
二、 启用及测试
To enable temporary undo on the primary database, use the TEMP_UNDO_ENABLED initialization parameter.On an Active Data Guard standby, temporary undo is always enabled by default, so the TEMP_UNDO_ENABLED parameter has no effect.
通过在主库设置temp_undo_enabled参数为true来启用该新特性(默认false),注意在ADG中,temporary undo 默认一直是启用的,与temp_undo_enabled参数无关。
下面做一个对比测试,测试DML与rollback产生的redo量
db版本为19.3,在第一个会话创建空的全局临时表
SQL> show parameter temp_undo_enabled
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
SQL>
SQL> create global temporary table t_test_temp 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 134
redo size 44792
新开一个会话,temp_undo_enabled为默认值
-- insert前
SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 0
redo size 0
SQL> insert into t_test_temp select * from dba_objects;
73152 rows created.
-- insert后
SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 2628
redo size 588124
SQL> rollback;
Rollback complete.
-- rollback后
SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 5258
redo size 851284
再新开一个会话,temp_undo_enabled为true,对比上个会话产生的redo量
SQL> alter session set temp_undo_enabled=true;
Session altered.
-- insert前
SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 0
redo size 0
SQL> insert into t_test_temp select * from dba_objects;
73152 rows created.
-- insert后
SQL> select name, value from v$mystat natural join v$statname where name in ('redo entries','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 1
redo size 448
SQL> rollback;
Rollback complete.
-- rollback后
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 660
明显产生的redo量变少了
三、 注意事项
虽然有上面说到的好处,但在mos上查这个新特性相关的bug不少,在12.1版本还是不建议开启,免得中招
参考