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


Other related posts: