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.
- Use a parallel export - You can [parallelize
your export to degree cpu_count-1, but it is important to remember
to also split to dmp file so that expdp can write to multiple files
at the same time. The %u argument allows Oracle to
create multiple dump files, one for each parallel process:
- 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
Get the Complete
Oracle Utility Information
The landmark book
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.