Donald K. Burleson Oracle Tips

Speeding-up Oracle imports

When importing large volumes of production data, getting the job done quickly can make a big difference to you end-users.  In Oracle, we often see imports being done as a part of Oracle table reorganization or as part of a data migration.

There are several other techniques you can use to improve the speed of the import utility.  These techniques include:

• Use a large BUFFER size in your parameter file. This reduces database I/O by reducing the number of times that Oracle has to go to the export file for data. Several megabytes is usually enough, but if you have the memory, consider using an even larger buffer size. Again, check for paging and swapping at the operating system level to see if your setup is too high.

• Always use INDEXES=N. It is always faster to build the indexes after the table has been imported.

• Use COMMIT=N in your parameter file. This will cause import to commit after each object (table), not after each buffer. This is why one large rollback segment is needed.

• Use one large, dedicated rollback segment for the import. To do this, take all other rollback segments offline. One rollback segment, approximately 50% of the size of the largest table being imported, should be large enough.

• Put the database in NOARCHIVELOG mode until the import is complete. This will reduce the overhead of creating and managing archive logs.  However, a cold backup must be taken immediately after doing unrecoverable operations to ensure roll-forward capability.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm