案例ORA-00600: internal error code, arguments: [qkaffsindex3], [], [], [], []

执行更新统计信息语句：

exec dbms_stats.gather_schema_stats(ownname=>'LIVE_KS',degree=>2,cascade=>true,options=>'GATHER AUTO',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO');

报错：

ORA-00600: internal error code, arguments: [qkaffsindex3], [], [], [], [], [], [], [], [], [], [], []

 

使用10046 trace跟踪：

PARSING IN CURSOR #140247979913912 len=501 dep=1 uid=0 oct=3 lid=0 tim=10144694746826 hv=3314025276 ad='11db7aa78' sqlid='8pwjxwg2sh0tw'

select substrb(dump(val,16,0,64),1,240) ep, cnt from (select /*+ no_expand_table(t) index_rs(t)   no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad  */mod("SYS_STSPQ0MN35WUGZJNRTQJ3QX27K",9999999999) val,count(*) cnt  from "LIVE_KS"."P_PRD_SERIAL_NUM" t  where mod("SYS_STSPQ0MN35WUGZJNRTQJ3QX27K",9999999999) is not null  group by mod("SYS_STSPQ0MN35WUGZJNRTQJ3QX27K",9999999999)) order by val

END OF STMT

PARSE #140247979913912:c=2034,e=1830,p=0,cr=6,cu=0,mis=1,r=0,dep=1,og=1,plh=283912825,tim=10144694746826

EXEC #140247979913912:c=16,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=283912825,tim=10144694746881

WAIT #140247979913912: nam='db file sequential read' ela= 2299 file#=20 block#=16230458 blocks=1 obj#=5232618 tim=10144694749235

WAIT #140247979913912: nam='db file sequential read' ela= 752 file#=20 block#=16230459 blocks=1 obj#=5232618 tim=10144694750044

FETCH #140247979913912:c=0,e=3195,p=2,cr=4,cu=0,mis=0,r=1,dep=1,og=1,plh=283912825,tim=10144694750112

FETCH #140247979913912:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=283912825,tim=10144694750167

STAT #140247979913912 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT GROUP BY (cr=4 pr=2 pw=0 time=3192 us cost=3 size=24 card=2)'

STAT #140247979913912 id=2 cnt=16 pid=1 pos=1 obj=5232618 op='INDEX FAST FULL SCAN IDX2_DLIND_ACTI_SALSTTID_PRDID (cr=4 pr=2 pw=0 time=3173 us cost=2 size=132 card=11)'

CLOSE #140247979913912:c=0,e=3,dep=1,type=0,tim=10144694750236

 

 

发现对于IDX2_DLIND_ACTI_SALSTTID_PRDID使用了INDEX FAST FULL SCAN 

 

现象和 Bug 28132202 - ORA-600[QKAFFSINDEX3] OCCURS IN INDEX FAST FULL SCAN 非常类似

 

进一步诊断查看sql语句，语句中的SYS_STSPQ0MN35WUGZJNRTQJ3QX27K是自动手机的extended statistics，如果语句把这个statistics移除，则不会出错， 

所以，手动重建extended statistics

 

EXEC DBMS_STATS.DROP_EXTENDED_STATS('LIVE_KS','P_PRD_SERIAL_NUM','("PRD_ID","SALES_STATUS_ID","ALLOC_COUNT")');

EXEC DBMS_STATS.CREATE_EXTENDED_STATS('LIVE_KS','P_PRD_SERIAL_NUM','("PRD_ID","SALES_STATUS_ID","ALLOC_COUNT")');

 

问题解决，未再发生。

 

针对12cR1，建议disable 多列自动统计信息收集，避免bug：

alter session set "_OPTIMIZER_USE_FEEDBACK"=FALSE;

alter session set "_optimizer_adaptive_plans" =false;

alter session set "_optimizer_dsdir_usage_control"=0;

 

如果部署了patch

 

Bug 21171382  Enhancement: AUTO_STAT_EXTENSIONS preference on DBMS_STATS

 

也可以通过

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS','OFF');

来实现。

具体建议：

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/6e9e0d66-08a0-4da0-a2ca-3598bcc28d7a/File/945c53b4121121a5e3054491565e8225/optimizer_for_poc_v4.pdf