|
The options used when the data is exported have 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. The
result is a plain export file whether is was 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.
When tuning import, emphasize reducing the amount
of work that import needs to do. This can be accomplished by committing
less frequently, not importing indexes, not generating statistics, or by
using the buffer parameter to reduce disk access.
For the table with one million rows, the following
benchmark tests were performed using the different import options. The
table was truncated after each import.
|
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% |
The table above shows that increasing the size of
the buffer has a positive performance impact. However, the most dramatic
increase in performance was obtained when setting commit=n. The increase
in the size of the buffer resulted in a marginal improvement when
commit=n.
Before devising a strategy for using export /
import to copy data from one database to another, the SQL*Plus copy
command should be considered.
 |
For more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo,
Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly
from
Rampant TechPress.
|
Regards,
Don Burleson
www.dba-oracle.com
www.remote-dba.net
|
|
|
• Phone (252) 431-0049
