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 ;) )
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::
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
NL Join
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-lReceived on Thu Oct 11 2012 - 19:04:07 CEST