Cant follow a cardinality estimate of CBO
- From: Martin Klier <usn@xxxxxxxxx>
- To: "Oracle-L@xxxxxxxxxxxxx" <Oracle-L@xxxxxxxxxxxxx>
- Date: Mon, 24 Feb 2014 14:07:21 +0100
Hi Listers,
I have an issue with an execution plan (or with my brain, whatever
direction you prefer :) ).
The plan always snaps to a table access full, because the cardinality
estimate for the predicates give back 854. This MAY be true, but I
didn't manage to understand the 10053 access path analysis for this
table enough to follow where the cardinality comes from.
"Cost: 32.31 Degree: 1 Resp: 32.31 Card: 853.76 Bytes: 0"
I will paste the full path analysis block from the trace file below,
maybe somebody can help me out?
Thanks a LOT in advance.
Martin Klier
Access path analysis for BINLOCK
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for BINLOCK[BINLOCK]
Column (#5):
NewDensity:0.000872, OldDensity:0.000055 BktCnt:9173,
PopBktCnt:9173, PopValCnt:3, NDV:3
Column (#5): STATUS(
AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.000872 Min: 0 Max: 99
Histogram: Freq #Bkts: 3 UncompBkts: 9173 EndPtVals: 3
Column (#3):
NewDensity:0.000382, OldDensity:0.000055 BktCnt:9173,
PopBktCnt:9173, PopValCnt:4, NDV:4
Column (#3): BINLOCKREASONID(
AvgLen: 3 NDV: 4 Nulls: 0 Density: 0.000382 Min: 1 Max: 4
Histogram: Freq #Bkts: 4 UncompBkts: 9173 EndPtVals: 4
Column (#23):
NewDensity:0.000116, OldDensity:0.000135 BktCnt:254, PopBktCnt:0,
PopValCnt:0, NDV:8614
Column (#23): SYS_STUCREZAUZDHJK1EE$WB6V9IJN(
AvgLen: 12 NDV: 8614 Nulls: 0 Density: 0.000116 Min: 3508926 Max:
9999860024
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Column (#22):
NewDensity:0.000115, OldDensity:0.000130 BktCnt:254, PopBktCnt:0,
PopValCnt:0, NDV:8670
Column (#22): SYS_STUBRBW053YA449$5CPKA75_61(
AvgLen: 12 NDV: 8670 Nulls: 0 Density: 0.000115 Min: 1146131 Max:
9999280688
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Column (#21):
NewDensity:0.000109, OldDensity:0.000109 BktCnt:254, PopBktCnt:0,
PopValCnt:0, NDV:9165
Column (#21): SYS_STUZ_CYWI3LEEOUIC9X74#YPYL(
AvgLen: 12 NDV: 9165 Nulls: 0 Density: 0.000109 Min: 1717423 Max:
9999531876
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Column (#20):
NewDensity:0.000112, OldDensity:0.000182 BktCnt:254, PopBktCnt:210,
PopValCnt:1, NDV:1541
Column (#20): SYS_STU7HGWBWOW4O5GI0ABWO40UNQ(
AvgLen: 12 NDV: 1541 Nulls: 0 Density: 0.000112 Min: 6362896 Max:
9997919992
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 46
Column (#10):
NewDensity:0.000652, OldDensity:0.000653 BktCnt:254, PopBktCnt:0,
PopValCnt:0, NDV:1534
Column (#10): CHECKTIME(
AvgLen: 3 NDV: 1534 Nulls: 7636 Density: 0.000652 Min: 2456685 Max:
2456713
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
ColGroup (#3, VC) SYS_STUZ_CYWI3LEEOUIC9X74#YPYL
Col#: 2 3 5 10 CorStregth: 17291.27
ColGroup (#4, VC) SYS_STU7HGWBWOW4O5GI0ABWO40UNQ
Col#: 3 5 10 CorStregth: 11.95
ColGroup (#2, VC) SYS_STUBRBW053YA449$5CPKA75_61
Col#: 2 3 CorStregth: 3.97
ColGroup (#1, VC) SYS_STUCREZAUZDHJK1EE$WB6V9IJN
Col#: 2 5 CorStregth: 3.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Table: BINLOCK Alias: BINLOCK
Card: Original: 9173.000000 Rounded: 854 Computed: 853.76 Non
Adjusted: 853.76
Access Path: TableScan
Cost: 32.31 Resp: 32.31 Degree: 0
Cost_io: 32.00 Cost_cpu: 4359753
Resp_io: 32.00 Resp_cpu: 4359753
kkofmx: index filter:"BINLOCK"."STATUS"=0
kkofmx: index filter:"BINLOCK"."STATUS"=0
kkofmx: index filter:"BINLOCK"."BINLOCKREASONID"=2
kkofmx: index filter:"BINLOCK"."CHECKTIME" IS NULL
kkofmx: index filter:"BINLOCK"."BINLOCKREASONID"=2
kkofmx: index filter:"BINLOCK"."STATUS"=0
Access Path: index (AllEqRange)
Index: INDEX_BINLOCK_BINID
resc_io: 117.00 resc_cpu: 5255031
ix_sel: 0.827101 ix_sel_with_filters: 0.827101
Cost: 117.38 Resp: 117.38 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Access Path: index (AllEqRange)
Index: I_TUNING_BINLOCK_1
resc_io: 122.00 resc_cpu: 1322286
ix_sel: 0.093073 ix_sel_with_filters: 0.093073
Cost: 122.09 Resp: 122.09 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Access Path: index (skip-scan)
SS sel: 0.093073 ANDV (#skips): 8609.000000
SS io: 30.000000 vs. table scan io: 32.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: I_TUNING_BINLOCK_2
resc_io: 204.00 resc_cpu: 1905394
ix_sel: 0.093073 ix_sel_with_filters: 0.093073
Cost: 204.14 Resp: 204.14 Degree: 1
Access Path: index (skip-scan)
SS sel: 0.135179 ANDV (#skips): 8609.000000
SS io: 24.000000 vs. table scan io: 32.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: I_TUNING_BINLOCK_3
resc_io: 274.00 resc_cpu: 2684886
ix_sel: 0.135179 ix_sel_with_filters: 0.135179
Cost: 274.19 Resp: 274.19 Degree: 1
Access Path: index (skip-scan)
SS sel: 0.827101 ANDV (#skips): 8609.000000
SS io: 23.000000 vs. table scan io: 32.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: I_TUNING_BINLOCK_4
resc_io: 1541.00 resc_cpu: 15395111
ix_sel: 0.827101 ix_sel_with_filters: 0.827101
Cost: 1542.11 Resp: 1542.11 Degree: 1
Best:: AccessPath: TableScan
Cost: 32.31 Degree: 1 Resp: 32.31 Card: 853.76 Bytes: 0
--
Usn's IT Blog for Oracle and Linux
http://www.usn-it.de
--
//www.freelists.org/webpage/oracle-l
Other related posts:
