Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why does a SELECT query needs REDO?

Re: Why does a SELECT query needs REDO?

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 11 Feb 2006 06:35:58 GMT
Message-Id: <pan.2006.02.11.06.35.56.817638@sbcglobal.net>


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%'

SQL> /
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.com
Received on Sat Feb 11 2006 - 00:35:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US