ANBOB™

今天一个客户的oracle 19c（19.17）环境显示top event中出现了一个比较稀奇的event ‘gc index operation’, 也可能是12cR2 RAC新特性”fast index split wait”引入的相同问题， 19c前已知bug常发生成 BASICFILE LOB 对象上，仅记录.

sys@ORA19C 17:27:47> @sed "index operation"
Show wait event descriptions matching %index operation%..
old  17: AND lower(e.name) like lower('&1')
new  17: AND lower(e.name) like lower('%index operation%')

EVENT# EVENT_NAME                                              WAIT_CLASS           PARAMETER1                PARAMETER2                PARAMETER3                ENQUEUE_NAME         REQ_REASON                        REQ_DESCRIPTION
------ ------------------------------------------------------- -------------------- ------------------------- ------------------------- ------------------------- ------------------------------ -------------------------------- ----------------------------------------------------------------------------------------------------
   230 gc index operation                                      Cluster              usn#                      slot#                     seq#

可能的原因

Oracle 19c 以前，可能存在Bug 28023081 Sessions Hang Due to Waits ‘gc current split’/ ‘gc index operation’， Update SQL for BASICFILE LOB hang with hangchain of ‘gc index operation'<=’enq: HW – contention’ (Doc ID 2778623.1)提到在Hanganalyze 时显示’gc index operation'<=’enq: HW – contention’。

做call stack 显示

kclsplitwait()+1228<-kcbgcur()+18212<-ktbgcur()+128<-kdifind()+600<-kdimod0()+1248

解决方法
1) patch 27657712
2) patch 28023081

或考虑BASICFILE LOBs to SECUREFILE LOBs 可能避免该问题。

或禁用特性

SQL> alter system set "_gc_fast_index_split_wait"=0 scope=spfile sid='*';

read more Oracle 12cR2 RAC新特性”fast index split wait” 引入gc index split transaction增长导致ora-4031

打赏

微信扫一扫，打赏作者吧～