Oracle common Export and export commands and performance efficiency comparisons

Description

The Oracle import and export commands are mainly EXPDP and IMPDP, exp, and IMP, with the following differences: EXP and IMP are client tool programs that can be used either on the client or on the server side. EXPDP and IMPDP are server-side utilities that can only be used on the Oracle server and not on the client side. IMP is only available for exp exported files, not for EXPDP export files, IMPDP only for EXPDP exported files, not for exp export files. EXPDP or IMPDP command, you can temporarily not indicate the username/password @ instance name as identity, and then input as prompted, such as: EXPDP Schemas=scott dumpfile=test.dmp directory=testdata; Two commands are executed directly in the cmd command.

First, EXPDP and IMPDP commands

1.1. Create a logical directory that does not create a real directory in the operating system, preferably created by an administrator such as system.

Create directory TestData as ' D:\test\dump ';

View the Management Manager directory (also see if the operating system exists, because Oracle does not care if the directory exists, and if it does not exist, an error occurs)

SELECT * from Dba_directories;

Give the Scott user permission to operate on the specified directory, preferably given by an administrator such as system.

Grant Read,write on directory testdata to Scott;

1.2. Export Data

1) According to the user guide

EXPDP Scott/[email protected] Schemas=scott dumpfile=test.dmp directory=testdata;

2) Parallel Process parallel

EXPDP Scott/[email protected] directory=testdata dumpfile=test.dmp parallel=40 job_name=testjob

3) According to the table name guide

EXPDP Scott/[email protected] tables=emp,dept dumpfile=test.dmp directory=testdata;

4) Guided by query criteria

EXPDP Scott/[email protected] directory=testdata dumpfile=test.dmp tables=emp query= ' WHERE deptno=20 ';

5) According to the Table space Guide

EXPDP System/manager directory=testdata dumpfile=test.dmp tablespaces=temp,example;

6) Guide the entire database

EXPDP System/manager directory=testdata dumpfile=full.dmp full=y;

1.3. Import Data

1) leads to the specified user

IMPDP Scott/tiger directory=testdata dumpfile=test.dmp Schemas=scott;

2) Change the owner of the table

IMPDP system/manager directory=testdata dumpfile=test.dmp tables=scott.dept remap_schema=scott:system;

3) Import Table space

IMPDP System/manager directory=testdata dumpfile=test.dmp tablespaces=example;

4) Import the database

impdb System/manager directory=testdata dumpfile=full.dmp full=y;

5) Append Data

IMPDP system/manager directory=testdata dumpfile=test.dmp schemas=system table_exists_action

II. Exp and IMP commands

2.1.exp Command

There are three main ways (full, user, table)
1. Complete:
EXP system/manager buffer=64000 file=d:\all.dmp log=d:\all.log full=y
If you want to perform a full export, you must have special permissions

2. User mode:
EXP test/test buffer=64000 file=d:\test. DMP Log=d:\test.log Owner=test

All objects of the user test are then output to the file.

3. Table mode:
EXP test/test buffer=64000 file=d:\test. DMP log=d:\test.log owner=test tables= (test) The table test for user test is exported

2.2.IMP Command
Three modes (full, user, table)
1. Complete:
IMP System/manager buffer=64000 file=d:\full. DMP Log=d:\impfull.log full=y

2. User mode:
IMP test/test buffer=64000 file=d:\test. DMP Log=d:\imptest.log fromuser=test Touser=test
This allows all objects of the user test to be imported into the file. You must specify the Fromuser, touser parameter so that data can be imported.

3. Table mode:
EXP test/test buffer=64000 file=d:\test. DMP log=d:\imptest.log owner=test tables= (test)
The table test of the user test is then imported.

2.3 about parameter ignore=y

If the parameter ignore=y is used, the data contents in the Exp file will be imported; If the table has a unique keyword constraint, the condition will not be imported; if the table does not have a constraint on a unique keyword, it will cause the record to be duplicated

Explain:

When a table (test) already exists in the database to be imported, if the table does not have a uniqueness constraint, then adding parameter ignore=y to the import will completely import the data into the table without error.
When a table already has a uniqueness constraint, especially a PRIMARY key constraint, only records that do not exist in the primary key are imported when importing. There will be warnings during the import process.
Use this principle to do an incremental import.

Third, performance efficiency comparison (direct reference to other people's article)

3.1 Exporting performance comparisons

Exp General mode, exp direct path mode and EXPDP three ways to export performance comparison

1) First is EXP's regular path export:

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 noticeable improvement over the normal path export speed.

3) The export speed of the data pump.

EXPDP Zhejiang/zhejiang DUMPFILE=ZJ_DATAPUMP.DP directory=d_test Schemas=zhejiang

The export time of the data pump takes only 14 minutes and is more than 20% faster than the direct path import method. and observe the size of the three export files can be found that the faster the export of the corresponding file is also smaller, where the data pump is exported to the file than the EXP mode teenager nearly 1.5G.

3.2 Import Performance Comparison

Imp and IMPDP import performance comparison

1) Import speed of IMP:

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) Import speed of IMPDP:

IMPDP Zhejiang/zhejiang DUMPFILE=ZJ_DATAPUMP.DP directory=d_test full=y logfile=zj_datapump.log

Data pump import operation actually took 3个小时8分钟 time, and Imp's import speed is very close, it does not appear in all cases as Oracle described, data pump import than the normal import efficiency has greatly improved.

During the test, the import speed of IMPDP was found to be similar to IMP import speed. While Oracle introduces the data pump, it mentions that the IMPDP import speed is 10 times times higher than that of IMP. Fortunately, IMPDP can still optimize the adjustment, that is, by setting the parallel to improve the degree of parallelism IMPDP.

Let's start by looking at the number of CPUs:

Sql> Show parameter CPU

Because the database server has a number of CPUs of 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 parallel=2

With the parallelism of 2 of the import method, found that the speed has improved a lot. The import speed of 1 for parallelism is 3 hours and 8 minutes, and it now uses less than 2.5 hours.

Since the parallelism setting should not exceed twice times the number of CPUs, try importing with parallelism of 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 the direct export:

$ EXPDP Zhejiang/zhejiang directory=d_test DUMPFILE=ZHEJIANG.DP

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

2) Try to export using the parallelism of 2, and then set up an exported data file:

$ EXPDP Zhejiang/zhejiang directory=d_test DUMPFILE=ZHEJIANG_P2_1FILE.DP parallel=2

The entire import process was less than 14 minutes, but the performance improvement was not obvious. There is a reason for this, however, because the parallelism is set, two processes are performing the export operation at the same time, but the two are going to write the exported data to the same data file, which inevitably leads to contention of 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

This export only used 10 minutes and a half, the efficiency of the export greatly improved.

4) test the degree of parallelism 4 and export to 4 data files respectively:

With 9 minutes to complete the export, set to the degree of parallelism 4 can still achieve a certain performance improvement, but it is not obvious, this is mainly due to the overall performance of the bottleneck is not the processing capacity of a single process, most of the performance of the bottleneck has become a disk IO bottleneck, this time alone by increasing the degree of parallelism can not significantly improve performance.

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://pizibaidu.blog.51cto.com/1361909/1852206

Oracle common Export and export commands and performance efficiency comparisons