等待事件：wait for unread message on broadcast channel

某客戶在使用expdp匯出資料時遇到wait for unread message on broadcast channel等待事件，查詢mos文件

WAITEVENT: "wait for unread message on broadcast channel" Reference Note (文件 ID 170464.1)

The Oracle process is waiting for a message on a broadcast channel. This is normally an idle wait - the process is waiting for an AQ message on a subscribed queue.

This event is classed as an "idle" wait so should be ignored when looking at systemwide timings. See <<61998.1>> for more information about "IDLE" waits.

該等待事件是由於oracle程式在請求message時出現等待，該等待是空閒(idle)等待，並且可以忽略
測試過程：
一、不使用並行
1、查詢表的行數
JZH@jzh>select count(*) from t;

  COUNT(*)
----------
   9202528

JZH@jzh>quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2、使用expdp匯出
[oracle@jzh5 ~]$ expdp jzh/jzh directory=dmp dumpfile=jzh.dmp logfile=jzh.log

Export: Release 11.2.0.3.0 - Production on Wed Jun 24 04:25:50 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JZH"."SYS_EXPORT_SCHEMA_01":  jzh/******** directory=dmp dumpfile=jzh.dmp logfile=jzh.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.063 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "JZH"."T"                                   888.5 MB 9202528 rows
. . exported "JZH"."TEST1"                               46.45 MB 4096000 rows
. . exported "JZH"."TEST"                                122.3 KB   10000 rows
. . exported "JZH"."JZH1"                                5.460 KB       4 rows
. . exported "JZH"."TEST2"                               5.007 KB       1 rows
Master table "JZH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JZH.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/jzh.dmp
Job "JZH"."SYS_EXPORT_SCHEMA_01" successfully completed at 04:28:12

3、查詢等待事件
SYS@jzh>select sid from v$session where username='JZH';

      SID
----------
        35
        42
        44
SYS@jzh>select event from v$session_wait where sid in (35,42,44);
EVENT
--------------------------------------------------
direct path read
wait for unread message on broadcast channel
wait for unread message on broadcast channel

二、使用並行，dumpfile與paralle一致
1、使用expdp匯出
[oracle@jzh5 ~]$ expdp jzh/jzh directory=dmp dumpfile=jzh_%u.dmp logfile=jzh.log parallel=2
Export: Release 11.2.0.3.0 - Production on Wed Jun 24 06:04:28 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JZH"."SYS_EXPORT_SCHEMA_01":  jzh/******** directory=dmp dumpfile=jzh_%u.dmp logfile=jzh.log parallel=2 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.063 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "JZH"."TEST1"                               46.45 MB 4096000 rows
. . exported "JZH"."TEST"                                122.3 KB   10000 rows
. . exported "JZH"."JZH1"                                5.460 KB       4 rows
. . exported "JZH"."TEST2"                               5.007 KB       1 rows
. . exported "JZH"."T"                                   888.5 MB 9202528 rows
Master table "JZH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JZH.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/jzh_01.dmp
  /home/oracle/jzh_02.dmp
Job "JZH"."SYS_EXPORT_SCHEMA_01" successfully completed at 06:06:10
2、查詢等待事件
SYS@jzh>select sid from v$session where username='JZH';
       SID
----------
        17
        27
        45
        50
SYS@jzh>select event from v$session_wait where sid in (17,27,45,50);
EVENT
--------------------------------------------------
direct path read
wait for unread message on broadcast channel
wait for unread message on broadcast channel
wait for unread message on broadcast channel

三、使用並行，parallel與dumpfile不一致
1、使用expdp匯出
[oracle@jzh5 ~]$ expdp jzh/jzh directory=dmp dumpfile=jzh.dmp logfile=jzh.log parallel=2

Export: Release 11.2.0.3.0 - Production on Wed Jun 24 06:09:06 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JZH"."SYS_EXPORT_SCHEMA_01":  jzh/******** directory=dmp dumpfile=jzh.dmp logfile=jzh.log parallel=2 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.063 GB
Processing object type SCHEMA_EXPORT/USER
. . exported "JZH"."T"                                   888.5 MB 9202528 rows
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "JZH"."TEST1"                               46.45 MB 4096000 rows
. . exported "JZH"."TEST"                                122.3 KB   10000 rows
. . exported "JZH"."JZH1"                                5.460 KB       4 rows
. . exported "JZH"."TEST2"                               5.007 KB       1 rows
Master table "JZH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JZH.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/jzh.dmp
Job "JZH"."SYS_EXPORT_SCHEMA_01" successfully completed at 06:11:12
2、查詢等待事件
SYS@jzh>select sid from v$session where username='JZH';

       SID
----------
        40
        42
        45
        50

SYS@jzh>select event from v$session_wait where sid in (40,42,45,50);

EVENT
--------------------------------------------------
direct path read
wait for unread message on broadcast channel
wait for unread message on broadcast channel
wait for unread message on broadcast channel

總結：1、使用expdp/impdp是儘量少用parallel，不但影響效能，而且會遭遇bug
       2、如果使用parallel，parallel與dumpfile數最好一致。