What is relation of datafile size and performace?(x7)


Has the size of the datafiles some influence on Oracle DB Server performance? Is it better to have more smaller datafiles or few larger datafiles?


Ans1:

If you use smaller datafile you can distribute ones accross available disks. It improves performance in multi-user or parallel query/server environment.
In some OS you can create larger datafile and use stripped logical volume for filesystem or raw device location.
It is correct for normal operation,but media recovery operation is slower for larger files and may be more complicated.



Ans2:

Look at your disk I/O (PHYBLKRD + PHYBLKWRT from v$filestat) per disk (join on file# to something like v$datafile or v$dbfile) to see if you have some disks being over-utilised and other disks under-utilised. If you have, splitting hot areas across smaller datafiles on different disks rather than one large datafile on one disk could remove an i/o bottleneck.
 
 
 

Hosted by www.Geocities.ws

1