Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Calculating IX_SEL in 10053.

Re: Calculating IX_SEL in 10053.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 5 Jan 2007 13:14:18 -0000
Message-ID: <jKidnfUrmpGs0gPYnZ2dnUVZ8qaqnZ2d@bt.com>

<mccmx_at_hotmail.com> wrote in message
news:1167999121.358583.77300_at_42g2000cwt.googlegroups.com...
> Oracle 9.2.0.6 on Windows.
>
> I'm having trouble interpreting the SINGLE TABLE ACCESS PATH section of
> a simple 10053 trace file.
>
> According to Jonathan Lewis' CBO book (Chapter 14, Page 414), the
> selectivity values are calculated only from filter conditions not join
> conditions.
>
> But I am having trouble determining how the optimizer has derived the
> IX_SEL, and TB_SEL values below.
>
> Query:
>
> explain plan for
> UPDATE PS_TY_MF_TIM_EMP EMP
> SET TY_YTD_TL_QTY_3 = nvl
> (
> (
> SELECT SUM( PAY.TL_QUANTITY ) FROM PS_TL_PAYABLE_TIME PAY
> WHERE
> EMP.EMPLID = PAY.EMPLID
> AND EMP.EMPL_RCD = PAY.EMPL_RCD
> AND PAY.DUR >= TRUNC(TO_DATE('27-NOV-06','DD-MON-YY'),'Y')
> AND PAY.DUR <= TO_DATE('24-DEC-06','DD-MON-YY')
> AND PAY.TRC = 'C169'
> ), 0
> )
> WHERE EMP.PROCESS_INSTANCE = 1145447;
>
> 10053 Trace:
>
> /* Snipped.....
>
> SINGLE TABLE ACCESS PATH
> Column: EMPLID Col#: 1 Table: PS_TL_PAYABLE_TIME Alias: PAY
> NDV: 15660 NULLS: 0 DENS: 6.3857e-005
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: EMPL_RCD Col#: 2 Table: PS_TL_PAYABLE_TIME Alias: PAY
> NDV: 1 NULLS: 0 DENS: 1.0000e+000 LO: 0 HI: 0
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: TRC Col#: 7 Table: PS_TL_PAYABLE_TIME Alias: PAY
> NDV: 218 NULLS: 0 DENS: 4.5872e-003
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: DUR Col#: 3 Table: PS_TL_PAYABLE_TIME Alias: PAY
> NDV: 1409 NULLS: 0 DENS: 7.0972e-004 LO: 2453006 HI:
> 2454414
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> TABLE: PS_TL_PAYABLE_TIME ORIG CDN: 25408251 ROUNDED CDN: 1
> CMPTD CDN: 0
> Access path: tsc Resc: 106979 Resp: 106979
> Access path: index (scan)
> Index: PSCTL_PAYABLE_TIME
> TABLE: PS_TL_PAYABLE_TIME
> RSC_CPU: 0 RSC_IO: 5
> IX_SEL: 1.4646e-008 TB_SEL: 1.4646e-008
> Access path: index (scan)
> Index: PSDTL_PAYABLE_TIME
> TABLE: PS_TL_PAYABLE_TIME
> RSC_CPU: 0 RSC_IO: 9822
> IX_SEL: 5.0000e-002 TB_SEL: 2.2936e-004
> Access path: index (scan)
> Index: PS_TL_PAYABLE_TIME
> TABLE: PS_TL_PAYABLE_TIME
> RSC_CPU: 0 RSC_IO: 26
> IX_SEL: 3.1928e-006 TB_SEL: 3.1928e-006
> BEST_CST: 5.00 PATH: 4 Degree: 1
>
> ....Snipped */
>
> PSCTL_PAYABLE_TIME is an index on (EMPLID,TRC,DUR).
>
> Since the EMPLID is not a filter condition (only a join condition) I
> would expect the IX_SEL to be 1 because the leading column isn't
> considered, and the TB_SEL to be a product of the selectivity of the 2
> columns which are used as filter conditions on PS_TL_PAYABLE_TIME:
>
> 4.5872e-003 * 7.0972e-004 = 0.000003255 (3.255e-006).
>
> The numbers don't add up - anyone know where my understanding is wrong
> here....
>
> Thanks for you help...
>
> Matt
>

Matt,

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.

Your exclusion of the join column is (probably) incorrect. I suspect you are looking at a query block optimisation which (at this point) has expressed the subquery as:

> SELECT SUM( PAY.TL_QUANTITY ) FROM PS_TL_PAYABLE_TIME PAY
> WHERE
> EMP.EMPLID = :b1
> AND EMP.EMPL_RCD = :b2
> AND PAY.DUR >= TRUNC(TO_DATE('27-NOV-06','DD-MON-YY'),'Y')
> AND PAY.DUR <= TO_DATE('24-DEC-06','DD-MON-YY')
> AND PAY.TRC = 'C169'
Which is why there will be a factor from all columns in the index, and IX_SEL = TB_SEL.

Nevertheless, throwing the extra selectivity into the mix, I now get a selectivity that is too small by a factor of about 2.5

    1/15990 * 1/218 * 29/1409

This may be because of an index sanity check that kicks in to stop the selectivity falling below 1/distinct_keys for the index.

-- 
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 - 07:14:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US