Re: Oracle7's (Non)Optimiser !

From article <1993Jul28.120644.112_at_decus.org.nz>, by wilsone_at_decus.org.nz:
> On analysing the tables in our new Oracle7 database, we found previously
> efficient queries were not using indexes and consequently not performing
> acceptably. To date Oracle have not provided any information and we have
> removed the analyse statistics (reverted back to the rule based optimiser)
> while waiting for the remedy.
>
> Does any other Oracle7 user have any similar experiences or have narrowed
> down the occassions where the cost based optimiser has failed to work?

Actually, I have a series of small queries that occur in an application. When I used the RULE based method it took about .5 seconds to execute the queries. But when I ANALYSE the tables it actually took more then a second. So I dropped the analyse statistics and went back to the normal RULE based instead of COST based methods.

My thoughts were that if a query is already optimized for RULE based methods it actually adds some overhead to allow the optimizer to come to the same conclousion. Now this probably dosn't matter on large queries, but in a Transaction Processing multi-user environtment it has a significant effect (especially on smaller CPU- I use a 486-50).

Of course this is just my experience and dosn't relate directly to your case, but I though some people might want to hear it.

P.S. Now if I could only remember/find that initorac.ora peramiter to force RULE based method, maybe I can shim a bit more time off by making it not look for statistics at all... Received on Wed Jul 28 1993 - 17:54:25 CEST