RE: Need some 10053 Guidance to help me solve a puzzler

From: <Christopher.Taylor2_at_parallon.net>
Date: Thu, 11 Oct 2012 12:04:07 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8851607BFDC_at_NADCWPMSGCMS10.hca.corpad.net>



Working my way through Hermant's DBA blog and applying it to my 10053 trace. (you guys can chime in any time you know ;) )

BASE STATISTICAL INFORMATION



Table Stats::
  Table: MON_ACCOUNT_PAYER_CALC_SERVICE Alias: MAPCS     #Rows: 18094159 #Blks: 753224 AvgRowLen: 207.00   Column (#2): MON_ACCT_PAYER_CALC_SUMMARY_ID(NUMBER)     AvgLen: 10.00 NDV: 9820620 Nulls: 0 Density: 1.0183e-07 Index Stats::
  Index: IND_MODCOMP_06  Col#: 3 2
    LVLS: 3  #LB: 117294  #DK: 17480026  LB/K: 1.00  DB/K: 1.00  CLUF: 8830708.00
  Index: IND_MODCOMP_07  Col#: 18

    LVLS: 2 #LB: 36241 #DK: 43531 LB/K: 1.00 DB/K: 107.00 CLUF: 4675041.00   Index: IND_MODCOMP_08 Col#: 19
    LVLS: 2 #LB: 46432 #DK: 16 LB/K: 2902.00 DB/K: 126236.00 CLUF: 2019788.00   Index: MAPY_CALC_SVC_CESVCID Col#: 3
    LVLS: 2 #LB: 66216 #DK: 1232 LB/K: 53.00 DB/K: 4310.00 CLUF: 5310582.00   Index: MAPY_CALC_SVC_PERF1 Col#: 2
    LVLS: 2 #LB: 71350 #DK: 9820620 LB/K: 1.00 DB/K: 1.00 CLUF: 11728286.00   Index: MAPY_CALC_SVC_PK Col#: 1
    LVLS: 2 #LB: 49416 #DK: 18094159 LB/K: 1.00 DB/K: 1.00 CLUF: 1012254.00   Index: MAPY_CALC_SVC_RULE_TYPE_IDX Col#: 5     LVLS: 2 #LB: 40595 #DK: 24 LB/K: 1691.00 DB/K: 55571.00 CLUF: 1333707.00


SINGLE TABLE ACCESS PATH

  BEGIN Single Table Cardinality Estimation

  Table: MON_ACCOUNT_PAYER_CALC_SERVICE Alias: MAPCS     Card: Original: 18094159 Rounded: 18094159 Computed: 18094159.00 Non Adjusted: 18094159.00

  END Single Table Cardinality Estimation

  Access Path: TableScan
--> Cost: 165159.42 Resp: 165159.42 Degree: 0
      Cost_io: 164769.00  Cost_cpu: 8801929733
      Resp_io: 164769.00  Resp_cpu: 8801929733
******** Begin index join costing ********
  • trying bitmap/domain indexes ****** Access Path: index (FullScan) Index: IND_MODCOMP_06 resc_io: 117297.00 resc_cpu: 4454155348 ix_sel: 1 ix_sel_with_filters: 1 Cost: 117494.57 Resp: 117494.57 Degree: 0 Access Path: index (FullScan) Index: MAPY_CALC_SVC_CESVCID resc_io: 66218.00 resc_cpu: 4090399314 ix_sel: 1 ix_sel_with_filters: 1 Cost: 66399.43 Resp: 66399.43 Degree: 0 --> Access Path: index (FullScan)
    --> Index: MAPY_CALC_SVC_PERF1
    resc_io: 71352.00 resc_cpu: 4126960787 ix_sel: 1 ix_sel_with_filters: 1
    --> Cost: 71535.06 Resp: 71535.06 Degree: 0
    Access Path: index (FullScan) Index: MAPY_CALC_SVC_PK resc_io: 49418.00 resc_cpu: 3970759122 ix_sel: 1 ix_sel_with_filters: 1 Cost: 49594.13 Resp: 49594.13 Degree: 0 Access Path: index (FullScan) Index: MAPY_CALC_SVC_PK resc_io: 49418.00 resc_cpu: 3970759122 ix_sel: 1 ix_sel_with_filters: 1 Cost: 49594.13 Resp: 49594.13 Degree: 0
  • finished trying bitmap/domain indexes ****** Access Path: index (FullScan) Index: IND_MODCOMP_06 resc_io: 117297.00 resc_cpu: 4454155348 ix_sel: 1 ix_sel_with_filters: 1 Cost: 117494.57 Resp: 117494.57 Degree: 0 ******** Cost index join ******** --> Index join: Considering index join to index MAPY_CALC_SVC_PK --> Index join: Joining index IND_MODCOMP_06 Ix HA Join Outer table: resc: 61992.66 card 18094159.00 bytes: 20 deg: 1 resp: 61992.66 Inner table: <no name> resc: 146868.21 card: 18094159.00 bytes: 27 deg: 1 resp: 146868.21 using dmeth: 2 #groups: 1 Cost per ptn: 61015.94 #ptns: 1 hash_area: 256 (max=128000) Hash join: Resc: 269876.81 Resp: 269876.81 [multiMatchCost=0.00] ******** Index join cost ******** --> Cost: 269876.81 ******** End index join costing ******** --> Best:: AccessPath: TableScan Cost: 165159.42 Degree: 1 Resp: 165159.42 Card: 18094159.00 Bytes: 0

...
...
Now joining: MON_ACCOUNT_PAYER_CALC_SERVICE[MAPCS]#3



NL Join
  Outer table: Card: 162174.47  Cost: 6038811747.44  Resp: 6038811747.44  Degree: 1  Bytes: 45
  Inner table: MON_ACCOUNT_PAYER_CALC_SERVICE  Alias: MAPCS
  Access Path: TableScan

SM Join
  Outer table:
    resc: 6038811747.44 card 162174.47 bytes: 45 deg: 1 resp: 6038811747.44   Inner table: MON_ACCOUNT_PAYER_CALC_SERVICE Alias: MAPCS

HA Join
  Outer table:
    resc: 6038811747.44 card 162174.47 bytes: 45 deg: 1 resp: 6038811747.44   Inner table: MON_ACCOUNT_PAYER_CALC_SERVICE Alias: MAPCS

Best:: JoinMethod: Hash

       Cost: 6039010867.83 Degree: 1 Resp: 6039010867.83 Card: 298800.96 Bytes: 72

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 11 2012 - 19:04:07 CEST

Original text of this message