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
- table "scan" IO is not always multiblock
- 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 <sfaroult_at_oriole.com> 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: sfaroult_at_oriole.com
>
> 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: ListGuru_at_fatcity.com (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: hamcdc_at_yahoo.co.uk
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: ListGuru_at_fatcity.com (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).
Received on Wed Oct 23 2002 - 14:44:41 CDT
