EXPDP Streams AQ: enqueue blocked on low memory

有一台11.2.0.4的库最近expdp导出非常慢，总是停留在99%以后还要持续2个多小时，还有另外一台同版本的数据库也碰到了类似的情况。

Job: SYS_EXPORT_SCHEMA_01
  Owner: XXX                    
  Operation: EXPORT                         
  Creator Privs: TRUE                           
  GUID: 8A27A5FA09F142F9E055000000000001
  Start Time: 
  Mode: SCHEMA                         
  Instance: 
  Max Parallelism: 4
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        backup_user/******** dumpfile=
     COMPRESSION           ALL                                     
  State: EXECUTING                      
  Bytes Processed: 45,234,549,312
  Percent Done: 99
  Current Parallelism: 4
  Job Error Count: 0

当前导出的对象都是一些空的分区，查询导出任务是否在等待

SQL> select dw.waiting_session,
       dw.holding_session,
       b.serial#,
       w.event,
       w.program          wprogram,
       b.program          bprogram,
       w.module           wmod,
       b.module           bmod,
       LOCK_ID1
  from sys.dba_waiters dw, v$session w, v$session b
 where dw.waiting_session = w.sid
   and dw.holding_session = b.sid
   and (w.module like 'Data Pump%' or w.program like '%EXPDP%' or
       w.program like '%IMPDP%')
 order by dw.holding_session;

 no rows selected


SQL> select sw.SID,
       sw.SEQ#,
       sw.EVENT,
       sw.WAIT_TIME,
       sw.SECONDS_IN_WAIT,
       sw.STATE,
       sw.P1TEXT,
       sw.P1,
       sw.P2TEXT,
       sw.P2,
       sw.P3TEXT,
       sw.P3
  from V$SESSION_WAIT sw, v$session s
 where sw.wait_class <> 'Idle'
   and sw.sid = s.sid
   and (s.module like 'Data Pump%' or s.program like '%EXPDP%' or
       s.program like '%IMPDP%');

      SID    SEQ# EVENT                             WAIT_TIME SECONDS_IN_WAIT STATE           P1TEXT         P1 P2TEXT         P2 P3TEXT         P3
---------- ---------- ---------------------------------------------------------------- ---------- --------------- ------------------- ---------- ---------- ---------- ---------- ---------- ----------
      1801  31734 Streams AQ: enqueue blocked on low memory                 0       1 WAITING                 0         0             0
      2540   3931 Streams AQ: enqueue blocked on low memory                 0       1 WAITING                 0         0             0

等待显示导出队列在等待与streams相关的内存，在sga中就是streams pool，查看当前streams pool的使用情况

select component
,current_size/1024/1024 "Current MB"
,min_size/1024/1024 "Min MB"
from v$sga_dynamic_components;

COMPONENT                            Current MB Min MB
---------------------------------------------------------------- ---------- ----------
shared pool                                3232   1472
large pool                               64     64
java pool                                32     32
streams pool                                0        0
DEFAULT buffer cache                               6624   6432
KEEP buffer cache                             0      0
RECYCLE buffer cache                              0      0
DEFAULT 2K buffer cache                           0      0
DEFAULT 4K buffer cache                           0      0
DEFAULT 8K buffer cache                           0      0
DEFAULT 16K buffer cache                          0      0
DEFAULT 32K buffer cache                          0      0
Shared IO Pool                                0      0
ASM Buffer Cache                              0      0

因为这个库设置了sga_target，相关组件的内存大小分配是由oracle自动来控制的，所以可能碰上了bug。

Workaround：

1. 重启数据库，通常能解决这个问题
2. 修改参数，设定streams_pool_size的最小值

CONNECT / as sysdba
ALTER SYSTEM SET streams_pool_size=150m SCOPE=both;

3. 如果无法动态调整，则需要重启

CONNECT / as sysdba
ALTER SYSTEM SET streams_pool_size=150m SCOPE=spfile;
SHUTDOWN IMMEDIATE
STARTUP

4. 打补丁Patch 17365043
   或者修改隐含参数

alter system set "_disable_streams_pool_auto_tuning"=TRUE;

做完以后重新执行datapump任务。