Re: Myths & legends: temporay segments and redo

As a cross-check, you need to compare the redo from using a GTT with the redo from using a normal table.

The answer to your puzzle is that changes to the GTT do not generate redo, but they do generate undo, and the undo generates redo.

Arguably, the undo has to be generated, as you may want to do a rollback to savepoint part way through the transaction. Since you have a GTT with no indexes, and 'on commit preserve' you might also like to try a very large insert with an /*+ append */ hint - this gets rid of the undo as well. (Note - there are various reasons why both the undo and redo volume generated can be much more than you expect - foreign keys, row-level triggers, and referential integrity constraints are all features that disable various of the special undo/redo optimisations).

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "Mladen Gogala" <gogala_at_sbcglobal.net> To: "oracle-l" <oracle-l_at_freelists.org> Sent: Saturday, February 11, 2006 9:26 PM Subject: 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

--

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