Comparison of exp and expdp export performance

Comparison of exp and expdp export performance

Performance Comparison between General EXP mode, direct EXP path mode, and EXPDP Export

1. First, export the normal path of EXP:

Exp bkjia/zhejiang file =/data1/zj_regular.dmp buffer = 20480000

The normal EXP export mode is executed for 1 hour and 24 minutes.

2. direct path export method:

Exp bkjia/zhejiang file =/data1/zj_direct.dmp buffer = 20480000

Recordlength = 65535 direct = y

Direct path import takes 18 minutes, which is significantly faster than normal path export.

3. Data Pump export speed.

Expdp bkjia/zhejiang dumpfile = zj_datapump.dp directory = d_test schemas = bkjia

The export time of the Data Pump is only 14 minutes, which is more than 20% faster than the direct path import method. In addition, observe the size of the three export files, and find that the faster the export speed, the smaller the size of the corresponding files, the data pump export method to get the file is nearly 1.5 GB smaller than the EXP method.

Performance Comparison between IMP and IMPDP Import

1. IMP import speed:

Imp bkjia/zhejiang file =/data1/zj_regular.dmp full = y buffer = 20480000 log =/data1/zj_regular.log

IMP import took 3 hours and 17 minutes,

2. Import speed of IMPdp:

Impdp bkjia/zhejiang DUMPFILE = zj_datapump.dp DIRECTORY = d_test FULL = y LOGFILE = zj_datapump.log

It took three hours and eight minutes to import the data pump, which is very similar to the import speed of IMP. It seems that not all cases are as described by Oracle, the import efficiency of the Data Pump is greatly improved than that of the normal import.

In the test in the previous article, we found that the import speed of IMPDP is almost the same as that of IMP. When introducing Data Pumps, Oracle mentioned that the import speed of IMPDP was up to 10 times that of IMP. However, IMPDP can be optimized and adjusted to improve the degree of parallelism of IMPDP by setting PARALLEL.

First, let's take a look at the number of CPUs:

SQL> show parameter cpu

Since the number of CPUs on the database server is 2, next we try to set PARALLEL to 2 for Import

Impdp bkjia/zhejiang DUMPFILE = zj_datapump.dp DIRECTORY = d_test FULL = y LOGFILE = zj_datapump.log parallel = 2

Using the import method with a degree of parallelism of 2, the speed of discovery has increased significantly. The import speed with a degree of parallelism of 1 is 3 hours and 8 minutes, but now it takes less than 2 and a half hours.

Since the concurrency setting should not exceed 2 times the number of CPUs, the import time of parallelism 3 and 4 is very close to that of parallelism 2. It seems that the performance can no longer be improved by increasing the degree of parallelism.

1. first look at the performance of direct export:

$ Expdp bkjia/zhejiang directory = d_test dumpfile = bkjia. dp

The entire export operation took about 14 minutes and a half,

2. Try to export with degree of parallelism 2, and then set an exported data file:

$ Expdp bkjia/zhejiang directory = d_test dumpfile = bkjia_p2_1file.dp parallel = 2

The entire import process was less than 14 minutes, but the performance improvement was not very obvious. However, this is caused by the concurrency setting. The two processes perform the export operation at the same time, but the two processes need to write the exported data to the same data file, which will inevitably lead to resource contention.

3. Still use degree of parallelism 2, but set two data files at the same time to check the export performance again:

$ Expdp bkjia/zhejiang directory = d_test dumpfile = bkjia_p2_2file1.dp, bkjia_p2_2file2.dp parallel = 2

This export only takes 10 minutes and a half, greatly improving the export efficiency.

4. Test the degree of parallelism 4 and export it to four data files:

After 9 minutes of full export, setting it to the degree of parallelism can still improve the performance, but it is not obvious, this is mainly because the overall performance bottleneck is no longer the processing capability of a single process. Most of the performance bottlenecks have become the disk I/O bottleneck. At this time, increasing the degree of parallelism alone cannot significantly improve the performance.