Re: analyze problems

[Tanel Poder]

Has you db_file_multiblock_read_count changed due upgrade? What are your optimizer_index_cost_adj and optimizer_index_caching parameter values? If they're default you should perhaps change them according to Tim Gorman's CBO article (or in 9i, gather system stats instead)   Tanel.   ----- Original Message ----- From: Jeroen van Sluisdam To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 10, 2003 11:29 PM Subject: 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