Streams aq enqueue blocked on low memory11/2/2022 ![]() 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. The problem also reproduces when running an Export Data Pump job from such a release database. Streams AQ: enqueue blocked on low memory. The slow Import Data Pump performance problem might also reproduce in other Oracle11g Releases (like: 11.2.0.3) or Oracle12c Release 12.1.0.1. The import with DataPump of tables into an Oracle11g Release 11.2.0.4 database takes almost 45 minutes, while the same import into Oracle10g Release 10.2.0.4 only take 2 minutes. Information in this document applies to any platform. Oracle Database - Standard Edition - Version 11.2.0.1 to 12.1.0.2 Oracle Database Cloud Service - Version N/A and later Datapump Expdp Or Impdp Slowdown Due To Frequent Waits On Streams AQ: Enqueue Blocked On Low Memory' (Doc ID 2386566.1) Two important information: Select shrinkphaseknlasg from XKNLASG Will return 1 if the stream pool is shrinking and return 0 if such action is finished. Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later I set it, then I was able to complete my impdp successfully.EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory In this case, I should set my STREAMS_POOL_SIZE to 1G. Either leave this parameter unset or set this. And sometimes there may be Streams AQ: enqueue blocked on low memory errors in expdp log and alert log, but note that it may not be, but it is possible. When set explicitly to a value of 0, it can negatively impact the speed of Advanced Queue operations, which in turn can negatively affect Data Pump operations since Data Pump uses Advanced Queueing. Select 'ALTER SYSTEM SET STREAMS_POOL_SIZE='||(max(to_number(trim(c.ksppstvl)))+67108864)||' SCOPE=SPFILE 'įrom sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv cĪnd lower(a.ksppinm) in ('_streams_pool_size','streams_pool_size') You can use parallel to speedup the datapump jobs. I found this query to help to identify proper value to set STREAMS_POOL_SIZE during impdp execution: It is related to bug #17365043 where we need to set/increase STREAMS_POOL_SIZE (if we don’t already have AMM in place, so we need to increase MEMORY_TARGET/MEMORY_MAX_TARGET).
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |