Myths & legends: temporay segments and redo

There is a popular myth that modifying blocks in the temporary segments will not generate redo. I created a global temporary table and found out that modifying blocks in the temporary segments does generate redo information. Yet, this myth is very persistent and I don't quite know the source of it. Has anyone else encountered that myth and why exactly are temporary blocks protected by redo? The only reason I can master is logical standby, but it looks like a long shot. In particular, the corollary of this statement is that select statements will generate redo logs, if they entail large sorts.

Here is the proof:

SQL>
SQL> drop table t_emp;

Table dropped.

SQL>
SQL> create global temporary table t_emp   2 on commit preserve rows
  3 as select * from emp where rownum<0;

Table created.

SQL>
SQL> select n.name,s.value
  2 from v$statname n, v$mystat s
  3 where n.statistic#=s.statistic# and   4 name = 'redo size';

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

redo size                                                             23212

SQL>
SQL> declare
  2 i integer:=0;
  3 begin
  4 for i in 1..5000
  5 loop
  6 insert into t_emp select * from emp;   7 end loop;
  8 end;
  9 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>
SQL> select n.name,s.value
  2 from v$statname n, v$mystat s
  3 where n.statistic#=s.statistic# and   4 name = 'redo size';

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

redo size                                                            937172

SQL> spool off;

In another session:

  1 select t.username,s.sid,t.tablespace,(t.blocks*8192)/1048576 "MB"   2 from v$sort_usage t,v$session s
  3* where t.session_addr=s.saddr
SQL> /

USERNAME                    SID TABLESPACE                             MB


-------------------- ---------- ------------------------------- ---------

SCOTT                        59 TEMP                                 4.00

--

Mladen Gogala
http://www.mgogala.com

--

http://www.freelists.org/webpage/oracle-l Received on Sat Feb 11 2006 - 15:26:20 CST