Ray,
I don't know exactly what was intended with the
comment, but I agree with your interpretation.
---
As far as any other reasons for the
comment...
<RANT>
In terms of myths that have persisted with
Oracle over the years, the idea that some performance benefit exists
from I/O parallelism due to separating tables and indexes to different
devices has been especially persistent. I've even heard it described as
"conventional wisdom". As a matter of fact,
there is no possibility for "parallelism" benefits on indexed I/O
operations. Never has been; might never be (though "never"
is a long time)...
</RANT>
The reason is that navigating a B*Tree index
structure is inherently sequential. Think about it -- first you have to
access the "root" block. Looking inside the contents of the "root" directs
you to the next "branch" or "leaf" block in the index B*Tree
structure. You cannot seek for the next block in parallel;
you've got to look inside one block in order to know what block to access
next. Then, once you've accessed down to the final "leaf" block, reading
its contents tells you which row in the table to access. If you are doing
a "range scan" operation, then you have to go back to the index "leaf" block in
order to find the next table row to access.
The name of the wait-event for this type
of I/O (a.k.a. "db file sequential read", a.k.a. single-block random-access
read) also suggests this "sequentialiality" (is that a word?).
Jeff Holt wrote a great paper on the reasons for the apparent mis-naming of the
wait-events "db file sequential read" and "db file scattered read" -- I'm sure
that it is downloadable from http://www.hotsos.com. Even when "asynchronous I/O" is available and configured, indexed
I/O operations are still essentially synchronous (and
non-parallel)...
There is a possibility of some form of
"parallelization" in "range-scan" operations, but there is no evidence that this
is happening. For example, while performing an indexed range-scan, if
we wanted to read a batch of index entries from the index "leaf blocks" and
submit a list of I/O requests for data blocks on the corresponding table, we
could do so. However, when I've performed "truss" operations on an Oracle
server process performing such a range-scan operation (at least through
Oracle8i), I've not seen this happening. Purely generic "read()"
operations, one at a time, sequentially...
---
The only real advantages of separating tables from
indexes into different tablespaces are:
- different recoverability requirements
- indexes can be rebuilt instead of restored
- data (tables and clusters) must be restored -- cannot be "rebuilt" from anything
- different types of I/O requests
- indexes are predominantly accessed using single-block, random read I/O (i.e. UNIQUE scans, RANGE scans, FULL scans)
- relatively seldom are accessed with multi-block sequentially-accessed read I/O (i.e. FAST FULL scans)
- while tables are often accessed with a mix of the two types of I/O, depending on the application
- OLTP usually has heavier single-block, random read I/O due to heavy use of indexes
- DW usually has heavier multi-block, sequentially-accessed read I/O due to heavy use of FULL table scans
- may be advantages from this in Oracle9i where different blocksizes are possible for different tablespaces
These last points are related to performance, but
not in the sense that the mythical "conventional wisdom"
dictates...
Hope this helps...
-Tim
----- Original Message -----
From: "Ray Stell" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>
Sent: Monday, October 21, 2002 2:43 PM
Subject: oraperf comment
> An recent oraperf report included the comment: "Never split index
> and data files to different sets of disks." It goes on to state that
> striping is better. If the system in question does not have
> raid support, wouldn't it be better to split the index and data across
> spindles? That would make the word "Never" inappropriate here? Maybe
> this is their way of saying don't use old technology. Is there some
> other reason I am missing?
> ===============================================================
> Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC 28^D
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ray Stell
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
