Re: Stats on Huge Partitioned Table and ORA-01555

From: Connor McDonald <>
Date: Thu, 28 May 2015 09:54:34 +0800
Message-ID: <>

The best way to gather stats is to not to gather them :-)

Now - I'm *not* saying that you dont *have* stats, I'm saying that (especially for OLTP systems) you often dont need to *gather* them...

Your email already suggests you know the volumes of the data, and with a little analysis, I'm sure you can work out the distributions of the columns etc...Armed with that, you can simply use dbms_stats.set_table_stats to prime the statistics.

I'm always amazed at the number of places that do "gather" jobs for hours to work out how the number of distinct sales departments they have, or number of different genders in the person table etc.... when a 10second look at the organisation chart would do just as nicely :-)


On Wed, May 27, 2015 at 2:28 AM, Michael Cunningham <> wrote:

> Oracle with UNDO 30G, undo_retention=900
> Table has 7.1 billion+ rows, 503 partitions, 3TB
> I am relatively new to partitioned tables and am getting ORA-01555 errors
> during execution of dbms_stats.gather_database_stats_job_proc.
> The alert log shows something like (I hope this is helpful)
> /* SQL Analyze(0) */ select /*+ full(t) no_parallel(t)
> no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl
> dynamic_sampling(0) no_monito
> ring xmlindex_sel_idx_tbl no_substrb_pad */to_char(count(
> Question: Is there a better way to gather stats on this table? Last time
> this happened I set the table stats manually (so they would not show
> stale), but now I need to fix this properly with a permanent solution.
> P.S. I can increase UNDO - which I'm willing to do - but I'm also trying
> to learn if there is a better way to solve this.
> Thanks,
> --
> Michael Cunningham

Connor McDonald

"If you are not living on the edge, you are taking up too much room."
- Jayne Howard

Received on Thu May 28 2015 - 03:54:34 CEST

Original text of this message