观察11g/12c环境下的对表与临时表进行insert，update，delete操作生成的redo比较

--观察11g环境下的对表与临时表进行insert，update，delete操作生成的redo比较EODA@PROD1> select * from v$version;  --11g环境BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE11.2.0.3.0ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionElapsed: 00:00:00.06EODA@PROD1> set echo on;EODA@PROD1> create table perm  2    ( x char(2000) ,  3   y char(2000) ,  4   z char(2000)  )  5  /Table created.Elapsed: 00:00:00.09EODA@PROD1> EODA@PROD1> create global temporary table temp  2    ( x char(2000) ,  3   y char(2000) ,  4   z char(2000)  )  5  on commit preserve rows  6  /Table created.Elapsed: 00:00:00.01EODA@PROD1> EODA@PROD1> create or replace procedure do_sql( p_sql in varchar2 )  2    as  3     l_start_redo    number;  4     l_redo     number;  5  begin  6     l_start_redo := get_stat_val( 'redo size' );  7    8     execute immediate p_sql;  9     commit; 10   11     l_redo := get_stat_val( 'redo size' ) - l_start_redo; 12   13     dbms_output.put_line 14     ( to_char(l_redo,'99,999,999') ||' bytes of redo generated for "' || 15       substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' ); 16  end; 17  /Procedure created.Elapsed: 00:00:00.09EODA@PROD1> EODA@PROD1> set serveroutput on format wrappedEODA@PROD1> begin  2     do_sql( 'insert into perm  3      select 1,1,1  4      from all_objects  5      where rownum <= 500' );  6    7     do_sql( 'insert into temp  8      select 1,1,1  9      from all_objects 10      where rownum <= 500' ); 11     dbms_output.new_line; 12   13     do_sql( 'update perm set x = 2' ); 14     do_sql( 'update temp set x = 2' ); 15     dbms_output.new_line; 16   17     do_sql( 'delete from perm' ); 18     do_sql( 'delete from temp' ); 19  end; 20  /  3,305,492 bytes of redo generated for "insert into perm  "...  --大量生成的redo数据     68,492 bytes of redo generated for "insert into temp  "...  --临时表不记录redo数据  5,070,804 bytes of redo generated for "update perm set x = 2"...  --生成的redo和记录undo的redo  1,775,736 bytes of redo generated for "update temp set x = 2"...  --记录undo的redo  3,247,464 bytes of redo generated for "delete from perm"...       --大量记录undo的redo  3,241,980 bytes of redo generated for "delete from temp"...       --大量记录undo的redoPL/SQL procedure successfully completed.Elapsed: 00:00:02.41--如果表上有索引的情况。 12,353,928 bytes of redo generated for "insert into perm  "...  3,355,824 bytes of redo generated for "insert into temp  "...  9,576,992 bytes of redo generated for "update perm set x = 2"...  5,496,428 bytes of redo generated for "update temp set x = 2"...  4,416,420 bytes of redo generated for "delete from perm"...  4,384,000 bytes of redo generated for "delete from temp"...PL/SQL procedure successfully completed.增加的开销都是维护索引的生成的undo。

--观察12c环境下的对表与临时表进行insert，update，delete操作生成的redo比较--12c新特性TEMP_UNDO_ENABLED--TEMP_UNDO_ENABLED determines whether transactions within a particular session can have a temporary undo log. The default choice for database transactions has been to have a single undo log per transaction. This parameter, at the session level / system level scope, lets a transaction split its undo log into temporary undo log (for changes on temporary objects) and permanent undo log (for changes on persistent objects).--这个特性将对于临时表的UNDO信息分离出去，独立存储在临时表空间中，这就减少了对于UNDO段的使用。SQL> select banner from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionPL/SQL Release 12.1.0.2.0 - ProductionCORE12.1.0.2.0ProductionTNS for Linux: Version 12.1.0.2.0 - ProductionNLSRTL Version 12.1.0.2.0 - Production 0SQL> create table perm  2    ( x char(2000) ,  3   y char(2000) ,  4   z char(2000)  )  5  /Table created.SQL> SQL> create global temporary table temp  2    ( x char(2000) ,  3   y char(2000) ,  4   z char(2000)  )  5  on commit preserve rows  6  /Table created.SQL> SQL> create or replace procedure do_sql( p_sql in varchar2 )  2    as  3     l_start_redo    number;  4     l_redo     number;  5  begin  6     l_start_redo := get_stat_val( 'redo size' );  7    8     execute immediate p_sql;  9     commit; 10   11     l_redo := get_stat_val( 'redo size' ) - l_start_redo; 12   13     dbms_output.put_line 14     ( to_char(l_redo,'99,999,999') ||' bytes of redo generated for "' || 15       substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' ); 16  end; 17  /Procedure created.SQL> SQL> set serveroutput on format wrappedSQL> begin  2     do_sql( 'insert into perm  3      select 1,1,1  4      from all_objects  5      where rownum <= 500' );  6    7     do_sql( 'insert into temp  8      select 1,1,1  9      from all_objects 10      where rownum <= 500' ); 11     dbms_output.new_line; 12   13     do_sql( 'update perm set x = 2' ); 14     do_sql( 'update temp set x = 2' ); 15     dbms_output.new_line; 16   17     do_sql( 'delete from perm' ); 18     do_sql( 'delete from temp' ); 19  end; 20  /  3,317,208 bytes of redo generated for "insert into perm  "...384 bytes of redo generated for "insert into temp  "...  5,048,932 bytes of redo generated for "update perm set x = 2"...340 bytes of redo generated for "update temp set x = 2"...  3,355,096 bytes of redo generated for "delete from perm"...384 bytes of redo generated for "delete from temp"...PL/SQL procedure successfully completed.--很明显临时表操作产生的redo都微乎其微。 

--参考来源《Oracle编程艺术深入理解数据库体系结构（第三版）》