Streams AQ: enqueue blocked on low memory processing issue in Oracle
Error:
Wait Event: “Streams AQ: enqueue blocked on low memory”
Streams will stop processing. When we restart streams it will go back to reading that same log and not complete or hanged.
However, if we stop and start the database it will start with the same log and complete it and move on with no issues until it does it all over again.
Check AWR report having Stream AQ waits:
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class ========================================= ===== ==================== ============= ========= =========== Streams AQ: enqueue blocked on low memory 554 2558.2 4618 39.6 Queueing library cache lock 5 1804.7 360934 28.0 Concurrency library cache pin 2 1536.1 768036 23.8 Concurrency enq: TQ - DDL contention 2 720.4 360200 11.2 Other
Cause:
wait 'Streams AQ: enqueue blocked on low memory' indicates exhaustion of the streams pool, so at some point, Streams processes are needed more memory.
Solution
1. The wait 'Streams AQ: enqueue blocked on low memory' indicates exhaustion of the streams pool.
2. Check the stream pool size parameter
SQL> show parameter streams; NAME TYPE VALUE ------------------------ ----------- -------- streams_pool_size big integer 0 SQL> show parameter sga_ NAME TYPE VALUE ----------------------- ----------- --------- sga_max_size big integer 1536M sga_min_size big integer 0 sga_target big integer 1536M
Note:
Steams pool is part of SGA memory.
If SGA is not defined then you need to check momeory target parameter.
3. Check the V_$STREAMS_POOL_STATISTICS and DBA_HIST_STREAMS_POOL_ADVICE view for suggestion.
Select * from v_$STREAMS_POOL_STATISTICS;
Select * from DBA_HIST_STREAMS_POOL_ADVICE;
4.Resize STREAMS_POOL_SIZE (and SGA parameters as needed, to accomodate increase in streams pool)
alter system set STREAMS_POOL_SIZE=100M scope=both;
--SGA
Alter system set sga_target=2000M scope=spfile;
5. Restart the database to take parameter effect.