Bigfile or smallfile tablespace?
633566Oct 31 2008 — edited Jul 27 2010Hi community,
I'd like to set up a Oracle database (Version 10.2.0.3 64-bit) on a Windows Server 2003 (64-bit).
Server Hardware:
- Dual Core AMD Opteron
- Processor 8224 SE, 3,21 GHz
- 16 GB RAM
Storage:
- HP StorageWorks MSA 1000
- 3 x 250 GB (RAID 1)
- At the moment the 250 GB drives are combined to one large 750 GB Logical Drive (RAID 0)
The data content for the new database are coming from an DB2 (will be copied to Oracle DB) and the size is about 350 GB at the moment, upward trend.
Data:
150 tables with less than 100.000 rows.
40 tables with more than 100.000 rows but less than 1.000.000 rows.
15 tables with more than 1.000.000 rows (biggest table has 125.000.000 rows)
So my questions now regarding how to size a tablespace for the new Oracle instance:
1. Should I create a bigfile tablespace with one large datafile (350 - 400 GB) or should I create a smallfile tablespace with several smaller datafiles?
2. At the moment the db_block_size is 8 kb. This means that I can create a datafile for a smallfile tablespace with a maximum of 32 GB. So for 350 GB I need at least 11 datafiles. I think this is not a good solution. So maybe I should change the db_block_size to 16 KB so I can create datafiles up to 64 GB? What effects will this change of the block size bring?
3. Is it better, for performace issues, to use the 750 GB RAID10 Array or should I use three 250 GB RAID1 Arrays and allocate different datafiles on them?
4. For the beginning should I size the tablespace as big as it really is (350GB) or should I size it bigger (400GB)?
I would really appreciated, if somebody can help me with this issue...
Thanks in advance,
Tobias Schmidt