Index in different tablespace

Situation:
I have a disk raid 1 mirror, that has the os.(windows 2000), and copy of online redo logs and control files.

And a raid 5, that has the database(2 gig).

I have only 1 tablespaces that contains my tables(638) and indexes(993).

I think that one of the good reason to keep index in a different tablespace is the save the io on disk. But since im on a raid 5, i dont see the utility to put the indexes on a different tablespace.

My tablespace is a localy managed tablespace with uniform extent 128k.

Do you have more arguments on why to put indexes on different tablespace. Maybe to use a tablespace with bigger extent?
Any suggestion?

There is still a slight benefit on performance (your DB is small at 2GB) but the major benefit is logical administration ease. I'd keep Table and Indexes apart.

You get better performance spliting the RAID 5 over two separate filesystems. Then having your TABLE and INDEX tablespaces on each indivudual filesystems.

Cheers,

Hi

First of all, who said that on RAID 5 i/o doesn't matter.

Use RAID 5 only when u have high reads e.g. for LDAP.

If u want to keep a database keep it on RAID (1+0)

Change the RAID if u can or u will be going to be in trouble....

U can ask any good consultant what they have to say about RAID 5

Regards
Amar

I don't believe there to be any performance of maintenance banefit from separating indexes and tables into different tablespaces -- look at asktom.oracle.com or the google forums for many discussions on this.

I've been following advice and putting objects in tablespaces based purely on their size for quite a while now, and it works great. Maybe a dedicated TS for a particular table, of for different partitions of a hash-partitioned table, but other than that make it simple for yourself.

Well, we always sell server with raid 5, and the server usely runs only oracle, so what can i say to my supervisor to make him sell good disk configuration to clients? And what is that disk configuration.

It is always windows 2000, with at least 1 gig of ram, the os is always on disk raid 1 mirror. I cant change the os we are selling for a lot of reasons, but i know i could change the damn disk configuration, i found at least 6 of my clients that have a lot of disk contention.

What does good consultant have to say about RAID 5?

Basicaly, i want to know advantage and disavantage of both configuration. Give me links if you have some.

Steve,

Not a consultant but . . .

I started with a RAID-1 plus RAID-5 just like your config. Quest's "Spotlight on Oracle" showed waits on the log files but (normally) nothing much else. I concluded it was due to having the pagefile on the same drive as the logs

I bought some consulting time from Oracle and we came up with the following:

- get the log & ctl files to their own disk (RAID-1) - every bit of doc I have read says this. Now you have make sure that the remaining 18Gb minus 80Mb is not used for anything else (at least during working hours)! I use it for "archive" material (NO, not archive logs!) and I backup my test system there over-night.

- the more logical disks the better, so get as many RAID-1 pairs as you can afford (until you have one db file per disk - the money usually runs out first).


The compromise I came to was 8 physical disks (4x2) instead of 5 (1x2 plus 1x3) - the question is, can you sell that?
YES, data & index are separate. A single query with full table scans does not slow down OLTP - two at the same time does, but it's acceptable.
(For completeness:
Dual processor
Disk1: OS, pagefile & "file server"
Disk2: Data & RBS
Disk3: Index, System, Temp & ArchLogs
Disk4: Logs & Ctls ONLY)
I'm sure there are arguments for different configs - I'm happy with this.

Im suprise that log and control file generate that much io to be only those on one disk, have any doc on this?

They don't! A commit is not complete until the log is physically written (anything else can remain in memory). Anything that gets in the way of the writing of logs can start a traffic jam. Putting logs on their own disk makes sure nothing else can hold up the process.

You could look at the Oracle supplied doc: "8i Designing & Tuning for Performance", chapter 20, section "Solving I/O Problems".

Thanks form the info :)

I will managed for my future client uses raid 1 mirror for their database.

But for the client i have that are on raid5, their is only 1 tablespace that has the tables and indexes.
Does index in a different tablespace with uniform extent lager than 128k will help?

There is no performance improvement through separating tables and indexes.

A good basic starting point is to arrange objects according to their size. You probably want something like three tablespaces for all your tables and indexes as a starting point ...

i) SMALL, uniform extent size 32kb
ii) MEDIUM, uniform extent size 1Mb
iii) LARGE, uniform extent size 32Mb

You can spread the data files for these over different arrays/controllers, or create one set of tablespaces for each array.
a tablespace for small objects with a uniform extent size somewhere between 16 and 64Kb, then another tablespace for lerger objects with a uniform extent size of 16 or 32-times the size of the small-object TX.

Quote:

---

Originally posted by slimdave
I don't believe there to be any performance of maintenance banefit from separating indexes and tables into different tablespaces -- look at asktom.oracle.com or the google forums for many discussions on this.

I've been following advice and putting objects in tablespaces based purely on their size for quite a while now, and it works great. Maybe a dedicated TS for a particular table

---

If you see no benefit to seperating the index and table, then why are you splitting tables into different tablespaces... managability?

Hmmm...


What I can gather there is no performance advantage of seperation, just a managability issue.

These may help..

http://asktom.oracle.com/pls/ask/f?p...1463404632043,

http://groups.google.com/groups?dq=&...40news.free.fr