Upgrade : Oracle DB migration from 8i to 9i by export and import route

Resolving The Problem

EMail In from QA Perf ENG group :
How to improve the speed of the IMPORT, here are some suggestions to try:
* Try importing in the data without the indices. Create the indices in later with NOLOGGING.
* Also, make sure PGA_AGGREGATE_TARGET is set high - e.g., 128M. This could help the sort process.
* check to see if the TEMP tablespace is large and is implemented as a true "temporary tempfile" - see PMG ( perf Mgmt guide ).
* Make sure TEMP file is not on RAID 5 but rather on RAID-10.
* Use a higher BUFFER (e.g., 64000000) and COMMIT=Y
* check to see how the disk subsystem is implemented - e.g.,
* is it RAID, if it is, what RAID level
* how large are the RAID LUNs - how many disks in a stripe, stripe width, etc

* Take STATSPACK snapshots every 15-30 minutes. Find out where Oracle is spending its time.
* What does STATSPACK say during the IMPORT - specifically, where are the waits
* if the waits are in the redo logs, consider moving the redo logs to raw devices
* if the waits are in DBWR waits (writing), check to see if the disk subsystem is efficient (e.g., is it RAID-5 (which could be very bad for large IMPORTs))

* Consider running 2 or 3 parallel IMPORTs - take a look at the EXPORT log and see which table took the longest or was the largest.
* Consider IMPORTing the large tables in parallel.
* consider EXPORT with DIRECT=Y - this approach makes EXPORT very fast because Oracle bypasses SQL processing. However, you can only use DIRECT=Y for tables that don't have LONG columns.

Therefore, you will have to have a separate EXPORT with DIRECT=N run for the tables with LONG columns. Fortunately, we don't have that many tables with LONG columns. Please be warned that we ran into issues in the past with DIRECT=Y especially for LONG columns. Make sure to test out this approach fully.