Re: Has anyone experimented with OPTIMZER_INDEX_COST_ADJ???

David,

The __scale__ of the change is a bit surprising, but the fact that the optimiser should do more work is not.

When you set 'OPTIMIZER_INDEX_COST_ADJ= {low value}' you are scaling down the cost of a nested loop join quite dramatically.

As a side effect of this, you are probably increasing the number of permutations of access paths considered, and the depth to which Oracle pursues each one.

To get such an extreme change in the cost of optimisation, though, I would guess that you had quite a large number of indexes on the tables involved in the query, and several tables.

A quick check of this hypothesis would be to run a query with trace 10053 set to get the CBO calculations dumped. You will probably find that one dump is much larger than the other and lists far more possible paths.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



David Sisk wrote in message ...


>I have a particular query which uses a function in the WHERE clause, and

>I've created a function-based index for it (Oracle8.1.7).  With the

>OPTIMIZER_GOAL = CHOOSE or ALL_ROWS, it doesn't use the index, but with it

>set to FIRST_ROWS, the optimizer does choose the function-based index.

 This


>is what we want because the particular query executes about 2500 times

>faster when using the function-based index (0.002 seconds vs. 6 seconds).

>

>I tried leaving OPTIMIZER_GOAL=CHOOSE and setting OPTIMIZER_INDEX_COST_ADJ=

>some low value, and the optimizer chose the same execution plan, but it

 took


>several seconds to do so.  I'm just wondering if anyone else has noticed

>this unusual (or it seems so to me) behavior.

>

>Please post or email!

>

>Regards,

>Dave

>

>

>