Re: Myths & legends: temporay segments and redo

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 11 Feb 2006 21:38:51 -0000


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@xxxxxxxxxxxxx>
To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
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

--
//www.freelists.org/webpage/oracle-l



--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2006