[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也很少。