Stats on Huge Partitioned Table and ORA-01555
Date: Tue, 26 May 2015 11:28:45 -0700
Message-ID: <CAPt39tsEJkb2SEz1hfKRgpedwo6=Y7aVVUUisSvk3utu_yXR7A_at_mail.gmail.com>
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
Received on Tue May 26 2015 - 20:28:45 CEST