Oracle7 Server Utilities
Export Utility: Direct Path Export
Oracle Export utility's Direct Path export feature extracts data much faster than a conventional path export. Direct path export achieves this performance gain by reading data directly, bypassing the SQL Command Processing layer.
For added performance, the database can be set to direct read mode thus eliminating contention with other users for database resources because database blocks are read into the Export session's private buffer, rather than a public buffer cache.
Conventional Vs. Direct Export Methods
Export provides two methods for exporting table data:
Conventional Path Export
A conventional path export uses the SQL statement "SELECT * FROM TABLE" to extract data from database tables. Data are read from disk into a buffer cache and rows are transferred to the evaluation buffer. The data, after passing expression evaluation, is transferred over the network to the Export client which then writes the data into the export file.
Direct Path Export
A direct path export causes the export data to be read directly, bypassing the evaluation buffer, and saves on data copies whenever possible. It optimizes the execution of a SELECT * FROM TABLE statement.
Used in conjunction with the database in direct read mode (which causes the Export session's private buffer to be used rather than a public buffer), contention with other users for database resources is eliminated and performance improved.
Figure 1 - 8 shows how database table data extraction differs between these two methods.
Figure 1 - 8. Database Reads on Direct Path and Conventional Path
In a direct path export, data is read from disk into the buffer cache and rows are transferred directly over the network to the Export client. The Evaluating buffer is bypassed. The data is already in the format that Export expects, thus avoiding unnecessary data conversion. The data is transferred over the network to the Export client which then writes the data into the export file.
Preparing the Database for Direct Path Export
Before you can use direct path Export, you must run the upgraded CATEXP.SQL script shipped with release 7.3 after the database has been created.
Additional Information: The actual names of the script files depend on your operating system. The script file names and the method for running them are described in your Oracle operating system-specific documentation.
CATEXP.SQL only needs to be run once. Once run, it need not be run before future exports. This script creates the export views and character set assignments necessary for direct path exports.
Before running Export, ensure that there is enough disk or tape storage space to write the export file to. If there is not enough space, Export will terminate with a write-failure error. You can use table sizes to estimate the maximum space needed.
Table sizes can be found in the USER_SEGMENTS view in the Oracle data dictionary. See the Oracle7 Server Reference for more information.
If you do not run CATEXP.SQL before attempting a direct path export, the following errors will occur:
EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name
EXP-00222: invalid column name
Export terminated successfully with warnings.
Invoking a Direct Path Export
You invoke a direct path export using the following syntax:
EXPORT DIRECT=Y
DIRECT=Y specifies that table data is to be extracted using direct path mode. The default is DIRECT=N, table data is to be extracted using the conventional path.
You can also specify direct path export from a parameter file by using the command-line option PARFILE. For more information about the parameter file, see "The Parameter File" on page 1 - 11 of this manual.
Note: The Export parameter BUFFER, used to specify the size of the fetch array, applies only to conventional path exports. It has no effect on a direct path export. The parameter RECORDLENGTH can be used to specify the size of the Export I/O buffer.
If you want to export release 7.3 database objects to a previous release, and you are concerned about backward compatibility, you may need to use conventional path export, because the direct path export dump file format is not compatible with releases prior to 7.3.
Following are factors that can affect the size differences between direct path and conventional exports.
- Column data can come in pieces in a direct path export, while it primarily comes in one piece in a conventional path export.
The overhead is 2 extra bytes per extra column piece, which can be significant. This overhead occurs because direct path export reads directly from the buffer cache, where data is not in rows. Conventional path export reads from the SQL interface, where the concept of rows is enforced.
- There can be side effects when the export I/O buffer fills. With direct path export, there is only as much buffer as is returned in a table scan. With conventional path export, data returned from the SQL interface is copied and pads the remaining buffer with zeroes.
Note that when a direct path export completes table scans, it reverts to conventional path export behavior when it must handle other objects or write table-end markers (when in table mode).
The amount of zero padding in direct path exports depends upon the point at which export switches from table scans to writing other objects, as well upon the size of the export I/O buffer (set in the RECORDLENGTH parameter).
Given these factors, it's unlikely that the size of dump files for direct path and conventional exports are never the same.
Direct Export Logging Information
Error, warning, and completion messages are logged as described in "Warning, Error, and Completion Messages" on page 1 - 4 of this manual.
Character Set Conversion
Direct path export only exports in the database character set. If the export session character set is different from the database character set, a warning will be displayed, and the export will abort. The user must set the session character set to that of the database before retrying the export. However, the import session and target database character set can differ from the source database character set requiring a character set conversion.
Any character set conversion will lengthen the processing time required for an import. Therefore it is advisable to limit the number of character sets conversions to as few as possible.
The ideal scenario is one in which the import session and target database character set are the same as the source database character set requiring no character set conversion.
In the situation where the import session character set and the target character set are the same, but differ from the source database character set, one character set conversion will be required.
If you currently have differing character sets for the source and target databases and/or the import session, and you plan to export/import between these databases regularly, it is advisable to do a one-time export to align these character sets.
Performance Issues
To reduce contention with other users for database resources during a direct path export, you can use database direct read mode. To enable the database direct read mode, enter the following in the INIT.ORA file:
compatible = <db_version_number>,
where
db_version_number is 7.1.5 or higher.
Set the RECORDLENGTH Parameter
You may improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path export. Your exact performance gain will vary depending upon the following factors:
- the types of columns in your table
- your I/O layout (Database files should be in a different partition than the export dump file. In fact, in an optimal situation these files will reside on separate disk drives.)
If you leave the RECORDLENGTH parameter undefined, it defaults to your system-dependent value. For more information about your RECORDLENGTH default value, see your operating system-specific documentation.
You can change RECORDLENGTH to any value equal to or greater than your system's BUFSIZE. The highest value is 64k because this value is read into a 2-byte number. Changing the RECORDLENGTH parameter only affects the size of data that accumulates before writing to the disk. It does not affect the operating system file block size. If DB_BLOCK_SIZE is greater than BUFSIZE, it is more advantageous to use DB_BLOCK_SIZE, because each table scan can only return data up to DB_BLOCK_SIZE. If this does not fit in the export I/O buffer, export performs separate writes to the disk.
Consider the following values for RECORDLENGTH:
- multiples of the file system I/O block size
- multiples of DB_BLOCK_SIZE
Additional Information: Other factors affect the use of direct read mode. See the Oracle7 Server Administrator's Guide for more information.
Restrictions
The following restrictions apply when executing a direct path Export:
- Direct path export cannot be invoked using Export's interactive mode.
- If the export session's character set is not the same as the database character set, when an export is initiated, a warning will be displayed and the export will abort. The user must set the session character set to that of the database before retrying the export.
Compatibility & Migration
Export files and dump sites generated using direct path export are incompatible with earlier Import versions. For example, an Export file generated using direct path export could not be imported using the release 7.2 Import utility. If backward compatibility is an issue, we recommend that you use Export's conventional path export.