Hi
For such large table you probably need special treatment for the statistics
such as copying partition statistics and gather global statistics
seperately. Using the default job is probably not such good idea (unless
there is soemthing new in 12c)
Gathering global stats might be a problem, in this case is use incremental
statistics (synopsis). The first time it will take long time but after that
the time should be shorter
Thanks
On Tue, May 26, 2015 at 8:28 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
