Exp General mode, exp direct path mode and EXPDP three-way export performance comparison

Exp General mode, exp direct path mode and EXPDP three-way export performance comparison
1. First is the general path export of Exp:
Exp Zhejiang/zhejiang file=/data1/zj_regular.dmp buffer=20480000
The general Exp Export method was performed for 1 hours and 24 minutes.

2. Direct path Export method:
Exp Zhejiang/zhejiang file=/data1/zj_direct.dmp buffer=20480000
recordlength=65535 direct=y
Direct path import takes 18 minutes, with a significant improvement over the normal path export speed.



3. Data pump export speed.
EXPDP Zhejiang/zhejiang DUMPFILE=ZJ_DATAPUMP.DP directory=d_test Schemas=zhejiang
The data pump has been exported for only 14 minutes, more than 20% faster than the direct path import mode. and observe the size of three exported files can be found, the faster the export of the corresponding file is smaller, which data pump export way to get the file than the exp way youngster nearly 1.5G.


Imp and IMPDP import performance comparisons
1.IMP Import Speed:
Imp Zhejiang/zhejiang file=/data1/zj_regular.dmp full=y buffer=20480000 log=/data1/zj_regular.log
IMP import took 3 hours and 17 minutes,

2.IMPDP Import Speed:
IMPDP Zhejiang/zhejiang DUMPFILE=ZJ_DATAPUMP.DP directory=d_test full=y logfile=zj_datapump.log
Data pump import operation actually spent 3个小时8分钟 time, and Imp's import speed is very close, it seems not all cases as Oracle described, data pump imports than the general import efficiency significantly improved.

The test in the previous article found that IMPDP's import speed is comparable to IMP's import speed. When Oracle introduced the data pump, it was mentioned that IMPDP's import speed was 10 times times higher than that of IMP. But fortunately IMPDP can still optimize the adjustment, that is by setting parallel to improve the IMPDP degree of parallelism.
First, take a look at the number of CPUs:

Sql> Show parameter CPU


Since the number of CPUs on the database server is 2, the following attempts to set parallel to import
IMPDP Zhejiang/zhejiang DUMPFILE=ZJ_DATAPUMP.DP directory=d_test full=y logfile=zj_datapump.log

Using the parallel degree of 2 of the import method, found that the speed is indeed improved a lot. The 1-Parallel import speed is 3 hours and 8 minutes, and now takes less than 2.5 hours.
Because the parallelism setting should not exceed twice times the CPU number, try the import of parallelism 3 and 4, which is very close to the import time and the degree of parallelism 2. It seems that there is no longer a way to improve performance by increasing the degree of parallelism.


1. First look at the performance of direct export:

$ EXPDP Zhejiang/zhejiang directory=d_test DUMPFILE=ZHEJIANG.DP
The entire export operation took about 14 and a half minutes,

2. An attempt to export using the degree of parallelism 2 will still set an exported data file:
$ EXPDP Zhejiang/zhejiang directory=d_test DUMPFILE=ZHEJIANG_P2_1FILE.DP parallel=2

The entire import process is less than 14 minutes, but the performance improvement is not obvious. There is a reason, however, that two processes are performing the export operation at the same time because the degree of parallelism is set, but both will write the exported data to the same data file, which will inevitably result in contention for the resource

3. Still use the degree of parallelism 2, but set two data files to check the export performance again:
$ EXPDP Zhejiang/zhejiang directory=d_test DUMPFILE=ZHEJIANG_P2_2FILE1.DP,ZHEJIANG_P2_2FILE2.DP parallel=2

The export was only 10 and a half, and the efficiency of the export was greatly improved.

4. Test the degree of parallelism 4, exported to 4 data files respectively:
It took 9 minutes for the entire export to complete, setting the degree of parallelism 4 can still achieve a certain performance improvement, but it is not obvious, this is mainly because the overall performance bottleneck is not a single process capacity, most of the performance bottleneck has become a disk IO bottleneck, at this time simply by increasing the degree of parallelism has not significantly improved performance.