expdp导出慢并提示Streams AQ: enqueue blocked on low memory

MOS:

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

 

Oracle Database - Enterprise Edition - 版本 11.2.0.4 和更高版本

Oracle Database - Standard Edition - 版本 11.2.0.4 和更高版本

Oracle Database Cloud Schema Service - 版本 N/A 和更高版本

Oracle Database Exadata Cloud Machine - 版本 N/A 和更高版本

Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本

本文档所含信息适用于所有平台

症状

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

 

以下是expdp logtime = all命令的示例症状。 (logtime 参数在 12.1 及以上版本可用) 

导出空表需要0-3秒甚至更多才能导出每个segment，而正常时通常需要不到一秒的时间。

11-APR-18 18:02:26.726: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
11-APR-18 18:02:37.672: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME1>" 0 KB 0 rows
11-APR-18 18:02:40.677: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME2>" 0 KB 0 rows
11-APR-18 18:02:42.686: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME3>" 0 KB 0 rows
11-APR-18 18:02:45.699: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME4>" 0 KB 0 rows
11-APR-18 18:02:48.702: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME5>" 0 KB 0 rows
11-APR-18 18:02:50.712: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME6>" 0 KB 0 rows
11-APR-18 18:02:53.724: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME7>" 0 KB 0 rows

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 gc cr request                                                             1
         1 db file scattered read                                                    1
         1 direct path read                                                          1
--->     1 Streams AQ: enqueue blocked on low memory                                 2
         1 latch: row cache objects                                                  2
         1 SQL*Net message to client                                                 6
         2 direct path read                                                          1
         2 db file sequential read                                                   1
         2 SQL*Net message to client                                                 1

9 rows selected.

数据库中的等待事件也会提示Streams AQ: enqueue blocked on low memory

并且有时候可能会在expdp log和alert日志中有Streams AQ: enqueue blocked on low memory的报错， 但是注意不一定有，只是有可能。

 

更改

在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';

执行该语句后，expdp恢复了往日的流畅，导出空表时唰唰的。

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

 

如果不只是性能变差，而是在 "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