Re: GATHER_TABLE_STATS vs GATHER_SCHEMA_STATS

On Feb 28, 11:31 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Feb 28, 11:19 am, "Joey.Dant..._at_gmail.com" <Joey.Dant..._at_gmail.com>
> wrote:
>
>
>
> > On Feb 28, 10:31 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
> > > On Feb 28, 9:14 am, "Joey.Dant..._at_gmail.com" <Joey.Dant..._at_gmail.com>
> > > wrote:
>
> > > > To all,
>
> > > > Have tar on this open, but support has been slow with updates.
>
> > > > 9.2.0.7 EE Sun 5.9 64 Bit.
>
> > > > Anyway have this table, query is:
>
> > > > SELECT record_key, t_id, '31-DEC-2099', 0
> > > > FROM target_tab
> > > > WHERE current_row = 'X'
>
> > > > There is a bitmap index on current_row and it has two distinct values.
> > > > For some reason (only in our prod) environment,
> > > > DBMS_STATS.GATHER_SCHEMA_STATS (method_opt=> all indexed columns size
> > > > 254) is generating the wrong data on that column. A quick check of
> > > > dba_histograms shows an endpoint number of 0 and 1 after
> > > > gather_Schema_stats. It does (properly according to 10053 trace and
> > > > its stats) a full table scan.
>
> > > > However, gather_table_Stats generates proper date (endpoints being
> > > > 24035 and 5952162) and after it is run the query uses the index.
>
> > > > I'm leaning towards this being a bug, but was wondering if anyone else
> > > > had seen this behavior from DBMS_STATS before?
>
> > > > Thanks
>
> > > Have you compared the database parameters including underbar parameter
> > > settings to make sure there are no parameters set differently?
>
> > > Are the patch levels 100% identical. Every dot patch potentially
> > > affects the CBO.
>
> > > Lastly are you sure the sample size and sample method being used are
> > > the same?
>
> > > If auto sample size is being used a difference in the data
> > > distribution could result in a different sample size which in turn
> > > could be resulting in significantly different statistics.
>
> > > Also check if system statistics are in use as these will modify the
> > > cost calculations.
>
> > > HTH -- Mark D Powell --

CPU patches are the same, and the data is relatively in sync. The QA copy is about two weeks old and is within 10% of the prod data. The skew of the record counts is also in line. I'm thinking it's a bug, but I was curious as to if anyone knew of specific difference in behavior between GATHER_SCHEMA and GATHER_TABLE. Received on Thu Feb 28 2008 - 10:43:11 CST