Datapump Expdp Or Impdp Slowdown Due To Frequent Waits On ”Streams AQ: Enqueue Blocked On Low Memory

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database - Standard Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

SYMPTOMS

Datapump export and import (expdp and impdp) may encounter sudden severe slowdowns due to DW and DM processes frequently waiting on "StreamsAQ: enqueue blocked on low memory".
 

Following is an example symptom from an expdp logtime=all command. (logtime parameter is available on 12.1 and onward)
Exporting an empty partitioned table takes 0-3 seconds to export each partition, which normally takes less than a second to export.

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

 

CHANGES

This problem may potentially occur in Auto SGA environment (sga_target or memory_target is set), when there is a load in the buffer cache and streams pool memory is being moved to buffer cache.

If encountering similar slowdowns, check if following query constantly returns "1".  This value indicates that streams pool is in shrinking phase.  The value should return "0" when streams pool finishes shrinking, but if it keeps returning "1", then you may have encountered this problem.

SQL> select shrink_phase_knlasg from X$KNLASG;

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

 

CAUSE

The flag was not dropped even though the streams pool has ended shrinking, which causes various streams pool operations (such as internal operations performed by Datapump Utility) to wait on "StreamsAQ: enqueue blocked on low memory".

The issue has been addressed by Development in BUG 27634991 - EXPDP FREQUENTLY WAITS ON 'STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY', fixed starting with 19.1.
 

SOLUTION

If severe slowdown is seen in expdp/impdp command due to "StreamsAQ: enqueue blocked on low memory" wait event, and X$KNLASG.SHRINK_PHASE_KNLASG column keeps returning 1 for several minutes, run following command from sqlplus to force streams pool shrink to complete.

connect / as sysdba
alter system set events 'immediate trace name mman_create_def_request level 6';

Patch 27634991 may be applied to prevent this problem from occurring.

If your problem is not just a slowdown but hangs or spins in "StreamsAQ: enqueue blocked on low memory" wait, then one of the following bugs may be suspected.

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