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

最新推荐文章于 2021-01-04 10:55:46 发布

blockheading

最新推荐文章于 2021-01-04 10:55:46 发布

阅读量704

点赞数

分类专栏： Oracle Basic Principle 文章标签： redo 12c 11g undo temp

本文链接：https://blog.csdn.net/u013169075/article/details/52893083

版权

Oracle Basic Principle 专栏收录该内容

57 篇文章 0 订阅

订阅专栏

--观察11g环境下的对表与临时表进行insert，update，delete操作生成的redo比较
EODA@PROD1> select * from v$version;  --11g环境

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Elapsed: 00:00:00.06

EODA@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.09
EODA@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.01
EODA@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.09
EODA@PROD1> 
EODA@PROD1> set serveroutput on format wrapped
EODA@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的redo

PL/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 Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
	 0

SQL> 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 wrapped
SQL> 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编程艺术深入理解数据库体系结构（第三版）》