[20150301]Temporary Tables and RedoUndo.txt 2.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@test01p> @ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

--12c 加入了temp_undo_enabled 参数,可以把临时表的产生的undo放在临时表空间.缺省设置是false.

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
alter session set temp_undo_enabled=true;
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,259,344 bytes of redo generated for "insert into perm         "...
536 bytes of redo generated for "insert into temp         "...
2,179,232 bytes of redo generated for "update perm set x = 2"...
536 bytes of redo generated for "update temp set x = 2"...
3,244,248 bytes of redo generated for "delete from perm"...
536 bytes of redo generated for "delete from temp"...

PL/SQL procedure successfully completed.

--可以发现生成的redo都是一样的.不论是insert,update,delete. 不像11g的情况.

--摘要:Apress.Oracle.Database.Transactions.and.Locking.Revealed.1484207610.pdf P153

Starting with Oracle12c, in an Oracle Active Data Guard configuration, you can issue DML statements directly on
a temporary table that exists in a standby database. We can view the amount of redo generated for a temporary table
in a standby database by running the same code (from the "Prior to 12c" section) against a standby database. The only
difference being the statements issuing transactions against permanent tables must be removed (because you cannot
issue DML on a permanent table in a standby database). Here is the output showing that 0 bytes of redo are generated:
0 bytes of redo generated for "insert into temp"...
0 bytes of redo generated for "update temp set x = 2"...
0 bytes of redo generated for "delete from temp"...

■ Note there's no need to set TEMP_UNDO_ENABLED in the standby database. this is because temporary undo is always
enabled in an Oracle active data guard standby database.
--好改进，有机会测试看看,没有做过12c的dataguard.

Global temporary tables are often used for reporting purposes—like generating and storing intermediate query
results. Oracle Active Data Guard is often used to offload reporting applications to the standby database. Couple
global temporary tables with Oracle Active Data Guard, and you have a more powerful tool to address your reporting
requirements.
--而且这样可以改进一些报表的处理。

3.看看一些细节:
SCOTT@test01p> alter session set temp_undo_enabled=true;
Session altered.

SCOTT@test01p> insert into temp select 1,1,1 from all_objects where rownum 2 rows created.

SCOTT@test01p> @xid
X
------------------------------
10.11.6462

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBAREC     UBASQN STATUS            USED_UBLK  USED_UREC XID              ADDR
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ----------------
        10         11       6462          0          0          0          0 ACTIVE                    1          1 0A000B003E190000 000007FF5EA2B4D8

--UBAFILE是0.

SCOTT@test01p> select rowid from temp ;
ROWID
------------------
AAQL8HAABAAAL8IAAA
AAQL8HAABAAAL8JAAA

SCOTT@test01p> @lookup_rowid AAQL8HAABAAAL8IAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
   4243207          1      48904          0 1,48904              alter system dump datafile 1 block 48904

--先放弃,有机会再研究看看。