Trick to trace or Dig export/Datapump slow job ...

Here is some trick to trace or Dig export/Dp slow job ...

For a detailed way to trace the DP processes and see what is going on behind the scenes follow the below steps :

-----------------------
1. Gather statistics for the dictionary and fixed objects. See MOS note:
Note 473423.1 Ext/Pub Poor Performance With DataPump Export on Large Databases

2. We will need to trace Data Pump export this way:

++ before restarting the expdp check if there are orphan Data Pump jobs left in database and remove them.
Use Note 336014.1 - "How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?"

++ Add/Replace the following parameters: METRICS=Y TRACE=480300 in expdp command line

3.
After expdp job started:

-- In SQL*Plus, obtain Data Pump process info:
CONNECT / as sysdba

ALTER SYSTEM SET MAX_DUMP_FILE_SIZE=UNLIMITED;

set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;

-- Get the sid and serial# for DM00 and DW00 and execute :
exec DBMS_SYSTEM.SET_EV([SID],[SERIAL#],10046,12 ,''); !!!!!!! Execute this for each process : DM00, DW00

If you reach the point where it is hanging let it for a couple of minutes and kill the process.

++ create standard tkprof output files for Data Pump Master and Worker SQL traces generated in bdump directory. For example:

% tkprof orcl_dm00_17292.trc tkprof_orcl_dm00_17292.out waits=y sort=exeela
% tkprof orcl_dw01_17294.trc tkprof_orcl_dw00_17294.out waits=y sort=exeela

------------------------
Check DM0, DW0 traces the TKPROFs obtained, this will give you the exact SQL that makes datapump to hang. The TKPROF outputs are ordered by the wait time.