Re: Why "Separating Data and Indexes improves performance" is a myth?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 23 Apr 2004 21:29:54 +0100
Message-ID: <00a701c42971$c219f1e0$7102a8c0@Primary>

Notes in-line

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Daniel Fink" <Daniel.Fink_at_Sun.COM> To: <oracle-l_at_freelists.org> Sent: Friday, April 23, 2004 8:41 PM Subject: Re: Why "Separating Data and Indexes improves performance" is a myth?

Rollback/Undo segments are not written from beginning to end. Blocks in an undo
segment are reused, perhaps by the original segment, perhaps 'stolen' by another
segment or deallocated. I've never done any formal testing (if anyone has, please
correct me), but it is logical to me that undo i/o patterns are similar to data/index
segments.

[JL] I think in general you are right. If I have 30 users doing small
transactions
then I keep updating 30 undo segment headers - which will therefore only be written on checkpoints, and I also have 30 scattered undo blocks being updated
and these will eventually be written because they are not likely to be subject
to many repeated cache reads. So several, scattered blocks will be written from an undo tablespace at any one time. i.e. pretty much like data/index, perhaps
slightly more like index.

[JL] However, if I have a single very large transaction, I will probably
update
many consecutive blocks in a single rollback segment - and consecutive groups
of blocks will probably be written at the same time. (Perhaps allowing for the
DBWR block coalesce that Tanel mentioned a few days ago). This would be more like TEMP than anything else.

                 It would not suprise me if the i/o pattern of temp

tablespaces are closer
to data/index/undo than redo.

[JL] Temp tends to be direct multi-block (i.e. large) reads and writes
though;
which tends to make it pretty different from anything other than direct path loads, CTAS and index rebuilds. So I think it might be a mistake to compare it with any other type of I/O.

Redo is written from beginning to end.

Daniel Fink

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Apr 23 2004 - 15:27:04 CDT