Re: Help with OPTIMIZER

On Mon, 27 Sep 1999 17:12:07 +1000, "Alex Daman" <alex_damanakis_at_exe.com.au> wrote:

What kind of ANALYZE you are using (ESTIMATE, COMPUTE ...)

We had a similiar effect with ESTIMATE. Based on the ESTIMATE result, the CBO decided NOT to use the index. After changing ESTIMATE to COMPUTE we got the CBO using the index.

Silvio

>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
>
>
>
>
>
>

Silvio Seifert
Foehrer Strasse 34
65199 Wiesbaden
Germany Received on Sun Oct 03 1999 - 07:35:17 CDT