Streams AQ: enqueue blocked on low memory Wait Event in Oracle

In this post, we will discuss about the wait event "Streams AQ: enqueue blocked on low memory" which was captured in the oracle AWR report.

In one of the environment there was a performance issue in which schema export backup was in hung state. Before this issue, ideally export was completing in one to two hours.

To resolve this issue, we increased the SGA but it didn't helped out. So we checked the wait event for which query was waiting for. Finally we checked the dynamic memory component and observed that streams pool was shrieked due to which it was blocked on low memory.  

The Oracle Streams pool is a portion of memory in the System Global Area (SGA) that is used by Oracle Streams. The Oracle Streams pool stores enqueued messages in memory, and it provides memory for capture processes and apply processes.

The Oracle Streams pool size is managed automatically when the MEMORY_TARGET, MEMORY_MAX_TARGET, or SGA_TARGET initialization parameter is set to a nonzero value. If these parameters are all set to 0 (zero), then you can specify the size of the Oracle Streams pool in bytes using the STREAMS_POOL_SIZE initialization parameter.

SQL> select SID,WAIT_CLASS,EVENT from v$session where SADDR in (select SADDR from dba_datapump_sessions);

       SID    WAIT_CLASS           EVENT
---------- --------------------  ----------------------------------------
        38     enqueue                    Streams AQ: enqueue blocked on low memory



SQL> select component,current_size/1024/1024,last_oper_type,last_oper_time from v$sga_dynamic_components;

Here we can see that streams pool got shrink-ed due to which it was in hung state.

SOLUTION:


As s  workaround, explicitly set the streams_pool_size to a fixed (large enough) value, e.g. 150 Mb (or 300 MB if needed) that will be used as a minimum value, e.g.:

CONNECT / as sysdba
ALTER SYSTEM SET streams_pool_size=150m SCOPE=both;

And re-run the Export or Import Data Pump job.

If you cannot modify the STREAMS_POOL_SIZE dynamically, then you need to set the value in the spfile, and restart the database.

CONNECT / as sysdba
ALTER SYSTEM SET streams_pool_size=150m SCOPE=spfile;
SHUTDOWN IMMEDIATE
STARTUP


NOTE:

If the problem is not fixed after implementing one of the above solutions, a fix for unpublished Bug 24560906 must be also installed before reporting the issue to Oracle Support.
Possible solutions for unpublished Bug 24560906 are:


Set the below parameter and restart the job.
alter system set "_disable_streams_pool_auto_tuning"=TRUE;
SHUTDOWN IMMEDIATE
STARTUP


EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)