In a Siebel database we have implemented the Siebel way to collect statistics.
(Performance Tuning Guidelines for Siebel CRM Application on Oracle Database (Doc ID 781927.1))
While running the siebel_stats.gather_siebel_stats; procedure we got:
ORA-01555 caused by SQL statement below (SQL ID: 9jqzgr0ajwzzj, Query Duration=0 sec, SCN: 0x0020.228661af):
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(rep) 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(val) repsq from (select /*+ no_expand_table(t) index_rs(t) parallel(t,24) parallel_index(t,24) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */substrb(“ROW_ID”,1,32) val, ntile(254) over (order by nlssort(substrb(“ROW_ID”,1,32),’NLS_SORT = binary’)) bkt from “SIEBEL”.”S_ESCL_LOG” t where substrb(“ROW_ID”,1,32) is not null) group by val) group by maxbkt order by maxbkt
The interesting part here is the Duration, it shows 0 seconds.
From Oracle Support:
In some cases, due to some sort of index corruption, some queries may fail with ORA-1555 with very low value of query duration (Query duration =0 or 1 seconds)
Rebuilding the index have removed the corruption or the inconsistency and the query ran fine then.
Rebuilding the indexes solved our problem.
If rebuilding the indexes won’t help, try to export/import the tables. We were told that migrated blocks also can cause this problem.