Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.

From: lsllcm <lsllcm_at_gmail.com>
Date: Wed, 4 Nov 2009 04:04:45 -0800 (PST)
Message-ID: <25ef25c4-c9d7-477b-a813-5b24d882884f_at_y10g2000prg.googlegroups.com>



part6-


Now joining: SETDETAILS[SD]#0

NL Join
  Outer table: Card: 1187146.28 Cost: 18174.63 Resp: 18174.63 Degree: 1 Bytes: 173
Access path analysis for SETDETAILS
  Inner table: SETDETAILS Alias: SD
  Access Path: TableScan
    NL Join: Cost: 1179682709.66 Resp: 1179682709.66 Degree: 1
      Cost_io: 1172275359.70  Cost_cpu: 239647535518960
      Resp_io: 1172275359.70  Resp_cpu: 239647535518960
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

kkofmx: index filter:"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1"

kkofmx: index filter:"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2"

kkofmx: index filter:"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3"

kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

OPTIMIZER PERCENT INDEX CACHING = 90   Access Path: index (RangeScan)
    Index: SETDETAILS_ADDR_IX
    resc_io: 3823.00 resc_cpu: 261762790     ix_sel: 0.999999 ix_sel_with_filters: 0.999999

  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 454824600.54 Resp: 454824600.54 Degree: 1 Cost_io: 453864063.50 Cost_cpu: 31075936080355 Resp_io: 453864063.50 Resp_cpu: 31075936080355 OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (RangeScan)
    Index: SETDETAILS_IX
    resc_io: 433.00 resc_cpu: 81263079
    ix_sel: 0.999999 ix_sel_with_filters: 0.000001
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ******
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ******
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ******
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 51719782.47 Resp: 51719782.47 Degree: 1 Cost_io: 51421569.50 Cost_cpu: 9647985131049 Resp_io: 51421569.50 Resp_cpu: 9647985131049 OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (AllEqJoinGuess)
    Index: SETDETAILS_PID_IX
    resc_io: 1.00 resc_cpu: 8467
    ix_sel: 0.000001 ix_sel_with_filters: 0.000001
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 136920.30 Resp: 136920.30 Degree: 1 Cost_io: 136862.30 Cost_cpu: 1876359817 Resp_io: 136862.30 Resp_cpu: 1876359817 OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (RangeScan)
    Index: SETDETAILS_PK
    resc_io: 354384.00 resc_cpu: 2758261918     ix_sel: 0.999999 ix_sel_with_filters: 0.999999
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 42080694123.37 Resp: 42080694123.37 Degree: 1 Cost_io: 42070572954.10 Cost_cpu: 327446831442229 Resp_io: 42070572954.10 Resp_cpu: 327446831442229 OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (RangeScan)
    Index: SETDETAILS_SETID1_IX
    resc_io: 7749.00 resc_cpu: 289721564     ix_sel: 0.999999 ix_sel_with_filters: 0.999999
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 921000711.82 Resp: 921000711.82 Degree: 1 Cost_io: 919937583.10 Cost_cpu: 34395050685775 Resp_io: 919937583.10 Resp_cpu: 34395050685775 OPTIMIZER PERCENT INDEX CACHING = 90 ColGroup Usage:: PredCnt: 2 Matches Full: Partial: ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
  • Virtual column Adjustment ****** Column name SYS_NC00017$ cost_cpu 150.00 cost_io 179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00
  • End virtual column Adjustment ****** Access Path: index (AllEqJoin) Index: SETDETAILS_SETID_IX resc_io: 1.00 resc_cpu: 19671 ix_sel: 0.000065 ix_sel_with_filters: 0.000065
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 136961.41 Resp: 136961.41 Degree: 1 Cost_io: 136862.30 Cost_cpu: 3206443506 Resp_io: 136862.30 Resp_cpu: 3206443506 OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (RangeScan)
    Index: SETDETAILS_STAT_IX
    resc_io: 3822.00 resc_cpu: 261755669     ix_sel: 0.999999 ix_sel_with_filters: 0.999999
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 454705859.81 Resp: 454705859.81 Degree: 1 Cost_io: 453745348.90 Cost_cpu: 31075090661454 Resp_io: 453745348.90 Resp_cpu: 31075090661454
    • trying bitmap/domain indexes ******
    • finished trying bitmap/domain indexes ******
  Best NL cost: 136920.30
          resc: 136920.30  resc_io: 136862.30  resc_cpu: 1876359817
          resp: 136920.30  resp_io: 136862.30  resc_cpu: 1876359817
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] = 1278471 SETDETAILS[SD] = 546
Join selectivity using 1 ColGroups: 0.003014 (sel1 = 0.000000, sel2 = 0.000000)
Join Card: 90650.350585 = = outer (1187146.275001) * inner (25.332653) * sel (0.003014)
Join Card - Rounded: 90650 Computed: 90650.35   Outer table: X4FEEITEM_INVOICE Alias: XFI     resc: 18174.63 card 1187146.28 bytes: 173 deg: 1 resp: 18174.63
  Inner table: SETDETAILS Alias: SD
    resc: 1.00 card: 25.33 bytes: 38 deg: 1 resp: 1.00     using dmeth: 2 #groups: 1
    SORT ressource         Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort: 29214 Row size:     201 Total Rows:
1187146
      Initial runs:   6 Merge passes:  1 IO Cost / pass:      15826
      Total IO sort cost: 45040      Total CPU sort cost: 1831081189
      Total Temp space used: 555738000
    SORT ressource         Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort: 1 Row size:     52 Total Rows:             25
      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 32357899
      Total Temp space used: 0

  SM join: Resc: 63273.22 Resp: 63273.22 [multiMatchCost=0.00] SM Join
  SM cost: 63273.22
     resc: 63273.22 resc_io: 63188.70 resc_cpu: 2734599482
     resp: 63273.22 resp_io: 63188.70 resp_cpu: 2734599482
  Outer table: X4FEEITEM_INVOICE Alias: XFI     resc: 18174.63 card 1187146.28 bytes: 173 deg: 1 resp: 18174.63
  Inner table: SETDETAILS Alias: SD
    resc: 1.00 card: 25.33 bytes: 38 deg: 1 resp: 1.00     using dmeth: 2 #groups: 1
    Cost per ptn: 10388.40 #ptns: 1
    hash_area: 124 (max=10240) buildfrag: 26810 probefrag: 1 ppasses: 1
  Hash join: Resc: 28564.31  Resp: 28564.31  [multiMatchCost=0.28]
  Outer table:  SETDETAILS  Alias: SD
    resc: 1.00  card 25.33  bytes: 38  deg: 1  resp: 1.00
  Inner table: X4FEEITEM_INVOICE Alias: XFI     resc: 18174.63 card: 1187146.28 bytes: 173 deg: 1 resp: 18174.63

    using dmeth: 2 #groups: 1
    Cost per ptn: 4.17 #ptns: 1
    hash_area: 124 (max=10240) buildfrag: 1 probefrag: 26810 ppasses: 1
  Hash join: Resc: 18179.80 Resp: 18179.80 [multiMatchCost=0.00] HA Join

  HA cost: 18179.80 swapped
     resc: 18179.80 resc_io: 18148.70 resc_cpu: 1006055078
     resp: 18179.80 resp_io: 18148.70 resp_cpu: 1006055078
