Best Oracle Tablespace/Disk Layout?

So what's the best Oracle tablespace / disk layout for JDE One World?

So far, to help prevent disk I/O bottlenecks, we have:

- tables & indexes split into seperate tablespaces;
- large, medium & small tables grouped into their own large, medium & small tablespaces for same extent sizes;
- tablespaces located on logical volumes spread over a number of physical disks & striped.

We currently have 450 user on a 200Gb database and heard that with a database our size we could just lump it all together and stripe across multiple disks.

We have a new SAN with 75Gb drives to plan the new layout!

Any views ?

Hi,

I would also try to make the index and data tablespaces exist on separate RAID setups, even better if they are on separate controllers.

Also try to make your redo logs and archive logs exist on separate setups as well.

Just my 2 cents worth.

Regards,
Harry

Hi

I/O performance
Make sure that table and index tablespaces go into 2 different physical disks. Only then you can gain I/O performance by splitting it into 2 different tablespaces. We also have NAS attached to the enterprise server. All the database files are stored on NAS. NAS uses RAID 4 technology and you can divide it into logical volumne. There is no way we can specify that table and Index tablespace go into different physical disks. According to NAS vendor they uses some kind of caching to enhance the I/O performance and we do not worry about the placement of tablespacs. I think that SAN will use the same technology. If that is the case then you will not gain any I/O performance gain by having 2 different tablespaces for tables and indexes.

Anyway it is always good idea to have separate tablespaces for tables, indexes, small and large objects because as you mentioned that you can specify the default storage parameters(e.g extent size etc) at the tablspace level ( It will be better if can identify some big objects and specify these parameter at the object level which then in turn will override the parameters at the tablespace level), which in turn help prevent fragmentation and thus enhancing the perfomance. Otherwise you will have to reorg the database at the regular intervals.

These are my 2 cents. I hope it is helpful to you.

Hi,

You said that your database is 200 gigs, do you only have one database for ALL environments or is the 200gigs for your production data?

If you have only ONE database, create different database per schema or environments. This will reduce the size of the databases and you can also move your data easier.

Well, a lot could depend on the SAN and your data. If you have a substantial SAN write cache (i.e. 1GB) dedicated to Oracle, the rule of splitting data and logs may not result in faster performance. Your "lump it all together" statement would defineately result in the best performance.

However, if you have no write cache or if the write cache is shared with another system (i.e. Email or file servers) splitting the log and data could result in better performance for process that add or modify data.

Well said Jeremy. There's lot of strategies for improving DBMS I-O by segregating tables/indexes/... that have obsoleted by hardware advances such as RAID and SAN.