Tag Archives: Streams AQ: enqueue blocked on low memory

Streams AQ: enqueue blocked on low memory wait event causes slow expdp export

Background:
it takes 2.5 hours for customers to export 2G data, which is exported in expdp mode (12c)

Problem solving:

According to the AWR report,
the exception is as follows

Refer to
expdp and impdp slow performance in 11gr2 and 12cr1 and waits on streams AQ: enqueue blocked on low memory (document ID 1596645.1)

View streams_ pool_ size

1 SQL> show parameter streams
2 
3 NAME TYPE VALUE
4 ------------------------------------ --------------------------------- ------------------------------
5 streams_pool_size big integer 0

This is automatic shared memory management, view the actual size

1 select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
2 from sys.x$ksppi x,sys.x$ksppcv y
3 where x.inst_id=userenv('Instance')
4 and y.inst_id=userenv('Instance')
5 and x.indx=y.indx
6 and x.ksppinm like '%streams_pool%'
7 /

the actual size is 32m

Adjust to 150m
alter system set streams_ POOL_ SIZE=150M SCOPE=BOTH;<
it is found that the export is still slow,
after restarting the database, the export is fast
(scope = both, you still need to restart here)

more content, please pay attention to WeChat official account: DBA every Journal