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>



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

Original text of this message