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:

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:-)




23 replies

  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.

  2. Wow, marvelous blog layout! How long have you been running a blog for?
    you made running a blog glance easy. The whole
    look of your web site is wonderful, as well as the content!

  3. Wow, fantastic weblog structure! How long have you ever been blogging for?
    you make running a blog glance easy. The total look of your
    site is fantastic, let alone the content!

  4. Awesome! Its actually awesome paragraph, I have got much clear idea on the topic of from this paragraph.

  5. I blog frequently and I truly appreciate your information. The article has truly peaked
    my interest. I will book mark your site and keep checking for new information about once
    per week. I subscribed to your RSS feed too.

  6. Good day! I just would like to offer you a big thumbs up for your great information you have got here on this post. I am returning to your web site for more soon.

  7. I love what you guys are up too. Such clever work and reporting! Keep up the amazing works guys I’ve incorporated you guys to my own blogroll.

  8. Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest.

  9. I like the valuable information you provide in your articles.

  10. I like the valuable information you provide in your articles.

  11. Spot on with this write-up, I truly assume this website wants much more consideration. I’ll in all probability be once more to learn far more, thanks for that info.

  12. I pay a visit day-to-day some web sites and sites to read content,
    but this webpage gives feature based articles.

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 )

Connecting to %s