Re: GATHER_TABLE_STATS vs GATHER_SCHEMA_STATS

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 --

Both on 9.2.0.7 and using the same script to generate stats. No system
stats in use, in either instance.
.

Relevant Pages

  • Re: GATHER_TABLE_STATS vs GATHER_SCHEMA_STATS
    ... For some reason (only in our prod) environment, ... could be resulting in significantly different statistics. ... Both on 9.2.0.7 and using the same script to generate stats. ...
    (comp.databases.oracle.server)
  • Re: SQL Tuning Regarding System CPU Stats
    ... Since the system statistics are used per database ... tuning a production system within a development environment? ... known book, published by O'Reilly few years ago, named "Optimizing Oracle ... CPU stats that will then be used to generate SQL statements, ...
    (comp.databases.oracle.server)
  • Re: OT: Umra posting statistics for February 2008
    ... because I'm now back to "update the sheet as soon as ... I see the stats on the group " mode. ... and download now if you wish: ...
    (uk.media.radio.archers)
  • Stats gathering (8i) to non-data dictionary tables
    ... I have a requirement to gather stats in prod without ... Instead I'd like them to go into a user stattab from where I can move ... stats go to the stattab instead of a backup of the previous values. ... Prev by Date: ...
    (comp.databases.oracle.server)