Best:: JoinMethod: Hash
       Cost: 18179.80  Degree: 1  Resp: 18179.80  Card: 90650.35
Bytes: 211

Now joining: F4INVOICE[FINV]#1

NL Join
  Outer table: Card: 90650.35 Cost: 18179.80 Resp: 18179.80 Degree: 1 Bytes: 211
Access path analysis for F4INVOICE
  Inner table: F4INVOICE Alias: FINV
  Access Path: TableScan
    NL Join: Cost: 89876206.15 Resp: 89876206.15 Degree: 1
      Cost_io: 89531248.70  Cost_cpu: 11160293933334
      Resp_io: 89531248.70  Resp_cpu: 11160293933334
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (UniqueScan)
    Index: F4INVOICE_PK
    resc_io: 1.00 resc_cpu: 8461
    ix_sel: 0.000003 ix_sel_with_filters: 0.000003

  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 27247.17 Resp: 27247.17 Degree: 1 Cost_io: 27213.70 Cost_cpu: 1082758031 Resp_io: 27213.70 Resp_cpu: 1082758031 kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"

OPTIMIZER PERCENT INDEX CACHING = 90   Access Path: index (RangeScan)
    Index: F4INVOICE_BATCH_DATE_IX
    resc_io: 3793.00 resc_cpu: 149236242     ix_sel: 0.813255 ix_sel_with_filters: 0.813255

  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 34443539.79 Resp: 34443539.79 Degree: 1 Cost_io: 34401693.70 Cost_cpu: 1353832588082 Resp_io: 34401693.70 Resp_cpu: 1353832588082 OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (RangeScan)
    Index: F4INVOICE_BATCH_NBR_IX
    resc_io: 3050.00 resc_cpu: 142805762     ix_sel: 0.813255 ix_sel_with_filters: 0.813255
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 27706443.01 Resp: 27706443.01 Degree: 1 Cost_io: 27666398.70 Cost_cpu: 1295540287608 Resp_io: 27666398.70 Resp_cpu: 1295540287608 OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (RangeScan)
    Index: F4INVOICE_DATE_IX
    resc_io: 3726.00 resc_cpu: 148184335     ix_sel: 0.813255 ix_sel_with_filters: 0.813255
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 33835890.05 Resp: 33835890.05 Degree: 1 Cost_io: 33794338.70 Cost_cpu: 1344297055841 Resp_io: 33794338.70 Resp_cpu: 1344297055841 OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (AllEqUnique)
    Index: F4INVOICE_PK
    resc_io: 1.00 resc_cpu: 8461
    ix_sel: 0.000004 ix_sel_with_filters: 0.000004
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 27247.17 Resp: 27247.17 Degree: 1 Cost_io: 27213.70 Cost_cpu: 1082758031 Resp_io: 27213.70 Resp_cpu: 1082758031
    • trying bitmap/domain indexes ******
    • finished trying bitmap/domain indexes ******
  Best NL cost: 27247.17
          resc: 27247.17  resc_io: 27213.70  resc_cpu: 1082758031
          resp: 27247.17  resp_io: 27213.70  resc_cpu: 1082758031
Join Card: 112700.054052 = = outer (90650.350585) * inner (248742.292433) * sel (0.000005)
Join Card - Rounded: 112700 Computed: 112700.05   Outer table: SETDETAILS Alias: SD
    resc: 18179.80 card 90650.35 bytes: 211 deg: 1 resp: 18179.80   Inner table: F4INVOICE Alias: FINV
    resc: 375.16 card: 248742.29 bytes: 24 deg: 1 resp: 375.16     using dmeth: 2 #groups: 1
    SORT ressource         Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort: 2697 Row size:     243 Total Rows:
90650
      Initial runs:   2 Merge passes:  1 IO Cost / pass:       1462
      Total IO sort cost: 4159      Total CPU sort cost: 166027617
      Total Temp space used: 42443000
    SORT ressource         Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort: 1127 Row size:     37 Total Rows:         248742
      Initial runs:   2 Merge passes:  1 IO Cost / pass:        612
      Total IO sort cost: 1739      Total CPU sort cost: 260983032
      Total Temp space used: 18047000

  SM join: Resc: 24466.15 Resp: 24466.15 [multiMatchCost=0.00] SM Join
  SM cost: 24466.15
     resc: 24466.15 resc_io: 24421.40 resc_cpu: 1447842668
     resp: 24466.15 resp_io: 24421.40 resp_cpu: 1447842668
  Outer table: SETDETAILS Alias: SD
    resc: 18179.80 card 90650.35 bytes: 211 deg: 1 resp: 18179.80   Inner table: F4INVOICE Alias: FINV
    resc: 375.16 card: 248742.29 bytes: 24 deg: 1 resp: 375.16     using dmeth: 2 #groups: 1
    Cost per ptn: 1382.91 #ptns: 1
    hash_area: 124 (max=10240) buildfrag: 2468 probefrag: 1094 ppasses: 1
  Hash join: Resc: 19937.86 Resp: 19937.86 [multiMatchCost=0.00]   Outer table: F4INVOICE Alias: FINV
    resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16   Inner table: SETDETAILS Alias: SD
    resc: 18179.80 card: 90650.35 bytes: 211 deg: 1 resp: 18179.80     using dmeth: 2 #groups: 1
    Cost per ptn: 1382.67 #ptns: 1
    hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 2468 ppasses: 1
  Hash join: Resc: 19937.69 Resp: 19937.69 [multiMatchCost=0.07] HA Join
  HA cost: 19937.69 swapped
     resc: 19937.69 resc_io: 19903.40 resc_cpu: 1109267585
     resp: 19937.69 resp_io: 19903.40 resp_cpu: 1109267585
ORDER BY sort
    SORT ressource         Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort: 3712 Row size:     269 Total Rows:
112700
      Initial runs:   2 Merge passes:  1 IO Cost / pass:       2012
      Total IO sort cost: 5724      Total CPU sort cost: 208978147
      Total Temp space used: 28861000
Best:: JoinMethod: Hash
       Cost: 25668.15  Degree: 1  Resp: 25668.15  Card: 112700.05
Bytes: 235

Best so far: Table#: 3 cost: 3953.0510 card: 1231517.0000 bytes: 51723714

              Table#: 2 cost: 18174.6269 card: 1187146.2750 bytes: 205376258

              Table#: 0 cost: 18179.7965 card: 90650.3506 bytes: 19127150

              Table#: 1 cost: 25668.1461 card: 112700.0541 bytes: 26484500



First K Rows: K = 100.00, N = 112700.00
First K Rows: old pf = 0.0008965, new pf = 0.0009054 Access path analysis for F4FEEITEM

