analyze problems

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jeroen van Sluisdam
  INET: jeroen.van.sluisdam_at_vrijuit.nl

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).