Performance degraded after a restore?

Hi all,

I've heard a rumour that the performance of Oracle on a Unix filesystem could degrade after a restore of the database from a backup. The reasoning being that a Unix file is not necessarily a contiguous area of disc but may consist of many fragments scattered all over the disc ( totally transparent to Oracle ). After a restore, the database files may be fragmented, resulting in more and slower physical I/O as the disc heads have to move significant distances to retrieve data which is logically contiguous within the database. The recommended solution being to later export, rebuild and re-import the database.

It seems to me that there could be some truth in this rumour, but does anyone know how significant the effect might be?

My thoughts on this subject:

1. I doubt if the effect is significant: Unless specific care was taken, there was no guarantee that the database files weren't fragmented BEFORE the restore. They might be fragmented AFTER the restore and the fragments might be in different places, but probably no worse than they were before.
2. The Unix filesystem buffers are designed to minimise the effect of fragmented Unix files.
3. I can't see that rebuilding the database is going to reduce the Unix file fragmentation. The creation of the tablespaces will be performed by the Unix file I/O mechanism in exactly the same way as it manages the restore operation, with the same amount of potential fragmentation.
4. However, if the database was originally built, or rebuilt, on an empty and recently newfs'ed filesystem, fragmentation should be minimal. If the database is later rebuilt or restored onto a fragmented filesystem, then fragmentation will increase, with a possible hit on performance.

Please don't advise me to use raw files: I can see all the advantages of raw files in light of the above. I'm just interested in, given that I am using Unix filesystems, whether anyone has noticed a significant reduction in performance after a restore.

Dave.

--
Remove the no-spam bit from my email address to reply. Received on Wed Aug 12 1998 - 04:04:36 CDT