Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
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
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
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
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
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
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: 1876359817ColGroup 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: 2734599482Outer 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.00Inner 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.35Bytes: 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: 11160293933334kkofmx: 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
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
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
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: 1082758031Join 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: 1447842668Outer 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.05Bytes: 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.953821Cost: 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: 1307552963017kkofmx: 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
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: 1845668ColGroup 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: 1681099831Outer 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: 871156375Join 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: 20442kkoqbc-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")
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 = trueoptimizer_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 = chooseoptimizer_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 = falseflashback_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 = trueadvanced_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 = falseBug 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 ======================