Stats on Huge Partitioned Table and ORA-01555

From: Michael Cunningham <napacunningham_at_gmail.com>
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

Original text of this message