Performance Comparison and Principle Analysis of expdp AND EXP

Author: skate

Time: 2010-08-31

 

Expdp Test

 

1. Single process, two Exported Files

 

C:/Documents and Settings/Administrator> expdp 'sys/Aibo @ test as sysdba 'ctor
Y = dpdump dumpfile = expdp1.dp, expdp2.dp tables = (skate. testbind, skate. testbind_tmp,
Skate. test_idx) filesize = 500 M & time

 

Export: Release 10.2.0.4.0-production on Tuesday, August, 2010 15:39:35

Copyright (c) 2003,200 7, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options
Start "sys". "sys_export_table_01": 'sys/********* @ test as sysdba 'directory = dpdum
P dumpfile = expdp1.dp, expdp2.dp tables = (skate. testbind, skate. testbind_tmp, skate. t
Est_idx) filesize = 500 m
Using the blocks Method for estimation...
Processing object type table_export/table/table_data
Total estimation using the blocks method: 926 MB
Processing object type table_export/table
Processing object type table_export/table/index/Index
Processing object type table_export/table/index/statistics/index_statistics
Processing object type table_export/table/statistics/table_statistics
.. Exported "skate". "test_idx" 668.8 MB 6529200 rows
.. Exported "skate". "testbind" 89.51 MB 912936 rows
.. Exported "skate". "testbind_tmp" 872.9 kb 100000 rows
The master table "sys". "sys_export_table_01" is successfully loaded/uninstalled"
**************************************** **************************************
The dump file set of SYS. sys_export_table_01 is:
D:/backup/Oracle/expdp1.dp
D:/backup/Oracle/expdp2.dp
The job "sys". "sys_export_table_01" was successfully completed at 15:42:21.

 

Time: 15:42:22. 15
Enter the new time:

 

Data export time: 2 minutes 46 seconds

 

2. Parallel 2 process, two Exported Files

C:/Documents and Settings/Administrator> expdp 'sys/Aibo @ test as sysdba 'ctor
Y = dpdump dumpfile = expdp1p. DP, expdp2p. DP tables = (skate. testbind, skate. testbind_tm
P, skate. test_idx) filesize = 500 m parallel = 2 & time

 

Export: Release 10.2.0.4.0-production on Tuesday, August, 2010 15:44:43

Copyright (c) 2003,200 7, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options
Start "sys". "sys_export_table_01": 'sys/********* @ test as sysdba 'directory = dpdum
P dumpfile = expdp1p. DP, expdp2p. DP tables = (skate. testbind, skate. testbind_tmp, skate
. Test_idx) filesize = 500 m parallel = 2
Using the blocks Method for estimation...
Processing object type table_export/table/table_data
Total estimation using the blocks method: 926 MB
Processing object type table_export/table
Processing object type table_export/table/index/Index
Processing object type table_export/table/index/statistics/index_statistics
Processing object type table_export/table/statistics/table_statistics
.. Exported "skate". "testbind" 89.51 MB 912936 rows
.. Exported "skate". "testbind_tmp" 872.9 kb 100000 rows
.. Exported "skate". "test_idx" 668.8 MB 6529200 rows
The master table "sys". "sys_export_table_01" is successfully loaded/uninstalled"
**************************************** **************************************
The dump file set of SYS. sys_export_table_01 is:
D:/backup/Oracle/expdp1p. DP
D:/backup/Oracle/expdp2p. DP
The job "sys". "sys_export_table_01" was successfully completed at 15:47:24.

 

Time: 15:47:25. 20
Enter the new time:

Data export time: 2 minutes 41 seconds

 

3. Parallel 2 process, an exported file

 

C:/Documents and Settings/Administrator> expdp 'sys/Aibo @ test as sysdba 'ctor
Y = dpdump dumpfile = expdpp. DP tables = (skate. testbind, skate. testbind_tmp, skate. Test
_ Idx) Parallel = 2 & time

 

Export: Release 10.2.0.4.0-production on Tuesday, August, 2010 15:53:17

