11GR2-EXPDP导出很慢

Expdp and event ‘Streams AQ: enqueue blocked on low memory’

Categories：Oracle

Our daily expdp backup job became abnormally and it would last more than one day, while in the past it just took about 40 minutes.

From the v$session view I found the expdp related processes were waiting for event 'Streams AQ: enqueue blocked on low memory', and I also found the export speed was very very slow even the exported tables were empty.

Searched and got below notes:

EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)

The key information I got was this: If the size of the streams_pool is being modified, then this can result in excessive waits for 'Streams AQ: enqueue blocked on low memory'.
Every occurrence for this event causes a 1 minutes delay.

And the suggested actions could be easily executed were:

1. Explicitly set the streams_pool_size to a fixed (large enough) value, 150 Mb or 300 Mb if needed. We had 40G SGA, so I updated it to 500M.
2. Alter system set "_disable_streams_pool_auto_tuning"=TRUE. We modified it and when I checked and found the default value was TRUE, so in fact no need to modify it.

Three patches were mentioned and I found all of them already were included in the latest PSU, so maybe I just need to restart the instance to observe the result.

I could not restart it as it is a production database, so I tried to find more and got below notes:

Datapump Expdp Or Impdp Slowdown Due To Frequent Waits On ”Streams AQ: Enqueue Blocked On Low Memory" (Doc ID 2386566.1)

Two important information:

1. Select shrink_phase_knlasg from X$KNLASG;  Will return 1 if the stream pool is shrinking and return 0 if such action is finished.
2. Alter system set events 'immediate trace name mman_create_def_request level 6'. Will force to complete the stream pool shrink.

I run the statement in step 1 and did get value 1. I was surprised as we set the stream pool to 500M one day before and we still in the shrink action.

So I run statement in step 2 to complete it and I did begin to get value 0.

I checked the wait event after that and I could not find such wait event, so I guessed the issue should be fixed. I tailed the log of the expdp job and found it finished already!