[MVS]Oracle Bug 27634991 - Datapump Frequently Waits On 'Streams AQ: enqueue blocked on low memory' 介绍

BUG介绍：

    该BUG覆盖范围： 数据库版本11.2.0.4-18.5 的所有操作系统平台上；

    Bug 27634991 导致在AMM,ASMM模式下，由于streams pool内存抖动触发了内存收缩，内存收缩的过程中会堵塞stream pool内存池里面的操作，导致expdp导出时间变长。    

BUG原因：

    This problem may be seen in Auto SGA environment (sga_target or memory_target is set).Activities on the db that utilize streams pool memory run persistently slower following actions on the database which trigger Automatic Memory Management to make the policy decision to shrink streams pool.

BUG示例：

    One example of such operation is datapump export import (expdp, impdp).Those commands may encounter sudden severe slowdowns due to DW and DM processes frequently waiting on "StreamsAQ: enqueue blocked on low memory".

    Following is an example symptom from an expdp logtime=all command. Exporting an empty partitioned table takes 0-3 seconds to export each partition, which normally takes less than a second to export.

 

 11-APR-18 18:02:26.726: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

 11-APR-18 18:02:37.672: . . exported "<USERNAME>"."PART_TAB1":"P1" 0 KB 0 rows

 11-APR-18 18:02:40.677: . . exported "<USERNAME>"."PART_TAB1":"P10" 0 KB 0 rows

 11-APR-18 18:02:42.686: . . exported "<USERNAME>"."PART_TAB1":"P11" 0 KB 0 rows

 11-APR-18 18:02:45.699: . . exported "<USERNAME>"."PART_TAB1":"P12" 0 KB 0 rows

 11-APR-18 18:02:48.702: . . exported "<USERNAME>"."PART_TAB1":"P13" 0 KB 0 rows

 11-APR-18 18:02:50.712: . . exported "<USERNAME>"."PART_TAB1":"P14" 0 KB 0 rows

 11-APR-18 18:02:53.724: . . exported "<USERNAME>"."PART_TAB1":"P15" 0 KB 0 rows

 

While this problem is being encountered, following query will always return 1. 

select shrink_phase_knlasg from X$KNLASG;

解决办法：   

    临时解决办法：

    When the conditions on this bug are seen,  you can force a complete status on the shrink by executing in sqlplus :

    alter system set events 'immediate trace name mman_create_def_request level 6';

    安装补丁：

    Patch 27634991: EXPDP FREQUENTLY WAITS ON 'STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY'