RE: Need some 10053 Guidance to help me solve a puzzler (*IGNORE* adjusted _optimizer_max_permutations, cost_based_transformation)
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <mwf@xxxxxxxx>, <niall.litchfield@xxxxxxxxx>, <oratune@xxxxxxxxx>, <breitliw@xxxxxxxxxxxxx>
- Date: Fri, 12 Oct 2012 09:38:18 -0500
IGNORE that information as something else appears to be going on.
When unsetting optmaxperm and optcbtransform I'm getting the better plan now by
default.
Investigating...
Chris
-----Original Message-----
From: Taylor Christopher - Nashville
Sent: Friday, October 12, 2012 9:15 AM
To: Taylor Christopher - Nashville; jonathan@xxxxxxxxxxxxxxxxxx; mwf@xxxxxxxx;
niall.litchfield@xxxxxxxxx; oratune@xxxxxxxxx; breitliw@xxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Need some 10053 Guidance to help me solve a puzzler (adjusted
_optimizer_max_permutations, cost_based_transformation)
Interesting....
alter session set "_optimizer_max_permutations"=80000;
alter session set "_OPTIMIZER_COST_BASED_TRANSFORMATION"=EXHAUSTIVE;
197876 rows selected.
Elapsed: 00:00:38.48
Plan:
https://gist.github.com/3879343
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: 902152.61 Resp: 902152.61 Degree: 0 <----------------- Changed
Cost_io: 900989.00 Cost_cpu: 8801929733
Resp_io: 900989.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: 117885.84 Resp: 117885.84 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: 66758.75 Resp: 66758.75 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: 71897.58 Resp: 71897.58 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: 49942.93 Resp: 49942.93 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: 49942.93 Resp: 49942.93 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: 117885.84 Resp: 117885.84 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: 62428.67 card 18094159.00 bytes: 20 deg: 1 resp: 62428.67
Inner table: <no name>
resc: 147357.30 card: 18094159.00 bytes: 27 deg: 1 resp: 147357.30
using dmeth: 2 #groups: 1
Cost per ptn: 433658.77 #ptns: 1
hash_area: 256 (max=128000) Hash join: Resc: 643444.73 Resp: 643444.73
[multiMatchCost=0.00]
******** Index join cost ********
Cost: 643444.73
******** Index join OK ********
******** End index join costing ********
Best:: AccessPath: IndexJoin
<--------------------------------------------------------------------------------
Changed
Cost: 643444.73 Degree: 1 Resp: 643444.73 Card: 18094159.00 Bytes: 0
***************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
