Re: gather_schema_stats failed with ORA-07445 ?

> ORA-07445: exception encountered: core dump [smboAQSdoRS()+336] [SIGSEGV] [Addre
> ss not mapped to object] [0x2E31373505391A36] [] []
> Current SQL statement for this session:
> select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(d
> ump(max(val),16,0,32),1,120) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(re
> p) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (
> select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(va
> l) repsq from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_
> sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */"MEMBERID"
> val, ntile(254) over (order by "MEMBERID") bkt from sys.ora_temp_1_ds_75749 t w
> here "MEMBERID" is not null) group by val) group by maxbkt order by maxbkt

Hi, Guang,

I can't find any article about the smboAQSdoRS function. But related functions starting with "smbo" appear in some notes or bug reports. They seem to be related to memory usage for sorting as in index creation, e.g., Note:423282.1 "ORA-00600 [smboGetNxtAlo:max] Using Large Sort". The workaround there is set "_newsort_enabled" to false. Alternatively, Bug 5202144 is "ORA-7445 [SMBOQBNXT] OR ORA-600 [RWORUPO.1] FROM SUM OVER PARTITION" and the workaround is set "_windowfunc_optimization_settings" to 8. Your SQL uses analytic functions over partitions, and is run when Oracle is generating a histogram (see p.156 of Jonathan's CBO book, or US patent 6732085). If neither workaround works for you, perhaps you can temporarily stop creating the histogram by explicitly setting method_opt to 'for all columns size 0', or (with some risk) modify the option with set_param. Work with Oracle support anyway.

Yong Huang       

--
http://www.freelists.org/webpage/oracle-l