Can you have too many datafiles (drawback)
Miho Shibata, January 26, 2004 - 8:57 am UTC
Hi Tom,
Is there a drawback to having too many tablespaces/datafiles?
I'm thinking of a backup solution for a data warehouse.
Once a partition is loaded, set the tablespace read-only
until it gets rolled out. Saves on Backup requirements.
This would require minimum one tablespace per partition
(i.e. a lot of tablespaces).
But this will result in a lot of datafiles. Can this
have adverse effects (e.g. CHECKPOIONT).
Thanks Tom.
Miho
January 26, 2004 - 9:14 am UTC
if they are read only -- checkpointing isn't going to be an issue there.....
it is a very valid approach. very common in fact.
Problem with a larger datafile
Pichai Bala, January 26, 2004 - 12:54 pm UTC
Hi Tom,
Thanks for your excellent service. Personally I know of an occassion where we had a trouble in dropping a tablespace of 80G having only one datafile. So now I follow the following: fan out the tablespace as a set of datafiles like 80G is split into 9 datafiles of 9G each. This way it will help the IO also by reducing the contention as well as helps in dropping the tablespace easily.
What is your opinion regarding?
January 26, 2004 - 1:09 pm UTC
why do you believe it was the size of the file.
sorry but "we had a trouble" is so vague.... no one can respond to that.
It was more likely (guess) that
a) this was a dictionary managed tablespace
b) you had an object with thousands of extents
c) you tried to drop it including contents
d) it took a really long time
solution: don't use DMT's, use LMT's.
I doubt the size of the file had anything to do with anything. You would have had that issue with 9 9gig files.
OS Striping is much better than putting files here there and everywhere.
Great
Pichai Bala, January 26, 2004 - 1:43 pm UTC
Hi Tom,
All your guesses are true. Also the files are OS striped.
Now we use only LMTs.
Thanks a lot