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

Traditional path Export VS direct path Export (oracle exp direct = y) Oracle traditional Export and Import are retained to 11 GB, in addition, 9i and 10g are many enterprises that still use Export and Import methods for backup and recovery. 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 and Conventional path Export use SQL SELECT statements in traditional path mode 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. In Direct path Export 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 issues a. direct path export has better performance and faster speed than traditional path export because it bypasses the SQL command processing section. B. The direct path export mode supports the RECORDLENGTH parameter (up to 64 k ), we recommend that you set this parameter value to an integer multiple of system I/O or DB_BLOCK_SIZE c and the specific factors that affect direct path export (DB_BLOCK_SIZE, column type, I/O performance, 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 a direct path export or a dump generated by a traditional path export, when importing data files using imp, will consume the same time 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. Restriction 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 modes are not supported, for example, exp scott/tiger TABLES = emp QUERY = \ "WHERE job = \ 'salesman \ '\" is not supported. d. You can use RECORDLENGTH to export data at a time in direct paths, replace the buffer setting in the traditional path. e. direct path export requires that the Environment Parameter NLS_LANG be equal to the database character set, receive EXP-41 warning and EXP-0 termination error 5. demonstrate two ways of performance difference [python] a. Traditional path export and direct path export performance comparison # The following direct 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 # data row 1.39 million Export terminated successfully with warnings. end dump at Fri Jun 21 15:34:31 CST 2013. # consumption time 15:34:31-15:32:57 = less than 2 min # Author: Robinson # Blog: http://blog.csdn.net/robinson_0612 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. # consumption time 15:37:30-15:37:13 = 17 s B. demonstrate the support for lob data types 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.