SQL> select inst_id,event,count(1) from gv$session where wait_class#<> 6 group by inst_id,event order by 1,3;
INST_ID EVENT COUNT(1)
---------- ------------------------------------------- ------------------
1 SQL*Net message to client 1
1 Streams AQ: enqueue blocked on low memory 1
(Doc ID 2469587.1)查询MOS,“由于频繁等待 "Streams AQ: Enqueue Blocked On Low Memory"
而导致Datapump Expdp或Impdp变慢”对该等待事件有描述:
因 Datapump导出和导入(expdp和impdp)可能会遇到突然严重的性能问题,
因为DW和DM进程经常等待 “StreamsAQ: enqueue blocked on low memory”。
由于streams_pool
如果遇到类似的性能问题时,请检查以下查询是否一直返回“1”。该值表示 streams pool 处于收缩阶段。
当 streams pool 完成收缩时,该值应返回“0”,但如果它一直返回“1”,则您可能遇到此问题。
SQL> select shrink_phase_knlasg from X$KNLASG;
SHRINK_PHASE_KNLASG
-------------------
1
--解决方案
如果由于"StreamsAQ: enqueue blocked on low memory"等待事件导致
expdp / impdp命令出现严重性能问题,并且X$KNLASG.SHRINK_PHASE_KNLASG 列保持返回1并持续几分钟,
则从sqlplus运行以下命令强制streams pool缩小完成。
connect / as sysdba
alter system set events 'immediate trace name mman_create_def_request level 6';
执行该语句后,expdp恢复了往日的流畅
alter system set events 'immediate trace name mman_create_def_request level 3';
SQL> select shrink_phase_knlasg from X$KNLASG;
SHRINK_PHASE_KNLASG
-------------------
1
SQL> alter system set events 'immediate trace name mman_create_def_request level 6';
System altered.
SQL> select shrink_phase_knlasg from X$KNLASG;
SHRINK_PHASE_KNLASG
-------------------
0