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

On Sun, 25 Feb 2001 14:42:55 +0100, "Fred A G" <nospam_at_allowed.localhost> 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
>

IMO the first assertion is just hogwash and should have been reported as a documentation bug, if Oracle 8.0.5 wouldn't have been desupported The reasoning above applies only when only one and at most only process is accessing that index and table. That, however, is a very rare situation (which usually occurs only in Personal Oracle, lol :) The last assertion before 'But hey' is also utter hogwash as that would mean all individual disks indexes on a separate spindles. Their position is more adequately described in the Oracle DBA handbook, if I don't forget I'm going to verify the newer versions of the mnual, and file a documentation bug.

Hth,

Sybrand Bakker, Oracle DBA Received on Sun Feb 25 2001 - 08:29:35 CST