Few big or many small datafiles per tablespace?

From: Ronny <nitelyjoy_at_ist-einmalig.de>
Date: Tue, 8 Jul 2008 16:41:14 -0700 (PDT)
Message-ID: <b4d7218f-ef3d-4d68-9f97-625be125eeb0@s50g2000hsb.googlegroups.com>

We are on on Linux SLES-10 (64-bit) and we are analyzing the IO setup of an OLAP/DWH system (about 800GB in total). We have a large table of about 50 GB in size and it is stored in its own tablespace.

Now the question comes up whether few big (say 2 x 25GB) or many small (say 25 x 2GB) datafiles are more beneficial. We have currently DB_WRITER_PROCESSES=8. Can there be only one file handle to access a datafile? Or can a datafile be accessed by more than one db writer process? So dbw0 could read at the beginning of a datafile, and dbw1 could read at the end at the same time.

Any suggestions for a good datafile size independently from DB_WRITER_PROCESSES? Thanks for your comments,
Ronny Received on Tue Jul 08 2008 - 18:41:14 CDT

Original text of this message