Streams AQ: enqueue blocked on low memory processing issue

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.