Re: Yet another "why is my index not used" question
Date: Wed, 28 Jan 2009 10:05:28 -0000
Message-ID: <kPydneBmk8R8sB3UnZ2dnUVZ8vSdnZ2d_at_bt.com>
"Radoulov, Dimitre" <cichomitiko_at_gmail.com> wrote in message
news:glmodo$b4$2_at_reader.motzarella.org...
>
> status : COMPLETED
> cpu in mhz : 516
> single block readtime in ms : 7.922
> multiblock readtime in ms : 4.157
> average multiblock readcount: 6
>
In your version of Oracle, the optimizer would ignore these
stats because mreadtim < sreadtim. You could fake some
stats (dbms_stats.set_system_stats()) - e.g.
sreadtim = 12
MBRC = 16
mreadtim = 42
This is based on the defaults for ioseektim and iotfrspeed that 10g would apply to your setting of db_file_multiblock_read_count.
This would have roughly the equivalent effect to you setting the optimizer_index_cost_adj to 100 * 12 / 42 = 29
>
>
>> fiddle about with the optimizer_index_cost_adj for the
>> duration of this query - a value of 40 is probably quite
>> appropriate. But you may not want to do this because
>> of side effects.
>
>
> I've already played with it,
> it needs an optimizer_index_cost_adj of 50.
> But then again, I need to consider the global impact.
>
In this example, any value which is a little less than your 100 * (cost of indexed path) / (cost of t/s path) would be sufficient to change the path. The trick is to pick a value with is reasonably truthful for the "system".
The first line of attack in cases like this, though is always to consider the truthfulness of the clustering_factor.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Wed Jan 28 2009 - 04:05:28 CST