Oracle AWR無法生成快照(ORA-32701)

環境說明：

DB:Oracle 11.2.0.4.0
OS:Redhat 7.6

問題現象：

資料庫告警日誌每天會有大量的ORA-32701錯誤，內容如下：

Sat Apr 03 22:09:48 2021
Errors in file /oracle/db/diag/rdbms/sytrnt/trnt1/trace/trnt1_dia0_126699.trc  (incident=264060):
ORA-32701: Possible hangs up to hang ID=34 detected
DIA0 terminating blocker (ospid: 160308 sid: 2960 ser#: 8539) of hang with ID = 34
    requested by master DIA0 process on instance 1
    Hang Resolution Reason: Although the number of affected sessions did not
    justify automatic hang resolution initially, this previously ignored
    hang was automatically resolved.
   by terminating the process ospid:160308

影響範圍：

AWR快照無法自動生成和手動生成，

嘗試手動收集AWR快照，卡住很久，對應等待事件為"enq: WF - contention";

EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

Oracle預設每小時生成一次快照，11g中，預設儲存時間為8天，而在10g中，預設儲存時間為7天

select * from dba_hist_wr_control;

檢視快照生成情況：

col BEGIN_INTERVAL_TIME for a50
col FLUSH_ELAPSED for a30
select SNAP_ID,BEGIN_INTERVAL_TIME,FLUSH_ELAPSED from dba_hist_snapshot order by snap_id;

問題分析：

檢視trnt1_dia0_126699.trc日誌:

Incident 264050 created, dump file: /oracle/db/diag/rdbms/sytrnt/trnt1/incident/incdir_264050/trnt1_dia0_126699_i264050.trc
ORA-32701: Possible hangs up to hang ID=29 detected

檢視trnt1_dia0_126699_i264050.trc日誌：

*** 2021-04-03 17:09:39.961
Resolvable Hangs in the System
                     Root       Chain Total               Hang               
  Hang Hang          Inst Root  #hung #hung  Hang   Hang  Resolution         
    ID Type Status   Num  Sess   Sess  Sess  Conf   Span  Action             
 ----- ---- -------- ---- ----- ----- ----- ------ ------ -------------------
     29 HANG RSLNPEND    1  2960     2     2   HIGH GLOBAL Terminate Process  
  Hang Resolution Reason: Although the number of affected sessions did not
    justify automatic hang resolution initially, this previously ignored
    hang was automatically resolved.
     Previous SESSION termination was unsuccessful. PROCESS termination
    will be attempted.

inst# SessId  Ser#     OSPID PrcNm Event
  ----- ------ ----- --------- ----- -----
      2   3162 21973    139799  M000 enq: WF - contention
      1   2960  3479    151457  M000 not in wait

檢視當前SQL：

*** 2021-04-03 17:09:40.084
current sql: insert into wrh$_sql_bind_metadata   (snap_id, dbid,    sql_id, name, position, dup_position,    datatype, datatype_string,    character_sid, precision, scale, max_length)  SELECT /*+ ordered use_nl(bnd) index(bnd sql_id) */      :lah_snap_id, :dbid,      bnd.sql_id, name, position, dup_position,      datatype, dataty

完整SQL如下：

insert into wrh$_sql_bind_metadata
  (snap_id,
   dbid,
   sql_id,
   name,
   position,
   dup_position,
   datatype,
   datatype_string,
   character_sid,
   precision,
   scale,
   max_length)
  SELECT /*+ ordered use_nl(bnd) index(bnd sql_id) */
   :lah_snap_id,
   :dbid,
   bnd.sql_id,
   name,
   position,
   dup_position,
   datatype,
   datatype_string,
   character_sid,
   precision,
   scale,
   max_length
    FROM x$kewrattrnew new, x$kewrsqlidtab tab, v$sql_bind_capture bnd
   WHERE new.str1_kewrattr = tab.s;

檢視SQL對應執行計劃如下：

13Plan hash value: 4222011306
14 
15----------------------------------------------------------------------------------------------
16| Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
17----------------------------------------------------------------------------------------------
18|   0 | INSERT STATEMENT          |                  |       |       |     2 (100)|          |
19|   1 |  LOAD TABLE CONVENTIONAL  |                  |       |       |            |          |
20|   2 |   NESTED LOOPS            |                  |   100 | 11500 |     2 (100)| 00:00:01 |
21|*  3 |    HASH JOIN              |                  |   100 |  4800 |     0   (0)|          |
22|   4 |     FIXED TABLE FULL      | X$KEWRATTRNEW    |   100 |  3400 |     0   (0)|          |
23|   5 |     FIXED TABLE FULL      | X$KEWRSQLIDTAB   |   100 |  1400 |     0   (0)|          |
24|*  6 |    FIXED TABLE FIXED INDEX| X$KQLFBC (ind:2) |     1 |    67 |     0   (0)|          |
25----------------------------------------------------------------------------------------------
26 
27Predicate Information (identified by operation id):
28---------------------------------------------------
29 
30   3 - access("NEW"."STR1_KEWRATTR"="TAB"."SQLID_KEWRSIE")
31   6 - filter(("INST_ID"=USERENV('INSTANCE') AND "TAB"."SQLID_KEWRSIE"="KQLFBC_SQLID" 
32              AND "TAB"."CHILDADDR_KEWRSIE"="KQLFBC_CADD"))

檢視X$KQLFBC資料量很大：

select count(*) from X$KQLFBC; ---長時間無返回結果

檢視MOS：

Error ORA-32701 'On Current SQL: insert into wrh$_sql_bind_metadata' (文件 ID 2226216.1)

原因：

CAUSE
檢視v$sqlbind_capture對應於固定表X$KQLFBC，該表主要用於儲存與資料繫結相關的變數。
View v$sqlbind_capture corresponds to fixed table X$KQLFBC table which is mainly used to store variables associated with the binding of data. 
在使用大量繫結變數的大型資料庫中可以注意到此錯誤。
This error can be noticed in large databases using large amount of binding variables.

解決方案：

1. Collect statistics on following fixed table:

exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW');

exec dbms_stats.gather_table_stats('SYS', 'X$KEWRSQLIDTAB');

立即生效

exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KEWRATTRNEW',no_invalidate => FALSE);

exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KEWRSQLIDTAB',no_invalidate => FALSE);

經測試，收集統計資訊後，並沒有解決問題，AWR還是無法生成快照。

Or

2.重新啟動資料庫將釋放X$KQLFBC表資料(需要停機視窗，謹慎操作)

2. Restarting the database will release of X$KQLFBC table data

Or

3.定期重新整理共享池(需要停機視窗，謹慎操作)

3. Flush shared_pool on a regular basis

alter system flush shared_pool;

Or

4.針對資料量較大的基表，也可以透過設定引數來遮蔽相關資料寫入到awr中。

alter system set "_awr_disabled_flush_tables" = 'wrh$_sql_bind_metadata';

其中 wrh$_sql_bind_metadata 可以替換成其他導致 AWR 無法正常完成的收集任務的基表名稱。

#####chenjuchao 20210830 21:45#####