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

最新推荐文章于 2021-04-05 09:10:27 发布

cuizhi4718

最新推荐文章于 2021-04-05 09:10:27 发布

阅读量2.5k

点赞数

某客户在使用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数最好一致。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10271187/viewspace-1710678/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10271187/viewspace-1710678/

cuizhi4718

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
等待事件：wait for unread message on broadcast channel

某客户在使用expdp导出数据时遇到waitfor unread message on broadcast channel等待事件，查找mos文档 WAITEVENT: "wait for unread messa...
复制链接

扫一扫