RE: Cant follow a cardinality estimate of CBO
Date: Mon, 24 Feb 2014 08:08:01 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF022A9A30_at_WIN02.hotsos.com>
Without actually predicates and the table /column stats is pretty difficult to see what is going on with the calculations.
The cardinality is based on the predicates of the query. Basic cardinality calculations are (NDV is number of distinct values)
column = val1 ---> 1/NDV
column between val1 and val2 ---> ((val2 - val1) / (Hi - Lo)) + (2 *
(1/NDV))
column > val1 --->(hi-val1) / (hi-lo)
Then once each individual cardinality is calculated they are put to getter based on the logical operator, S1 and S2 are the selectivity calculations for 2 different predicates here:
If they are ANDed together: S1 * S2
If they are ORed together: S1 + S2 - (S1 * S2)
Then this final number is multiplied by the row count and you have your cardinality for the table.
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
Ric Van Dyke
Education Director
Hotsos Ltd.
Hotsos Symposium March 2-6 2014
Make your plans to be there now!
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Klier
Sent: Monday, February 24, 2014 8:07 AM
To: Oracle-L_at_freelists.org
Subject: Cant follow a cardinality estimate of CBO
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 24 2014 - 15:08:01 CET