Hi,
I'm in
the middle of migrating oracle 7.3.4 to oracle
9.2.0.4
In
process of testing we encounter a big query that is now taking full table
scans
Where it
used to take indexes. When we
compare plans this is evidently so,
Optimizer_mode is on
both choose, tables and indexes are analyzed
When I
add a rule hint on 9.2.0.4 plan it takes the
indexes
On 7.3.4
we use for analyzing : analyze table, now I tried
using
exec
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'VRIJ_UIT',CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS');
exec
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'CONTRACTEN',CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS'
);
exec
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'FIN',CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS');
rem for all
indexes columns size 75
exec
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'VRIJ_UIT', CASCADE => FALSE, METHOD_OPT =>
'FOR ALL IND
EXED
COLUMNS size 75');
Etc...
Are
there any known do and don'ts concerning dbms_stats
which might explain this?
Is it
better to stay on analyze table ?
Can I
expect lot's of problems in execute plans when
migrating?
Any
answers, tips and trics are
appreciated.
Details:
HP-UX11.11, Oracle 9.2.0.4
Regards,
Jeroen
