Re: Yet another "why is my index not used" question
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Tue, 27 Jan 2009 15:01:58 -0600
Message-ID: <lBKfl.16314$c45.1300_at_nlpi065.nbdc.sbc.com>
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
>
>
>
>
> 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
>
>
Date: Tue, 27 Jan 2009 15:01:58 -0600
Message-ID: <lBKfl.16314$c45.1300_at_nlpi065.nbdc.sbc.com>
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. Received on Tue Jan 27 2009 - 15:01:58 CST