What is the diff. on performance if using large vs small datafile

[markco]

I have Ora817 in Sun Solaris (E450), and have a 6G tablespace and have data size ~4GB.

Now it have 6 x 1024MB as whole Tablespace.
What is the diff. on performance if
a) a single 6GB datafile as whole Tablespace?
b) 3 x 2GB datafile as whole Tablespace?

Total disksize is 36GB to hold the datafile.

DB have no special option, e.g. intermedia, XML, java, bitmap index.... only have traditional sql operation and have Oracle Standby DB work with it.
Main propose is save/get text (mainly), jpg files in/out the DB.

And it act as a DataWare house, about max 300 DB connection as the same time (peal hrs), avg about 100 DB connection.


Markco Wong

 

[sybaseguru]

Markco,

I do not know the version of Solaris you are running (do uname -a to find out). However, the following general guidelines apply

As you are running on Solaris you have to be aware that the maximum size of a single file cannot exeed 2048MB. In other words each file cannot exceed 2GB. On the other hand a Solaris partition can be as big as you like. So remember that.

Now to the point of using smaller datafiles for tablespaces compared to the large ones, when you use smaller chuncks say 3x2GB datafiles you are effectively using three available spindles in parallel for writes(although this really depends on the underlying number of hard disks). This makes  performance better because you do not have to wait for a single spindle to be available for write where a tablespace is built on a single datafile.

Hope this helps

 

[jimbopalmer]

Yes, the most important division is not to put your redos or archive on the same disk as the Indexes or data.

It is a perfomance gain not to put the indexes with the data.

you can gain further performance by seperating the master tables from the transaction tables

I tried to remain child-like, all I acheived was childish.