missing link in my 10053 trace
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Jun 2011 22:17:25 +0200
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
Other related posts:
