Help with OPTIMIZER

From: Alex Daman <alex_damanakis_at_exe.com.au>
Date: Mon, 27 Sep 1999 17:12:07 +1000
Message-ID: <_MEH3.22331$1E2.172245@ozemail.com.au>

Hello All,

I am fairly new in the ORACLE world and need some input with a problem we are facing.

Oracle Version : 7.3.4
Platform : IBM AIX UNIX 4.3

The Oracle optimizer does not choose the index path that is reported in the query plan unless the SQL statement contains hints. I know this is happening because the statement without the hints takes 2minutes to run as compared to 3 seconds with the hints.
You might say then, "Use Hints!" but I really need to understand what is going on here because I dont want to go and modify every program thats querying the database.
I also do not believe that a RDBMS product like Oracle has an optimizer that behaves this way.

OPTIMIZER_MODE is set to CHOOSE and we run ANALYZE every night.

I have also read somewhere that this can be caused by type casting. I have checked the application and this is not the case.

Can anyone please give me any other reasons for the optimizer to behave this way.

Thanks in advance.

Alex Received on Mon Sep 27 1999 - 02:12:07 CDT