Question about spreading I/O accross multiple datafiles for a tablespace

Hi,

I searched the internet, and I wanted to confirm somthing with the experts.

Suppose you create a tablespace using 'x' datafiles instaed of a single one. Will oracle allocate extents in a round robin manner accross all datafiles ?

According to what I've read:

• If you use a locally managed tablespace with an UNIFORM extent size, it will allocate extents in a round robin manner
• If you use a locally managed tablespace with AUTO allocation, it will NOT allocate extents in a round robin manner, that is, it will fill the first datafile and then proceed to the next
• I have absolutely no idea what will happen in the case of dictionnary managed tablespaces.

The reason I'm asking this, is that I have a big application here (An ERP system) that has numerous tablespaces. I have many disks. When the ERP was implemented, it was decided to separate the tables for GL, AR, AP etc... in different tablespace. Index tablespaces were also separated.

The problem is that the ERP vendor sometims send us scripts to change the data model (add tables, index, etc...) but the scripts are generic and do not take in consideration the tablespace or (worst) the storage parameters.

To alleviate the problems, I rebuilt the database and used locally managed tablespacs with auto allocation to fix the storage parameter problems. I still have to move things around and rebuild index after running the vendors scripts, but it's less painful than before.

I was thinking of creating just a couple of tablespaces (LMT, auto allocation) now (one data, one index) and spread the load accross many disks. I do not have hardware raid, the disks are mirrored with sun volume manager (aka disksuite, I know there is a write penalty but I can live with that)

But according to what I read, if I try to to this, and use LMT with auto allocation, oracle will not spread the load. I could use uniform extent size, but I would then have to segregate tables by size, which will be as worst has before when come the time to run one of the vendors scripts.

Povided someone understand my bad english and my situation, does anyone here have a good suggestion for dealing with this ?

Thanks a lot Received on Fri Sep 17 2004 - 12:36:40 CDT