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

...   

--後面還很多內容