Re: GATHER_SCHEMA_STATS always gathers histograms..!

On Nov 15, 7:30 am, m..._at_hotmail.com wrote:
> 10.2.0.2 SE on W2K3.
>
> When I run:
>
> dbms_stats.gather_schema_stats('SYSADM',cascade=>TRUE,options=>'GATHER
> AUTO', method_opt=>'FOR ALL COLUMNS SIZE 1',
> estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>2);
>
> histograms are generated for many columns in the SYSADM schema, see
> below:
>
> SQL> select count(*) from user_tab_columns;
>
> COUNT(*)
> ----------
> 483995
>
> Elapsed: 00:00:17.07
> SQL> select count(*) from user_tab_columns where histogram <> 'NONE';
>
> COUNT(*)
> ----------
> 52823
>
> My understanding is that FOR ALL COLUMNS SIZE 1 should explicitly cause
> Oracle to not gather any histograms.....
>
> Chapter 2 in J Lewis's recent book seems to confirm this too....
>
> Can anyone confirm whether they are seeing the same behaviour. I've
> got a feeling this is a bug.
>
> Cheers
>
> Matt

Perhaps the following is of interest >>
GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.
<<

HTH -- Mark D Powell -- Received on Wed Nov 15 2006 - 09:03:24 CST