Performance Comparison of Oracle data pump in different working modes (IV.) test external table export, import method

According to Oracle's documentation, the data pump in different ways to export imports, performance can be significantly different, this time just have the opportunity to test, migration table space, direct path, external table, and database chain export, import performance differences.

This test external table export, import method.

First clear the user and tablespace imported from the previous article, and re-establish the test user and tablespace.

sql> DROP USER Tjsq_ndmain CASCADE;

User dropped.

sql> DROP USER Tjsq_trade CASCADE;

User dropped.

sql> DROP USER tjsq_gov CASCADE;

User dropped.

sql> DROP USER tjsq_ndmain_oper CASCADE;

User dropped.

sql> DROP USER tjsq_trade_oper CASCADE;

User dropped.

Sql> DROP tablespace tjsq including CONTENTS and datafiles;

Tablespace dropped.

Sql> DROP tablespace tjsq_tmp including CONTENTS and datafiles;

Tablespace dropped.

Re-create the user below and set permissions:

Sql> CREATE USER Tjsq_ndmain identified by Tjsq_ndmain;

User created.

Sql> CREATE USER Tjsq_trade identified by Tjsq_trade;

User created.

Sql> CREATE USER tjsq_gov identified by tjsq_gov;

User created.

Sql> CREATE USER Tjsq_ndmain_oper identified by Tjsq_ndmain_oper;

User created.

Sql> CREATE USER Tjsq_trade_oper identified by Tjsq_trade_oper;

User created.

Sql> GRANT CONNECT to tjsq_gov;

Grant succeeded.

Sql> GRANT RESOURCE to tjsq_gov;

Grant succeeded.

Sql> GRANT CONNECT to Tjsq_ndmain;

Grant succeeded.

Sql> GRANT RESOURCE to Tjsq_ndmain;

Grant succeeded.

Sql> GRANT CONNECT to Tjsq_ndmain_oper;

Grant succeeded.

Sql> GRANT RESOURCE to Tjsq_ndmain_oper;

Grant succeeded.

Sql> GRANT CONNECT to Tjsq_trade;

Grant succeeded.

Sql> GRANT RESOURCE to Tjsq_trade;

Grant succeeded.

Sql> GRANT CONNECT to Tjsq_trade_oper;

Grant succeeded.

Sql> GRANT RESOURCE to Tjsq_trade_oper;

Grant succeeded.

sql> GRANT CREATE synonym to tjsq_gov;

Grant succeeded.

sql> GRANT CREATE synonym to Tjsq_ndmain;

Grant succeeded.

sql> GRANT CREATE synonym to Tjsq_ndmain_oper;

Grant succeeded.

sql> GRANT CREATE DATABASE LINK to Tjsq_trade;

Grant succeeded.

sql> GRANT CREATE synonym to Tjsq_trade;

Grant succeeded.

sql> GRANT CREATE synonym to Tjsq_trade_oper;

Grant succeeded.

The creation of table spaces and the time required for data file initialization need to be timed separately:

Sql> SET TIMING on

sql> CREATE tablespace tjsq datafile '/data/oracle/oradata/tjsq/tjsq01.dbf ' size20g,

2 '/data/oracle/oradata/tjsq/tjsq02.dbf ' size4g;

Tablespace created.

elapsed:00:02:07.68

sql> CREATE tablespace tjsq_tmp datafile '/data/oracle/oradata/tjsq/tjsq_tmp.dbf ' size4g;

Tablespace created.

elapsed:00:00:22.19

This step always takes 2 minutes and 30 seconds.

Obviously the export of external tables is performed, because Oracle uses direct paths by default, so changing this default setting requires some changes.

For export, if the external table is required for all tables, the best approach is to use query to export, plus an identity export condition that will make the export not use the direct path method: