Re: Yet another "why is my index not used" question
Date: Tue, 27 Jan 2009 09:51:13 -0000
- 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 188.8.131.52 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.
Author: Cost Based Oracle: Fundamentals
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