Myths & legends: temporay segments and redo
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 11 Feb 2006 16:26:20 -0500
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
Other related posts:
