Re: Why "Separating Data and Indexes improves performance" is a myth?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Apr 2004 21:29:54 +0100
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@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
