Re: Stats on Huge Partitioned Table and ORA-01555

  • From: Cherif Ben Henda <cherif.benhenda@xxxxxxxxx>
  • To: contact@xxxxxxxx
  • Date: Wed, 27 May 2015 10:33:16 +0100

Hi Michael,

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@xxxxxxxx>:

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: @OracleSK

Michael Cunningham <napacunningham@xxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l





--
Cordialement,
Cherif Ben Henda

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2015