RE: Myths & legends: temporay segments and redo

From: David Kurtz <info2_at_go-faster.co.uk>
Date: Sun, 12 Feb 2006 13:34:31 -0000
Message-ID: <CKEAJBMGFEOCDBFILPJDIEFOHFAA.info2@go-faster.co.uk>

And just in case there is any ambiguity, it does not matter whether the sort operation is based on a normal permanent table or a global temporary table. Either way this test produces the same result, indicating that there is no redo during a sort-to-disk operation.

regards

---

David Kurtz
Go-Faster Consultancy Ltd.

tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
web: www.go-faster.co.uk

mailto:david.kurtz_at_go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

> -----Original Message-----
> 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
>
> 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

Received on Sun Feb 12 2006 - 07:34:31 CST