RE: Choosing data file size for a multi TB database?

 There is one other point to think about, and anyone on 10G is welcome to correct me on this. There is an Oracle limit on the number of database blocks that can exist in a file which is something like 4,194,303. There is a document on Metalink, Note:112011.1. It states that this is only pertinent to Oracle 8.1, but I've seen exceeding this limit to cause a crash in 9.2 as well. The answer is to use a very large block size if needed but an 8K block size will allow you up to 34,359,730,176 bytes per data file. OH, you'll also want to check with your OS vendor. HP I know is not happy if you exceed 90% utilization on a mount point.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F
(LABOR)

Sent: Tuesday, August 30, 2005 7:58 AM
To: BranimirP_at_cpas.com; oracle-l_at_freelists.org Subject: RE: Choosing data file size for a multi TB database?

Branimr,

I would choose file sizes as follows:

First, you need to get a ruling on hardware. You might be limited in file size choices depending on the hardware.

Second, I would ask for individual table sizing estimates. Somebody has got to give you information on the data distribution. I would then make file size choices dependent on managing these objects. Cluster small reference tables in the same tablespaces. Separate large/large-growth objects into their own tablespaces. For tables that are ginormous, start with tablespace sizes of 5G. This is completely arbitrary - maybe you want this to be 2G or 10Gig. But pick a value that you can live with. If the table is going to grow to be 500 Gig, how many file pieces do you want to support the one table?

Same thing with indexes. Really it's just a matter of picking a value and going with it. There are no other implications involved - Oracle acts the same either way.

Look closely at partitioning. It may be your best friend here when it comes to managing the objects. You could create a tablespace for each partition - or combine partitions in a set of tablespaces, depending on whether you have a purge criteria for the data.

Good Luck!

Tom

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Branimir Petrovic Sent: Tuesday, August 30, 2005 7:33 AM
To: oracle-l_at_freelists.org
Subject: Choosing data file size for a multi TB database?

How would you approach task of sizing data files for a project that will start with
a 1TB database but may relatively quickly grow to stabilize at around 10TB
mark?

Obvious options are:

• start with many smallish files (like 2GB each), then add some thousands more as the database grows, or
• start with a number of largish data files (in 10-100GB range each), then add more such files to accommodate growth.

Neither of the above options look very desirable (to me at least). First option
might be bad choice with checkpointing in mind, but the second option is not
the
winner if data files ever needs to be moved around. Anyway some initial choice must
be made, and all I'd like at this moment is not to give perilous initial advice...
(admission: once the "ball" starts rollin', this bastard ain't gonna be
mine:))

So from practical perspective - what would be the least troublesome choice?

Branimir

FYI I - OS platform is the darkest secret at this point, as is the hardware
specs
(no-one can tell, early signs of "well communicated, well managed"
project
are all
there)

FYI II - I've never had to deal with DBs much bigger than 100GB, thus the
need for
"reality check"..

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l