Data Pump Export workers are waiting on ‘wait for unread message on broadcast channel’

Version:

Oracle 11.2.0.3.0 - Production

Issue:

A database full export job triggered couple of days back is very slow or hung. Around 2 TB of dump files were generated, but it seemed to be hung after that.

The job status is shown as "Executing" but the dump file size showed no progress and stayed at same value for more than a day.

Analysis:

There were no messages or warnings in the alert log file.
Export status showed as executing and there was n't any errors.


Export> status

Job: SYS_EXPORT_FULL_01

  Operation: EXPORT

  Mode: FULL

  State: EXECUTING

  Bytes Processed: 2,104,376,791,712

  Percent Done: 94

  Current Parallelism: 10

  Job Error Count: 0

  Dump File: /backup/prod/DB/orcl/expdp/orcl_datapump_%u.dmp  

An event 10046 trace showed that it is waiting on the following

"wait for unread message on broadcast channel"

There is a support Doc for similar issue. Reason is the statistics are not up-to-date

Solution:

1) Gather statistics on the data dictionary and system tables.

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (null);

2) Restart the EXPDP job 

After gathering statistics. The performance was back to normal.