Re: Yet another "why is my index not used" question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
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.html
Received on Wed Jan 28 2009 - 04:05:28 CST

Original text of this message