Re: Stats on Huge Partitioned Table and ORA-01555

The stats being gathered the default from the dbms_stats.gather_database_stats_job_proc and we have not made any customizations. I will assume that means it is gathering histograms. I don't know what incremental statistics means so I will look that up after I send this email. We are not using parallel and that would be due to the default behavior of dbms_stats.gather_database_ stats_job_proc.

The stats job has been turned off and there is a cron job that executes the dbms_stats.gather_database_stats_job_proc procedure.

On Tue, May 26, 2015 at 11:44 AM, Stefan Koehler <contact_at_soocs.de> wrote:

> Hi Michael,
>
> > 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
>

-- 
Michael Cunningham

--
http://www.freelists.org/webpage/oracle-l