Why not just change the number 003 to the character value '003' and eliminate the conversion to number? -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Martin Berger Sent: Wednesday, June 08, 2011 4:17 PM To: Oracle-L Freelists Subject: missing link in my 10053 trace Dear List, maybe someone can help me with my interpretation of a 10053 trace file. DB: 11.2.0.2.0 - 64bit I have a small query with a little error, which causes big troubles. The relevant part of the query is WHERE .... AND inst_prod_type=003 AND setid='COM01' but INST_PROD_TYPE is VARCHAR2. this leads to filter[ (TO_NUMBER("INST_PROD_TYPE")=3 AND "SETID"='COM01') ] based on this TO_NUMBER ( I guess!) the optimiser takes a fix selectivity of 1%. ? can someone tell me if this 1% is right? Jonathan Lewis "CBO Fundamentals" on page 133 is only talking about character expressions. ? Unfortunately there are only 2 distinct values of INST_PROD_TYPE - so this artificial selectivity leads to my problem: An INDEX SKIP SCAN on PS0RF_INST_PROD is choosen. (columns of PS0RF_INST_PROD: INST_PROD_TYPE, SETID, INST_PROD_ID ) After fixing the statement to AND inst_prod_type='003' another index is used and the statement performs as expected. Now I have no problem, but want to find the optimizers decisions in my 10053 traces. I guess the relevant parts of the traces are: --- bad plan --- Column (#3): NewDensity:0.028190, OldDensity:0.000000 BktCnt:21373, PopBktCnt:21373, PopValCnt:2, NDV:2 Column (#3): INST_PROD_TYPE( AvgLen: 4 NDV: 2 Nulls: 0 Density: 0.028190 Histogram: Freq #Bkts: 2 UncompBkts: 21373 EndPtVals: 2 ... Using prorated density: 0.000000 of col #3 as selectvity of out-of-range/non-existent value pred Access Path: index (skip-scan) SS sel: 0.000000 ANDV (#skips): 2.000000 SS io: 2.000000 vs. table scan io: 148005.000000 Skip Scan chosen Access Path: index (SkipScan) Index: PS0RF_INST_PROD resc_io: 6.00 resc_cpu: 44843 ix_sel: 0.000000 ix_sel_with_filters: 0.000000 ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 150.00 ***** End Logdef Adjustment ****** Cost: 6.00 Resp: 6.00 Degree: 1 --- /bad plan --- and --- good plan --- ColGroup Usage:: PredCnt: 2 Matches Full: Partial: Access Path: index (RangeScan) Index: PS0RF_INST_PROD resc_io: 485525.00 resc_cpu: 5911063840 ix_sel: 0.056355 ix_sel_with_filters: 0.056355 Cost: 485651.20 Resp: 485651.20 Degree: 1 Access Path: index (skip-scan) SS sel: 0.999977 ANDV (#skips): 21111599.103659 SS io: 196288.000000 vs. table scan io: 148005.000000 Skip Scan rejected --- /good plan --- But I did not find any trace from the TO_NUMBER to "Using prorated density" (and so "cst 0.00"). Maybe someone can help me find the little steps the CBO did in between. I think I _know_ what is going on, but I'd like to prove! If needed I can provide all traces, but does not want to spam this list. thank you all for reading, Martin -- Martin Berger -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l