Re: GATHER_SCHEMA_STATS always gathers histograms..!

Mark D Powell wrote:
> 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.
> <<
>

You probably want options => 'GATHER' instead of what you have currently specified. Received on Wed Nov 15 2006 - 09:16:12 CST