Re: Myths & legends: temporay segments and redo

On 02/11/2006 05:27:26 PM, Jonathan Lewis wrote:
>
>
> I may have missed something, but I didn't see
> an example of temporary segments used by
> sorting generating any redo. Your example showed:
>

You are right, as usual. I created a bit larger table and forced sort to disk. Redo size remained the same:

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
sorts (memory)                                                         2741
sorts (disk)                                                              2
sorts (rows)                                                        4017884

SQL> select n.name,s.value
from v$statname n, v$mystat s
where n.statistic#=s.statistic# and
name = 'redo size'; 2 3 4

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               672

SQL> select count(*) from scott.test_redo;

  COUNT(*)

   2000001

SQL> desc scott.test_redo;

 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------- COL1                                               NUMBER(9)
COL2                                               VARCHAR2(20)

SQL> Redo size was 672 before sorts that were forced by setting WORKAREA_SIZE_POLICY to "MANUAL" and setting the sort_area_size for the session to 64K. The LED on my hard disk immediately showed intense disk activity, but redo size remained the same. Had there been redo activity, it would have shown. GTT are obviously a very special case.

-- 
Mladen Gogala
http://www.mgogala.com

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