What is the benefit to crate multiple datafiles for single Tablespaces

Oracle 8.1.7 and Sun Solaris

Each tablespace need 12GB spaces
Should I create one 12 GB datafiles (OS allow 15GB), or create 4, 3 GB datafiles.
Storage is RAID 5 system

Thanks in advance
OraMan
:)

I think you cann't get performance improvement by creating multiple datafiles in a RAID system.

Bye the way. There is a heavy impact on performance to use RAID 5 in an OLTP system.

Imagine you have to move your datafiles to another volume. The chances of finding single chunk of 12Gb(even more when datafile grows) are smaller than finding 3Gb one. Also moving one big file will take much longer than four small files in parallel.

And keep in mind that some Oracle versions have limitations of 2Gb datafiles, that includes exp/imp utilities.

Think about restore/recovery of this huge datafile too. If you do datafile recovery, do you prefer to do it on 12Gb file or 2Gb one??


Hope that helps,

clio_usa
OCP 8/8i/9i DBA

I don't know, but something about plugging your forums in our forums doesn't seem quite right to me.

Regardless, just wanted to let you know that I followed the first link to your site (the main page) and got code instead of a web page. You might want to look into that.

- Chris

Quote:

---

Originally posted by chrisrlong
I don't know, but something about plugging your forums in our forums doesn't seem quite right to me.

---

I agree, too much like advertising to me...

Clio, I have never encountered an "Oracle version" that had a limit on datafile size. I have seen OS's with that problem, Unix can have a 2G limit unless large files are enabled. I know winzip has a 4G limit and files size.

MH

----------------------------------------------------------------------
Originally posted by chrisrlong
I don't know, but something about plugging your forums in our forums doesn't seem quite right to me.


--------------------------------------------------------------------

Also looks like a complete rip off of this site as well

Now, I don't want to come down too hard here. clio has 75 posts and honestly answered the question here. I also looked through the history and found only real responses to threads. So he/she isn't here simply to advertise but to actually participate. And I don't want to dissuade anyone from actively participating. I simply think that plugging a 'competing' (in the loosest possible sense of the word) forum is a little 'odd', shall we say :). I don't think it 'crosses the line', as it were (although it may be in the 'grey area' ;)).

I had simply felt the need to make a little passive-aggressive comment about the plug before telling him/her about the problem with the home page. We'll save the lynch mob for when Rory Wong comes back :D.

- Chris

Leaving the Madison Ave. aside.

Versions of Oracle prior to 8i had a 2GB file limit.
Export and Import files (per se) prior to 9i had a 2 GB file lime, but the file size limit since 8i has been the OS limit. (We are ignoring the problems with 4GB boundaries on several platforms, those are/were bugs that were fixed in the next release.):rolleyes:

9i is basically the OS limit (except for bugs.......)

Some one mentioned that if you run Parallel query it could access all files simultaneously and give you fast result. Any one has this kind of issue


-- Thanks

I meant to say, but forgot, (Thanks ORaman).

The advantage to smaller files/multiple files for a tablespace is not only the parallel query OraMan mentioned, but overall performance can improve with the load balancing of putting the files on different spindles/drives (sets of spindles/drives if RAIDED.)

I am not sure (don't think that) it helps if you are on a monolithic drive or as in our case single channeled to EMC/SAN.