案例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 

現象和 - 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');

來實現。

具體建議：

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