Impdp Slowdown due to StreamsAQ: enqueue blocked on low memory

Essa semana o processo de importação de dados demorou muito mais do que o normal. Ótimo ! Mais um problema para resolver 🙂

This week my impdp process took longer then expected. Great ! One more issue to play with 🙂

Verificando o Grid Control ficou claro que a sessão de impdp estava “em espera” pelo evento Oracle StreamsAQ: enqueue blocked on low memory.

Checking Grid Control it was clear that impdp process was in queuing state at Oracle event StreamsAQ: enqueue blocked on low memory

impdp_slowdown2

Os três notes abaixo trouxeram a solução:

With theses three documents below I could crack this issue:

  1. Datapump Expdp Or Impdp Slowdown Due To Frequent Waits On ”Streams AQ: Enqueue Blocked On Low Memory” (Doc ID 2386566.1)
  2. What DataPump And Oracle RDBMS Parameters And Features Can Significantly Affect DataPump Performance ? (Doc ID 1611373.1)
  3. Bug 17365043 : “STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY” WHEN REDUCING STREAMS_POOL_SIZE

Que foi aumentar o valor do parâmetro STREAMS_POOL_SIZE para o valor da query:

Which was to increase the parameter STREAMS_POOL_SIZE to the value returned by the query:

select 'ALTER SYSTEM SET STREAMS_POOL_SIZE='||(max(to_number(trim(c.ksppstvl)))+67108864)||' SCOPE=SPFILE;'
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c 
where a.indx = b.indx and a.indx = c.indx and lower(a.ksppinm) in ('__streams_pool_size','streams_pool_size');

Após a alteração, o processo de import voltou ao normal.

The impdp process came back to work after increasing the streams_pool_size.

impdp_slowdown1

 

Author: reguchi

Oracle ACE, OCP 12c, OCE 12c MAA, OCE Exadata, RHCE, AWS CSAA, OCI CSAA. Technology geek, homebrewer, dad.

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