[20150225]Temporary Tables and RedoUndo.txt

摘要:

Apress.Oracle.Database.Transactions.and.Locking.Revealed.1484207610.pdf

Temporary tables generate no redo for their blocks. Therefore, an operation on a temporary table is not recoverable.

When you modify a block in a temporary table, no record of this change will be made in the redo log files. However, temporary tables do generate undo, and the undo is logged. Hence, temporary tables will generate some redo. At first glance, this doesn't seem to make total sense: Why would they need to generate undo? This is because you can roll back to a SAVEPOINT within a transaction. You might erase the last 50 INSERTs into a temporary table, leaving the first 50. Temporary tables can have constraints and everything else a normal table can have. They might fail a statement on the five-hundredth row of a 500-row INSERT, necessitating a rollback of that statement. Since temporary tables behave in general just like normal tables, temporary tables must generate undo. Since undo data must be logged, temporary tables will generate some redo log for the undo they generate.

This is not nearly as ominous as it seems. The primary SQL statements used against temporary tables are INSERTs

and SELECTs. Fortunately, INSERTs generate very little undo (you need to restore the block to "nothing," and it doesn't take very much room to store "nothing"), and SELECTs generate no undo. Hence, if you use temporary tables for INSERTs and SELECTs exclusively, this section means nothing to you. It is only if you UPDATE or DELETE that you might be concerned about this.

I set up a small test to demonstrate the amount of redo generated while working with temporary tables, an

indication therefore of the amount of undo generated for temporary tables, since only the undo is logged for them. To demonstrate, I'll take identically configured permanent and temporary tables, and then perform the same operations on each, measuring the amount of redo generated each time. The tables I'll use are as follows:

1.建立测试环境：

SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table perm( x char(2000) ,  y char(2000) ,  z char(2000)  );

create global temporary table temp ( x char(2000) , y char(2000) , z char(2000)  ) on commit preserve rows;

create or replace function get_stat_val( p_name in varchar2 ) return number

as l_val number; begin     select b.value into l_val from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = p_name; return l_val; end;

create or replace procedure do_sql( p_sql in varchar2 )

as     l_start_redo    number;     l_redo            number; begin     l_start_redo := get_stat_val( 'redo size' );

    execute immediate p_sql;

    commit;

    l_redo := get_stat_val( 'redo size' ) - l_start_redo;

    dbms_output.put_line

    ( to_char(l_redo,'99,999,999') ||' bytes of redo generated for "' ||       substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' ); end; /

2.开始测试：

set serveroutput on format wrapped begin     do_sql( 'insert into perm              select 1,1,1                from all_objects               where rownum

    do_sql( 'insert into temp

             select 1,1,1                from all_objects               where rownum     dbms_output.new_line;

    do_sql( 'update perm set x = 2' );

    do_sql( 'update temp set x = 2' );     dbms_output.new_line;

    do_sql( 'delete from perm' );

    do_sql( 'delete from temp' ); end; /

  3,300,328 bytes of redo generated for "insert into perm         "...

     78,768 bytes of redo generated for "insert into temp         "...

  2,165,168 bytes of redo generated for "update perm set x = 2"...

  1,096,796 bytes of redo generated for "update temp set x = 2"...

  3,232,460 bytes of redo generated for "delete from perm"...

  3,229,900 bytes of redo generated for "delete from temp"...

PL/SQL procedure successfully completed.

--可以发现insert最少，update居中，delete最多。

--主要是因为对于undo讲： insert  undo记录信息最少，反相操作是delete，仅仅知道rowid就ok了。 update  undo记录的信息是修改前映像，这种情况取决于前映像的大小，如果开始插入的是NULL，这样undo生成不会太大。 delete  undo记录的信息是完整的一条记录，反相操作是insert。这种情况undo记录的信息最大。

3.如果字段开始插入是NULL的情况：

set serveroutput on format wrapped

begin     do_sql( 'insert into perm              select null,1,1                from all_objects               where rownum

    do_sql( 'insert into temp

             select null,1,1                from all_objects               where rownum     dbms_output.new_line;

    do_sql( 'update perm set x = 2' );

    do_sql( 'update temp set x = 2' );     dbms_output.new_line;

    do_sql( 'delete from perm' );

    do_sql( 'delete from temp' ); end; /

  2,244,640 bytes of redo generated for "insert into perm         "...

     90,748 bytes of redo generated for "insert into temp         "...

  1,163,996 bytes of redo generated for "update perm set x = 2"...

     98,808 bytes of redo generated for "update temp set x = 2"...

  3,235,736 bytes of redo generated for "delete from perm"...

  3,225,808 bytes of redo generated for "delete from temp"...

PL/SQL procedure successfully completed.

--对比上面update的情况可以发现，第2次update生成的redo很少，几乎与insert接近。

--主要是开始插入x都是NULL，这样undo记录的信息很少，生成的redo也很少。