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

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

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

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 Received on Tue Jan 27 2009 - 03:51:13 CST