RE: Need some 10053 Guidance to help me solve a puzzler (*IGNORE* adjusted _optimizer_max_permutations, cost_based_transformation)

From: <Christopher.Taylor2_at_parallon.net>
Date: Fri, 12 Oct 2012 09:38:18 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885625327AA_at_NADCWPMSGCMS10.hca.corpad.net>

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_at_jlcomp.demon.co.uk; mwf_at_rsiz.com; niall.litchfield_at_gmail.com; oratune_at_yahoo.com; breitliw_at_centrexcc.com Cc: oracle-l_at_freelists.org
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

    ---

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 12 2012 - 16:38:18 CEST