RE: Cant follow a cardinality estimate of CBO
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 24 Feb 2014 14:04:26 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDFBAF_at_exmbx05.thus.corp>
Date: Mon, 24 Feb 2014 14:04:26 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDFBAF_at_exmbx05.thus.corp>
The last one got away too quickly - finger trouble. .... failing the check on the column group histogram. possibly the optimizer has calculated a) The number of non-null rows for checktime b) The fraction of rows for status based on the histogram on status c) The fraction of rows for binlockreasonid based on the histogram on that column calculated a * b * c But you wouldn't see either of these strategies from the 10053 trace because it doesn't report the histograms. Regards Jonathan Lewis http://jonathanlewis.wordpress.com _at_jloracle ________________________________________ From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Martin Klier [usn_at_usn-it.de] Sent: 24 February 2014 13:07 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:04:26 CET