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

Jonathan Lewis wrote:
> ----- Original Message -----
> From: "Radoulov, Dimitre" <cichomitiko_at_gmail.com>
> Newsgroups: comp.databases.oracle.server
> Sent: Monday, January 26, 2009 8:18 PM
> Subject: Yet another "why is my index not used" question
>
>

>> Hi all,
>>
>> our environment: 9i EE 9.2.0.4 64bit, Solaris 8
>>
>> We're trying to understand why the CBO picks suboptimal
>> single partition scan, when there is a more efficient
>> index range scan available.
>>

Yes,
db_file_multiblock_read_count is set to 16.

> The estimated cardinality 56082 (with no filter predicates
> after the indexed access to the table) gives us a selectivity
> of 56082/274800 which is about 2%.
>
> 2% of the clustering factor is about 10400 and 2% of the leaf block
> count is about 320.
>
> The access path is the optimizer deciding that getting 10,400 table
> blocks one at a time will be slower than getting all 62,000 of them
> 10 at a time. (Because it has no information that a 10-block read
> will take longer than a 1 block read).

OK, I overlooked a quite important point: the fact that the CBO will consider 62,000/10 (adjusted mbrc?).
As far as the sreadtim vs mreadtim calculation are concerned, I've already got system stats in a custom stat table, I'm reporting the results for completeness (I'll need to understand why sreadtim > mreadtim and what I am missing again):

status : COMPLETED
cpu in mhz : 516
single block readtime in ms : 7.922
multiblock readtime in ms : 4.157
average multiblock readcount: 6

> Why did you use 5 as the "history" figure when generating the clustering
> factor for the partition ? You have freelists 1 and freelist groups 1 on
> the table.

Just because it's the example in your book :)

Just joking, it's because when I realized that freelists/freelists groups were set to 1, I wanted to check the clustered factor with a value greater than the default and similar to the actual access concurrency degree
(I'm not sure if it's still the case with freelists set to 1).

> There are three ways to address the issue -
>
> adjust the clustering_factor (again), If we assume the
> 1,599 physical reads in your autotrace run with the index
> were not artificially low because of previous data access
> that has buffered a lot of blocks from the table, then a
> value of 80,000 (= 1,599 * 50) would seem to be
> appropriate.

I'll investigate further. The problem is what to do if there are many cases like this one, find them all and adjust the clustering factor for every table would be difficult to implement.

> 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.

> fiddle with the System Statistics - so that you get some
> numbers that tell Oracle what is really likely to happen
> when you do a tablescan, and how much slower big
> reads are than little reads when there is no caching
> going on. Any you almost certainly won't want to do
> this because of global side effects.

I'll investigate further.

As always,
thank you very much for your (more than) valuable input.

Best regards
Dimitre Received on Tue Jan 27 2009 - 04:42:00 CST