SINGLE TABLE ACCESS PATH (First K Rows)
  Single Table Cardinality Estimation for F4FEEITEM[F4]   Table: F4FEEITEM Alias: F4
    Card: Original: 1169.000000 Rounded: 1115 Computed: 1115.02 Non Adjusted: 1115.02
  Access Path: TableScan
    Cost: 11.04 Resp: 11.04 Degree: 0
      Cost_io: 11.00  Cost_cpu: 1450008
      Resp_io: 11.00  Resp_cpu: 1450008


  Access Path: index (RangeScan)
    Index: F4FEEITEM_PK
    resc_io: 459.00 resc_cpu: 4641421
    ix_sel: 0.953821 ix_sel_with_filters: 0.953821     Cost: 45.91 Resp: 45.91 Degree: 1

  Access Path: index (RangeScan)

    Index: F4FEEITEM_POS_IX
    resc_io: 39.00  resc_cpu: 1650416
    ix_sel: 0.953821  ix_sel_with_filters: 0.953821
    Cost: 3.91 Resp: 3.91 Degree: 1
  • trying bitmap/domain indexes ******
  • finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: F4FEEITEM_POS_IX Cost: 3.91 Degree: 1 Resp: 3.91 Card: 1115.02 Bytes: 42

First K Rows: unchanged join prefix len = 1 Join order[19]: F4FEEITEM[F4]#3 X4FEEITEM_INVOICE[XFI]#2 SETDETAILS [SD]#0 F4INVOICE[FINV]#1



Now joining: X4FEEITEM_INVOICE[XFI]#2

NL Join
  Outer table: Card: 1115.02 Cost: 3.91 Resp: 3.91 Degree: 1 Bytes: 42
Access path analysis for X4FEEITEM_INVOICE   Inner table: X4FEEITEM_INVOICE Alias: XFI   Access Path: TableScan
    NL Join: Cost: 7709786.51 Resp: 7709786.51 Degree: 1
      Cost_io: 7669370.90  Cost_cpu: 1307552963017
      Resp_io: 7669370.90  Resp_cpu: 1307552963017
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"

kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"

OPTIMIZER PERCENT INDEX CACHING = 90   Access Path: index (RangeScan)
    Index: X4FEEITEM_INVOICE_NBR_IX
    resc_io: 26639.00 resc_cpu: 1324363816     ix_sel: 0.954312 ix_sel_with_filters: 0.954312

  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 2974816.68 Resp: 2974816.68 Degree: 1 Cost_io: 2970252.40 Cost_cpu: 147666730559 Resp_io: 2970252.40 Resp_cpu: 147666730559 OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (RangeScan)
    Index: X4FEEITEM_INVOICE_PK
    resc_io: 2.00 resc_cpu: 15073
    ix_sel: 0.000000 ix_sel_with_filters: 0.000000
  • Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00
  • End Logdef Adjustment ****** NL Join : Cost: 226.96 Resp: 226.96 Degree: 1 Cost_io: 226.90 Cost_cpu: 1845668 Resp_io: 226.90 Resp_cpu: 1845668
    • trying bitmap/domain indexes ******
    • finished trying bitmap/domain indexes ******
  Best NL cost: 226.96
          resc: 226.96  resc_io: 226.90  resc_cpu: 1845668
          resp: 226.96  resp_io: 226.90  resc_cpu: 1845668
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] = 1278471 F4FEEITEM[F4] = 1291140
Join selectivity using 1 ColGroups: 0.000001 (sel1 = 0.000000, sel2 = 0.000000)
Join Card: 1074.843933 = = outer (1115.017251) * inner (1187146.275001) * sel (0.000001)
Join Card - Rounded: 1075 Computed: 1074.84   Outer table: F4FEEITEM Alias: F4
    resc: 3953.05 card 1231517.00 bytes: 42 deg: 1 resp: 3953.05   Inner table: X4FEEITEM_INVOICE Alias: XFI     resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37     using dmeth: 2 #groups: 1
    SORT ressource         Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort: 8595 Row size:     57 Total Rows:        1231517
      Initial runs:   2 Merge passes:  1 IO Cost / pass:       4658
      Total IO sort cost: 13253      Total CPU sort cost: 1366595347
      Total Temp space used: 148382000

  SM join: Resc: 20287.66 Resp: 20287.66 [multiMatchCost=0.00] SM Join
  SM cost: 20287.66
     resc: 20287.66 resc_io: 20235.70 resc_cpu: 1681099831
     resp: 20287.66 resp_io: 20235.70 resp_cpu: 1681099831
  Outer table: F4FEEITEM Alias: F4
    resc: 3953.05 card 1231517.00 bytes: 42 deg: 1 resp: 3953.05   Inner table: X4FEEITEM_INVOICE Alias: XFI     resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37     using dmeth: 2 #groups: 1
    Cost per ptn: 11182.21 #ptns: 1
    hash_area: 124 (max=10240) buildfrag: 8118 probefrag: 20723 ppasses: 1
  Hash join: Resc: 18174.63 Resp: 18174.63 [multiMatchCost=0.00] HA Join
  HA cost: 18174.63
     resc: 18174.63 resc_io: 18147.70 resc_cpu: 871156375
     resp: 18174.63 resp_io: 18147.70 resp_cpu: 871156375
Join order aborted: cost > best plan cost

(newjo-stop-1) k:0, spcnt:0, perm:19, maxperm:1000

Number of join permutations tried: 19

Consider using bloom filter between FINV[F4INVOICE] and XFI [X4FEEITEM_INVOICE]
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because not a hash join
Consider using bloom filter between XFI[X4FEEITEM_INVOICE] and SD [SETDETAILS]
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because not a hash join
Consider using bloom filter between SD[SETDETAILS] and F4[F4FEEITEM] kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because not a hash join
(newjo-save) [1 3 2 0 ]
    SORT ressource         Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort: 4 Row size:     269 Total Rows:            117
      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 32388885
      Total Temp space used: 0

