Re: Stats on Huge Partitioned Table and ORA-01555

  • From: Michael Cunningham <napacunningham@xxxxxxxxx>
  • To: Stefan Koehler <contact@xxxxxxxx>
  • Date: Tue, 26 May 2015 11:52:35 -0700

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.


On Tue, May 26, 2015 at 11:44 AM, Stefan Koehler <contact@xxxxxxxx> 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: @OracleSK


Michael Cunningham <napacunningham@xxxxxxxxx> 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

Other related posts:

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