Re: Has anyone experimented with OPTIMZER_INDEX_COST_ADJ???

Jonathan Lewis wrote:
>
> 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
> >
> >
> >

I've got a gut feel there's a bug in there somewhere - I had a play at a previous site with a (admittedly very complex) query (8.1.6.2) with the various optimiser parameters, and got some parse times over an hour...I also have to concede that the final plan chosen was spot on - the query was instantaneous !

Cheers
Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"