Or-Expansion validity checks failed on query block SEL$1 (#0) because no OR expansion if old/new first rows mode and we have eliminatedsort via an index, unless USE_CONCAT hint is specified Transfer Optimizer annotations for query block SEL$1 (#0) id=0 frofkks[i] (index start key)
predicate="FINV"."SERV_PROV_CODE"='SACRAMENTO' id=0 frofkke[i] (index stop key)
predicate="FINV"."SERV_PROV_CODE"='SACRAMENTO' id=0 frofkks[i] (index start key)
predicate="XFI"."SERV_PROV_CODE"='SACRAMENTO' id=0 frofkks[i] (index start key)
predicate="FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR" id=0 frofkke[i] (index stop key)
predicate="XFI"."SERV_PROV_CODE"='SACRAMENTO' id=0 frofkke[i] (index stop key)
predicate="FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR" id=0 frofand predicate="XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND "XFI"."REC_STATUS"='A'
id=0 frofkks[i] (index start key)
predicate="SD"."SERV_PROV_CODE"='SACRAMENTO' id=0 frofkks[i] (index start key)
predicate="SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" id=0 frofkks[i] (index start key)
predicate="SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" id=0 frofkks[i] (index start key)
predicate="SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" id=0 frofkke[i] (index stop key)
predicate="SD"."SERV_PROV_CODE"='SACRAMENTO' id=0 frofkke[i] (index stop key)
predicate="SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" id=0 frofkke[i] (index stop key)
predicate="SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" id=0 frofkke[i] (index stop key)
predicate="SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" id=0 frofand predicate=UPPER("SD"."SET_ID")='SET07' id=0 frofkks[i] (index start key)
predicate="F4"."SERV_PROV_CODE"='SACRAMENTO' id=0 frofkks[i] (index start key)
predicate="XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" id=0 frofkks[i] (index start key)
predicate="XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" id=0 frofkks[i] (index start key)
predicate="XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" id=0 frofkks[i] (index start key)
predicate="XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR" id=0 frofkke[i] (index stop key)
predicate="F4"."SERV_PROV_CODE"='SACRAMENTO' id=0 frofkke[i] (index stop key)
predicate="XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" id=0 frofkke[i] (index stop key)
predicate="XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" id=0 frofkke[i] (index stop key)
predicate="XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" id=0 frofkke[i] (index stop key)
predicate="XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR" Final cost for query block SEL$1 (#0) - First K Rows Plan:   Best join order: 9
  Cost: 4.5006  Degree: 1  Card: 117.0000  Bytes: 27495
  Resc: 4.5006  Resc_io: 4.5000  Resc_cpu: 20442
  Resp: 4.5006  Resp_io: 4.5000  Resc_cpu: 20442
kkoqbc-subheap (delete addr=0x6ddabc, in-use=487024, alloc=505200) kkoqbc-end:

        :
    call(in-use=129404, alloc=653452), compile(in-use=121860, alloc=126188), execution(in-use=3640, alloc=4060)

kkoqbc: finish optimizing query block SEL$1 (#0) apadrv-end

          :
    call(in-use=129404, alloc=653452), compile(in-use=122576, alloc=126188), execution(in-use=3640, alloc=4060)

Starting SQL statement dump

user_id=85 user_name=TEST module=SQL*Plus action= sql_id=5n7ufx7tz1uks plan_hash_value=-546246515 problem_type=3 ----- Current SQL Statement for this session (sql_id=5n7ufx7tz1uks)


SELECT xfi.serv_prov_code,
       xfi.b1_per_id1,
       xfi.b1_per_id2,
       xfi.b1_per_id3,
       xfi.feeitem_seq_nbr,
       xfi.invoice_nbr,
       xfi.gf_fee_period,
       xfi.gf_fee,
       xfi.gf_des,
       xfi.gf_unit,
       xfi.gf_udes,
       finv.invoice_date AS gf_fee_apply_date,
       xfi.feeitem_invoice_status,
       xfi.gf_l1,
       xfi.gf_l2,
       xfi.gf_l3,
       xfi.x4feeitem_invoice_udf1,
       xfi.x4feeitem_invoice_udf2,
       xfi.x4feeitem_invoice_udf3,
       xfi.x4feeitem_invoice_udf4,
       xfi.gf_fee_schedule,
       xfi.fee_schedule_version,
       xfi.rec_date,
       xfi.rec_ful_nam,
       xfi.rec_status,
       f4.GF_COD,
       f4.GF_PRIORITY

  FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM f4
 WHERE sd.serv_prov_code = xfi.serv_prov_code
   AND sd.b1_per_id1 = xfi.b1_per_id1
   AND sd.b1_per_id2 = xfi.b1_per_id2
   AND sd.b1_per_id3 = xfi.b1_per_id3
   AND xfi.serv_prov_code = f4.serv_prov_code
   AND xfi.b1_per_id1 = f4.b1_per_id1
   AND xfi.b1_per_id2 = f4.b1_per_id2

   AND xfi.b1_per_id3 = f4.b1_per_id3
   And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr    AND finv.serv_prov_code = xfi.serv_prov_code
   AND finv.invoice_nbr = xfi.invoice_nbr
   AND sd.serv_prov_code = 'SACRAMENTO'
   AND upper(sd.set_id) = 'SET07'

   AND xfi.rec_status = 'A'
   AND xfi.feeitem_invoice_status = 'INVOICED'  ORDER BY gf_fee_apply_date
sql_text_length=1406
sql=SELECT xfi.serv_prov_code,
       xfi.b1_per_id1,
       xfi.b1_per_id2,
       xfi.b1_per_id3,
       xfi.feeitem_seq_nbr,
       xfi.invoice_nbr,
       xfi.gf_fee_period,
       xfi.gf_fee,
       xfi.gf_des,
       xfi.gf_unit,
       xfi.gf_udes,

sql=   finv.invoice_date AS gf_fee_apply_date,
       xfi.feeitem_invoice_status,
       xfi.gf_l1,
       xfi.gf_l2,
       xfi.gf_l3,
       xfi.x4feeitem_invoice_udf1,
       xfi.x4feeitem_invoice_udf2,
       xfi.x4feeitem_invoice_udf3,
       xfi.x4feeite
sql=m_invoice_udf4,
       xfi.gf_fee_schedule,
       xfi.fee_schedule_version,
       xfi.rec_date,
       xfi.rec_ful_nam,
       xfi.rec_status,
       f4.GF_COD,
       f4.GF_PRIORITY

  FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM
sql=f4
 WHERE sd.serv_prov_code = xfi.serv_prov_code
   AND sd.b1_per_id1 = xfi.b1_per_id1
   AND sd.b1_per_id2 = xfi.b1_per_id2
   AND sd.b1_per_id3 = xfi.b1_per_id3

   AND xfi.serv_prov_code = f4.serv_prov_code    AND xfi.b1_per_id1 = f4.b1_per_id1
   AND xf
sql=i.b1_per_id2 = f4.b1_per_id2

   AND xfi.b1_per_id3 = f4.b1_per_id3
   And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr    AND finv.serv_prov_code = xfi.serv_prov_code    AND finv.invoice_nbr = xfi.invoice_nbr    AND sd.serv_prov_code = 'SACRAMENTO'
   AND uppe
sql=r(sd.set_id) = 'SET07'

   AND xfi.rec_status = 'A'
   AND xfi.feeitem_invoice_status = 'INVOICED'  ORDER BY gf_fee_apply_date
----- Explain Plan Dump -----
----- Plan Table -----



Plan Table

+-----------------------------------+
| Id  | Operation                        | Name                    |
Rows  | Bytes | Cost  | Time      |
-------------------------------------------------------------------
+-----------------------------------+
| 0   | SELECT STATEMENT                 |
|       |       |     5 |           |
| 1   |  NESTED LOOPS                    |
|       |       |       |           |
| 2   |   NESTED LOOPS                   |                         |
117 |   27K |     5 |  00:00:01 |
| 3   |    NESTED LOOPS                  |
|     1 |   193 |     4 |  00:00:01 |
| 4   |     NESTED LOOPS                 |
|    15 |  2325 |     2 |  00:00:01 |
| 5   |      TABLE ACCESS BY INDEX ROWID | F4INVOICE               |
243K | 5830K |     1 |  00:00:01 |
| 6   |       INDEX RANGE SCAN           | F4INVOICE_DATE_IX
|     3 |       |     1 |  00:00:01 |
| 7   |      TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE
|     5 |   655 |     1 |  00:00:01 |
| 8   |       INDEX RANGE SCAN           |
X4FEEITEM_INVOICE_NBR_IX|     5 |       |     1 |  00:00:01 |
| 9   |     TABLE ACCESS BY INDEX ROWID  | SETDETAILS
|     1 |    38 |     1 |  00:00:01 |
| 10  |      INDEX RANGE SCAN            | SETDETAILS_PID_IX
|     1 |       |     1 |  00:00:01 |
| 11  |    INDEX UNIQUE SCAN             | F4FEEITEM_PK
|     1 |       |     1 |  00:00:01 |
| 12  |   TABLE ACCESS BY INDEX ROWID    | F4FEEITEM               |
101 |  4242 |     1 |  00:00:01 |
-------------------------------------------------------------------
+-----------------------------------+

Predicate Information:
  • 2009-11-04 12:27:19.326 6 - access("FINV"."SERV_PROV_CODE"='SACRAMENTO') 7 - filter(("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND "XFI"."REC_STATUS"='A')) 8 - access("XFI"."SERV_PROV_CODE"='SACRAMENTO' AND "FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR") 9 - filter(UPPER("SET_ID")='SET07') 10 - access("SD"."SERV_PROV_CODE"='SACRAMENTO' AND "SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND "SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND "SD"."B1_PER_ID3"="XFI"."B1_PER_ID3") 11 - access("F4"."SERV_PROV_CODE"='SACRAMENTO' AND "XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND "XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND "XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND "XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR")
Content of other_xml column
  db_version     : 11.2.0.1
  parse_schema   : TEST
  plan_hash      : 3748720781
  plan_hash_2    : 1520493255

  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('_optimizer_cost_based_transformation' 'off')
      OPT_PARAM('optimizer_index_cost_adj' 10)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(100)
      OUTLINE_LEAF(_at_"SEL$1")
      INDEX_RS_ASC(_at_"SEL$1" "FINV"@"SEL
$1" ("F4INVOICE"."SERV_PROV_CODE" "F4INVOICE"."INVOICE_DATE"))
      INDEX_RS_ASC(_at_"SEL$1" "XFI"@"SEL
$1" ("X4FEEITEM_INVOICE"."SERV_PROV_CODE" "X4FEEITEM_INVOICE"."INVOICE_NBR"))
      INDEX_RS_ASC(_at_"SEL$1" "SD"@"SEL $1" ("SETDETAILS"."SERV_PROV_CODE" "SETDETAILS"."B1_PER_ID1" "SETDETAILS"."B1_PER_ID2" "SETDETAILS"."B1_PER_ID3"))
      INDEX(_at_"SEL$1" "F4"@"SEL$1" ("F4FEEITEM"."SERV_PROV_CODE"
"F4FEEITEM"."B1_PER_ID1" "F4FEEITEM"."B1_PER_ID2"
"F4FEEITEM"."B1_PER_ID3" "F4FEEITEM"."FEEITEM_SEQ_NBR"))
      LEADING(_at_"SEL$1" "FINV"@"SEL$1" "XFI"@"SEL$1" "SD"@"SEL$1"
"F4"_at_"SEL$1")
      USE_NL(_at_"SEL$1" "XFI"@"SEL$1")
      USE_NL(_at_"SEL$1" "SD"@"SEL$1")
      USE_NL(_at_"SEL$1" "F4"@"SEL$1")
      NLJ_BATCHING(_at_"SEL$1" "F4"@"SEL$1")
    END_OUTLINE_DATA
  */

Optimizer state dump:
Compilation Environment Dump

optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true
parallel_query_forced_dop           = 0
parallel_dml_forced_dop             = 0
parallel_ddl_forced_degree          = 0
parallel_ddl_forced_instances       = 0
_query_rewrite_fudge                = 90
optimizer_features_enable           = 11.1.0.6
_optimizer_search_limit             = 5
cpu_count                           = 4
active_instance_count               = 1
parallel_threads_per_cpu            = 2
hash_area_size                      = 131072
bitmap_merge_area_size              = 1048576
sort_area_size                      = 65536
sort_area_retained_size             = 0
_sort_elimination_cost_ratio        = 0
_optimizer_block_size               = 8192
_sort_multiblock_read_count         = 2
_hash_multiblock_io_count           = 0
_db_file_optimizer_read_count       = 8
_optimizer_max_permutations         = 2000
pga_aggregate_target                = 204800 KB
_pga_max_size                       = 204800 KB
_query_rewrite_maxdisjunct          = 257
_smm_auto_min_io_size               = 56 KB
_smm_auto_max_io_size               = 248 KB
_smm_min_size                       = 204 KB
_smm_max_size                       = 40960 KB
_smm_px_max_size                    = 102400 KB
_cpu_to_io                          = 0
_optimizer_undo_cost_change         = 11.1.0.6
parallel_query_mode                 = enabled
parallel_dml_mode                   = disabled
parallel_ddl_mode                   = enabled
optimizer_mode                      = first_rows_100
sqlstat_enabled                     = false
_optimizer_percent_parallel         = 101
_always_anti_join                   = choose
_always_semi_join                   = choose
_optimizer_mode_force               = true
_partition_view_enabled             = true
_always_star_transformation         = false
_query_rewrite_or_error             = false
_hash_join_enabled                  = true
cursor_sharing                      = exact
_b_tree_bitmap_plans                = true
star_transformation_enabled         = false
_optimizer_cost_model               = choose
_new_sort_cost_estimate             = true
_complex_view_merging               = true
_unnest_subquery                    = true
_eliminate_common_subexpr           = true
_pred_move_around                   = true
_convert_set_to_join                = false
_push_join_predicate                = true
_push_join_union_view               = true
_fast_full_scan_enabled             = true
_optim_enhance_nnull_detection      = true
_parallel_broadcast_enabled         = true
_px_broadcast_fudge_factor          = 100
_ordered_nested_loop                = true
_no_or_expansion                    = false
optimizer_index_cost_adj            = 10
optimizer_index_caching             = 90
_system_index_caching               = 0
_disable_datalayer_sampling         = false
query_rewrite_enabled               = true
query_rewrite_integrity             = enforced
_query_cost_rewrite                 = true
_query_rewrite_2                    = true
_query_rewrite_1                    = true
_query_rewrite_expression           = true
_query_rewrite_jgmigrate            = true
_query_rewrite_fpc                  = true
_query_rewrite_drj                  = true
_full_pwise_join_enabled            = true
_partial_pwise_join_enabled         = true
_left_nested_loops_random           = true
_improved_row_length_enabled        = true
_index_join_enabled                 = true
_enable_type_dep_selectivity        = true
_improved_outerjoin_card            = true
_optimizer_adjust_for_nulls         = true
_optimizer_degree                   = 0
_use_column_stats_for_function      = true
_subquery_pruning_enabled           = true
_subquery_pruning_mv_enabled        = false
_or_expand_nvl_predicate            = true
_like_with_bind_as_equality         = false
_table_scan_cost_plus_one           = true
_cost_equality_semi_join            = true
_default_non_equality_sel_check     = true
_new_initial_join_orders            = true
_oneside_colstat_for_equijoins      = true
_optim_peek_user_binds              = true
_minimal_stats_aggregation          = true
_force_temptables_for_gsets         = false
workarea_size_policy                = auto
_smm_auto_cost_enabled              = true
_gs_anti_semi_join_allowed          = true
_optim_new_default_join_sel         = true
optimizer_dynamic_sampling          = 2
_pre_rewrite_push_pred              = true
_optimizer_new_join_card_computation = true
_union_rewrite_for_gs               = yes_gset_mvs
_generalized_pruning_enabled        = true
_optim_adjust_for_part_skews        = true
_force_datefold_trunc               = false
statistics_level                    = typical
_optimizer_system_stats_usage       = true
skip_unusable_indexes               = true
_remove_aggr_subquery               = true
_optimizer_push_down_distinct       = 0
_dml_monitoring_enabled             = true
_optimizer_undo_changes             = false
_predicate_elimination_enabled      = true
_nested_loop_fudge                  = 100
_project_view_columns               = true
_local_communication_costing_enabled = true
_local_communication_ratio          = 50
_query_rewrite_vop_cleanup          = true
_slave_mapping_enabled              = true
_optimizer_cost_based_transformation = off
_optimizer_mjc_enabled              = true
_right_outer_hash_enable            = true
_spr_push_pred_refspr               = true
_optimizer_cache_stats              = false
_optimizer_cbqt_factor              = 50
_optimizer_squ_bottomup             = true
_fic_area_size                      = 131072
_optimizer_skip_scan_enabled        = true
_optimizer_cost_filter_pred         = false
_optimizer_sortmerge_join_enabled   = true
_optimizer_join_sel_sanity_check    = true
_mmv_query_rewrite_enabled          = true
_bt_mmv_query_rewrite_enabled       = true
_add_stale_mv_to_dependency_list    = true
_distinct_view_unnesting            = false
_optimizer_dim_subq_join_sel        = true
_optimizer_disable_strans_sanity_checks = 0
_optimizer_compute_index_stats      = true
_push_join_union_view2              = true
_optimizer_ignore_hints             = false
_optimizer_random_plan              = 0
_query_rewrite_setopgrw_enable      = true
_optimizer_correct_sq_selectivity   = true
_disable_function_based_index       = false
_optimizer_join_order_control       = 3
_optimizer_cartesian_enabled        = true
_optimizer_starplan_enabled         = true
_extended_pruning_enabled           = true
_optimizer_push_pred_cost_based     = true
_optimizer_null_aware_antijoin      = true
_optimizer_extend_jppd_view_types   = true
_sql_model_unfold_forloops          = run_time
_enable_dml_lock_escalation         = false
_bloom_filter_enabled               = true
_update_bji_ipdml_enabled           = 0
_optimizer_extended_cursor_sharing  = udo
_dm_max_shared_pool_pct             = 1
_optimizer_cost_hjsmj_multimatch    = true
_optimizer_transitivity_retain      = true
_px_pwg_enabled                     = true
optimizer_secure_view_merging       = true
_optimizer_join_elimination_enabled = true
flashback_table_rpi                 = non_fbt
_optimizer_cbqt_no_size_restriction = true
_optimizer_enhanced_filter_push     = true
_optimizer_filter_pred_pullup       = true
_rowsrc_trace_level                 = 0
_simple_view_merging                = true
_optimizer_rownum_pred_based_fkr    = true
_optimizer_better_inlist_costing    = all
_optimizer_self_induced_cache_cost  = false
_optimizer_min_cache_blocks         = 10
_optimizer_or_expansion             = depth
_optimizer_order_by_elimination_enabled = true
_optimizer_outer_to_anti_enabled    = true
_selfjoin_mv_duplicates             = true
_dimension_skip_null                = true
_force_rewrite_enable               = false
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_optimizer_connect_by_cost_based    = true
_gby_hash_aggregation_enabled       = true
_globalindex_pnum_filter_enabled    = true
_px_minus_intersect                 = true
_fix_control_key                    = 0
_force_slave_mapping_intra_part_loads = false
_force_tmp_segment_loads            = false
_query_mmvrewrite_maxpreds          = 10
_query_mmvrewrite_maxintervals      = 5
_query_mmvrewrite_maxinlists        = 5
_query_mmvrewrite_maxdmaps          = 10
_query_mmvrewrite_maxcmaps          = 20
_query_mmvrewrite_maxregperm        = 512
_query_mmvrewrite_maxmergedcmaps    = 50
_query_mmvrewrite_maxqryinlistvals  = 500
_disable_parallel_conventional_load = false
_trace_virtual_columns              = false
_replace_virtual_columns            = true
_virtual_column_overload_allowed    = true
_kdt_buffering                      = true
_first_k_rows_dynamic_proration     = true
_optimizer_sortmerge_join_inequality = true
_optimizer_aw_stats_enabled         = true
_bloom_pruning_enabled              = true
result_cache_mode                   = MANUAL
_px_ual_serial_input                = true
_optimizer_skip_scan_guess          = false
_enable_row_shipping                = true
_row_shipping_threshold             = 80
_row_shipping_explain               = false
transaction_isolation_level         = read_commited
_optimizer_distinct_elimination     = true
_optimizer_multi_level_push_pred    = true
_optimizer_group_by_placement       = true
_optimizer_rownum_bind_default      = 10
_enable_query_rewrite_on_remote_objs = true
_optimizer_extended_cursor_sharing_rel = simple
_optimizer_adaptive_cursor_sharing  = true
_direct_path_insert_features        = 0
_optimizer_improve_selectivity      = true
optimizer_use_pending_statistics = false
_optimizer_enable_density_improvements = true
_optimizer_aw_join_push_enabled     = true
_optimizer_connect_by_combine_sw    = true
_enable_pmo_ctas                    = 0
_optimizer_native_full_outer_join   = force
_bloom_predicate_enabled            = true
_optimizer_enable_extended_stats    = true
_is_lock_table_for_ddl_wait_lock    = 0
_pivot_implementation_method        = choose
optimizer_capture_sql_plan_baselines = false
optimizer_use_sql_plan_baselines    = true
_optimizer_star_trans_min_cost      = 0
_optimizer_star_trans_min_ratio     = 0
_with_subquery                      = OPTIMIZER
_optimizer_fkr_index_cost_bias      = 10
_optimizer_use_subheap              = true
parallel_degree_policy              = manual
parallel_degree                     = 0
parallel_min_time_threshold         = 10
_parallel_time_unit                 = 10
_optimizer_or_expansion_subheap     = true
_optimizer_free_transformation_heap = true
_optimizer_reuse_cost_annotations   = true
_result_cache_auto_size_threshold   = 100
_result_cache_auto_time_threshold   = 1000
_optimizer_nested_rollup_for_gset   = 100
_nlj_batching_enabled               = 1
parallel_query_default_dop          = 0
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = false
flashback_data_archive_internal_cursor = 0 _optimizer_extended_stats_usage_control = 240
_parallel_syspls_obey_force         = true
cell_offload_processing             = true
_rdbms_internal_fplib_enabled       = false
db_file_multiblock_read_count       = 128
_bloom_folding_enabled              = false
_mv_generalized_oj_refresh_opt      = true
cell_offload_compaction             = ADAPTIVE
parallel_degree_limit               = 65535
parallel_force_local                = false
parallel_max_degree                 = 8
total_cpu_count                     = 4
cell_offload_plan_display           = AUTO
_optimizer_coalesce_subqueries      = false
_optimizer_fast_pred_transitivity   = false
_optimizer_fast_access_pred_analysis = false
_optimizer_unnest_disjunctive_subq  = false
_optimizer_unnest_corr_set_subq     = false
_optimizer_distinct_agg_transform   = false
_aggregation_optimization_settings  = 32
_optimizer_connect_by_elim_dups     = false
_optimizer_eliminate_filtering_join = false
_connect_by_use_union_all           = old_plan_mode
dst_upgrade_insert_conv             = true
advanced_queuing_internal_cursor = 0
_optimizer_unnest_all_subqueries    = true
_bloom_predicate_pushdown_to_storage = true
_bloom_vector_elements              = 0
_bloom_pushing_max                  = 524288
parallel_autodop                    = 0
parallel_ddldml                     = 0
_parallel_cluster_cache_policy      = adaptive
_parallel_scalability               = 50
iot_internal_cursor                 = 0
_optimizer_instance_count           = 0
_optimizer_connect_by_cb_whr_only   = false
_suppress_scn_chk_for_cqn           = nosuppress_1466
_optimizer_join_factorization       = false
_optimizer_use_cbqt_star_transformation = false
_optimizer_table_expansion          = false
_and_pruning_enabled                = false
_deferred_constant_folding_mode     = DEFAULT
_optimizer_distinct_placement       = false
partition_pruning_internal_cursor   = 0
parallel_hinted                     = none
_sql_compatibility                  = 0
_optimizer_use_feedback             = false
_optimizer_try_st_before_jppd       = false
Bug Fix Control Environment
    fix  3834770 = 1
    fix  3746511 = enabled
    fix  4519016 = enabled
    fix  3118776 = enabled
    fix  4488689 = enabled
    fix  2194204 = disabled
    fix  2660592 = enabled
    fix  2320291 = enabled
    fix  2324795 = enabled
    fix  4308414 = enabled
    fix  3499674 = disabled
    fix  4569940 = enabled
    fix  4631959 = enabled
    fix  4519340 = enabled
    fix  4550003 = enabled
    fix  1403283 = enabled
    fix  4554846 = enabled
    fix  4602374 = enabled
    fix  4584065 = enabled
    fix  4545833 = enabled
    fix  4611850 = enabled
    fix  4663698 = enabled
    fix  4663804 = enabled
    fix  4666174 = enabled
    fix  4567767 = enabled
    fix  4556762 = 15
    fix  4728348 = enabled
    fix  4708389 = enabled
    fix  4175830 = enabled
    fix  4752814 = enabled
    fix  4583239 = enabled
    fix  4386734 = enabled
    fix  4887636 = enabled
    fix  4483240 = enabled
    fix  4872602 = disabled
    fix  4711525 = enabled
    fix  4545802 = enabled
    fix  4605810 = enabled
    fix  4704779 = enabled
    fix  4900129 = enabled
    fix  4924149 = enabled
    fix  4663702 = enabled
    fix  4878299 = enabled
    fix  4658342 = enabled
    fix  4881533 = enabled
    fix  4676955 = enabled
    fix  4273361 = enabled
    fix  4967068 = enabled
    fix  4969880 = disabled
    fix  5005866 = enabled
    fix  5015557 = enabled
    fix  4705343 = enabled
    fix  4904838 = enabled
    fix  4716096 = enabled
    fix  4483286 = disabled
    fix  4722900 = enabled
    fix  4615392 = enabled
    fix  5096560 = enabled
    fix  5029464 = enabled
    fix  4134994 = enabled
    fix  4904890 = enabled
    fix  5104624 = enabled
    fix  5014836 = enabled
    fix  4768040 = enabled
    fix  4600710 = enabled
    fix  5129233 = enabled
    fix  4595987 = enabled
    fix  4908162 = enabled
    fix  5139520 = enabled
    fix  5084239 = enabled
    fix  5143477 = disabled
    fix  2663857 = enabled
    fix  4717546 = enabled
    fix  5240264 = disabled
    fix  5099909 = enabled
    fix  5240607 = enabled
    fix  5195882 = enabled
    fix  5220356 = enabled
    fix  5263572 = enabled
    fix  5385629 = enabled
    fix  5302124 = enabled
    fix  5391942 = enabled
    fix  5384335 = enabled
    fix  5482831 = enabled
    fix  4158812 = enabled
    fix  5387148 = enabled
    fix  5383891 = enabled
    fix  5466973 = enabled
    fix  5396162 = enabled
    fix  5394888 = enabled
    fix  5395291 = enabled
    fix  5236908 = enabled
    fix  5509293 = enabled
    fix  5449488 = enabled
    fix  5567933 = enabled
    fix  5570494 = enabled
    fix  5288623 = enabled
    fix  5505995 = enabled
    fix  5505157 = enabled
    fix  5112460 = enabled
    fix  5554865 = enabled
    fix  5112260 = enabled
    fix  5112352 = enabled
    fix  5547058 = enabled
    fix  5618040 = enabled
    fix  5585313 = enabled
    fix  5547895 = enabled
    fix  5634346 = enabled
    fix  5620485 = enabled
    fix  5483301 = enabled
    fix  5657044 = enabled
    fix  5694984 = enabled
    fix  5868490 = enabled
    fix  5650477 = enabled
    fix  5611962 = enabled
    fix  4279274 = enabled
    fix  5741121 = enabled
    fix  5714944 = enabled
    fix  5391505 = enabled
    fix  5762598 = enabled
    fix  5578791 = enabled
    fix  5259048 = enabled
    fix  5882954 = enabled
    fix  2492766 = enabled
    fix  5707608 = enabled
    fix  5891471 = enabled
    fix  5884780 = enabled
    fix  5680702 = enabled
    fix  5371452 = enabled
    fix  5838613 = enabled
    fix  5949981 = enabled
    fix  5624216 = enabled
    fix  5741044 = enabled
    fix  5976822 = enabled
    fix  6006457 = enabled
    fix  5872956 = enabled
    fix  5923644 = enabled
    fix  5943234 = enabled
    fix  5844495 = enabled
    fix  4168080 = enabled
    fix  6020579 = enabled
    fix  5842686 = disabled
    fix  5996801 = enabled
    fix  5593639 = enabled
    fix  6133948 = enabled
    fix  3151991 = enabled
    fix  6146906 = enabled
    fix  6239909 = enabled
    fix  6267621 = enabled
    fix  5909305 = enabled
    fix  6279918 = enabled
    fix  6141818 = enabled
    fix  6151963 = enabled
    fix  6251917 = enabled
    fix  6282093 = enabled
    fix  6119510 = enabled
    fix  6119382 = enabled
    fix  3801750 = enabled
    fix  5705630 = disabled
    fix  5944076 = enabled
    fix  5406763 = enabled
    fix  6070954 = enabled
    fix  6282944 = enabled
    fix  6138746 = enabled
    fix  6082745 = enabled
    fix  3426050 = enabled
    fix   599680 = enabled
    fix  6062266 = enabled
    fix  6087237 = enabled
    fix  6122894 = enabled
    fix  6377505 = disabled
    fix  5893768 = enabled
    fix  6163564 = enabled
    fix  6073325 = enabled
    fix  6188881 = enabled
    fix  6007259 = enabled
    fix  6239971 = enabled
    fix  5284200 = enabled
    fix  6042205 = enabled
    fix  6051211 = enabled
    fix  6434668 = enabled
    fix  6438752 = disabled
    fix  5936366 = disabled
    fix  6439032 = enabled
    fix  6438892 = disabled
    fix  6006300 = disabled
    fix  5947231 = enabled
    fix  5416118 = 1
    fix  6365442 = 1
    fix  6239039 = enabled
    fix  6502845 = disabled
    fix  6913094 = disabled
    fix  6029469 = enabled
    fix  5919513 = enabled
    fix  6057611 = enabled
    fix  6469667 = enabled
    fix  6608941 = disabled
    fix  6368066 = disabled
    fix  6329318 = enabled
    fix  6656356 = enabled
    fix  4507997 = enabled
    fix  6671155 = enabled
    fix  6694548 = enabled
    fix  6688200 = enabled
    fix  6612471 = disabled
    fix  6708183 = disabled
    fix  6326934 = enabled
    fix  6520717 = disabled
    fix  6714199 = enabled
    fix  6681545 = disabled
    fix  6748058 = disabled
    fix  6167716 = disabled
    fix  6674254 = enabled
    fix  6468287 = enabled
    fix  6503543 = disabled
    fix  6808773 = disabled
    fix  6766962 = disabled
    fix  6120483 = enabled
    fix  6670551 = disabled
    fix  6771838 = enabled
    fix  6626018 = disabled
    fix  6530596 = enabled
    fix  6778642 = enabled
    fix  6699059 = disabled
    fix  6376551 = disabled
    fix  6429113 = enabled
    fix  6782437 = enabled
    fix  6776808 = disabled
    fix  6765823 = disabled
    fix  6768660 = disabled
    fix  6782665 = disabled
    fix  6610822 = enabled
    fix  6514189 = enabled
    fix  6818410 = disabled
    fix  6827696 = disabled
    fix  6773613 = enabled
    fix  5902962 = enabled
    fix  6956212 = enabled
    fix  3056297 = enabled
    fix  6440977 = disabled
    fix  6972291 = disabled
    fix  6904146 = enabled
    fix  6221403 = enabled
    fix  5475051 = enabled
    fix  6845871 = enabled
    fix  5468809 = enabled
    fix  6917633 = disabled
    fix  4444536 = disabled
    fix  6955210 = enabled
    fix  6994194 = enabled
    fix  6399597 = disabled
    fix  6951776 = disabled
    fix  5648287 = 3
    fix  6987082 = disabled
    fix  7132036 = enabled
    fix  6980350 = disabled
    fix  5199213 = enabled
    fix  7138405 = enabled
    fix  7148689 = enabled
    fix  6820988 = enabled
    fix  7032684 = disabled
    fix  6617866 = enabled
    fix  7155968 = disabled
    fix  7127980 = disabled
    fix  6982954 = disabled
    fix  7241819 = enabled
    fix  6897034 = enabled
    fix  7236148 = enabled
    fix  7298570 = enabled
    fix  7249095 = enabled
    fix  7314499 = disabled
    fix  7324224 = disabled
    fix  7289023 = enabled
    fix  7237571 = enabled
    fix  7116357 = enabled
    fix  7345484 = enabled
    fix  7375179 = disabled
    fix  6430500 = disabled
    fix  5897486 = disabled
    fix  6774209 = disabled
    fix  7306637 = disabled
    fix  6451322 = enabled
    fix  7208131 = enabled
    fix  7388652 = disabled
    fix  7127530 = disabled
    fix  6751206 = enabled
    fix  6669103 = enabled
    fix  7430474 = enabled
    fix  6990305 = enabled
    fix  7043307 = disabled
    fix  6921505 = disabled
    fix  7388457 = disabled
    fix  3120429 = enabled
    fix  7452823 = disabled
    fix  6838105 = enabled
    fix  6769711 = disabled
    fix  7170213 = enabled
    fix  6528872 = enabled
    fix  7295298 = enabled
    fix  5922070 = enabled
    fix  7259468 = enabled
    fix  6418552 = enabled
    fix  4619997 = enabled
    fix  7524366 = disabled
    fix  6942476 = disabled
    fix  6418771 = enabled
    fix  7375077 = enabled
    fix  5400639 = disabled
    fix  4570921 = disabled
    fix  7426911 = disabled
    fix  5099019 = disabled
    fix  7528216 = enabled
    fix  7521266 = enabled
    fix  7385140 = disabled
    fix  7576516 = enabled
    fix  7573526 = enabled
    fix  7576476 = enabled
    fix  7165898 = enabled
    fix  7263214 = enabled
    fix  3320140 = enabled
    fix  7555510 = enabled
    fix  7613118 = disabled
    fix  7597059 = enabled
    fix  7558911 = disabled
    fix  5520732 = disabled
    fix  7679490 = disabled
    fix  7449971 = disabled
    fix  3628118 = enabled
    fix  4370840 = enabled
    fix  7281191 = enabled
    fix  7519687 = enabled
    fix  5029592 = 0
    fix  6012093 = 1
    fix  6053861 = disabled
    fix  6941515 = disabled
    fix  7696414 = enabled
    fix  7272039 = enabled
    fix  7834811 = enabled
    fix  7640597 = enabled
    fix  7341616 = enabled
    fix  7168184 = disabled
    fix   399198 = disabled
    fix  7831070 = enabled
    fix  7676897 = disabled
    fix  7414637 = disabled
    fix  7585456 = enabled
    fix  8202421 = disabled
    fix  7658097 = disabled
    fix  8251486 = disabled
    fix  7132684 = enabled
    fix  7512227 = enabled
    fix  6972987 = disabled
    fix  7199035 = disabled
    fix  8243446 = disabled
    fix  7650462 = disabled
    fix  6720701 = enabled
    fix  7592673 = enabled
    fix  7718694 = disabled
    fix  7534027 = disabled
    fix  7708267 = enabled
    fix  5716785 = disabled
    fix  7356191 = enabled
    fix  7679161 = disabled
    fix  7597159 = disabled
    fix  7499258 = enabled
    fix  8328363 = disabled
    fix  7452863 = disabled
    fix  8284930 = disabled
    fix  7298626 = disabled
    fix  7657126 = enabled
    fix  8371884 = enabled
    fix  8318020 = enabled
    fix  8255423 = enabled
    fix  7135745 = enabled
    fix  8356253 = disabled
    fix  7534257 = enabled
    fix  8323407 = enabled
    fix  7539815 = enabled
    fix  8289316 = enabled
    fix  8447850 = disabled
    fix  7675944 = enabled
    fix  8355120 = disabled
    fix  7176746 = enabled
    fix  8442891 = disabled
    fix  8373261 = disabled
    fix  7679164 = disabled
    fix  7670533 = enabled
    fix  8408665 = disabled
    fix  8491399 = disabled
    fix  8348392 = disabled
    fix  8348585 = enabled
    fix  8508056 = disabled
    fix  8335178 = disabled
    fix  8515269 = disabled
    fix  8247017 = enabled
    fix  7325597 = enabled
    fix  8531490 = disabled
    fix  6163600 = enabled
    fix  8589278 = disabled
    fix  8557992 = disabled
    fix  7556098 = enabled
    fix  8580883 = enabled
    fix  5892599 = disabled
    fix  8609714 = enabled
    fix  8514561 = enabled
    fix  8619631 = disabled


Query Block Registry:
SEL$1 0x6d3604 (PARSER) [FINAL]

:

    call(in-use=143084, alloc=653452), compile(in-use=197532, alloc=256168), execution(in-use=28564, alloc=32592)

End of Optimizer State Dump
Dumping Hints


  • END SQL Statement Dump ======================
Received on Wed Nov 04 2009 - 06:04:45 CST

Original text of this message