Re: gather stats

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 9 Nov 2015 18:20:41 -0500

Hi Jonathan, my comments are in-line

On 11/09/2015 01:54 PM, Jonathan Lewis wrote:

Bear in mind that even though auto_sample_size means 100% approximate_ndv for
simple stats Oracle will still sample for histograms; so many of your
histograms could be built on a sample of about 5,500 rows; with some built on
larger samples.
If there is a table for which such method produces undesirable SQL execution plans, it is possible to set table preferences on per table basis. This is, as a matter of fact, what I usually recommend my clients. Sample size can be set in table level preferences.


I think "for all hidden columns size 254" might be implied by "for all columns size
254", but the way - that might only be true for relative new versions of Oracle, of course.

Hmmm, I tested it in 11.2.0.2 and did not get histograms on the virtual column for the function based index without it. I haven't tested it in a while, though. I've been using "FOR ALL HIDDEN COLUMNS" just automatically, cut and paste from my collection of scripts. I will have to re-test this.



--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

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


Other related posts:

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