Oracle Utilities Tips
Maximizing Export Performance
Many Remote DBAs are faced with the challenge of speeding up utility
functions such as export. Typically, an organization has only
a small window for maintenance, and utility jobs must complete
within that timeframe. Fortunately, there are a few things a
Remote DBA can do to expedite exports. These include:
• 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 Subsets – By subsetting the data using the 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.
• Use a Larger 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
buffer size = rows in array * max row size
• Separate 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.
For the table with one million rows, the following benchmark
tests were performed using the different export options.
Table 4.1- Benchmark tests performed using the different
The table above reveals a small improvement in performance was
obtained by increasing the buffer size on a conventional
export. Using direct=y offered no performance boost over
conventional, until it was accompanied by recordlength, which
reduced the elapsed time by 25 percent.
Once data has been successfully copied to an export file, it
can then be used by the import utility, as described in the
To learn more about these techniques, see the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it directly from the
and get instant access to the code depot of utilities scripts.