I've inherited an oracle 8.1.7 database running unix 2.6 on a Sun server. All tablespaces have 1 datafile that is currently 3-4 gig and automatically extends when space is needed. Is there any downside to having datafiles > 2 gig?
Printable View
I've inherited an oracle 8.1.7 database running unix 2.6 on a Sun server. All tablespaces have 1 datafile that is currently 3-4 gig and automatically extends when space is needed. Is there any downside to having datafiles > 2 gig?
depends. If you want to distribute I/O across several disks create multiple datafiles. If I/O is not a problem let it be as it is.
Other than managing problems i don't see any down falls.
Definately a managebility concern (oh, and an OS max filesize concern). I keep then either 2GB or 4GB. It's easier to find room for a 2/4 GB file is moving (or recovering) than a 20GB files. If you have to restore and recover a single datafile, smaller is definatly preferred.
2G seems to be the un-written rule. Makes managability a lot easier, as everyone has said. Nothing worse than looking for a 8 gig mount point for one datafile.
MH
Ps. I suppose having your eyes ripped out with a rusty fork would be worse than looking for a 8 gig mount point.
Ofcourse, there are some downsides of having bigger sized datafiles.
1) As everybody said managability becomes a concern.
2) As many will not aggree to this point..and had been detabe on this with no conerete outcome.
The downside is...
OS will always try to allocate, contigious block of Memory on harddisk...and if say while creation of datafile if you give size 2 or more GB, then OS cannot allocate conitigious block to it...rather peices of small blocks will be allocated..
This would have indirect imapct on overall performance of the DB, as its easy to read the Data which is stored as Contigious Block of Memory on harddisk rather than dispersed/scattered blocks of Memory segments.
So its always benificial if you have smaller datafiles, in the perspective of physical reads.
That however small might be the duration to have the data read from scattered Memory blocks to that of Contigious Blocks...it will be significant, had it been a very large DB, or had it been a DB where active Transaction are very large.
Abhay.
Quote:
Originally posted by abhaysk
The downside is...
OS will always try to allocate, contigious block of Memory on harddisk...and if say while creation of datafile if you give size 2 or more GB, then OS cannot allocate conitigious block to it...rather peices of small blocks will be allocated..
This would have indirect imapct on overall performance of the DB, as its easy to read the Data which is stored as Contigious Block of Memory on harddisk rather than dispersed/scattered blocks of Memory segments.
That is, you are using plain disks and your queries are all using full table scans.
Fortunately so far I have not seen a database running on plain disks (except my PC). If database is running in RAID 0+1 or 5 who cares about random, it´s random by nature in that configuration (so it can use fully the SCSI or Fiber channels)
Could you be more explainative on this. I mean the mode of scan on the RAID hardisks...and how random search would not really matter much.Quote:
Originally posted by pando
If database is running in RAID 0+1 or 5 who cares about random, it´s random by nature in that configuration (so it can use fully the SCSI or Fiber channels)
Well, please dont yell at me by saying STFW..and so. I would rather search google..but would be confused to choose among them, the best.:rolleyes:
If any link on that, would be good.
Abhay.
well just tell me how do you ensure contiguos space in a 100 disk RAID 0+1 or RAID 5? contiguos space in which disk?
Abhay,
Even if you have smaller datafiles (say 2GB or less), you have no control over how the blocks/sectors are filled in the hard disk. If there are other files/data on the disk your new files/data bolcks will be scattered around the disk.
Windows disk defragmentaor is a simple example that gives you a visual idea of the data in the disk.
Frankly,I won't be much concerned about disk level frgamentation.
Sanjay :Quote:
Originally posted by SANJAY_G
Abhay,
Even if you have smaller datafiles (say 2GB or less), you have no control over how the blocks/sectors are filled in the hard disk.
Yes, we dont have control on the allocation of Mem on hard disk...That OS controls..
Well with smaller sized files, there is 90% chance of having cotigious blocks...and with bigger sized files you can u have 10% chance of having cotigious blocks..
Well no one will be much concerned about Fragmentation of the Mem at hardisk level...
but Yes, it does have small impact, on wat so ever appl...( This is my feeling )
Abhay.