expdp导出慢的解决思路__增加参数metrics记录每个步骤时间,增加参数trace记录trace...

两者都是未公开的参数expdp -help看不到

expdp system/xxx directory=DATADIR dumpfile=data1.dmp logfile=log1.log schemas=hr CONTENT=METADATA_ONLY INCLUDE=TABLE:\"IN\(\'SCANLOG\'\)\" METRICS=Y TRACE=480300  

Majority of issues use following two data pump parameters: 

METRICS=Y -- this will provide timing data on number of objects 

TRACE=480300 -- this will trace dm and dw processes 

开启了METRICS=Y的话，导出日志记录了每个对象的导出时间

没有加METRICS=Y的情况下 ，D20181029_01.log日志信息

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_03":  system/******** directory=DATA_PUMP_DIR dumpfile=D20181029_01 logfile=D20181029_01.log schemas=HR CONTENT=METADATA_ONLY METRICS=Y TRACE=480300 INCLUDE=TABLE:"IN('APPROVELOG')" 

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

加了METRICS=Y的情况下 ，D20181029_02.log日志信息

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_03":  system/******** directory=DATA_PUMP_DIR dumpfile=D20181029_02 logfile=D20181029_02.log schemas=HR CONTENT=METADATA_ONLY METRICS=Y TRACE=480300 INCLUDE=TABLE:"IN('APPROVELOG')" 

Startup took 118 seconds

Processing object type SCHEMA_EXPORT/TABLE/TABLE

     Completed 1 TABLE objects in 56 seconds

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

     Completed 2 OBJECT_GRANT objects in 11 seconds

开启了TRACE=480300的话(IMDP的话TRACE=480301)，导出时，会额外的往ora_dm00和ora_dw00两个进程里写入更多详细的信息

DMnn和DWnn(数据泵主进程/工作进程)

DM00是Data Pump Master Process

The Data Pump master (control) process is started during job creation and coordinates all tasks performed by the Data Pump job. It handles all client interactions and communication, establishes all job contexts, and coordinates all worker process activities on behalf of the job.

数据泵主（控制）过程在作业创建期间启动，并协调数据泵作业执行的所有任务。 它处理所有客户端交互和通信，建立所有作业上下文，并代表作业协调所有工作进程活动。

DW00是Data Pump Worker Process

The Data Pump worker process is responsible for performing tasks that are assigned by the Data Pump master process, such as the loading and unloading of metadata and data.

Data Pump工作进程负责执行由Data Pump主进程分配的任务，例如元数据和数据的加载和卸载。

没有加TRACE=480300的情况下 ,SID_dm00_23031.trc和SID_dw00_23040.trc内容分别如下

*** 2018-10-29 16:36:35.010

*** SESSION ID:(1506.95) 2018-10-29 16:36:35.010

*** CLIENT ID:() 2018-10-29 16:36:35.010

*** SERVICE NAME:(SYS$USERS) 2018-10-29 16:36:35.010

*** MODULE NAME:(Data Pump Master) 2018-10-29 16:36:35.010

*** ACTION NAME:(SYS_EXPORT_SCHEMA_01) 2018-10-29 16:36:35.010

KUPC: Setting remote flag for this process to FALSE

KUPP: Initialization complete for master process DM00  

--到KUPP此次就结束了

*** 2018-10-29 16:37:08.623

*** SESSION ID:(2107.5) 2018-10-29 16:37:08.623

*** CLIENT ID:() 2018-10-29 16:37:08.623

*** SERVICE NAME:(SYS$BACKGROUND) 2018-10-29 16:37:08.623

*** MODULE NAME:(Data Pump Worker) 2018-10-29 16:37:08.623

*** ACTION NAME:(SYS_EXPORT_SCHEMA_01) 2018-10-29 16:37:08.623

KUPC: Setting remote flag for this process to FALS   

--到KUPC此处就结束了

加了TRACE=480300的情况下 ，SID_dm00_23086.trc和SID_dw00_23083.trc内容分别如下

*** 2018-10-29 16:40:29.183

*** SESSION ID:(5.31) 2018-10-29 16:40:29.183

*** CLIENT ID:() 2018-10-29 16:40:29.183

*** SERVICE NAME:(SYS$USERS) 2018-10-29 16:40:29.183

*** MODULE NAME:() 2018-10-29 16:40:29.183

*** ACTION NAME:() 2018-10-29 16:40:29.183

KUPP:16:40:29.135: Current trace/debug flags: 00480300 = 4719360

*** MODULE NAME:(Data Pump Master) 2018-10-29 16:40:29.193

*** ACTION NAME:(SYS_EXPORT_SCHEMA_01) 2018-10-29 16:40:29.193

KUPC:16:40:29.193: Setting remote flag for this process to FALSE

KUPM:16:40:29.250: Attached to control queue as MCP

KUPM:16:40:29.251: While starting, control queue subscriber count is: 2

KUPP:16:40:29.252: Initialization complete for master process DM00

KUPM:16:40:29.309: Entered main loop

KUPM:16:40:29.312: ****IN DISPATCH at 60029, request type=1001

KUPM:16:40:29.313: Current user is: SYSTEM

KUPM:16:40:29.314: hand := DBMS_DATAPUMP.OPEN ('EXPORT', 'SCHEMA', '', 'SYS_EXPORT_SCHEMA_01', '', '2');

KUPM:16:40:29.508: Resumable enabled

....

--后面还很多内容

*** 2018-10-29 16:40:31.127

*** SESSION ID:(304.13) 2018-10-29 16:40:31.128

*** CLIENT ID:() 2018-10-29 16:40:31.128

*** SERVICE NAME:(SYS$BACKGROUND) 2018-10-29 16:40:31.128

*** MODULE NAME:() 2018-10-29 16:40:31.128

*** ACTION NAME:() 2018-10-29 16:40:31.128

KUPP:16:40:31.065: Current trace/debug flags: 00480300 = 4719360

*** MODULE NAME:(Data Pump Worker) 2018-10-29 16:40:31.132

*** ACTION NAME:(SYS_EXPORT_SCHEMA_01) 2018-10-29 16:40:31.132

KUPW:16:40:31.132: 0: ALTER SESSION ENABLE PARALLEL DML called.

KUPW:16:40:31.135: 0: ALTER SESSION ENABLE PARALLEL DML returned.

KUPC:16:40:31.140: Setting remote flag for this process to FALSE

KUPW:16:40:31.198: 0: KUPP$PROC.WHATS_MY_ID called.

KUPW:16:40:31.199: 1: KUPP$PROC.WHATS_MY_ID returned.

KUPW:16:40:31.200: 1: worker max message number: 1000

KUPW:16:40:31.202: 1: Full cluster access allowed

KUPW:16:40:31.203: 1: Original job start time: 18-OCT-29 04:40:29 PM

KUPW:16:40:31.216: 1: Seqno 64 is SCHEMA_EXPORT/TABLE/TABLE_DATA

KUPW:16:40:31.217: 1: Seqno 248 is SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/TABLE_DATA

KUPW:16:40:31.217: 1: KUPP$PROC.WHATS_MY_NAME called.

KUPW:16:40:31.218: 1: KUPP$PROC.WHATS_MY_NAME returned. Process name: DW00

...   

--后面还很多内容

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2217978/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2217978/