![]() ![]() For more information about Oracle (NYSE:ORCL), visit . The key information I got was this: If the size of the streamspool is being modified, then this can result in excessive waits for Streams AQ: enqueue blocked. Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts. ![]() To view full details, sign in with your My Oracle Support account.ĭon't have a My Oracle Support account? Click to get started! Decemby Shripal 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. (logtime parameter is available on 12.1 and onward)Įxporting an empty partitioned table takes 0-3 seconds to export each partition, which normally takes less than a second to export. Waits Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn log file parallel write 36,463 0.00 566 16 0.23 Streams AQ: enqueue blocked on low memory. Information in this document applies to any platform.ĭatapump 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".įollowing is an example symptom from an expdp logtime=all command. In memory undo latch totalwaits Queueing: Streams AQ: enqueue blocked on low memory timewaitedmicro Queueing: Streams AQ: enqueue blocked on low. ![]() Oracle Database - Standard Edition - Version 11.2.0.4 and later Oracle Database - Enterprise Edition - Version 11.2.0.4 and later The same database export that I have to kill after 24 hours, now takes only 16.5 minutes with parallel degree of 4.Datapump Expdp Or Impdp Slowdown Due To Frequent Waits On ”Streams AQ: Enqueue Blocked On Low Memory" If you’ll still have the same wait event even after increasing minimum amount of the stream pool size, you’ll have to execute the following command:Īlter system set "_disable_streams_pool_auto_tuning"=TRUE scope=spfile sid = '*' Īfter increasing the value and disabling auto tuning, performance of the Data Pump were back to normal. ![]() ideally, the export backup was completed between one to two hours. If your database is very busy, you’ll have to do the following:Īlter system set streams_pool_size=256m scope=spfile sid = '*' 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. The production database was adjusted from original AMM (Automatic memory management) to ASMM (Automatic Shared Memory Management), then it takes 4.5 hours instead of 30 minutes to run logical backup. This value is dependent of the number of parallel processes (parameter parallel=4 in my case). Streams AQ: enqueue blocked on low memory. I’ve changed the minimum size of the stream pool to 256MB by executing the following command:Īlter system set streams_pool_size=256m scope=both sid = '*' EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (ID 1596645. Select current_size/1024/1024 as MB from v$sga_dynamic_components where component = 'streams pool' 38 enqueue Streams AQ: enqueue blocked on low memory SQL> select component,currentsize/1024/1024,lastopertype,lastopertime from vsgadynamiccomponents Here we can see that streams pool got shrink-ed due to which it was in hung state. Select value from v$parameter where name like '%stream%' EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On. To resolve the problem, there are several workarounds, but this is what is working in my case. Streams AQ: enqueue blocked on low memoryexpdp. blocked due to flow control - Streams AQ: enqueue blocked on low memory. You can see on the picture below how it look like. PMgrp0 grant - gc assume - gc block recovery request - gc buffer. In database version 11.2.0.4, Oracle has implemented a new algorithm for modifying components of the SGA memory, which has issue while changing the size of the stream pool size. When I checked in the evening, on my surprise, Data Pump job was still running.Īfter monitoring the Data Pump session, soon I’ve realized what is going on.Īlmost 94% of time was spent on the “Streams AQ: enqueue blocked on low memory” wait event.Īfter digging, Oracle MOS portal helped me in this case. While helping with migration of one Retail production system, I need to export certain schemas by using Data Pump utility.Īs the database is pretty small (about 3TB), I’ve just executed expdp command and expected it will finish within 2 to 3 hours. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |