RE: Cant follow a cardinality estimate of CBO

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
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"."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

This message: [ Message body ]
Next message: rajendra.pande_at_ubs.com: "ORACLE Statements in a PLSQL Block"
Previous message: Jonathan Lewis: "RE: Cant follow a cardinality estimate of CBO"
In reply to: Martin Klier: "Cant follow a cardinality estimate of CBO"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message