Re: GATHER_TABLE_STATS vs GATHER_SCHEMA_STATS

On Feb 28, 2:19 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Feb 28, 8:43 am, "Joey.Dant..._at_gmail.com" <Joey.Dant..._at_gmail.com>
> wrote:
>
>
>
>
>
> > 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
>
> You may be skating right up to a bug on one machine and not on the
> other.  Can't really tell if it applies to you or not, but see
> metalink bugs 5944076 and 5354444.
>
> Also seehttp://www.freelists.org/archives/oracle-l/08-2005/msg00564.html
>
> Of course, "relatively in sync" may be meaningless if you are near a
> transitional boundary.
>
> jg
> --
> @home.com is bogus.
> "The most technologically advanced border security initiative in
> American history."  http://www.signonsandiego.com/uniontrib/20080228/news_1n28fence.html- Hide quoted text -
>
> - Show quoted text -

Then there is the issue of bind variable peeking which can result in two totally different plans being used on two systems for identical data because of the first query parsed on each system. In this case the actual plan and the explain plan for the query will likely be different. Have the explain plans been verified against the v $sql_plan since histograms appear to being generated for this table?

Joel's comment on a boundry line value is also important though this is a hard situation for most of us to identify.

HTH -- Mark D Powell -- Received on Fri Feb 29 2008 - 11:47:37 CST