Re: The cardinality of tablespace and datafile

An additional factor is the application, that is, are you doing OLTP with single row retrievals (even if several tables) or are you doing reporting with large amounts of multiple row retrievals (OLAP), coupled with the platform.

For OLAP, a small number of large files can help performance (given platform file size limitations, number of drives, etc.) For OLTP a large number of small files might be better. All assume multiple drives.

If you are using an EMC array (1 logical drive, irrelevant number of physical drives), the platform issue is more important.MS platforms tend to retrieve 2K blocks up to free buffer space. HP platforms tend to retrieve an entire inode, irrespective of block size. So in MS, file size is less important, while with HP it is more important, but less so than the number of inodes the file utilizes.

In article <393E48A6.65941290_at_edcmail.cr.usgs.gov>,   Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:
> In terms of performance: If you place all the tablespace's datafiles
 on
> one drive, then it doesn't really matter if you use option 1 or 2.
> You'll improve performance if you use option 2 and put the datafiles
 on
> different drives. Option 3 is similar to option 2, but it doesn't
 make a
> difference (performance wise) if you put 10 tables into 10 small
> tablespace or put the 10 tables into 1 bigger tablespace. The biggest
> factors to performance are: did you spread the data among many drives?
> are you using multiple disk controllers? and the biggest, did you tune
> your application?!?!?!?
>
> HTH,
> Brian
>
> chuan zhang wrote:
> >
> > Hi, all,
> >
> > Could anyone tell me that which the following option is better in
 terms of
> > performance:
> >
> > 1) One tablespace with one big datafile, this datafile might
 increase more
> > if there are more storage.
> >
> > 2) One tablespace with many small or medium datafiles
> > or
> > 3) Splitting many datafiles into many tablespaces.
> >
> > Note, I have considered to put the datafiles into different drives
 in order
> > to increase the performance.
> >
> > Thanks
> >
> > Chuan Zhang
> >
> > Asiaonline Ltd Co.
>
> --
> ========================================
> Brian Peasland
> Raytheons Systems at
> USGS EROS Data Center
> These opinions are my own and do not
> necessarily reflect the opinions of my
> company!
> ========================================
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.


Sent via Deja.com http://www.deja.com/
Before you buy.