Performance comparison and optimization for exporting data using exp and EXPDP

1. Preface

Data backup is critical to the safe operation of information systems, and our users are using Rman or third-party professional backup software more and more, but many users still retain the traditional exp as part of the backup strategy, mainly because this kind of backup method is easy to use, and restore to other machines is also very convenient, so, Although there are other ways of backing up, the Exp method will still be used at the same time, and even many users have only this backup method.

With the increase of the user's data volume, the problem of exp export is becoming more and more prominent, mainly is time-consuming, some even more than 3 hours, plus common background auto-work: Summary table calculation, automatic cost calculation, statistical information collection and so on, make an evening time schedule more and more tense.

Is there a way to improve the performance of exp execution? Before I did some experiments, consulted a lot of information, summed up some experience, recently got a user of nearly 300G data, and again a series of large data export comparison test, found that several parameters of the setting of EXP export time-consuming impact, after optimization, EXP export performance has been greatly improved, But compared to EXPDP, the latter is still much faster, and the following is an introduction to the experiment and related knowledge so that we can refer to it when we help the user to develop a backup strategy.

2. Test Situation

2.1 test Environment

Hardware:

CPU: Xeon 5405,4*2g

Memory: ddr2,4g

Hard disk: IDE 1T

Software: Windows+oracle 10.2.0.3

Data: XX Hospital full library imported ZLHIS10.30 data, the original total 300G, after shrinking back to the table space, temporary table space, and some tablespace files unused space, a total of 267G, which contains a large number of electronic medical records related to LOB data.

Other notes: Because the hospital is used for a long time, DB control repository contains a large number of historical monitoring data, table Sysman.mgmt_metrics_raw 13 million LOB format data, only the import of this table takes more than 24 hours, in order to facilitate testing, The data for the table is emptied.

2.2 test Method

In the same environment, it is time consuming to experiment with 5 different ways of exporting data

1) export with exp regular path without parameter optimization

2) export with exp direct path without parameter optimization

3) export using exp direct path, parameter optimization

4) Use EXPDP to export, parameter optimization

2.3 Test Results

Way	Take	Description
Exp regular path, not optimized	5 hours and 15 minutes	Do not add parameters
Exp Direct path, not optimized	2 hours and 38 minutes	Direct=y
Exp Direct Path, optimized	1 hours and 40 minutes	Direct=y recordlength=65535 buffer=104857600
EXPDP, optimizing	59 min	Parallel=3 dumpfile= Expdp_0225_1.dmp,expdp_0225_2.dmp, Expdp_0225_3.dmp

The results show that:

The fastest is the EXPDP way, and the Exp method after the parameter optimization, relative to the case without any parameters, nearly 3 times times faster.

The relevant principles and parameters are described in detail below.

3. explanation of the principle

The exp default is the traditional path, which uses Select to query the data, then writes the buffer cache, writes the data to evaluate buffer, and finally uploads the export client to the dump file.

Direct path mode, directly from the hard disk to read data, and then write to the PGA, format is the export format, do not need to convert, the data will be transmitted directly to the export client, write dump file. This mode does not pass evaluation buffer, a process is less, the export speed is also very obvious.

EXP does not have parallel parameters, for parallel export, you can write multiple commands to export at the same time, this method is used for specific data migration situation, by table or table space for rapid data migration.

EXPDP is an advanced data export method on Oracle 10G, which has a higher performance than exp, and EXPDP can be seen as an upgraded version of exp, equivalent to exp + Direct mode + parallel.

EXPDP default is the direct path mode, it has 4 ways, the other 3 kinds are:
External table mode (equivalent to EXP's regular path export);

Data file copy mode (table space transfer);

Network link import (import via data link export)

The general situation can replace exp, but it cannot be completely replaced, mainly because it needs to be executed on the database server, and exp can be executed on any client. In addition, according to the test, EXPDP when exporting a large partition table (above 1T), the analysis time alone is more than 2 hours, and there are some bugs. Therefore, some users will still use EXP for data backup.

4. parameter Optimization

Exp Related Parameters

From the above analysis, we know that the use of "direct path" can improve the export speed, this mode focuses on 2 parameters: direct and RecordLength parameters.

The direct parameter defines whether the export is using either a straight Path mode (direct=y) or a regular path mode (direct=n). Regular path export uses the SELECT statement to extract data from a table, evaluate and then write, and direct path export reads the data directly from the disk to the PGA and writes the exported file as it is, thus avoiding the data conversion process of the SQL command processing layer and greatly improving the export efficiency.

The buffer parameter is used to set the size of the cache to read the record in bytes, which is the maximum number of records in the array, which is valid only for regular path mode exports.

The RecordLength parameter is a parameter that is used with the direct=y, which defines the size of the export I/o buffer and acts like the buffer parameter used by the regular path export. It is recommended to set the RecordLength parameter to the maximum I/O buffer, which is 65535 (64KB).

It is necessary to emphasize that the direct path export mode is used, where the table involving the LOB object is only exported through the traditional schema, so when direct=y, in addition to setting the RecordLength parameter, you need to set the buffer parameter, The general situation can be set to 104857600 (100M), which is ignored by many optimization articles on the web.

In addition, you can modify the Oracle initialization parameters multiple Block read to submit read data performance, after testing, the effect is not obvious, the previous test environment, can reduce the time-consuming of about 10 minutes.

Follow the above parameters to optimize the settings, here is an example of an export script:

Expuserid=sys/[email protected] full=y direct=y recordlength=65535 buffer=104857600file=f:\zyyy\exp20120218.dmp log= F:\zyyy\exp20120218.log feedback=10000

EXPDP Related Parameters

Only two parameters are described here: parallel and DumpFile

The parallel parameter indicates the degree of export parallelism, set according to the number of CPUs, default is 1, if not set, export performance and exp direct path export mode is not much difference, did not play its advantage.

The DumpFile parameter can be used with the parallel parameter to specify multiple export files to reduce IO contention for concurrent writes.

Combining these two parameters, an example of an export script is given below:

Sql>createdirectory dump_dir as ' F:\data\zyyy ';

Cmd:

EXPDP sys/[email protected]full=y Directory=dump_dir parallel=3

Dumpfile=expdp_0225_1.dmp,expdp_0225_2.dmp,expdp_0225_3.dmplogfile=expdp_0225.log

5. Summary

If you are still accustomed to using the Exp method, if you want to reduce time-consuming, it is best to use direct path, and set the values of RecordLength and buffer two parameters, can greatly improve the export performance. If you have mastered the EXPDP approach, using the appropriate parallel parameters for fast export, and more importantly, the IMPDP performance improvement is the real advantage of the data pump model, from the test results of some of the Oracle enthusiasts, it is really quite different.

Performance comparison and optimization for exporting data using exp and EXPDP