0 Replies Latest reply on Mar 30, 2020 8:09 PM by Bob Bryla

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

    Bob Bryla

      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)