Re: URGENT - query problem

Arturo wrote:
> Hi all,
>
> I'm having a problem running a query. Problem is that in test environment
> the query is optimized perfectly (I'm using CHOOSE option), it uses all
> indexes and it's quick.
>
> In production the same query doesn't use indexes at all. All indexes exist
> like in test, are valid and are on the same tables/columns. I'm using the
> same optimizer hint.
>
> I've already tried putting all indexes I want as the optimizer hint, it
> didn't help.
>
> Any ideas? Thanks in advance,
> Arturo
>
> PS: Oracle Server 7.3.4.0.1.
>

First of all, hints are just that - a hint. The Optimizer can choose to ignore you if it thinks it has a better plan than the one you hinted at. That said, our developers used to ask why their tuned SQL (8.1.7) on the development db acted differently on the production db. I had to explain that statistics were different on the two databases ... and hence, the CBO made different choices.

Of course, in our situation, it was the exact reverse of your situation. The CBO chose full table scans rather than index scans because we have rather small-ish tables on the development db (and CBO correctly assumed they would all fit within our designated buffer size).

However, the point is, you have to make sure you feed the same statistics to CBO if you want it to choose the same plan. And you should also be aware that the 7.3.4 CBO wasn't as clever as it now is. :) Received on Mon Oct 07 2002 - 23:09:23 CEST