Re: User datafiles and index on separate disks

From: Rainer Herbst <rherbst__at__rz.uni-potsdam.de>
Date: Tue, 30 Sep 2003 16:49:15 +0200
Message-ID: <blc54m$711$1@zeppelin.rz.uni-potsdam.de>

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.

-- 
------------------------------------------------
Rainer Herbst         Linux - Registered
ZEIK                  User #319157
Universität Potsdam   Usual disclaimers applied!
------------------------------------------------

Received on Tue Sep 30 2003 - 09:49:15 CDT