11204环境，expdp突然变慢

客户反馈，最近expdp备份特别的慢，9月份的时候还是18分钟完成，现在都1个多小时，数据量也没啥变化。

9月底备份log

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_37 is:
  /backup/orcl20230930.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Sat Sep 30 22:48:40 2023 elapsed 0 00:18:30
 

手动备份log

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /backup/orcl20231120_1550.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 20 17:02:56 2023 elapsed 0 01:11:58

mos文档

Bug 27634991 - Datapump Frequently Waits On 'Streams AQ: enqueue blocked on low memory' (Doc ID 27634991.8)

处理

1、调整参数，需要重启才生效

 SQL>  alter system set streams_pool_size=512M ;
 alter system set streams_pool_size=512M
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

2、临时处理

SQL> select shrink_phase_knlasg from X$KNLASG;

SHRINK_PHASE_KNLASG
-------------------
                1

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

SQL> select shrink_phase_knlasg from X$KNLASG;

SHRINK_PHASE_KNLASG
-------------------
                0

处理后再次备份，备份正常