Cant follow a cardinality estimate of CBO

From: Martin Klier <usn_at_usn-it.de>
Date: Mon, 24 Feb 2014 14:07:21 +0100
Message-ID: <530B4409.3030609_at_usn-it.de>

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

Received on Mon Feb 24 2014 - 14:07:21 CET