Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Has anyone experimented with OPTIMZER_INDEX_COST_ADJ???

Re: Has anyone experimented with OPTIMZER_INDEX_COST_ADJ???

From: Hans Henrik Krohn <hhk_at_tips.dk>
Date: Thu, 31 May 2001 09:46:17 +0200
Message-ID: <9f4src$dga$1@news.inet.tele.dk>

Try setting

   optimizer_max_permutations = 1000
in stead of the default 80000. Can be done at session level to test out whether this has any ill effects. My production system has been running with this in init.ora these last six last months.

Regards, Hans Henrik Krohn

"David Sisk" <davesisk_at_ipass.net> skrev i en meddelelse news:oagR6.61462$i56.19595649_at_typhoon.southeast.rr.com...
> 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
>
>
>
Received on Thu May 31 2001 - 02:46:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US