Re: Calculating IX_SEL in 10053.

<mccmx_at_hotmail.com> wrote in message
news:1168004089.489629.301050_at_i15g2000cwa.googlegroups.com...
> >
>> It's always worth checking the full execution
>> plan - possibly hinted to make Oracle take a
>> path that has been rejected - so that you can
>> see what access and filter predicates Oracle has
>> produced from your inputs.
>>
>
> Thanks for the feedback.
>
> Here is the full explain plan - unhinted....
>
> --------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes | Cost |
> --------------------------------------------------------------------------------------
> | 0 | UPDATE STATEMENT | | 1 |
> 20 | 3 |
> | 1 | UPDATE | PS_TY_MF_TIM_EMP | |
> | |
> |* 2 | INDEX RANGE SCAN | PS_TY_MF_TIM_EMP | 1 |
> 20 | 2 |
> | 3 | SORT AGGREGATE | | 1 |
> 27 | |
> |* 4 | FILTER | | |
> | |
> |* 5 | TABLE ACCESS BY INDEX ROWID| PS_TL_PAYABLE_TIME | 1 |
> 27 | 5 |
> |* 6 | INDEX RANGE SCAN | PSCTL_PAYABLE_TIME | 1 |
> | 4 |
> --------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - access("SYS_ALIAS_2"."PROCESS_INSTANCE"=1145447)
> 4 -
> filter(TRUNC(TO_DATE('27-NOV-06','DD-MON-YY'),'fmy')<=TO_DATE('24-DEC-06','DD-M
> ON-YY'))
> 5 - filter("PAY"."EMPL_RCD"=:B1)
> 6 - access("PAY"."EMPLID"=:B1 AND "PAY"."TRC"='C169' AND
>
> "PAY"."DUR">=TRUNC(TO_DATE('27-NOV-06','DD-MON-YY'),'fmy') AND
> "PAY"."DUR"<=TO_DATE('24-DEC-06','DD-MON-YY'))
>
> It looks like it is using EMPLID, TRC, and DUR. But like you say, even
> with the extra selectivity added, the overall selectivity is way out.
>
>> This may be because of an index sanity check that kicks in
>> to stop the selectivity falling below 1/distinct_keys for the
>> index.
>
> I wasn't aware of this check....Its difficult to keep up with beast
> that is the CBO..!
>
> Is there any way I can work out how it arrives at the IX_SEL and TB_SEL
> values. Because it is obviously applying some calculations which are
> different to the formula that you include in your book.
>
> Matt
>

There are some updates on my website

    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html#Addenda that might help.

How does the calculated value compare with the index ? Another detail to check is if you have other indexes with two out of three columns of the first index - Oracle may be using the 1/num_distinct for those instead of multiplying two separate values (that's a guess only at this point).

Another thought - check the notes (from Alberto Dell'Era, indicated on my website) about how Oracle adjusts the num_distinct on joins . Possibly the calculation here is out by a factor of three because the optimizer has used an adjusted num_distinct based on the num_distinct of the driving table.

If you want to track down what's going on, try creating the same structure of tables and indexes, but with simple number of distinct values - then see what happens as you vary the simple numbers (possibly cheating with dbms_stats to set column stats). That's what I do in the (luckily few) critical cases.

Just one odd extra thought -

    If you create a simple query which is just     the select statement, with constants, do     the selectivity figures stay the same, or     do they match my original formulae ?

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 Fri Jan 05 2007 - 13:55:49 CST