RE: Need some 10053 Guidance to help me solve a puzzler
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Oct 2012 12:04:07 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
