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
Adityanath's Database & Cloud Blog 
Leave a reply to Blondell Lacewell Cancel reply