RE: Stats on Huge Partitioned Table and ORA-01555

From: Herring, David <HerringD_at_DNB.com>
Date: Sat, 27 Jun 2015 10:30:14 -0500
Message-ID: <AD8FE6616C097545A4C9A8B0792909AC58E30DD1E2_at_DNBEXCH01.dnbint.net>

Amen!! On one of our dbs I've had arguments about this (like Spinal Tap and going up to 11). They have a bunch of tables that are constantly getting updated. They want stats gathered every night in case something changes. But they also want THE EXACT SAME XPLANs.

(apps) We need stats run so our xplans are optimal.
(DBA) But new stats could cause an xplan to change. A better xplan may be chosen because of new stats but (this is 10.2.0.2) because of invalidation due to stats you'll introduce a lot of bind variable peeking issues. Are you sure you want stats gathered every night?
(apps) We want the same xplans all the time but we need to make sure stats are gathered nightly so we can pick up any optimal xplans.
(DBA) But those optimal xplans could be different than your current ones.
(apps) We want optimal xplans, which are the same ones that are always use.
(DBA) So you want the exact same xplan every time but at the same time you want stats gathered every night which could changes those xplans.
(apps) If that's the case can you create profiles or write code to invalidate xplans we don't want?
(DBA) You're paying me, right?

Regards,

Dave

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Connor McDonald Sent: Wednesday, May 27, 2015 8:55 PM
Cc: oracle-l_at_freelists org
Subject: Re: Stats on Huge Partitioned Table and ORA-01555

The best way to gather stats is to not to gather them :-)

Now - I'm *not* saying that you dont *have* stats, I'm saying that (especially for OLTP systems) you often dont need to *gather* them...

Your email already suggests you know the volumes of the data, and with a little analysis, I'm sure you can work out the distributions of the columns etc...Armed with that, you can simply use dbms_stats.set_table_stats to prime the statistics.

I'm always amazed at the number of places that do "gather" jobs for hours to work out how the number of distinct sales departments they have, or number of different genders in the person table etc.... when a 10second look at the organisation chart would do just as nicely :-)

Cheers,
Connor

On Wed, May 27, 2015 at 2:28 AM, Michael Cunningham <napacunningham_at_gmail.com> wrote: 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

--
Connor McDonald

---

blog:   connormcdonald.wordpress.com
web:   http://www.oracledba.co.uk
"If you are not living on the edge, you are taking up too much room."  - Jayne Howard

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Sat Jun 27 2015 - 17:30:14 CEST