Re: Stats on Huge Partitioned Table and ORA-01555

At what percentage are you collecting stats? 100% (i.e. compute), or something less? In general, it's pretty difficult to convince me that using anything other than AUTO_SAMPLE_SIZE is a good idea, particularly for large tables.

If you're already gathering stats at auto sample size, and still hitting ORA-01555, then consider what time the stats gathering is happening, and whether there's a lot of DML going on the table at that time? You didn't say data warehouse, but "huge partitioned tables" would seem to suggest it. If that's the case, then you shouldn't have large-scale DML (other than scheduled APPEND insert style data loads) going on.....Also, when the ORA-1555 occurs, look in the alert log, and see how long the query was running when the ORA-1555 error occurred. Compare that time to the undo_retention setting. If the time at which the error occurred is less than the undo_retention, then you probably need to increase size of undo tablespace. Look at V$UNDOSTAT to confirm. If the undo_retention is less than the time it took for query to hit the ORA-1555, then increase undo_retention.

Hope that's useful...

-Mark

On Tue, May 26, 2015 at 2:29 PM Michael Cunningham <napacunningham_at_gmail.com> wrote:

> Oracle 12.1.0.1.0 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
>

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