To summarise, the goal should be to spead I/O evenly across the devices.
Right?
Raj
"Markham, Richard"
<RMarkham@hafeleame To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
ricas.com> cc:
Sent by: Subject: RE: oraperf comment
[EMAIL PROTECTED]
October 22, 2002
11:49 AM
Please respond to
ORACLE-L
Tim, point well said. Thank you.
-----Original Message-----
From: Tim Gorman [mailto:Tim@;SageLogix.com]
Sent: Tuesday, October 22, 2002 11:15 AM
To: Multiple recipients of list ORACLE-L
Subject: Fw: oraperf comment
...resending, as the original send encountered some kind of "locking
problem" at fatcity...
----- Original Message -----
From: Tim Gorman
To: [EMAIL PROTECTED]
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 -----
From: Yechiel Adar
To: Multiple recipients of list ORACLE-L
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 -----
From: Tim Gorman
To: Multiple recipients of list ORACLE-L
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 -----
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
