Re: GATHER_TABLE_STATS vs GATHER_SCHEMA_STATS

On Feb 28, 8:43 am, "Joey.Dant...@xxxxxxxxx" <Joey.Dant...@xxxxxxxxx>
wrote:
On Feb 28, 11:31 am, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:



On Feb 28, 11:19 am, "Joey.Dant...@xxxxxxxxx" <Joey.Dant...@xxxxxxxxx>
wrote:

On Feb 28, 10:31 am, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:

On Feb 28, 9:14 am, "Joey.Dant...@xxxxxxxxx" <Joey.Dant...@xxxxxxxxx>
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 see http://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
.

Relevant Pages