Re: Yet another "why is my index not used" question
From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Wed, 28 Jan 2009 12:52:00 +0100
Message-ID: <glpdeg$9ad$1_at_reader.motzarella.org>
Jonathan Lewis wrote:
> "Radoulov, Dimitre" <cichomitiko_at_gmail.com> wrote in message
> news:glmodo$b4$2_at_reader.motzarella.org...
>
> 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
Date: Wed, 28 Jan 2009 12:52:00 +0100
Message-ID: <glpdeg$9ad$1_at_reader.motzarella.org>
Jonathan Lewis wrote:
> "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
[...]
> 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.
I believe that this is not a single-statement-issue, so I'll try to tweak optimizer_index_cost_adj and of course, check its impact on the entire application.
Thank you once again for your precious support.
Best regards
Dimitre
Received on Wed Jan 28 2009 - 05:52:00 CST