Re: Re: oraperf comment
The main problem as I see it is that you might be lucky in getting IO balance with a tables-here-indexes-there approach in rule based databases, where pretty much the only thing Oracle can do is table scan and single block index read. But since 7.3, and even more so with the more recent releases, suddenly there's index fast full scan, sort direct IO operations, table sampling which means that a) table "scan" IO is not always multiblock b) index IO is not always single block So my IO mantra (which I fail to achieve most of the time) is to aim to obtain a balanced IO load independent of the time quanta. So if I average the IO load for 3 hours, it will be balanced, but if I average it over 3 seconds, 3 minutes, 30 minutes etc then it will still be balanced. Sort of like the Sierpinski curves of IO. hth connor --- Stephane Faroult <[EMAIL PROTECTED]> wrote: > > 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 > > Tim, > > I fully subscribe to your conclusion but I > wouldn't be that harsh > about conventional wisdom, which once had some ring > of truth to it and > still has it on rustic configurations. Granted, for > a given user > parallelizing his or her table and index accesses > doesn't make much > sense. But when you have a lot of happy users > merrily issuing their > queries, you can hope that at some point in time > some will be hitting > indexes while others will be hitting tables - and > when dbwr and its gang > will join the party, both indexes and tables will be > hit too. This is > probably what Yechiel meant. I see conventional > wisdom as a > rough-and-ready rule-of-thumb to make people spread > their I/Os. And at > least the benefit of having separate tablespaces is > that you have > separate files which are easier to move around when > you have a finer > appreciation of what is going on. > > -- > Regards, > > Stephane Faroult > Oriole Software > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Stephane Faroult > 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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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,
Re: oraperf comment
Hello Tim
Maybe I did not express myself as I should
have.
I am in complete agreement with you on this
point.
Yechiel AdarMehish
- Original Message -
From:
Tim Gorman
To: Multiple recipients of list ORACLE-L
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 -
From:
Yechiel
Adar
To: Multiple
recipients of list ORACLE-L
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 AdarMehish
- Original Message -
From:
Tim Gorman
To: Multiple
recipients of list ORACLE-L
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 -
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 ar
Re: 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 Tim, I fully subscribe to your conclusion but I wouldn't be that harsh about conventional wisdom, which once had some ring of truth to it and still has it on rustic configurations. Granted, for a given user parallelizing his or her table and index accesses doesn't make much sense. But when you have a lot of happy users merrily issuing their queries, you can hope that at some point in time some will be hitting indexes while others will be hitting tables - and when dbwr and its gang will join the party, both indexes and tables will be hit too. This is probably what Yechiel meant. I see conventional wisdom as a rough-and-ready rule-of-thumb to make people spread their I/Os. And at least the benefit of having separate tablespaces is that you have separate files which are easier to move around when you have a finer appreciation of what is going on. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
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 -
From:
Yechiel
Adar
To: Multiple recipients of list ORACLE-L
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 AdarMehish
- Original Message -
From:
Tim Gorman
To: Multiple recipients of list ORACLE-L
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 -
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 AdarMehish
- 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
Re: Fw: oraperf comment
Thanks for your comments Tim. The reason I brought this up is that the "conventional wisdom" is, well, conventional. I like your logic, but have always lived on the other side of the tracks on this. I'll say one thing for oraperf, they got my attention. The oraperf writer used the exclusive: "Never split index and data files to different sets of disks." I wonder if they were making an overstatement in an effort to dispell "conventional wisdom". A statement about i/o balancing may be more appropriate. BTW, some of my ts actually tend to operate in the opposite fashion from your generalized argument, but perhaps your argument holds true on the opposite side of the coin, but I still find "Never" tough to swallow: Av Av AvAvBuffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -- --- - --- -- -- TACPLUS_BINDEX 65,711 1 2.7 1.0 59,3531 00.0 TACPLUS_BDATA 4,342 0 12.4 1.01,6150 00.0 Never say never! On Tue, Oct 22, 2002 at 07:14:46AM -0800, Tim Gorman wrote: > ...resending, as the original send encountered some kind of "locking problem" at >fatcity... > > - Original Message - > To: [EMAIL PROTECTED] > Sent: Tuesday, October 22, 2002 6:35 AM > > > 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... > > > 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)... > > > 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
RE: oraperf comment
To summarise, the goal should be to spead I/O evenly across the devices.
Right?
Raj
"Markham, Richard"
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...
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
"p
Re: oraperf comment
Sorry if I caused confusion. I meant disks that have different controllers because Ray is talking about a system WITHOUT raid so striping is not an option. Tim said: 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 just wanted to point that the parallelism helps because Oracle serves more then one user at a time and can benefit from parallel I/O. Yechiel AdarMehish - Original Message - From: Markham, Richard To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 22, 2002 5:03 PM Subject: RE: oraperf comment I'm a little confused when one is talking about putting indexes and tables into seperate TABLESPACES and the other is talking about seperate DISKS. To any extent, I cant imagine how seperating IO types across physical controllers could be anything but rewarding. Yet, splitting across "DISKS" and splitting across "SPINDLES" are two different concepts. You have striping so you can benefit from more heads to do more IO and you'll only benefit more with having more spindles, ~again~, to handle more IO. Splitting these across multiple spindles has proven performance gains for me and I think the "Never split index and data files to different sets of disks." has a bit of ~a CACHE will solve everything mentality~ (no pun intended). ORACLE will feast on a disk cache especially with 11i applications, but thats not to say it doesn't help. Please correct me as i'm looking for guidance. =) -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Tuesday, October 22, 2002 5:44 AMTo: Multiple recipients of list ORACLE-LSubject: 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 AdarMehish - 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... 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)... 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 "paral
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 AdarMehish
- Original Message -
From:
Tim Gorman
To: Multiple recipients of list ORACLE-L
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 -
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 AdarMehish
- 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...
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)...
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"
RE: oraperf comment
Tim,
point well said. Thank you.
-Original Message-From: Tim Gorman
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, October 22, 2002 11:15
AMTo: Multiple recipients of list ORACLE-LSubject: 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 AdarMehish
- 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...
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)...
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
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 AdarMehish - 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... 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)... 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 perfo
RE: oraperf comment
I'm a little confused when one is talking about putting indexes and tables into seperate TABLESPACES and the other is talking about seperate DISKS. To any extent, I cant imagine how seperating IO types across physical controllers could be anything but rewarding. Yet, splitting across "DISKS" and splitting across "SPINDLES" are two different concepts. You have striping so you can benefit from more heads to do more IO and you'll only benefit more with having more spindles, ~again~, to handle more IO. Splitting these across multiple spindles has proven performance gains for me and I think the "Never split index and data files to different sets of disks." has a bit of ~a CACHE will solve everything mentality~ (no pun intended). ORACLE will feast on a disk cache especially with 11i applications, but thats not to say it doesn't help. Please correct me as i'm looking for guidance. =) -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Tuesday, October 22, 2002 5:44 AMTo: Multiple recipients of list ORACLE-LSubject: 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 AdarMehish - 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... 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)... 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 rest
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 AdarMehish - 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... 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)... 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: "
Re: oraperf comment
Ray, In addition, there are apps that expect to find indexes and data in separate locations. SAP is one of those. Jared Ray Stell <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/21/2002 01:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: 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 KE4TJC28^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).
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... 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)... 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
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 KE4TJC28^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).
