最近维护的一套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 Platforms affected Generic (all / most platforms affected)
Fixed:
The fix for 28023081 is first included in
Interim patches may be available for earlier versions - click here to check.
Symptoms: | Related To: |
|
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