----- Original Message -----
Sent: Tuesday, October 22, 2002 5:14
PM
Subject: Fw: oraperf comment
...resending, as the original send encountered
some kind of "locking problem" at fatcity...
----- Original Message -----
Sent: Tuesday, October 22, 2002 6:35 AM
Subject: Re: oraperf comment
Why? What are the chances of
precisely that scenario happening, as opposed to Oracle doing
concurrent I/O to tables for both users A and B? Or to indexes for both
users A and B simultaneously?
Splitting tables and indexes into separate
tablespaces makes sense, but mainly for recovery purposes. This has
little to do with the placement of the datafiles of those tablespaces
on devices (non-RAID or RAID).
Generally, indexes tend to cache extremely well
in Oracle (because they are more compact and because of the nature of the
I/O), so they usually don't get as much physical I/O as tables. Check
V$FILESTAT on a busy application to prove it for yourself...
After seeing this performance data, why would you
place a datafile/tablespace which only gets a small amount of I/O on one
device while placing a much busier datafile/tablespace onto another device,
just because one contains indexes and the other tables?
Please think in terms of I/O counts, not
poorly-conceived but oft-repeated "conventional wisdom". Keep indexes
and tables segregated to different tablespaces, but for decisions on placement
of datafiles upon devices, use empirical performance data only.
----- Original Message -----
Sent: Tuesday, October 22, 2002 3:43
AM
Subject: Re: oraperf comment
Hello Tim
I beg to differ. Without raid it is better to put
indexes and tables on different disks and controllers.
This way Oracle can do I/O to a table for user A while
doing I/O to the index for user B.
It is better if you can find the high I/O areas of the
database and split them across disks, but as a rule of thumb splitting
indexes and tables make sense (again - when you work without
raid).
Yechiel Adar
Mehish
----- Original Message -----
Sent: Tuesday, October 22, 2002 12:39
AM
Subject: Re: oraperf comment
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 -----
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).