Rules for 12.2+ optimizer costing (esp on Exadata)? — oracle-tech

    Forum Stats

  • 3,715,917 Users
  • 2,242,907 Discussions
  • 7,845,683 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Rules for 12.2+ optimizer costing (esp on Exadata)?

Bob Bryla
Bob Bryla Member Posts: 106 Blue Ribbon

Two things that I know go into the optimizer costing (and doesn't specifically know about Exadata storage) are statistics and hardware performance. Specifically I/O throughput (e.g., when FTS is offloaded for direct path reads). And that throughput number can cost a FTS lower than an index range scan. But correct me if I'm wrong... the example below, in my attempts to read a 10053 trace, it looks like the index is chosen since the expected number of rows is quite low.

But the specific question is, assuming the statistics are up to date, and cardinalities are correct, how do the system statistics like IOTFRSPEED and MBRC get used in some or all of the costing below? (Of course, much disagreement over the years about whether to gather system statistics on Exadata or not). Thanks.

Access path analysis for APP_TABLE

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for APP_TABLE[VERMEDS]

  SPD: Directive valid: dirid = 5471914869064206779, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {C(131369)[1, 5]}

  SPD: Return code in qosdDSDirSetup: EXISTS, estType = TABLE

*** 2020-03-02 22:48:50.686

** Performing dynamic sampling initial checks. **

** Dynamic sampling initial checks returning FALSE.

  Column (#1): APP_ID(NUMBER)

    AvgLen: 9 NDV: 537985024 Nulls: 0 Density: 0.000000 Min: 2121.000000 Max: 2201362827073.000000

  Column (#39): VERIFY_CONTDATREAL(FLOAT)

    AvgLen: 3 NDV: 95488 Nulls: 1013217934 Density: 0.000010 Min: 59840.010000 Max: 65331.000000

  Column (#5):

    NewDensity:0.000052, OldDensity:0.000060 BktCnt:5436.000000, PopBktCnt:2442.000000, PopValCnt:48, NDV:10560

  Column (#5): DISPENSE_ID(NUMBER)

    AvgLen: 7 NDV: 10560 Nulls: 1631097 Density: 0.000052 Min: 1207.000000 Max: 1217000015.000000

    Histogram: Hybrid  #Bkts: 254  UncompBkts: 5436  EndPtVals: 254  ActualVal: yes

  Table: APP_TABLE  Alias: VERMEDS

    Card: Original: 728697714.000000  Rounded: 1  Computed: 0.000002  Non Adjusted: 0.000002

  Scan IO  Cost (Disk) =   1336544.000000

  Scan CPU Cost (Disk) =   167600474260.000000

  Total Scan IO  Cost  =   1336544.000000 (scan (Disk))

                         + 0.000000 (io filter eval) (= 0.000000 (per row) * 728697714.000000 (#rows))

                       =   1336544.000000

  Total Scan CPU  Cost =   167600474260.000000 (scan (Disk))

                         + 36434885767.724823 (cpu filter eval) (= 50.000000 (per row) * 728697714.000000 (#rows))

                       =   204035360027.724823

  Access Path: TableScan

    Cost:  1342481.374349  Resp: 11653.484152  Degree: 0

      Cost_io: 1336544.000000  Cost_cpu: 204035360028

      Resp_io: 11601.944444  Resp_cpu: 1771140278

****** Costing Index PK_APP_TABLE

  SPD: Directive valid: dirid = 5268686867092156575, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(131369)[1]}

  SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_SCAN

  Column (#1): APP_ID(NUMBER)

    AvgLen: 9 NDV: 203161600 Nulls: 0 Density: 0.000000 Min: 2205306014.000000 Max: 2201362827060.000000

  Column (#2): CONTACT_DATE_REAL(FLOAT)

    AvgLen: 6 NDV: 230272 Nulls: 0 Density: 0.000004 Min: 59840.000000 Max: 65331.020000

  Column (#3):

    NewDensity:0.000000, OldDensity:0.000000 BktCnt:728697714.000000, PopBktCnt:728697638.000000, PopValCnt:68, NDV:144

  Column (#3): LINE(NUMBER)

    AvgLen: 4 NDV: 144 Nulls: 0 Density: 0.000000 Min: 1.000000 Max: 144.000000

    Histogram: Freq  #Bkts: 144  UncompBkts: 728697714  EndPtVals: 144  ActualVal: yes

  ColGroup (#1, Index) PK_APP_TABLE

    Col#: 1 2 3    CorStregth: 9222089.80

  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: #1 (1 2 )  Sel: 1.3689e-09

  SPD: Directive valid: dirid = 5268686867092156575, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(131369)[1]}

  SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_FILTER

  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: #1 (1 2 )  Sel: 1.3689e-09

Access Path: index (RangeScan)

    Index: PK_APP_TABLE

    resc_io: 5.000000  resc_cpu: 36127

    ix_sel: 1.3689e-09  ix_sel_with_filters: 1.3689e-09

    Cost: 5.001051  Resp: 5.001051  Degree: 1

  ****** trying bitmap/domain indexes ******

****** Costing Index PK_APP_TABLE

  Access Path: index (FullScan)

    Index: PK_APP_TABLE

    resc_io: 2762864.000000  resc_cpu: 165774117804

    ix_sel: 1.000000  ix_sel_with_filters: 1.000000

    Cost: 2767687.982444  Resp: 2767687.982444  Degree: 0

  ****** finished trying bitmap/domain indexes ******

  Best:: AccessPath: IndexRange

  Index: PK_APP_TABLE

         Cost: 5.001051  Degree: 1  Resp: 5.001051  Card: 0.000002  Bytes: 0.000000

***************************************

On the system I'm working with, no system stats have ever been gathered(?), so they look like this:

-----------------------------

SYSTEM STATISTICS INFORMATION

-----------------------------

Using dictionary system stats.

  Using NOWORKLOAD Stats

  CPUSPEEDNW: 2864 millions instructions/sec (default is 100)

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM:  10 milliseconds (default is 10)

  MBRC:       NO VALUE blocks (default is 8)

Tagged:
Sign In or Register to comment.