Re: Oracle's tuning guidelines (Was: Re: Need suggestions for filesystem config, spindle separation, striping for HP-UX/PeopleSoft)

That's right, Fred. Reading that Oracle manual is somewhat less than eye-opening. It sure does seem to be self contradictory. But, as far as doing the index & table reads in parallel goes - that would not be possible, since the table read could not occur until after the index read completed since the first is a prerequisite in order to do the second. But I agree, that stuff about the drive head on one disk being on the table while the head on another disk is on the index does not make sense - unless they're talking about writes, which would explain the reference to OLTP.

John

Fred A G wrote:

> About this Oracle Perf. Tuning manual...
>
> "John K. Hayes" <aikosys_at_earthlink.net> wrote in message
> news:3A8D753D.63CDF937_at_earthlink.net...
> > The version of the Performance manual is the latest one, for 8.1.7,
 called
> > "Designing and Tuning for Performance". Although, I first saw this in
> the 8.0.5
> (snip)
>
> > Here's what it now says:
> > Separating Tables and Indexes
> > It is not necessary to separate a frequently used table from
 its index.
> > During the
> > course of a transaction, the index is read first, and then the
 table is
> > read. Because
> > these I/Os occur sequentially, the table and index can be
 stored on the
> > same disk
> > without contention.
>
> I can detect some sense in that. But, why won't the O benefit from
> having this logically sequential access split up and done in parallel?
> Maybe they have some sweet deal with the IO God and can guarantee that
> this type of access (small+small IO) never does two physical disk seeks?
>
> > However, for very high OLTP systems, separating
> > indexes from
> > tables may be required.
>
> Because of the lots of small updates?
>
> > Split indexes and tables into separate tablespaces to minimize
 disk head
> > movement
> > and parallelize I/O. Both reads happen faster because one disk
 head is
> > on the index
> > data and the other is on the table data.
>
> What kind of magic disk system are they using?! (And it sounds almost on
> the contrary to what they've said above)
>
> > The idea of separating objects accessed simultaneously applies
 to
> > indexes as well.
> > For example, if a SQL statement uses two indexes at the same
 time, then
> > performance is improved by having each index on a separate
 disk.
> >
>
> But hey, didn't they just say "...can be stored on the same disk without
> contention."
>
> Regards
> /Fad
Received on Mon Feb 26 2001 - 14:22:12 CST