Copyright (c) 2003,200 7, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options
Start "sys". "sys_export_table_02": 'sys/********* @ test as sysdba 'directory = dpdum
P dumpfile = expdpp. DP tables = (skate. testbind, skate. testbind_tmp, skate. test_idx) P
Arallel = 2
Using the blocks Method for estimation...
Processing object type table_export/table/table_data
Total estimation using the blocks method: 926 MB
Processing object type table_export/table
Processing object type table_export/table/index/Index
Processing object type table_export/table/index/statistics/index_statistics
Processing object type table_export/table/statistics/table_statistics
.. Exported "skate". "test_idx" 668.8 MB 6529200 rows
.. Exported "skate". "testbind" 89.51 MB 912936 rows
.. Exported "skate". "testbind_tmp" 872.9 kb 100000 rows
The master table "sys". "sys_export_table_02" is successfully loaded/uninstalled"
**************************************** **************************************
SYS. sys_export_table_02's dump file set is:
D:/backup/Oracle/expdpp. DP
The job "sys". "sys_export_table_02" was successfully completed at 15:56:30.

 

Time: 15:56:30. 87
Enter the new time:

Data export time: 3 minutes 13 seconds

 

Exp Testing

1. General path Export

C:/Documents and Settings/Administrator> exp userid = 'sys/Aibo @ test as sysdba 'fil
E = D:/backup/Oracle/expfull. dmp tables = (skate. testbind, skate. testbind_tmp, skate.
Test_idx) buffer = 409600000 & time

 

Export: Release 10.2.0.4.0-production on Tuesday August 31 16:09:52 2010

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

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options
The zhs16gbk and al16utf16 nchar character sets have been exported.

To export the specified table, use the regular path...
The current user has been changed to skate
... Exporting table testbind exported 912936 rows
.. Export table testbind_tmp export 100000 rows
... Exporting table test_idx exported 6529200 rows
The export is successfully terminated without a warning.

Current Time: 16:14:51. 14
Enter the new time:

 

Data export time: 4 minutes 59 seconds

 

2. direct path Export

 

C:/Documents and Settings/Administrator> exp userid = 'sys/Aibo @ test as sysdba 'fil
E = D:/backup/Oracle/expfull. dmp tables = (skate. testbind, skate. testbind_tmp, skate.
Test_idx) buffer = 409600000 direct = Y & time

Export: Release 10.2.0.4.0-production on Tuesday August 31 16:17:25 2010

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

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options
The zhs16gbk and al16utf16 nchar character sets have been exported.

Export the specified table through the direct path...
The current user has been changed to skate
... Exporting table testbind exported 912936 rows
.. Export table testbind_tmp export 100000 rows
... Exporting table test_idx exported 6529200 rows
The export is successfully terminated without a warning.
Current Time: 16:21:56. 75
Enter the new time:

Data export time: 4 minutes 31 seconds

Test result analysis summary: Because my testing environment is a common PC, I/O can easily reach the bottleneck, so the parallel testing effect is not very obvious.

 

Compare the exported file size

 

Export File Size sequence: EXP general path Export File> exp direct path Export File> expdp Export File

Export speed comparison: export from normal exp path <exp direct path <expdp Export

 

Comparison of exp and expdp principles

1. exp/IMP client program, affected by network and disk; impdp/expdp server program, only affected by disk

 

2. Assume that the file is exported to the server, and the normal method of exp is to query the data in the database using the select method.
The SQL statement processing layer is then transferred to the exp export file (that is, the PGA is transmitted to the client only after it passes through SGA, And the exp direct path is directly transferred from the disk
Data is transferred to exp export files, so the speed is faster. expdp is an upgraded version of exp, equivalent to exp + direct mode + parallel
The output file is also compressed, which is found from the comparison results of the exported file. It is not clear ). In general, expdp provides parallel
Method (write multiple exported files)

 

Impdp's parallel mechanism also makes it much faster than imp

 

 

 

----- End -----