Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Why does a SELECT query needs REDO?
On Thu, 09 Feb 2006 08:05:19 -0800, Jining Han wrote:
> I am wondering why there's 7749080 redo size (physical read, I assume)
> involved.
You probably have some sorting there. Temporary extents do generate redo information. They don't generate undo, but they do generate redo. Here is the proof;
1 create global temporary table t_emp
2 on commit delete rows
3* as select * from emp
SQL> /
Table created.
SQL> declare
2 i integer;
3 begin
4 for i in 1..2000
5 loop
6 insert into t_emp
7 select * from emp;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select distinct sid from v$mystat;
SID
50
SQL>
1 select n.name,s.value
2 from v$statname n,v$sesstat s
3 where n.statistic#=s.statistic# and
4 s.sid=50 and 5* name like '%redo%'
NAME VALUE ---------------------------------------------------------------- ---------- redo synch writes 3 redo synch time 0 redo blocks read for recovery 0 redo entries 2152 redo size 366360 redo buffer allocation retries 0 redo wastage 0 redo writer latching time 0 redo writes 0 redo blocks written 0 redo write time 0 redo log space requests 0 redo log space wait time 0 redo log switch interrupts 0 redo ordering marks 0 redo subscn max counts 0
16 rows selected.
SQL> So, insert into global temporary table caused some amount of redo information to be written. If you have a large sort, it is conceivable that some redo information will be written as well. If redo information wasn't written, standby database could never work. What do you think would happen if I wrote an insert from the global temporary table back to a permanent table? How would standby reconstruct that without redo entries? Contrary to the popular myth, writing to temporary segments does generate redo information. It doesn't generate undo, but it does generate redo. Please, don't tell that to anyone, let the myth survive.
-- http://www.mgogala.comReceived on Sat Feb 11 2006 - 00:35:58 CST