Re: Stats on Huge Partitioned Table and ORA-01555

> Oracle 12.1.0.1.0 with UNDO 30G, undo_retention=900

I have not researched this for Oracle 12.1.0.1, but based on your UNDO tablespace settings you may use auto tuned undo retention.

> 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.

• Are you already using incremental statistics?
• Are you creating/gathering unnecessary histograms (default setting)? Gathering a lot of histograms cause high runtime of DBMS_STATS.
• Are you already using parallel query for gathering statistics (parameter degree)? I guess not based on your provided SQL snap.

Please provide some more insights what you are using and how you gather the statistics.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

> Michael Cunningham <napacunningham_at_gmail.com> hat am 26. Mai 2015 um 20:28 geschrieben:
>
> 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