|
 |
|
Optimize Oracle import (data pump impdp) performance
Don Burleson
|
Note: This
is a quick overview of improving data pump import performance for
beginners. For expert-only tips on improving import speed,
see here.
Regardless of
which options were used when the data was exported, it has no influence
on how the data is imported. For example, it is irrelevant to the
import process whether it was a direct path export or not, since it is a
plain export file, be it generated from direct or conventional means.
Unfortunately,
there is no direct option available for imports (only for export
and SQL*loader). The import process has more tuning limitations than
other utilities. The DBA should consider the following when trying to
optimize import performance:
Set
commit=n – For tables that can afford not to commit until the
end of the load, this option provides a significant performance
increase. Larger tables may not be suitable for this option due to
the required rollback/undo space.
Set
indexes=n – Index creation can be postponed until after import
completes, by specifying indexes=n. If indexes for the target
table already exist at the time of execution, import performs index
maintenance when data is inserted into the table. Setting
indexes=n eliminates this maintenance overhead.
Use the
buffer parameter – By using a larger buffer setting, import
can do more work before disk access is performed.
Hidden parameters - You can also
use the hidden parameter _disable_logging = true to reduce
redo, but beware that the resulting import will be unrecoverable.
|
Import
Option |
Elapsed
Time (Seconds) |
Time
Reduction |
|
commit=y |
120 |
- |
|
commit=y
buffer=64000
|
100
|
17% |
|
commit=n
buffer=30720 |
72 |
40% |
|
commit=N
buffer = 64000 |
67 |
44% |
 |
For more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Andrew Kerber,
Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
Also see:
|
