expdp之所以會變慢,可能發生的原因是在auto SGA的設定下,stream pool 與 buffer cache間的資料移動有關(是個bug),Oracle在11g之後導入了新的演算法,但這個演算法還不夠完善,所以偶而會發生stream pool 回收(shrink)時異常。

特徵:

expdp期間會有"Streams AQ: enqueue blocked on low memory"的wait event.

SQL> select shrink_phase_knlasg from X$KNLASG;

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

正常應該為0,1表示stream pool處在shrink的狀態,shrink完成後,flag=0.

解決方式:

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

再做這個動作時,將會增加系統loading約1%,執行大約需費時5~10分鐘。

 

以下為Oracle Support上的文件:
 
 

In this Document

  Symptoms
  Changes
  Cause
  Solution
  References

 

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database - Standard Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

SYMPTOMS

Datapump export and import (expdp and impdp) 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. (logtime parameter is available on 12.1 and onward)
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 "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME1>" 0 KB 0 rows
11-APR-18 18:02:40.677: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME2>" 0 KB 0 rows
11-APR-18 18:02:42.686: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME3>" 0 KB 0 rows
11-APR-18 18:02:45.699: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME4>" 0 KB 0 rows
11-APR-18 18:02:48.702: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME5>" 0 KB 0 rows
11-APR-18 18:02:50.712: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME6>" 0 KB 0 rows
11-APR-18 18:02:53.724: . . exported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PART_NAME7>" 0 KB 0 rows

 

CHANGES

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
-------------------
1

 

CAUSE

The flag was not dropped even though the streams pool has ended shrinking, which causes various streams pool operations (such as internal operations performed by Datapump Utility) to wait on "StreamsAQ: enqueue blocked on low memory".

The issue has been addressed by Development in BUG 27634991 - EXPDP FREQUENTLY WAITS ON 'STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY', fixed starting with 19.1.
 

SOLUTION

If severe slowdown is seen in expdp/impdp command due to "StreamsAQ: enqueue blocked on low memory" wait event, and X$KNLASG.SHRINK_PHASE_KNLASG column keeps returning 1 for several minutes, run following command from sqlplus to force streams pool shrink to complete.

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


Patch 27634991 may be applied to prevent this problem from occurring.

If your problem is not just a slowdown but hangs or spins in "StreamsAQ: enqueue blocked on low memory" wait, then one of the following bugs may be suspected.

Bug:17365043 Session hangs on "Streams AQ: enqueue blocked on low memory"
Bug:24560906 High CPU usage for Background q0 processes with fix for bug 21286665

REFERENCES

BUG:27634991 - EXPDP FREQUENTLY WAITS ON 'STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY'
Didn't find what you are looking for?
arrow
arrow
    文章標籤
    oracle
    全站熱搜

    噗噗噗的潛水珽 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