Re: User datafiles and index on separate disks

Comments embedded . . .

On Tue, 30 Sep 2003 16:49:15 +0200, Rainer Herbst <rherbst_@_rz.uni-potsdam.de> 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.

Sure it is. It's not a matter of how many users are "on the server", it's (partly) a matter of how many oracle client connections are making requests. While client_a is doing a full table scan of the emp table, client_b is accessing the dept_idx to then access the dept table, and client_c is inserting . . . . and client_d is updateing. . . . . and logs are being read and archivelogs are being written . .

> 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.
>

Exactly! Spreading the *actual load*. Not blindly separating indexs and tables as some sort of pre-emptive tuning.

>
>> 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.
Received on Tue Sep 30 2003 - 11:10:02 CDT