Hi Michael,
Wanted to share something we use frequently in large environments - If the
statistical distribution of the data in your partitions is reasonably the same,
and your goal is to have the same plans for your workload across all
partitions, you have the option of "exporting the statistics" from an old
partition and "import it into the new partition". You can do this ahead of time
for all partitioning mechanisms other than INTERVAL (which can be done after
the said partition is created). This obviates the need to calculate statistics
for the ENTIRE table and deals with the statistics at the partition-level. Food
for thought!
Cheers,
Gaja
Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com
Phone - +1 (650) 743-6060
LinkedIn - http://www.linkedin.com/in/gvaidyan
Co-author: Oracle Insights:Tales of the Oak Table -
http://www.apress.com/9781590593875
Primary Author: Oracle Performance Tuning 101 - http://www.amzn.com/0072131454
Enabling Exadata, Big Data and Cloud Deployment & Management for Oracle
From: Michael Cunningham <napacunningham@xxxxxxxxx>
To: Stefan Koehler <contact@xxxxxxxx>
Cc: "oracle-l@freelists org" <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, May 26, 2015 11:52 AM
Subject: Re: Stats on Huge Partitioned Table and ORA-01555
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
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.
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
