Oracle Data Pump Export expdp Tuning Tips
Oracle Tips by Burleson Consulting
December 13, 2008
Question: I am using the Data Pump Utility for
an export with expdp, and it runs terribly slow. What are
techniques for speeding-up an export? Are there performance tuning
techniques for exports?
Answer: Yes, there are several techniques for
speeding up Oracle exports. I recommend Bert Scalzo's book "Oracle
Utilities" for a full discussion of export performance tuning, and
these tips for using the
export query clause.
- Traditional export vs., Data Pump Export -
benchmark tests indicate that the traditional export utility
(exp) runs twice as fast as the Data Pump export (expdp).
- Use a Larger Export Buffer – For conventional
path exports, a larger buffer will increase the number of rows that
are processed between each physical write to the export file. Fewer
physical writes equals greater performance. The following formula
can be used to determine a proper buffer size:
buffer size = rows in array * max row size
- Separate the Tables – Separate those tables
that require consistent=y from those that don’t, in order to
expedite the export. This way, the performance penalty will only be
incurred for those tables that actually require it.
- Use Direct Path – Direct path exports (direct=y)
allow the export utility to skip the SQL evaluation buffer, whereas
the conventional path export executes SQL SELECT statements. With
direct path, the data is read from disk into the buffer cache,
returning rows directly to the export client. This can offer
substantial performance gains, depending on the actual data. When
using the direct path, the recordlength parameter should
also be used to optimize performance.
- Use faster media - An expert is I/O intensive
and exporting from faster media like SSD will greatly reduce total
- Use export Subsets – By sub-setting the data
using the export query option, the export process is only
executed against the data that needs to be exported. If tables have
old rows that are never updated, the old data should be exported
once, and from that point only the newer data subsets should be
exported. Subsets cannot be specified with direct path exports since
SQL is necessary to create the subset.
file=$file tables=myexport query\=" where myexport_pidm in (select myexport_pidm
from purdue.myexport where myexport_to_extract\=\'Y\' and myexport_export_status\=\'N\')\"
In addition to Oracle export utility modes,
the export utility enables the user to specify
runtime parameters interactively, on the
command line, or defined in a parameter file
Also, see my notes on
Export expdp command line syntax
For complete Oracle utilities tips and tricks, see the book "Advanced
Oracle Utilities" by Bert Scalzo.
You can buy it direct
from the publisher for 30%-off and get instant access to the code depot of