expdp导出数据慢的问题
Oracle11g生产库遇到expdp导出数据非常慢，通过AWR报告查看等待事件:Streams AQ: enqueue blocked on low memory
Expdp and event ‘Streams AQ: enqueue blocked on low memory’Categories：OracleOur 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:
- 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.
- 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:
- Select shrink_phase_knlasg from X$KNLASG; Will return 1 if the stream pool is shrinking and return 0 if such action is finished.
- 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!
解决办法:
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@iypsprod-> select shrink_phase_knlasg from X$KNLASG;
SHRINK_PHASE_KNLASG
-------------------
1
sys@iypsprod-> alter system set events 'immediate trace name mman_create_def_request level 6';
系统已更改。
sys@iypsprod-> select shrink_phase_knlasg from X$KNLASG;
SHRINK_PHASE_KNLASG
-------------------
0
修改完参数后，重新导数据，速度瞬间提升。