Re: User datafiles and index on separate disks

"Goran" <goran99_makni_at_yahoo.com> wrote in message news:blc1oe$i8e$1_at_fegnews.vip.hr...
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:3f793720$0$1691$afc38c87_at_news.optusnet.com.au...
> > Peter wrote:
> >
> > > 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
> >
> >
> > Please go to www.google.com and search the archives there for a thread
in
> > this very newsgroup called 'Oracle Myths'. A number of hits will appear,
> > but you want the one with about 149 posts in the thread.
> >
> > It *was* a very long-running argument.
> >
> > Short story: indexes and tables do not need to be separated for
> performance
> > reasons, but probably do for management convenience. Undo segments
should
>
> This is not (always) true.
> Please try to perform few simple real-world measurements and you'll find
out
> that *whatever* files (tablespaces) you separate between (physical) disks
> and no matter what you do with them, you'll always gain some (or
> significant) performance gain compared to the files on the same (physical)
> disk.
>

Hi Goran,

Following is a common argument which I've heard (and participated in) a number of times (the latest in a metalink forum thread a few months ago). It kinda goes like things:

That separating indexes from their tables does indeed provide performance improvements can easily be proven by performing a simple experiment. Firstly place both indexes and tables in their own tablespace on a single physical disk and run a sample load on these objects. Hummmm, performance is a little sluggish. Now separate your indexes and place them on a separate tablespace on a separate physical disk from it's tables. Run the same load again and guess what, performance has improved.

There you go, clear proof and evidence that indeed separating indexes from their tables is a performance winner !!

Well actually, that's a little unfair isn't. In the second example, you've spread the load across 2 physical devices whilst in the first you only had the one. How about a third test where you place both the index and the tables in the same tablespace but stripe the tablespace across the 2 physical devices. How does she go then.

Well bugger me, it now actually performs even better than in the second test !!

Conclusion ?

There's a lot of poop regarding the separation of objects and you'll be surprised at how many justify and prove performance improvements on the separation of objects rather than on the addition of a room full of disk drives. Result A may not be due to the action of B but rather as an effect of C or D or E ....

If you can spread I/O load evenly across your devices and reduce any hotspots, you'll go a long way to tuning your environment (although if you can reduce I/O in the first place, you'll do even better). That's not necessarily achieved by separating indexes (which are often heavily accessed and cached remember) from their tables.

It can actually work against you.

Cheers

Richard Received on Wed Oct 01 2003 - 08:12:47 CDT