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: 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).