EXPDP/IMPDP has performance issue due to “Streams AQ: enqueue blocked on low memory”

In this post, we are discussing about performance issue which I faced with EXPDP schema backup.

We have a production database, for which we take daily EXPDP schema backup before EOD starts. The control-m job configuration has made in such a way that EOD will not start until schema level EXPDP backup gets complete. From last month, EXPDP backup started taking considerable time. Initially it used to take hardly 15 minutes, and all of sudden it started taking 90 minutes, in turn causing delays in start of EOD. Application team raised problem ticket with us for the same issue.

I did basic tuning, like increasing PGA or excluding statistics, but none of it helped me to minimize backup time. Then I thought of observing wait event for EXPDP master & slave processes. It was “Streams AQ: enqueue blocked on low memory”. Of course it was something to related to performance issue I was observing.

I did check for memory configuration of database & found memory is configured with automatic management:

2
If you see above output, due to sudden growth in shared pool size, other components like large pool, db cache & streams pool have shrunk.

I tried to dig in history backup logfiles & found, till 6th Jan 2017, backups were running fine. So lack of streams pool memory is causing EXPDP to run longer.

So as to resolve this issue, I switched from automatic memory to manual one & allocated 200 MB to streams pool. Post memory configuration changes, EXPDP performance was back to normal.

After searching on MOS for similar issues, I found EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

Advertisements

2 thoughts on “EXPDP/IMPDP has performance issue due to “Streams AQ: enqueue blocked on low memory”

  1. Hello There. I found your blog using msn. This is a really well written article. I will make sure to bookmark it and come back to read more of your useful info. Thanks for the post. I’ll certainly return.

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s