ORA-1555 running DBMS_STATS (Siebel)

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s