Re: User datafiles and index on separate disks

Rainer Herbst wrote:

> Howard J. Rogers schrieb:
>
>> Rainer Herbst wrote:
>>
>>
>>> Peter schrieb:
>>>
>>>> Is it a must to place datafiles, undo segments and table index on
>>>> separate disks? If not, what are the benefits of doing so?
>>>>
>>>> Thanks
>>>>
>>>>
>>>
>>> No, you can have all files on one disk.
>>> The benefit is performance - when e.g. indices and data files are on
>>> the
>>> same disk, and oracle will use an index to locate a row, the disk
>>> header
>>> should go to the index file first, read some blocks, than go to the
>>> datafile and read the data block. The movement of the header is very
>>> expensive in terms of time!
>>>
>>> Regards!
>>> Rainer
>>>
>>
>>
>> Why will this myth not simply die quietly as it should? There's no
>> performance benefit to separating indexes from tables, and the disk head
>> movement argument is just spurious. On a multi-user system, your disk
>> head
>> will probably move in between reads of rows from even a single table,
>> because of the I/O requests that other users are making. Even on a
>> single
>> user system, your disk head moves between *tables* when you read two or
>> more of them in a single SQL statement (ie, a join): so you get disk
>> head
>> movement, with not an index in sight.
>>
>
> How many users are working on your database server? I am in the lucky
> position to have only oracle and apache running on the server, so the
> disk heads are not jumping left and right for other users. And
> spreading the files to more than one disk _did_ make a difference! Of
> course, there is no need to separate the indices from the tables, but
> spreading the load on more than one disk normally lead to performance
> gains.
>
>
>> Never mind that the blocks of an extent of a single segment are not
>> contiguous on disk (unless you use raw partitions), so that even doing a
>> full table scan your disk head is bobbing all over the place.
>>
>> So yes, you can separate indexes from tables to eliminate the head
>> movement,
>> only to discover that the head movement happens because of other
>> reasons.
>> You might as well store every single segment in its own unique data
>> file if
>> that's going to be your argument. And don't forget to dump your file
>> system
>> whilst you're at it, because for true sequential access to Oracle blocks
>> involving minimal head movement you're going to need raw.
>>
>> Sorry Rainer. This one's been done to death, and the argument won't fly.
>>
>> Regards
>> HJR
>
>
> Thanks for your remark, and the link to the Oracle myths. See above
> for comments.
>

Correct. But separate tablespaces has nothing to do with separate disks. Not on a machine with a single large disk, not on a machine with a single channel disk controller, not on a machine with RAID or SAME. Which means pretty
much every piece of hardware on the planet.

Be sure you don't give advice, without clarification, that in almost every case is incorrect. Readers here may not realize the unstated caveats.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)