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 # comparison of logs directly below 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 productionexport done in us7ascii Character Set and utf8 nchar character setserver 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_0612robin@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 productionexport done in us7ascii Character Set and utf8 nchar character setserver 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 exportedexport terminated successfully with warnings. end dump at Fri Jun 21 15:37:30 CST 2013. # The consumption time is 15:37:30-15:37:13 = 17sb. It demonstrates 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 (*) ---------- 5000robin @ szdb: /u02/database/sybo2sz/BNR/dump> exp Scott/tiger file = exp_testtab2.dmp tables = Scott. testtab2 direct = yexport: 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 productionexport done in us7ascii Character Set and utf8 nchar character setserver 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 testtable 2 5000 rows exportedexport terminated successfully without warnings.

 

6. References
Use of Data Pump expdp export tool
Use of Data Pump impdp import tool
Use of exclude/include in expdp impdp
Use the Oracle DataPump API to export data
Import and Export Oracle Partition Table Data

Oracle DataPump expdp/impdp hang

Use DataPump to export the import synonym (export and import synonym using DataPump)

 

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)