ORACLE 12c索引分裂引起的會話夯

    最近維護的一套4節點12c資料庫出現會話操作響應緩慢的情況。

    檢視問題時間段的awr，發現資料庫當時負載并不高：

    進一步檢視問題時間段awr的top等待事件，發現有比較嚴重的gc index operation等待事件：

    觀察問題時間段awr的sql部分，發現SQL語句dvr0q1vq2jwx4很怪異，一條簡單的insert SQL語句執行效率十分低下：

dvr0q1vq2jwx4：

insert into tsys_p

  (piljybss,

   ljhaoshi,

   jiaoyzht,

   plzxmosh,

   plrwzdbz,

   plrwtjsj,

   plrwuyxj,

   jyksshij,

   kshishjc,

   jyjsshij,

   jshishjc,

   xunijibs,

   ipdizhii,

   zhujimic,

   shujuquu,

   qslcbzha,

   qszxxhao,

   qspljyzu,

   qsbuzhou,

   cuowxinx,

   cuowduiz,

   fuwbiaoz,

   zxtongbh,

   xitongbs,

   farendma)

values

  (:10,

   :11,

   :12,

   :13,

   :14,

   :15,

   :16,

   :17,

   :18,

   :19,

   :20,

   :21,

   :22,

   :23,

   :24,

   :25,

   :26,

   :27,

   :28,

   :29,

   :30,

   :31,

   :32,

   :33)

    個人經驗而論像是oracle的bug，網上倒是發現了一個類似的問題文章：

https://www.cnblogs.com/cqdba/p/073afa55d272ee9ccb65c2102ba437e3.html 但是，進一步核實又不太一樣，

因為本次問題涉及的表并沒有blob/clob大字段。針對本次問題，向oracle提起了sr請求進一步确認是否是oracle自身的

bug，得到了印證：

    MOS文檔28023081.8詳細内容：

Bug 28023081  Sessions Hang Due to Waits 'gc current split'/ 'gc index operation'

 This note gives a brief overview of bug 28023081.

 The content was last updated on: 20-OCT-2020

 Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 19.1
Versions confirmed as being affected	18.4.0 18.3.0 18.1.0 12.2.0.1 (Base Release)
Platforms affected	Generic (all / most platforms affected)

Fixed:

The fix for 28023081 is first included in

19.1.0 Database Release Update (DB RU) 18.5.0.0.190115 (Jan 2019) 12.2.0.1.190115 (Jan 2019) Database Jan 2019 Release Update (DB RU) 12.2.0.1.190416 (Apr 2019) Bundle Patch for Windows Platforms

Interim patches may be available for earlier versions - click here to check.

Symptoms:	Related To:
Performance Affected (General) Waits for "gc current split" Waits for "gc index operation"	RAC (Real Application Clusters) / OPS

Description

This bug is only relevant when using Real Application Clusters (RAC)

In a RAC environment the sessions may hang on 'gc current split'/ 'gc index operation' 
Rediscovery Notes
 Session hanging on 'gc current split'/ 'gc index operation' cycle, local DLM lock shows repeated message BAST being sent:
 
GCS SHADOW 0x30661c288,256 resp[0x30e6efa18,0x39b6f.23] pkey 0.11.35289 
...
KJBL history 0x84.0x84.0x84.0x84.0x84.0x84.0x84.0x84... 
Workaround
 _gc_fast_index_split_wait=0
           

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

Bug:28023081 (This link will only work for PUBLISHED bugs)

Note:245840.1 Information on the sections in this article