[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字节。
时间: 2024-09-15 02:57:46