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:-)
Cheers
Regards,
Adityanath
Categories: Administration, backup & recovery, Peformance Tuning
Adityanath's Oracle Blog 
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.
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
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!
Very interesting subject , regards for posting.
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
Enjoyed looking at this, very good stuff, thanks.
Thanks!
Regards for this post, I am a big big fan of this website would like to continue updated.
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!
Awesome! Its actually awesome paragraph, I have got much clear idea on the topic of from this paragraph.
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.
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.
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.
Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest.
I like the valuable information you provide in your articles.
I like the valuable information you provide in your articles.
I used to be able to find good info from your blog posts.
Really enjoyed this article post.
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.
Hello, I enjoy reading all of your article post. I wanted to
write a little comment to support you.
A big thank you for your blog post.Much thanks again. Will read on…
I pay a visit day-to-day some web sites and sites to read content,
but this webpage gives feature based articles.
Enjoyed looking through this, very good stuff, regards.