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