Shulou(Shulou.com)05/31 Report--

This article mainly introduces what is the difference in the performance of exporting data using Exp and Expdp. It is very detailed and has certain reference value. Friends who are interested must finish reading it.

1. Preface

Data backup is very important to the safe operation of the information system. More and more of our users use RMan or third-party professional backup software, but many users still retain the traditional Exp as part of the backup strategy, mainly because this backup method is easy to use and convenient to restore to other machines, so although there are other backup methods, Exp will still be used at the same time. There are even many users who have only this backup method (perhaps because they think it is too complicated to master other technologies).

With the growth of users' data, the problems existing in Exp export have become increasingly prominent, mainly taking a long time, some even more than 3 hours, coupled with the common background automatic jobs: summary table calculation, automatic cost calculation, statistical information collection and other work, making the schedule of one night more and more tense.

Is there any way to improve the performance of Exp execution? I have done some experiments before, consulted a lot of data, summed up some experience, recently got a user's nearly 300G data, and again carried out a series of comparative experiments on the export of a large amount of data. It is found that the setting of several parameters has a great impact on the time-consuming export of Exp. After optimization, the performance of Exp export has been greatly improved, but compared with Expdp, the latter is still much faster. The following will be an introduction to the experiment and related knowledge, so that we can refer to it when helping users to formulate backup strategies.

two。 Test situation

2.1 Test environment

Hardware:

CPU: Zhi Qiang 5405 and 4Q 2G

Memory: DDR2,4G

Hard disk: IDE 1T

Software: Windows+Oracle 10.2.0.3

Data: XX hospital database imported ZLHIS10.30 data, the original total of 300G, after shrinking rollback table space, temporary table space, and some table space file unused space, a total of 267g, which contains a large number of LOB data related to electronic medical records.

Other notes: due to the long use of the hospital, DB Control Repository contains a large number of historical monitoring data, table SYSMAN.MGMT_METRICS_RAW in 13 million LOB format data, only the import of this table took more than 24 hours, in order to facilitate testing, emptied the data of the table.

2.2 Test method

It is time-consuming to experiment with four different ways to export data in the same environment

1) use Exp regular path to export without parameter optimization

2) use Exp direct path export without parameter optimization

3) using Exp direct path export, parameter optimization

4) use Expdp to export and optimize parameters

2.3 Test results

Mode

Time-consuming

Description

Exp regular path, not optimized

5 hours and 15 minutes

No parameters added

Exp direct path, not optimized

2 hours and 38 minutes

Direct=y

Exp direct path, optimization

One hour and 40 minutes.

Direct=y recordlength=65535 buffer=104857600

Expdp, optimization

59 points

Parallel=3 dumpfile=

Expdp_0225_1.dmp,expdp_0225_2.dmp

Expdp_0225_3.dmp

The results show that:

The fastest is Expdp mode, while after parameter optimization, Exp mode is nearly 3 times faster than that without any parameters.

The related principles and parameters are described in detail below.

3. Principle explanation

Exp defaults to the traditional path. In this mode, the data is queried by Select, then written to buffer cache, then written to evaluate buffer, finally transferred to the Export client, and then written to the dump file.

In the direct path mode, the data is read directly from the hard disk and then written to PGA. The format is the format of Export, which does not need to be converted. The data is then directly transferred to the Export client and written to the dump file. This mode does not go through evaluation buffer, without a process, and the export speed is improved obviously.

Exp has no parallel parameters. For parallel export, you can write multiple commands to export at the same time. This method can be used for specific data migration situations, such as fast data migration by table or tablespace.

Expdp is an advanced data export method launched on Oracle 10G, which has a greater performance improvement than Exp. Expdp can be regarded as an upgraded version of Exp, equivalent to exp + direct mode + parallel.

Expdp defaults to direct path, which has four ways, and the other three are:

External table schema (equivalent to regular path export of Exp)

Data file copy mode (tablespace transfer)

Network link import (export import through data link)

In general, Exp can be replaced, but it cannot be completely replaced, mainly because it needs to be executed on the database server, while Exp can be executed on any client. In addition, according to tests, when Expdp exports a large partition table (more than 1T), it takes more than 2 hours to analyze, and there are some BUG. As a result, some users 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 model focuses on two parameters: DIRECT and RECORDLENGTH parameters.

The DIRECT parameter defines whether the export uses direct path mode (DIRECT=Y) or regular path mode (DIRECT=N). Conventional path export uses select statements to extract data from the table, evaluate it and then write it, while direct path export reads the data directly from disk to PGA and then writes to the export file as is, thus avoiding the data conversion process of the SQL command processing layer and greatly improving the export efficiency.

The BUFFER parameter, which sets the size of the cache to read records in bytes, that is, the maximum number of records in array, is valid only for regular path mode exports.

The RECORDLENGTH parameter, which is used in conjunction with DIRECT=Y, defines the size of the Export I _ BUFFER O buffer, which acts similar to the BUFFER parameter used in regular path exports. It is recommended that you set the RECORDLENGTH parameter to the maximum Imax O buffer, i.e. 65535 (64kb).

It should be emphasized that even if the direct path is used to export the mode, the tables involving LOB objects will only be exported through the traditional mode, so when DIRECT=Y, in addition to setting the RECORDLENGTH parameter, you also need to set the BUFFER parameter, which can be set to 104857600 (100m), which is ignored by many optimization articles on the Internet.

In addition, you can also modify the Oracle initialization parameter Multiple Block Read to submit the performance of reading data. After testing, the effect is not very obvious. In the previous test environment, the time consumption can be reduced by about 10 minutes.

According to the parameter optimization settings above, here is an example of an export script:

Exp userid=sys/his@zyyy full=y direct=y recordlength=65535 buffer=104857600 file=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, which is set according to the number of CPU. The default is 1. If it is not set, the export performance is not much different from the direct path export mode of Exp, and its advantages will not be brought into full play.

The Dumpfile parameter can be used in conjunction with the Parallel parameter to specify multiple export files to reduce IO contention when writing concurrently.

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

SQL > create directory dump_dir as'F:\ data\ zyyy'

Cmd:

Expdp sys/his@zyyy full=y directory=dump_dir parallel=3

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

5. Summary

If you are still used to using Exp mode, if you want to reduce time-consuming, it is best to use the direct path mode, and set the values of RECORDLENGTH and BUFFER parameters, which can greatly improve the export performance. If you master the Expdp mode, use the appropriate Parallel parameters to achieve fast export, and more importantly, the performance improvement of Impdp is the real advantage of the data pump mode. From the test results of some Oracle enthusiasts, it is indeed very different.

These are all the contents of this article entitled "what is the difference between the performance of exporting data using Exp and Expdp". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.