等待 ”Streams AQ: Enqueue Blocked On Low Memory" 导致Datapump缓慢

遇到问题如下

执行impdp导入的时候，即使导入对象非常小。也在初始化阶段等待很久。
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table “SYS_IMPORT_TABLE_01” successfully loaded/unloaded
…
最终耗时：elapsed 0 00:01:35
等待事件为：Streams AQ: Enqueue Blocked On Low Memory

于是找到这篇文章

由于频繁等待 ”Streams AQ: Enqueue Blocked On Low Memory" 而导致Datapump Expdp或Impdp变慢 (文档 ID 2469587.1)

症状

Datapump导出和导入（expdp和impdp）可能会遇到突然严重的性能问题，因为DW和DM进程经常等待 “StreamsAQ: enqueue blocked on low memory”。

以下是expdp logtime = all命令的示例症状。 (logtime 参数在 12.1 及以上版本可用)
导出空分区表需要0-3秒才能导出每个分区，而正常时通常需要不到一秒的时间。

11-APR-18 18:02:26.726: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
11-APR-18 18:02:37.672: . . exported ""."":"

" 0 KB 0 rows


更改

在Auto SGA环境（设置了sga_target或memory_target）下，当 buffer cache 负载较高并且 streams pool 中的内存正被移动到 buffer cache 时，可能会发生此问题。

如果遇到类似的性能问题时，请检查以下查询是否一直返回“1”。该值表示 streams pool 处于收缩阶段。当 streams pool 完成收缩时，该值应返回“0”，但如果它一直返回“1”，则您可能遇到此问题。

SQL> select shrink_phase_knlasg from X$KNLASG;

SHRINK_PHASE_KNLASG
-------------------
1


原因

即使 streams pool 已经结束收缩，该标志也没有被修改，这导致各种 stream pool 操作（例如数据泵的内部操作）等待 “StreamsAQ: enqueue blocked on low memory”。

该问题是由于Bug 27634991引起的，在版本19.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';


可以应用Patch 27634991以防止发生此问题。

如果您的问题不只是性能变差，而是在 “StreamsAQ: enqueue blocked on low memory” 等待中hang或spin，则可能是以下错误之一所引起。

Bug:17365043 Session hangs on “Streams AQ: enqueue blocked on low memory”
Bug:24560906 High CPU usage for Background q0 processes with fix for bug 21286665

问题解决

SQL> select shrink_phase_knlasg from X$KNLASG; --反复查询都为1
SHRINK_PHASE_KNLASG
-------------------
1
-- 查看SGA变动
SQL> select * from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME GRANULE_SIZE
------------------------------ ------------ ---------- ---------- ------------------- ---------- ------------- --------- ------------------- ------------
shared pool 9932111872 2684354560 9932111872 0 84 GROW DEFERRED 2024-05-16 16:31:16 67108864
large pool 469762048 469762048 3758096384 0 70 SHRINK DEFERRED 2022-09-17 04:30:43 67108864
java pool 67108864 67108864 469762048 0 6 SHRINK DEFERRED 2024-02-06 07:54:35 67108864
streams pool 134217728 0 134217728 0 8 GROW IMMEDIATE 2024-02-06 18:00:02 67108864 --上次调整是2月，不是当前，可能长期处于异常状态
DEFAULT buffer cache 1.5502E+10 1.5301E+10 2.1676E+10 0 174 SHRINK DEFERRED 2024-05-16 16:31:16 67108864

SQL> select * from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME GRANULE_SIZE
------------------------------ ------------ ---------- ---------- ------------------- ---------- ------------- --------- ------------------- ------------
shared pool 9932111872 2684354560 9932111872 0 84 GROW DEFERRED 2024-05-16 16:31:16 67108864
large pool 469762048 469762048 3758096384 0 70 SHRINK DEFERRED 2022-09-17 04:30:43 67108864
java pool 67108864 67108864 469762048 0 6 SHRINK DEFERRED 2024-02-06 07:54:35 67108864
streams pool 67108864 0 134217728 0 9 SHRINK DEFERRED 2024-05-30 17:14:36 67108864
DEFAULT buffer cache 1.5569E+10 1.5301E+10 2.1676E+10 0 175 GROW DEFERRED 2024-05-30 17:14:36 67108864
-- streams pool 和 buffer cache 的 LAST_OPER_TIME 同时发生变化


再次执行导入验证，恢复正常： elapsed 0 00:00:01