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

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 28 Jan 2009 07:38:00 +0100
Message-ID: <497ffd47$0$197$e4fe514c_at_news.xs4all.nl>

Michael Austin schreef:

> 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.
>>>
>>
>>
>> Picking out the most relevant bits:
>>
>> | Id | Operation | Name | Rows | Bytes | Cost |
>> Pstart| Pstop |
>> ------------------------------------------------------------------------------------
>>
>> | 0 | SELECT STATEMENT | | 56082 | 9091K| 5941 |
>> | |
>> |* 1 | TABLE ACCESS FULL | T | 56082 | 9091K| 5941
>> | 18 | 18 |
>>
>>
>> | Id | Operation | Name | Rows |
>> Bytes | Cost | Pstart| Pstop |
>> -------------------------------------------------------------------------------------------------
>>
>> | 0 | SELECT STATEMENT | | 56082 |
>> 9091K| 10787 | | |
>> | 1 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 56082 |
>> 9091K| 10787 | 18 | 18 |
>> |* 2 | INDEX RANGE SCAN | IDX_2 | 56082 |
>> | 327 | 18 | 18 |
>>
>>
>> PARTI NUM_ROWS BLOCKS
>> PDV18 2748000 61760
>>
>> PARTI PCT_FREE BLEVEL LEAF_BLOCKS DISTINCT_KEYS
>> AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS
>> PDV18 10 2 15918 2054 249
>> 512527 2631709
>>
>>
>> It looks like you have db_file_multiblock_read_count set to 16
>> (as the tablescan cost of 5941 is roughly blocks/10.397).
>>
>> 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).
>>
>> 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.
>>
>> 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.
>>
>> 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.
>>
>> 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.
>>
>>
>>
>>
>> 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
>>
>>

> 
> Jonathan,  Based on his WHERE Clause - no amount of fiddling is going to 
>  fix his problem until he gives the optimizer something other than "is 
> not null" to work with- it is still going to do a FTS.

There are no not null clauses in his original query.... it's on top of his post.

Shakespeare Received on Wed Jan 28 2009 - 00:38:00 CST