Re: Help with OPTIMIZER

This might help you:
http://www.oracle.com/oramag/oracle/99-Mar/29datam.html

On Mon, 27 Sep 1999, Alex Daman wrote:

> 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 - 11:33:08 CDT