Hello Tim
Maybe I did not express myself as I should
have.
I am in complete agreement with you on this
point.
Yechiel Adar Mehish
----- Original Message -----
Sent: Tuesday, October 22, 2002 10:00
PM
Subject: Re: oraperf comment
Yechiel,
You had mentioned only one possible scenario
(i.e. "user A accesses table while user B simultaneously accesses index")
where there are several other possible, equally-likely scenarios (i.e. "user A
accesses table while user B simultaneously accesses table", "user A accesses
index while user B simultaneously accesses index", etc). Separating
tables and indexes to separate devices does nothing for those other,
equally-likely scenarios, does it? That's the reason for the question
"why?" in the beginning of my last reply...
At issue here is not the concept of parallelism
in I/O. At issue (at least for me) is the "conventional wisdom" that
states/implies that there is some performance benefit of separating tables and
indexes to separate devices. My assertion is that this is
irrelevant for two reasons: a) within a single process the
accessing of table blocks and index blocks are purely sequential and b) tables
and indexes have different I/O characteristics which make it less likely that
they will conflict with each other. In fact, in most situations
datafiles/tablespaces containing indexes generate far fewer physical I/Os than
datafiles/tablespaces containing tables. From an I/O perspective, the
key is not to focus on whether the datafile/tablespace contains tables or
indexes but rather to focus on the volume and type of physical I/O they
generate.
By focusing on the I/O statistics rather than
whether they are tables or indexes, one can make better determinations on how
to distribute I/O across non-RAID devices.
Hope this helps...
-Tim
----- Original Message -----
Sent: Tuesday, October 22, 2002 10:09
AM
Subject: Re: oraperf comment
I do not understand the WHY in the
beginning.
I said that it is better to split according to the I/O
load, but without more data, split between indexes and tables as a typical
sql select will use both.
Yechiel Adar Mehish
----- 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).
|