10053 Trace File anomolies

Oracle 9.2.0.6 EE on Win2K Adv Server SP3

I have an SQL statement which runs in 2/3 seconds in one database and over 30 minutes in another environment.

I have narrowed the problem down to the optimizer choosing 2 totally different plans between these 2 databases.

It appears that the slow database is not selecting to use an index on a 1.8 million row table despite the fact that the 2 columns it contains are used in the where condition. If I force it to use this index via a hint, the plan and response time matches the fast database.

In addition to this, the fast database only considers 25 possible join orders whereas the slow database tries over 195 combinations of join order.

The 10053 trace files show no differences in optimizer parameters in the header of the file and they are both running the exact same Oracle version/patch. The row counts in each table are very close....

Why would one database consider so many more join combinations than the other with the same optimizer parameters and row counts..?

Thanks in advance

Matt Received on Mon Jun 27 2005 - 05:23:41 CDT