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

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 Sun Feb 25 2001 - 07:42:55 CST