GATHER_TABLE_STATS vs GATHER_SCHEMA_STATS
Joey.D...@gmail.com
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
hirde...@gmail.com
wrote:
.. You can try with creating copy of table and try gather stats and
see.
--Girish
Mark D Powell
wrote:
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 --
Joey.D...@gmail.com
Both on 9.2.0.7 and using the same script to generate stats. No system
stats in use, in either instance.
Mark D Powell
wrote:
>
> - 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?
Joey.D...@gmail.com
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.
joel garry
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
Mark D Powell
>
> 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
>
> - 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.