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: 10053 Trace File anomolies

Re: 10053 Trace File anomolies

From: Antoine <a310860_at_yahoo.fr>
Date: Thu, 30 Jun 2005 11:31:43 +0200
Message-ID: <42C3BBFF.3030303@yahoo.fr>


mccmx_at_hotmail.com wrote:
> The optimizer seems to choose the FTS over the Index Skip Scan despite
> the fact that the latter had a much lower cost of 831 (and the FTS cost
> was 2297):
>
> BEST_CST: 2297.00 PATH: 2 Degree: 1
>
>
> SINGLE TABLE ACCESS PATH
> Column: DUR Col#: 2 Table: PS_TY_SCH_CLND_TBL Alias:
> SYS_ALIAS_14
> NDV: 7686 NULLS: 0 DENS: 1.3011e-004 LO: 2447893 HI:
> 2455578
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> TABLE: PS_TY_SCH_CLND_TBL ORIG CDN: 1812808 ROUNDED CDN: 1887
> CMPTD CDN: 1887
> Access path: tsc Resc: 2297 Resp: 2297
> Skip scan: ss-sel 0 andv 831
> ss cost 831
> table io scan cost 2297
> Skip scan chosen
> Access path: index (no sta/stp keys)
> Index: PS0TY_SCH_CLND_TBL
> TABLE: PS_TY_SCH_CLND_TBL
> RSC_CPU: 0 RSC_IO: 14936
> IX_SEL: 2.7592e-004 TB_SEL: 1.9508e-001
> Skip scan: ss-sel 0 andv 831
> ss cost 831
> table io scan cost 2297
> Skip scan chosen

Well,
your snippet of the trace file show that CBO is taking the skip scan access method.

Is this the output from the "wrong plan"?

rgds
> Access path: index (no sta/stp keys)
> Index: PS_TY_SCH_CLND_TBL
> TABLE: PS_TY_SCH_CLND_TBL
> RSC_CPU: 0 RSC_IO: 4051
> IX_SEL: 2.7592e-004 TB_SEL: 1.9508e-001
> Access path: index (no sta/stp keys)
> Index: PS0TY_SCH_CLND_TBL
> TABLE: PS_TY_SCH_CLND_TBL
> RSC_CPU: 0 RSC_IO: 8932
> IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
> Access path: index (no sta/stp keys)
> Index: PS_TY_SCH_CLND_TBL
> TABLE: PS_TY_SCH_CLND_TBL
> RSC_CPU: 0 RSC_IO: 6980
> IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
> BEST_CST: 2297.00 PATH: 2 Degree: 1
>


Received on Thu Jun 30 2005 - 04:31:43 CDT

Original text of this message

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