Re: Basic question on RAID array / Tablespace configuration.

Nope, I'm not biting! We've done this one to death in recent weeks on this very newsgroup. I'm not being dragged back into rehashing the entire thread. All I'll say is that indexes being separated from their tables, be it on separate disks, separate controllers or anything else, confers zero performance benefit, in the general case (there will always be particular patterns of access for particular applications in particular environments that refute the general rule). As Niall said just yesterday: if it were that important, why do we stick the entire data dictionary, tables and indexes and all in the one tablespace? Why not have separate DD_TABLES and DD_INDEX tablespaces?

The reason is simple: index access is not simultaneous with table access, but is serialized. Therefore, there is no contention arising when DML is issued against the table. Therefore, separating the two segments resolves precisely zero contention, and thus is pointless.

From the performance point of view, that is. From the ease-of-management point of view, from the different backup schedules point of view, from the options-in-the-event-of-recovery point of view, I still think it's a good idea to separate them into separate tablespaces (separate hard disks is another matter). And I always do. But that's a management convenience, and anyone touting the idea that's it an essential performance pre-requisite (as Mr Niemic does, ad infinitum) is just plain wrong.

I strongly urge you to visit Google and look at the thread 'Oracle Myths' : it started way back in April (I think), with my off-hand comment about the lack of need for index separation. It's had a life of its own since then, but suffice it to say that Jonathan Lewis and Thomas Kyte both contributed along much the same lines: it's performance-wise pointless.

Regards
HJR "Ganesh Raja" <ganesh_at_gtfs-gulf.com> wrote in message news:a8aed4.0206232230.90389fc_at_posting.google.com...
> Howard,
>
> Sepearating the Index and Tables are not really necessary if they are
> going to reside in the same Disk. But according to the OP he is going
> to put the Data and Index TBS in different Disks.
>
> Why do u still feel that seperating Data and Indexes will not help
> him.
>
> Hope to hear your comments on this.
>
> Regards,
> Ganesh R
>
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:<af586l$pl9$1_at_lust.ihug.co.nz>...
> > Not sure, actually. Start with www.ixora.com.au, and follow any links
that
> > Steve has put up. Then visit www.oraperf.com, and read any papers that
Anjo
> > or Cary have up there. Then read Jonathan Lewis' Practical 8i.
> >
> > I'd also recommend a visit to Connor's site, where he has a script that
lets
> > you 'dial a hit ratio'. That's an essential: first time you show your
> > colleagues that, they'll never look at hit ratios in quite the same way
> > again.
> >
> > After that, start testing!
> >
> > Regards
> > HJR
> >
> >
> > "Ryan" <rgaffuri_at_cox.net> wrote in message
> > news:jJlR8.70984$hF5.3034730_at_news2.east.cox.net...
> > > Thanks Howard.
> > >
> > > Some I work with recommends Oracle Performance Tuning 101. What do you
> > think
> > > of this one? Which performance tuning book would you recommend?
> > >
> > > Thanks.
> > >
> > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > > news:af385r$j9b$1_at_lust.ihug.co.nz...
> > > > Niemic's book is about as bad as it gets.
> > > >
> > > > He's the guy who also rants on about hit ratios as if they were
somehow
> > > > important.
> > > >
> > > > Take a look at google.com for the thread in this group called (I
think)
> > > > "Oracle Myths". This is one of 'em for sure.
> > > >
> > > > Regards
> > > > HJR
> > > >
> > > > "Ryan" <rgaffuri_at_cox.net> wrote in message
> > > > news:N28R8.68325$hF5.2890600_at_news2.east.cox.net...
> > > > > Really howard?
> > > > >
> > > > > I could have sworn that I read the the Oracle Performance tuning
book
> > by
> > > > > Richard Niemac and others that you should seperate your table
extents
> > from
> > > > > your index extents on separate drives to avoid I/O problems?
> > > > >
> > > > > Did I read this wrong or is this just another bad book?
> > > > >
> > > > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > > > > news:af21tj$700$1_at_lust.ihug.co.nz...
> > > > > >
> > > > > > "TR" <tman_at_tman.dnsalias.com> wrote in message
> > > > > > news:6t%Q8.26123$XF6.3372526731_at_newssvr10.news.prodigy.com...
> > > > > > > Have an approx 50 GB database (that is data+indexes) that will
be
> > used
> > for
> > > > > > > aggregation queries and other OLAP type of stuff. E.g. write
> > performance
> > > > > > > next to irrelevant, massive sequential reads from index and
> > tables,
> > I
> > guess
> > > > > > > not a whole lot of probe-type random reads. Beware some of
the
> > queries
> > do
> > > > > > > heavy writes to TEMP space.
> > > > > > >
> > > > > > > Hardware available is 8 80GB drives. Loss of data in the
event of
> > a
> > drive
> > > > > > > loss is of very little concern.
> > > > > > >
> > > > > > > Thoughts?:
> > > > > > >
> > > > > > > -> Stripe (RAID0) all 8 drives, then logically partition into
> > Data,
> > Index,
> > > > > > > Temp, etc.
> > > > > > > -> Stripe (RAID0) 3 drives for Data, 3drives for Index, 2 for
> > Temp.
> > E.g.
> > > > > > > ensure that index and data are on separate physical devices.
> > > > > >
> > > > > >
> > > > > > You've not been reading recent threads here, have you?
> > > > > >
> > > > > > There is precisely zero benefit in separating tables from their
> > indexes
> > for
> > > > > > performance reasons. Both are segments. Just like Table A and
Table
> > B
> > are
> > > > > > both segments. Separate A from B by all means, but unless you're
> > consistent,
> > > > > > there's no point in separating a table from its index.
> > > > > >
> > > > > > Except for ease of management. Which I don't think you're even
going
> > to
> > > > > > obtain in a RAID environment.
> > > > > >
> > > > > > TEMP, yes. Maybe. Rollback, yes, maybe. But not tables and
indexes.
> > > > > >
> > > > > > > -> Any better configurations?
> > > > > > >
> > > > > >
> > > > > > I'd be going for 3 for data+indexes. 3 for rollback. 2 for temp.
> > Just
> > my
> > > > > > thoughts.
> > > > > >
> > > > > > Regards
> > > > > > HJR
> > > > > >
> > > > > >
> > > > > > > Thanks,
> > > > > > > TR.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
Received on Mon Jun 24 2002 - 02:35:15 CDT