RE: missing link in my 10053 trace

 Why not just change the number 003 to the character value '003' and eliminate the conversion to number?

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 08 2011 - 15:38:40 CDT