|
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.
Deep inside the operating system executables there are
many utilities at the fingertips of Oracle professionals, but until now
there has been no advice on how to use these utilities. From
tnsping.exe to dbv.exe to wrap.exe, Dave Moore
describes each utility and has working examples in the online code
depot. Your time savings from a single script is worth the price of this
great book.
Get your copy of Oracle Utilities: Using Hidden Programs,
Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More today
and receive immediate access to the Online Code Depot!
http://www.rampant-books.com/book_2003_1_utils.htm
Regards,
Don Burleson
www.dba-oracle.com
www.remote-dba.net
|
• Phone (252) 431-0049
