Re: User datafiles and index on separate disks

"Rainer Herbst" <rherbst_@_rz.uni-potsdam.de> wrote in message news:blc54m$711$1_at_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.

So you have SMON,DBWR,the apache connection pool all talking to the DB. Oracle is always a multi-user system, even if you have only one end user process. Spreading the load across more disks will naturally result in performance gains over one disk, but how much of a performance gain did you see, now compare it to splitting objects by size, or by name, or by creation date. In and of itself data/index probably won't show any noticeable improvement.

That doesn't mean that it doesn't make sense to separate data and indices - but the benefit is manageability not performance.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK