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 ...    --后面还很多内容