Michael,
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@xxxxxxxxx>
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
