In this article, we are going to discuss the oracle wait event “Streams AQ: enqueue blocked on low memory” due to this wait event we are facing a performance issue in the export backup, today my export backup was in a hung state and it takes a very long time. ideally, the export backup was completed between one to two hours.
We try to resolve this issue by increasing the SGA size, but it didn’t help out. So we checked the wait event to find the query which is in waiting. Finally, we found that the streams pool was shrieked due to which it was blocked on low memory.
The Oracle Database Streams pool is an area of memory in the SGA that is used by Oracle Streams. The Streams pool stores enqueued messages in memory, and it provides memory for capturing processes and applying processes.
Check Wait event:
SQL> select SID, WAIT_CLASS, EVENT from v$session where SADDR in (select SADDR from dba_datapump_sessions); SID WAIT_CLASS EVENT ———- ——————– —————————————- 27 enqueue Streams AQ: enqueue blocked on low memory
Check pool details:
SQL> select component,current_size/1024/1024,last_oper_type,last_oper_time from v$sga_dynamic_components;
Output from the above query:
In the above output, you can see the steams pool status is "SHRINK", due to this reason the export backup got hung. So, we need to increase memory space for the streams pool.
Solution: Streams AQ: enqueue blocked
We are going to change the memory for the streams pool using the below query.
SQL> ALTER SYSTEM SET streams_pool_size=150m SCOPE=both;
Restart the Database and then run the export backup. If the still issue still exists then it must be a bug 24560906.
Please this article is useful for you please write in the comment box.
One thought on “Streams AQ: enqueue blocked on low memory Oracle wait event”