Traditional path export VS direct path Export (oracle exp direct = y)

Oracle's traditional Export and Import are retained to 11 GB, and many enterprises still use Export and Import to back up and restore 9i and 10 Gb. Since Oracle 7.3, the traditional exp Export Program provides two Export Path methods: Conventional Path Export and Direct Path Export ). This article describes the performance of traditional import and export by a recent customer.

1. Differences between the two
A. Conventional path Export
The traditional path mode uses SQL SELECT statements to extract table data. The data is read from the disk into the buffer cache buffer, and the row is transferred to the evaluation buffer.
Then, the records are returned to the export client based on the SQL expression and written to the dump file.

B. Direct path Export
In direct export mode, data is directly read from the disk to the PGA of the exported session, and the row is directly transferred to the private buffer of the exported session, thus skipping the SQL command processing layer.
This avoids unnecessary data conversion. Finally, the record is returned to the export client and written to the dump file.

2. Performance problems
A. direct path export has better performance and speed than traditional path export, because it bypasses the SQL command processing section.
B. direct path export supports the RECORDLENGTH parameter (up to 64 k). We recommend that you set this parameter to an integer multiple of system I/O or DB_BLOCK_SIZE.
C. Specific factors that affect direct path Export (DB_BLOCK_SIZE, column type, I/O performance, that is, whether the disk drive where the data file is located is independent of the disk drive where the dump file is located)
D. Whether it is direct path export or dump generated by traditional path export, it will consume the same time when using imp for import.

3. Simple Example
> Exp system/manager FILE = exp_full.dmp LOG = exp_full.log \
FULL = y DIRECT = y RECORDLENGTH = 65535

> Imp system/manager FILE = exp_full.dmp LOG = imp_full.log \
FULL = y RECORDLENGTH = 65535

4. Restrictions on direct path Export
A. direct path export does not support Interactive Mode
B. Table space transmission mode is not supported (that is, TRANSPORT_TABLESPACES = Y is not supported). FULL, OWNER, and TABLES export modes are supported.
C. QUERY is not supported. For example, exp scott/tiger TABLES = emp QUERY = \ "WHERE job = \ 'salesman \ '\" is not supported.
D. Use RECORDLENGTH for direct path export to set the amount of data that can be exported at a time, replacing the buffer setting for traditional paths.
E. direct path export requires that the NLS_LANG environment parameter is equal to the database character set, responsible for receiving EXP-41 warnings and EXP-0 termination errors

5. demonstrate the performance differences between the two methods

A. Performance Comparison between traditional path export and direct path Export
# Log comparison
Robin @ SZDB:/u02/database/SYBO2SZ/BNR/dump> more dump1.log
Start to dump at Fri Jun 21 15:32:57 CST 2013 ....

Connected to: Oracle Database 10g Release 10.2.0.3.0-64bit Production
Export done in US7ASCII character set and UTF8 NCHAR character set
Server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path... # This is the key description, specifying the export Method
.. Exporting table TRADE_CLIENT_TBL_ARC 1395093 rows exported #1.39 million rows of data
Export terminated successfully with warnings.

End dump at Fri Jun 21 15:34:31 CST 2013. # consumed time 15:34:31-15:32:57 = less than 2 minutes

# Author: Robinson

Robin @ SZDB:/u02/database/SYBO2SZ/BNR/dump> more dump2.log
Start to dump at Fri Jun 21 15:37:13 CST 2013 ....

Connected to: Oracle Database 10g Release 10.2.0.3.0-64bit Production
Export done in US7ASCII character set and UTF8 NCHAR character set
Server uses UTF8 character set (possible charset conversion)

About to export specified tables via Direct Path... # This is the key description, specifying the export Method
.. Exporting table trade_client_tbl_arc1395093 rows exported
Export terminated successfully with warnings.

End dump at Fri Jun 21 15:37:30 CST 2013. # consumed time 15:37:30-15:37:13 = 17 s

B. Demonstration of lob Data Type Support
Scott @ SYBO2SZ> create table scott. testtab2 (nr number, txt clob );

Table created.

Scott @ SYBO2SZ> declare
2 x varchar2 (50 );
3 begin
4 for I in 1 .. 5000 loop
5 x: = 'this is a line with the number: '| I;
6 insert into scott. testtab2 values (I, x );
7 commit;
8 end loop;
9 end;
10/

PL/SQL procedure successfully completed.

Scott @ SYBO2SZ> select count (*) from testtab2;

COUNT (*)
----------
5000

Robin @ SZDB:/u02/database/SYBO2SZ/BNR/dump> exp scott/tiger file = exp_testtab2.dmp tables = scott. testtab2 direct = y

Export: Release 10.2.0.3.0-Production on Fri Jun 21 11:56:37 2013

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.3.0-64bit Production
Export done in US7ASCII character set and UTF8 NCHAR character set
Server uses UTF8 character set (possible charset conversion)

About to export specified tables via Direct Path...
Table TESTTAB2 will be exported in conventional path.
.. Exporting table TESTTAB2 5000 rows exported
Export terminated successfully without warnings.