Re: Stats on Huge Partitioned Table and ORA-01555

Why having "Table has 7.1 billion+ rows, 503 partitions, 3TB" ? I think you can use historical table ( copy of your current table ) to save no needed data, and the current table contains only need data for daily processing, reporting...- for example data for the last year ...

Otherwise ,You can have several performances issues, because if the gathering stats job fails to update stats on others table with 10% change on time (night window), we will finish with tables with no up to date  statistics.

As said by Stefan, you can think to SET_TABLE_PREFS.

Thanks,
Cherif

2015-05-27 8:17 GMT+01:00 Stefan Koehler <contact_at_soocs.de>:

> Hi Michael,
>
> > 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.
>
> The mentioned settings are not necesarily part of the job definition. It
> is a global, object or schema definition. Just quoting from the official
> documentation "You can change the default values of some of the parameters
> of DBMS_STATS procedures using the SET_DATABASE_PREFS Procedure,
> SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS
> Procedure." - http://docs.oracle.com/database/121/ARPLS/d_stats.htm
>
> 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:52 geschrieben:
> >
> > Thanks Stefan,
> >
> > 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.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Cordialement,
Cherif Ben Henda

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