Re: Tablespace distribution on 3 external hard disks

"Ursula Lee" <ursula.lee_at_thales-is.com> wrote in message news:c3av9r$nl22_at_imsp212.netvigator.com...
> Hi all,
>
> Just a general question on tablespace distribution on 3 external hard
> disks. (Running on Oracle 9i database on HP-UX11)
>
> I have one tablespace created TBS_HIS for 2 big tables storing almost
> 100000 daily. And there are 3 external hard disks with 18 G each (but
> one of the hard disks has 12 G left only).
>
> I plan to evenly distributed the table space into that 3 hard disks,
> e.g. total of 29 G, 12 G on one, 12 G on another one, then 5 G on the
third.
>
> Question:
> 1. Is that better to put everything on one hard disk first, then the
> second one and so on....instead evenly distributing the space?
>
> E.g. One table on one tablespace and one hard disk.
>
> 2. I am thinking if this is evenly distributed, would I lost all the
> data if one hard disk fails?

You never lose data in Oracle... provided you're in archivelog mode and take reasonable backups now and then. Oh, and provided you multiplex your online redo logs. And, perhaps most importantly, you know what you are doing.

> 3. What is the benefit of evenly distributed?

I'm genuinely surprised you have to ask. The benefit is nil. What you *will* benefit from, however, is evenly-distributed physical I/O. And the chances are that if you evenly distribute your segments, then the evenly-distributed I/O will follow (though it depends on what your application and your users are actually doing).

What I'm getting at is this. Suppose I have three tables, each of 1GB, and 3 hard disks, each of (say) 4GB. I could store one table on each disk, claim the thing was evenly distributed, and go home for the day. Trouble is, my application hardly ever touches tables 2 and 3, but hammers table 1 to death all day long. The tables may be evenly-distributed, therefore, and so is my free space: but my poor Hard Disk 1 is a terrible I/O hot spot, causing sluggish checkpointing, free buffer waits and (because of the poor checkpointing) redo allocation retries (and so on). It would be better under those circumstances to create table 1 with three extents and arrange for each extent to be on a different hard disk (so that's a tablespace spanning all three disks). The other two tables could go on pretty much any hard disk (so one more tablespace spanning all three disks might be the go).

Even then, it may turn out that extent 3 of my table 1 is where all the DML action is taking place, with only read activity taking place on extents 1 and 2. So maybe things shouldn't be even that simple.

Anyway, you get the idea: stop worrying about evenly-distributing your *space*, and worry about where the I/O is happening, and make sure that's spread as evenly as possible instead.

Regards
HJR Received on Wed Mar 17 2004 - 21:07:03 CST