RE: Cant follow a cardinality estimate of CBO
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 24 Feb 2014 14:00:47 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDFB9C_at_exmbx05.thus.corp>
Date: Mon, 24 Feb 2014 14:00:47 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDFB9C_at_exmbx05.thus.corp>
It's always possible you've found a special case. (e.g. you've got a lot of rows where checktime is null). It looks like you have a column group on all three of the columns that seem to be in your where clause. status, binlockreasonid, checktime. The histogram on the column group shows popular values covering about 80% ((254 - 46)/254) of the whole data set (9173 rows) - which means about 7511 rows. This is consistent with the very large number of rows where checktime is null (7636 rows) - but you now need to look at the histogram in detail because those rows are probably split across a few combinations of stats and binlockreasonid (3, and 4, distinct values - 12 combinations). You may find that there is a stored hash value that accounts for about the right number of buckets (23 or 24) 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@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:00:47 CET