【ASK_ORACLE】資料泵由於”Streams AQ: Enqueue Blocked On Low Memory" 問題變慢

實驗環境

搭建平臺：VMware Workstation

OS：RHEL 7.6

Grid&DB：Oracle 12.2.0.1

問題描述

在使用資料泵匯出和匯入（expdp和impdp）突然遇到嚴重的效能問題，導致導數時間極其漫長，檢視日誌發現資料泵兩個程式DMnn（資料泵主程式）和DWnn（資料泵工作程式）經常等待出現"StreamsAQ: enqueue blocked on low memory"。

以下是使用expdp命令匯出資料時命令的示例症狀（能顯示匯出時間是因為新增Oracle 12.1及以上版本的引數logtime = all） 

正常時間匯出空分割槽表通常需要不到一秒的時間，但是現在突然要2-3秒才能匯出每個分割槽，而且是空分割槽表。。。

12-Dec-21 10:09:15.573: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
12-Dec-21 10:09:17.589: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME1>" 0 KB 0 rows
12-Dec-21 10:09:20.661: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME2>" 0 KB 0 rows
12-Dec-21 10:09:22.672: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME3>" 0 KB 0 rows
12-Dec-21 10:09:25.698: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME4>" 0 KB 0 rows
12-Dec-21 10:09:27.721: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME5>" 0 KB 0 rows
12-Dec-21 10:09:30.733: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME6>" 0 KB 0 rows
12-Dec-21 10:09:32.758: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME7>" 0 KB 0 rows

 

解決辦法

這是由於當資料庫記憶體使用了ASMM或者AMM的管理方式時，如果此時的buffer cache負載較高並且streams pool中的記憶體正被轉移到buffer cache時，可能會發生此問題。

可以使用以下SQL來檢查以下查詢是否一直返回“1”：

SQL> select shrink_phase_knlasg from X$KNLASG;
SHRINK_PHASE_KNLASG
-------------------
1

注：

該欄位表示 streams pool 處於收縮階段。當 streams pool 完成收縮時，該值應返回“0”，但如果它一直返回“1”，這個問題就可能發生。

 

所以，我們知道現象背後的原理就有思路了：

如果SHRINK_PHASE_KNLASG列在幾分鐘之內的值仍然是“1”的時候，則從sqlplus執行以下命令強制streams pool縮小完成：

connect / as sysdba
alter system set events 'immediate trace name mman_create_def_request level 6';

 

但是！

即使 streams pool 已經結束收縮，該標誌也可能沒有被修改！所以"StreamsAQ: enqueue blocked on low memory"這個問題會一直存在。這是一個官方bug，需要透過補丁27634991修復（Oracle 19c已經預設修復了該bug）。