有套oracle12.2.0.1环境,expdp备份一直很正常,但突然有一天备份的特别慢,由于数据库有1个多T,导致24小时都备份不完,但之前大概需要5个小时左右。
监控的时候发现备份进程一直在等待StreamsAQ: enqueue blocked on low memory,印象中以前也遇到过,增加了large pool的大小后即可,但这次不好使了。
查了下相关资料,有种情况是sga设置成自动调整,expdp备份的过程中需要调整stream pool大小,但没有调整成功,就会一直卡在该等待事件上:
This problem may potentially occur in Auto SGA environment (sga_target or memory_target is set), when there is a load in the buffer cache and streams pool memory is being moved to buffer cache.
If encountering similar slowdowns, check if following query constantly returns "1". This value indicates that streams pool is in shrinking phase. The value should return "0" when streams pool finishes shrinking, but if it keeps returning "1", then you may have encountered this problem.
执行了如下语句:
SQL> select shrink_phase_knlasg from X$KNLASG;
SHRINK_PHASE_KNLASG
----