Re: GATHER_TABLE_STATS vs GATHER_SCHEMA_STATS

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 --
>
> Both on 9.2.0.7 and using the same script to generate stats. No system
> stats in use, in either instance.- Hide quoted text -
>
> - Show quoted text -

Even if both databases are on 9.2.0.7 did you check the orapatch statistics for path application, including CPU patches?

When all else is the same then it looks like it is the data. Is it possible to transport the data from one system to the other, update the stats, and rerun your test?

HTH -- Mark D Powell -- Received on Thu Feb 28 2008 - 10:31:28 CST