performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
in [Oracle]
|
From: lsllcm on 4 Nov 2009 07:03 part-3 =========================================================================== *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 32.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 62 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 300498.24 Resp: 300498.24 Degree: 1 Cost_io: 298969.00 Cost_cpu: 49474989829 Resp_io: 298969.00 Resp_cpu: 49474989829 Access Path: index (RangeScan) Index: F4FEEITEM_PK resc_io: 486475.00 resc_cpu: 4932290674 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 NL Join : Cost: 1557209.85 Resp: 1557209.85 Degree: 1 Cost_io: 1556722.00 Cost_cpu: 15783340331 Resp_io: 1556722.00 Resp_cpu: 15783340331 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 38254.00 resc_cpu: 1740311716 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 NL Join : Cost: 122586.93 Resp: 122586.93 Degree: 1 Cost_io: 122414.80 Cost_cpu: 5569007664 Resp_io: 122414.80 Resp_cpu: 5569007664 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 122586.93 resc: 122586.93 resc_io: 122414.80 resc_cpu: 5569007664 resp: 122586.93 resp_io: 122414.80 resc_cpu: 5569007664 Join Card: 38190277.832992 = = outer (32.001126) * inner (1193404.203335) * sel (1.000000) Join Card - Rounded: 38190278 Computed: 38190277.83 Join order aborted: cost > best plan cost *********************** ****** Recost for ORDER BY (using index) ************ First K Rows: switch to Kmode plans Access path analysis for F4INVOICE *************************************** SINGLE TABLE ACCESS PATH (First K Rows) Single Table Cardinality Estimation for F4INVOICE[FINV] Table: F4INVOICE Alias: FINV Card: Original: 2.000000 Rounded: 2 Computed: 2.00 Non Adjusted: 2.00 Access Path: TableScan Cost: 2.00 Resp: 2.00 Degree: 0 Cost_io: 2.00 Cost_cpu: 7801 Resp_io: 2.00 Resp_cpu: 7801 Access Path: index (RangeScan) Index: F4INVOICE_DATE_IX resc_io: 4.00 resc_cpu: 29466 ix_sel: 1.000000 ix_sel_with_filters: 1.000000 Cost: 1.00 Resp: 1.00 Degree: 1 Best:: AccessPath: IndexRange Index: F4INVOICE_DATE_IX Cost: 1.00 Degree: 1 Resp: 1.00 Card: 2.00 Bytes: 24 Join order[8]: F4INVOICE[FINV]#1 SETDETAILS[SD]#0 F4FEEITEM[F4]#3 X4FEEITEM_INVOICE[XFI]#2 *************** Now joining: SETDETAILS[SD]#0 *************** NL Join Outer table: Card: 2.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 24 Access path analysis for SETDETAILS Inner table: SETDETAILS Alias: SD Access Path: TableScan NL Join: Cost: 1256.97 Resp: 1256.97 Degree: 1 Cost_io: 1250.00 Cost_cpu: 225379544 Resp_io: 1250.00 Resp_cpu: 225379544 Access Path: index (RangeScan) Index: SETDETAILS_ADDR_IX resc_io: 3441.00 resc_cpu: 172638228 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 690.27 Resp: 690.27 Degree: 1 Cost_io: 689.20 Cost_cpu: 34530592 Resp_io: 689.20 Resp_cpu: 34530592 Access Path: index (RangeScan) Index: SETDETAILS_IX resc_io: 9269.00 resc_cpu: 214141980 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 1856.12 Resp: 1856.12 Degree: 1 Cost_io: 1854.80 Cost_cpu: 42831343 Resp_io: 1854.80 Resp_cpu: 42831343 Access Path: index (RangeScan) Index: SETDETAILS_PID_IX resc_io: 236253.00 resc_cpu: 1830594917 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 47262.92 Resp: 47262.92 Degree: 1 Cost_io: 47251.60 Cost_cpu: 366121930 Resp_io: 47251.60 Resp_cpu: 366121930 Access Path: index (RangeScan) Index: SETDETAILS_PK resc_io: 224862.00 resc_cpu: 1749474594 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 44984.22 Resp: 44984.22 Degree: 1 Cost_io: 44973.40 Cost_cpu: 349897865 Resp_io: 44973.40 Resp_cpu: 349897865 Access Path: index (RangeScan) Index: SETDETAILS_SETID1_IX resc_io: 5833.00 resc_cpu: 189672712 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 1168.77 Resp: 1168.77 Degree: 1 Cost_io: 1167.60 Cost_cpu: 37937489 Resp_io: 1167.60 Resp_cpu: 37937489 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: 4.00 resc_cpu: 36136 ix_sel: 0.000065 ix_sel_with_filters: 0.000065 NL Join : Cost: 2.00 Resp: 2.00 Degree: 1 Cost_io: 2.00 Cost_cpu: 10174 Resp_io: 2.00 Resp_cpu: 10174 Access Path: index (RangeScan) Index: SETDETAILS_STAT_IX resc_io: 3441.00 resc_cpu: 172638228 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 690.27 Resp: 690.27 Degree: 1 Cost_io: 689.20 Cost_cpu: 34530592 Resp_io: 689.20 Resp_cpu: 34530592 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 2.00 resc: 2.00 resc_io: 2.00 resc_cpu: 10174 resp: 2.00 resp_io: 2.00 resc_cpu: 10174 Join Card: 32.001126 = = outer (2.000000) * inner (16.000563) * sel (1.000000) Join Card - Rounded: 32 Computed: 32.00 Best:: JoinMethod: NestedLoop Cost: 2.00 Degree: 1 Resp: 2.00 Card: 32.00 Bytes: 62 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 32.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 62 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 300498.24 Resp: 300498.24 Degree: 1 Cost_io: 298969.00 Cost_cpu: 49474989829 Resp_io: 298969.00 Resp_cpu: 49474989829 Access Path: index (RangeScan) Index: F4FEEITEM_PK resc_io: 486475.00 resc_cpu: 4932290674 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 NL Join : Cost: 1557209.85 Resp: 1557209.85 Degree: 1 Cost_io: 1556722.00 Cost_cpu: 15783340331 Resp_io: 1556722.00 Resp_cpu: 15783340331 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 38254.00 resc_cpu: 1740311716 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 NL Join : Cost: 122586.93 Resp: 122586.93 Degree: 1 Cost_io: 122414.80 Cost_cpu: 5569007664 Resp_io: 122414.80 Resp_cpu: 5569007664 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 122586.93 resc: 122586.93 resc_io: 122414.80 resc_cpu: 5569007664 resp: 122586.93 resp_io: 122414.80 resc_cpu: 5569007664 Join Card: 38190277.832992 = = outer (32.001126) * inner (1193404.203335) * sel (1.000000) Join Card - Rounded: 38190278 Computed: 38190277.83 Join order aborted: cost > best plan cost *********************** ****** Recost for ORDER BY (using index) ************ First K Rows: switch to Kmode plans Access path analysis for F4INVOICE *************************************** SINGLE TABLE ACCESS PATH (First K Rows) Single Table Cardinality Estimation for F4INVOICE[FINV] Table: F4INVOICE Alias: FINV Card: Original: 2.000000 Rounded: 2 Computed: 2.00 Non Adjusted: 2.00 Access Path: TableScan Cost: 2.00 Resp: 2.00 Degree: 0 Cost_io: 2.00 Cost_cpu: 7801 Resp_io: 2.00 Resp_cpu: 7801 Access Path: index (RangeScan) Index: F4INVOICE_BATCH_DATE_IX resc_io: 4.00 resc_cpu: 29466 ix_sel: 1.000000 ix_sel_with_filters: 1.000000 Cost: 1.00 Resp: 1.00 Degree: 1 Best:: AccessPath: IndexRange Index: F4INVOICE_BATCH_DATE_IX Cost: 1.00 Degree: 1 Resp: 1.00 Card: 2.00 Bytes: 24 Join order[8]: F4INVOICE[FINV]#1 SETDETAILS[SD]#0 F4FEEITEM[F4]#3 X4FEEITEM_INVOICE[XFI]#2 *************** Now joining: SETDETAILS[SD]#0 *************** NL Join Outer table: Card: 2.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 24 Access path analysis for SETDETAILS Inner table: SETDETAILS Alias: SD Access Path: TableScan NL Join: Cost: 1256.97 Resp: 1256.97 Degree: 1 Cost_io: 1250.00 Cost_cpu: 225379544 Resp_io: 1250.00 Resp_cpu: 225379544 Access Path: index (RangeScan) Index: SETDETAILS_ADDR_IX resc_io: 3441.00 resc_cpu: 172638228 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 690.27 Resp: 690.27 Degree: 1 Cost_io: 689.20 Cost_cpu: 34530592 Resp_io: 689.20 Resp_cpu: 34530592 Access Path: index (RangeScan) Index: SETDETAILS_IX resc_io: 9269.00 resc_cpu: 214141980 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 1856.12 Resp: 1856.12 Degree: 1 Cost_io: 1854.80 Cost_cpu: 42831343 Resp_io: 1854.80 Resp_cpu: 42831343 Access Path: index (RangeScan) Index: SETDETAILS_PID_IX resc_io: 236253.00 resc_cpu: 1830594917 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 47262.92 Resp: 47262.92 Degree: 1 Cost_io: 47251.60 Cost_cpu: 366121930 Resp_io: 47251.60 Resp_cpu: 366121930 Access Path: index (RangeScan) Index: SETDETAILS_PK resc_io: 224862.00 resc_cpu: 1749474594 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 44984.22 Resp: 44984.22 Degree: 1 Cost_io: 44973.40 Cost_cpu: 349897865 Resp_io: 44973.40 Resp_cpu: 349897865 Access Path: index (RangeScan) Index: SETDETAILS_SETID1_IX resc_io: 5833.00 resc_cpu: 189672712 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 1168.77 Resp: 1168.77 Degree: 1 Cost_io: 1167.60 Cost_cpu: 37937489 Resp_io: 1167.60 Resp_cpu: 37937489 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: 4.00 resc_cpu: 36136 ix_sel: 0.000065 ix_sel_with_filters: 0.000065 NL Join : Cost: 2.00 Resp: 2.00 Degree: 1 Cost_io: 2.00 Cost_cpu: 10174 Resp_io: 2.00 Resp_cpu: 10174 Access Path: index (RangeScan) Index: SETDETAILS_STAT_IX resc_io: 3441.00 resc_cpu: 172638228 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 NL Join : Cost: 690.27 Resp: 690.27 Degree: 1 Cost_io: 689.20 Cost_cpu: 34530592 Resp_io: 689.20 Resp_cpu: 34530592 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 2.00 resc: 2.00 resc_io: 2.00 resc_cpu: 10174 resp: 2.00 resp_io: 2.00 resc_cpu: 10174 Join Card: 32.001126 = = outer (2.000000) * inner (16.000563) * sel (1.000000) Join Card - Rounded: 32 Computed: 32.00 Best:: JoinMethod: NestedLoop Cost: 2.00 Degree: 1 Resp: 2.00 Card: 32.00 Bytes: 62 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 32.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 62 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 300498.24 Resp: 300498.24 Degree: 1 Cost_io: 298969.00 Cost_cpu: 49474989829 Resp_io: 298969.00 Resp_cpu: 49474989829 Access Path: index (RangeScan) Index: F4FEEITEM_PK resc_io: 486475.00 resc_cpu: 4932290674 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 NL Join : Cost: 1557209.85 Resp: 1557209.85 Degree: 1 Cost_io: 1556722.00 Cost_cpu: 15783340331 Resp_io: 1556722.00 Resp_cpu: 15783340331 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 38254.00 resc_cpu: 1740311716 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 NL Join : Cost: 122586.93 Resp: 122586.93 Degree: 1 Cost_io: 122414.80 Cost_cpu: 5569007664 Resp_io: 122414.80 Resp_cpu: 5569007664 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 122586.93 resc: 122586.93 resc_io: 122414.80 resc_cpu: 5569007664 resp: 122586.93 resp_io: 122414.80 resc_cpu: 5569007664 Join Card: 38190277.832992 = = outer (32.001126) * inner (1193404.203335) * sel (1.000000) Join Card - Rounded: 38190278 Computed: 38190277.83 Join order aborted: cost > best plan cost *********************** Join order[9]: F4INVOICE[FINV]#1 X4FEEITEM_INVOICE[XFI]#2 SETDETAILS [SD]#0 F4FEEITEM[F4]#3 *************** Now joining: X4FEEITEM_INVOICE[XFI]#2 *************** NL Join Outer table: Card: 248742.29 Cost: 375.16 Resp: 375.16 Degree: 1 Bytes: 24 Access path analysis for X4FEEITEM_INVOICE Inner table: X4FEEITEM_INVOICE Alias: XFI Access Path: TableScan NL Join: Cost: 1719952146.00 Resp: 1719952146.00 Degree: 1 Cost_io: 1710935948.70 Cost_cpu: 291698039955363 Resp_io: 1710935948.70 Resp_cpu: 291698039955363 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"."INVOICE_NBR"="XFI"."INVOICE_NBR" OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (AllEqJoinGuess) Index: X4FEEITEM_INVOICE_NBR_IX resc_io: 1.00 resc_cpu: 12613 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: 25259.05 Resp: 25259.05 Degree: 1 Cost_io: 25248.90 Cost_cpu: 328507427 Resp_io: 25248.90 Resp_cpu: 328507427 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_PK resc_io: 1209.00 resc_cpu: 252625462 ix_sel: 0.954312 ix_sel_with_filters: 0.000004 ***** 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: 30267512.88 Resp: 30267512.88 Degree: 1 Cost_io: 30073282.50 Cost_cpu: 6283871048957 Resp_io: 30073282.50 Resp_cpu: 6283871048957 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 25259.05 resc: 25259.05 resc_io: 25248.90 resc_cpu: 328507427 resp: 25259.05 resp_io: 25248.90 resc_cpu: 328507427 Join Card: 1188973.610473 = = outer (248742.292433) * inner (1187146.275001) * sel (0.000004) Join Card - Rounded: 1188974 Computed: 1188973.61 Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 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: 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 SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 22528 Row size: 155 Total Rows: 1187146 Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204 Total IO sort cost: 34732 Total CPU sort cost: 1666431753 Total Temp space used: 442065000 SM join: Resc: 39945.11 Resp: 39945.11 [multiMatchCost=0.01] SM Join SM cost: 39945.11 resc: 39945.11 resc_io: 39880.90 resc_cpu: 2077290535 resp: 39945.11 resp_io: 39880.90 resp_cpu: 2077290535 Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 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: 8457.76 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 20723 ppasses: 1 Hash join: Resc: 11872.29 Resp: 11872.29 [multiMatchCost=0.01] HA Join HA cost: 11872.29 resc: 11872.29 resc_io: 11855.90 resc_cpu: 530295770 resp: 11872.29 resp_io: 11855.90 resp_cpu: 530295770 Best:: JoinMethod: Hash Cost: 11872.29 Degree: 1 Resp: 11872.29 Card: 1188973.61 Bytes: 155 *************** Now joining: SETDETAILS[SD]#0 *************** NL Join Outer table: Card: 1188973.61 Cost: 11872.29 Resp: 11872.29 Degree: 1 Bytes: 155 Access path analysis for SETDETAILS Inner table: SETDETAILS Alias: SD Access Path: TableScan NL Join: Cost: 1181492887.32 Resp: 1181492887.32 Degree: 1 Cost_io: 1174074141.90 Cost_cpu: 240016209170854 Resp_io: 1174074141.90 Resp_cpu: 240016209170854 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: 455518621.62 Resp: 455518621.62 Degree: 1 Cost_io: 454556616.10 Cost_cpu: 31123445457784 Resp_io: 454556616.10 Resp_cpu: 31123445457784 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: 51793091.68 Resp: 51793091.68 Degree: 1 Cost_io: 51494430.10 Cost_cpu: 9662499161372 Resp_io: 51494430.10 Resp_cpu: 9662499161372 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: 130800.81 Resp: 130800.81 Degree: 1 Cost_io: 130753.30 Cost_cpu: 1537047052 Resp_io: 130753.30 Resp_cpu: 1537047052 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: 42145484801.05 Resp: 42145484801.05 Degree: 1 Cost_io: 42135348057.50 Cost_cpu: 327950700860227 Resp_io: 42135348057.50 Resp_cpu: 327950700860227 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: 922412563.68 Resp: 922412563.68 Degree: 1 Cost_io: 921347808.50 Cost_cpu: 34447670926989 Resp_io: 921347808.50 Resp_cpu: 34447670926989 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: 130841.98 Resp: 130841.98 Degree: 1 Cost_io: 130753.30 Cost_cpu: 2869178840 Resp_io: 130753.30 Resp_cpu: 2869178840 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: 455399698.05 Resp: 455399698.05 Degree: 1 Cost_io: 454437718.70 Cost_cpu: 31122598737083 Resp_io: 454437718.70 Resp_cpu: 31122598737083 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 130800.81 resc: 130800.81 resc_io: 130753.30 resc_cpu: 1537047052 resp: 130800.81 resp_io: 130753.30 resc_cpu: 1537047052 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: 90789.885708 = = outer (1188973.610473) * inner (25.332653) * sel (0.003014) Join Card - Rounded: 90790 Computed: 90789.89 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 11872.29 card 1188973.61 bytes: 155 deg: 1 resp: 11872.29 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: 26348 Row size: 181 Total Rows: 1188974 Initial runs: 6 Merge passes: 1 IO Cost / pass: 14274 Total IO sort cost: 40622 Total CPU sort cost: 1762283928 Total Temp space used: 499508000 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: 52550.76 Resp: 52550.76 [multiMatchCost=0.00] SM Join SM cost: 52550.76 resc: 52550.76 resc_io: 52478.90 resc_cpu: 2324941615 resp: 52550.76 resp_io: 52478.90 resp_cpu: 2324941615 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 11872.29 card 1188973.61 bytes: 155 deg: 1 resp: 11872.29 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: 9393.09 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 24239 probefrag: 1 ppasses: 1 Hash join: Resc: 21266.66 Resp: 21266.66 [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: 11872.29 card: 1188973.61 bytes: 155 deg: 1 resp: 11872.29 using dmeth: 2 #groups: 1 Cost per ptn: 4.18 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1 probefrag: 24239 ppasses: 1 Hash join: Resc: 11877.47 Resp: 11877.47 [multiMatchCost=0.00] HA Join HA cost: 11877.47 swapped resc: 11877.47 resc_io: 11856.90 resc_cpu: 665377273 resp: 11877.47 resp_io: 11856.90 resp_cpu: 665377273 Best:: JoinMethod: Hash Cost: 11877.47 Degree: 1 Resp: 11877.47 Card: 90789.89 Bytes: 193 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 90789.89 Cost: 11877.47 Resp: 11877.47 Degree: 1 Bytes: 193 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 879795704.16 Resp: 879795704.16 Degree: 1 Cost_io: 875305008.90 Cost_cpu: 145285973761220 Resp_io: 875305008.90 Resp_cpu: 145285973761220 kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (UniqueScan) Index: F4FEEITEM_PK resc_io: 1.00 resc_cpu: 9201 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: 20959.05 Resp: 20959.05 Degree: 1 Cost_io: 20935.90 Cost_cpu: 748917146 Resp_io: 20935.90 Resp_cpu: 748917146 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 (AllEqUnique) Index: F4FEEITEM_PK resc_io: 1.00 resc_cpu: 9201 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: 20959.05 Resp: 20959.05 Degree: 1 Cost_io: 20935.90 Cost_cpu: 748917146 Resp_io: 20935.90 Resp_cpu: 748917146 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 34070.00 resc_cpu: 1757393371 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00 ***** End Logdef Adjustment ****** NL Join : Cost: 309826577.75 Resp: 309826577.75 Degree: 1 Cost_io: 309333386.90 Cost_cpu: 15956039790766 Resp_io: 309333386.90 Resp_cpu: 15956039790766 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 20959.05 resc: 20959.05 resc_io: 20935.90 resc_cpu: 748917146 resp: 20959.05 resp_io: 20935.90 resc_cpu: 748917146 ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] = 192 F4FEEITEM[F4] = 1291140 Join selectivity using 1 ColGroups: 0.000293 (sel1 = 0.000000, sel2 = 0.000000) Join Card: 32739848.219760 = = outer (90789.885708) * inner (1231517.000000) * sel (0.000293) Join Card - Rounded: 32739848 Computed: 32739848.22 Outer table: SETDETAILS Alias: SD resc: 11877.47 card 90789.89 bytes: 193 deg: 1 resp: 11877.47 Inner table: F4FEEITEM Alias: F4 resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05 using dmeth: 2 #groups: 1 SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 2479 Row size: 223 Total Rows: 90790 Initial runs: 2 Merge passes: 1 IO Cost / pass: 1344 Total IO sort cost: 3823 Total CPU sort cost: 160772130 Total Temp space used: 38151000 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: 33051.12 Resp: 33051.12 [multiMatchCost=97.39] SM Join SM cost: 33051.12 resc: 33051.12 resc_io: 32880.40 resc_cpu: 5523166325 resp: 33051.12 resp_io: 32880.40 resp_cpu: 5523166325 Outer table: SETDETAILS Alias: SD resc: 11877.47 card 90789.89 bytes: 193 deg: 1 resp: 11877.47 Inner table: F4FEEITEM Alias: F4 resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05 using dmeth: 2 #groups: 1 Cost per ptn: 4032.85 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 2272 probefrag: 8118 ppasses: 1 Hash join: Resc: 19960.76 Resp: 19960.76 [multiMatchCost=97.39] HA Join HA cost: 19960.76 resc: 19960.76 resc_io: 19827.40 resc_cpu: 4314521554 resp: 19960.76 resp_io: 19827.40 resp_cpu: 4314521554 ORDER BY sort SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 1078235 Row size: 269 Total Rows: 32739848 Initial runs: 210 Merge passes: 1 IO Cost / pass: 584046 Total IO sort cost: 1662281 Total CPU sort cost: 63409659445 Total Temp space used: 8651785000 Best:: JoinMethod: Hash Cost: 1684201.71 Degree: 1 Resp: 1684201.71 Card: 32739848.22 Bytes: 235 *********************** Best so far: Table#: 1 cost: 375.1567 card: 248742.2924 bytes: 5969808 Table#: 2 cost: 11872.2911 card: 1188973.6105 bytes: 184290970 Table#: 0 cost: 11877.4664 card: 90789.8857 bytes: 17522470 Table#: 3 cost: 1684201.7105 card: 32739848.2198 bytes: 7693864280 *********************** First K Rows: K = 100.00, N = 32739848.00 First K Rows: old pf = 0.0000040, new pf = 0.0000121 Access path analysis for F4INVOICE *************************************** SINGLE TABLE ACCESS PATH (First K Rows) Single Table Cardinality Estimation for F4INVOICE[FINV] Table: F4INVOICE Alias: FINV Card: Original: 4.000000 Rounded: 3 Computed: 3.25 Non Adjusted: 3.25 Access Path: TableScan Cost: 2.00 Resp: 2.00 Degree: 0 Cost_io: 2.00 Cost_cpu: 8341 Resp_io: 2.00 Resp_cpu: 8341 Access Path: index (RangeScan) Index: F4INVOICE_BATCH_DATE_IX resc_io: 4.00 resc_cpu: 30446 ix_sel: 0.813255 ix_sel_with_filters: 0.813255 Cost: 1.00 Resp: 1.00 Degree: 1 Access Path: index (RangeScan) Index: F4INVOICE_BATCH_NBR_IX resc_io: 4.00 resc_cpu: 30446 ix_sel: 0.813255 ix_sel_with_filters: 0.813255 Cost: 1.00 Resp: 1.00 Degree: 1 Access Path: index (RangeScan) Index: F4INVOICE_DATE_IX resc_io: 4.00 resc_cpu: 30446 ix_sel: 0.813255 ix_sel_with_filters: 0.813255 Cost: 1.00 Resp: 1.00 Degree: 1 Access Path: index (RangeScan) Index: F4INVOICE_PK resc_io: 5.00 resc_cpu: 37567 ix_sel: 0.813255 ix_sel_with_filters: 0.813255 Cost: 1.00 Resp: 1.00 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: F4INVOICE_BATCH_DATE_IX Cost: 1.00 Degree: 1 Resp: 1.00 Card: 3.25 Bytes: 24 First K Rows: old pf = 1.0000000, new pf = 0.9763020 Access path analysis for X4FEEITEM_INVOICE *************************************** SINGLE TABLE ACCESS PATH (First K Rows) Single Table Cardinality Estimation for X4FEEITEM_INVOICE[XFI] ColGroup Usage:: PredCnt: 3 Matches Full: Partial: Table: X4FEEITEM_INVOICE Alias: XFI Card: Original: 1248174.000000 Rounded: 1159013 Computed: 1159013.47 Non Adjusted: 1159013.47 Access Path: TableScan Cost: 6752.21 Resp: 6752.21 Degree: 0 Cost_io: 6717.00 Cost_cpu: 1139283163 Resp_io: 6717.00 Resp_cpu: 1139283163 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_NBR_IX resc_io: 29633.00 resc_cpu: 1317190529 ix_sel: 0.954312 ix_sel_with_filters: 0.954312 Cost: 2967.37 Resp: 2967.37 Degree: 1 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_PK resc_io: 207257.00 resc_cpu: 2582129187 ix_sel: 0.954312 ix_sel_with_filters: 0.954312 Cost: 20733.68 Resp: 20733.68 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: X4FEEITEM_INVOICE_NBR_IX Cost: 2967.37 Degree: 1 Resp: 2967.37 Card: 1159013.47 Bytes: 131 First K Rows: old pf = 0.6315959, new pf = 0.9354201 Access path analysis for SETDETAILS *************************************** SINGLE TABLE ACCESS PATH (First K Rows) Single Table Cardinality Estimation for SETDETAILS[SD] 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 ****** Table: SETDETAILS Alias: SD Card: Original: 365652.000000 Rounded: 24 Computed: 23.70 Non Adjusted: 23.70 Access Path: TableScan Cost: 930.59 Resp: 930.59 Degree: 0 Cost_io: 926.00 Cost_cpu: 148615537 Resp_io: 926.00 Resp_cpu: 148615537 Access Path: index (RangeScan) Index: SETDETAILS_ADDR_IX resc_io: 5096.00 resc_cpu: 255681988 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 510.39 Resp: 510.39 Degree: 1 Access Path: index (RangeScan) Index: SETDETAILS_IX resc_io: 13725.00 resc_cpu: 317132894 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 1373.48 Resp: 1373.48 Degree: 1 Access Path: index (RangeScan) Index: SETDETAILS_PID_IX resc_io: 349898.00 resc_cpu: 2711168743 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 34998.18 Resp: 34998.18 Degree: 1 Access Path: index (RangeScan) Index: SETDETAILS_PK resc_io: 333029.00 resc_cpu: 2591037172 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 33310.91 Resp: 33310.91 Degree: 1 Access Path: index (RangeScan) Index: SETDETAILS_SETID1_IX resc_io: 8638.00 resc_cpu: 280906129 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 864.67 Resp: 864.67 Degree: 1 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 (AllEqRange) Index: SETDETAILS_SETID_IX resc_io: 4.00 resc_cpu: 39286 ix_sel: 0.000065 ix_sel_with_filters: 0.000065 Cost: 1.00 Resp: 1.00 Degree: 1 Access Path: index (RangeScan) Index: SETDETAILS_STAT_IX resc_io: 5095.00 resc_cpu: 255674867 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 510.29 Resp: 510.29 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: SETDETAILS_SETID_IX Cost: 1.00 Degree: 1 Resp: 1.00 Card: 23.70 Bytes: 62 First K Rows: old pf = 0.9690518, new pf = 0.2800801 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: 361623.000000 Rounded: 344924 Computed: 344923.77 Non Adjusted: 344923.77 Access Path: TableScan Cost: 2715.81 Resp: 2715.81 Degree: 0 Cost_io: 2702.00 Cost_cpu: 446865598 Resp_io: 2702.00 Resp_cpu: 446865598 Access Path: index (RangeScan) Index: F4FEEITEM_PK resc_io: 140606.00 resc_cpu: 1425573713 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 Cost: 14065.01 Resp: 14065.01 Degree: 1 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 11059.00 resc_cpu: 503012525 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 Cost: 1107.45 Resp: 1107.45 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: F4FEEITEM_POS_IX Cost: 1107.45 Degree: 1 Resp: 1107.45 Card: 344923.77 Bytes: 104 First K Rows: unchanged join prefix len = 1 Join order[9]: F4INVOICE[FINV]#1 X4FEEITEM_INVOICE[XFI]#2 SETDETAILS [SD]#0 F4FEEITEM[F4]#3 *************** Now joining: X4FEEITEM_INVOICE[XFI]#2 *************** NL Join Outer table: Card: 3.25 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 24 Access path analysis for X4FEEITEM_INVOICE Inner table: X4FEEITEM_INVOICE Alias: XFI Access Path: TableScan NL Join: Cost: 20256.17 Resp: 20256.17 Degree: 1 Cost_io: 20150.00 Cost_cpu: 3434729666 Resp_io: 20150.00 Resp_cpu: 3434729666 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"."INVOICE_NBR"="XFI"."INVOICE_NBR" OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (AllEqJoinGuess) Index: X4FEEITEM_INVOICE_NBR_IX resc_io: 1.00 resc_cpu: 12601 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: 2.00 Resp: 2.00 Degree: 1 Cost_io: 2.00 Cost_cpu: 6825 Resp_io: 2.00 Resp_cpu: 6825 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_PK resc_io: 1180.00 resc_cpu: 246636529 ix_sel: 0.954312 ix_sel_with_filters: 0.000004 ***** 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: 357.29 Resp: 357.29 Degree: 1 Cost_io: 355.00 Cost_cpu: 73994003 Resp_io: 355.00 Resp_cpu: 73994003 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 2.00 resc: 2.00 resc_io: 2.00 resc_cpu: 6825 resp: 2.00 resp_io: 2.00 resc_cpu: 6825 Join Card: 15.180769 = = outer (3.253022) * inner (1159013.473636) * sel (0.000004) Join Card - Rounded: 15 Computed: 15.18 Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 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: 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 SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 22528 Row size: 155 Total Rows: 1187146 Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204 Total IO sort cost: 34732 Total CPU sort cost: 1666431753 Total Temp space used: 442065000 SM join: Resc: 39945.10 Resp: 39945.10 [multiMatchCost=0.00] SM Join SM cost: 39945.10 resc: 39945.10 resc_io: 39880.90 resc_cpu: 2077107735 resp: 39945.10 resp_io: 39880.90 resp_cpu: 2077107735 Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 Inner table: X4FEEITEM_INVOICE Alias: XFI resc: 2967.37 card: 1159013.47 bytes: 131 deg: 1 resp: 2967.37 using dmeth: 2 #groups: 1 Cost per ptn: 8267.52 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 20232 ppasses: 1 Hash join: Resc: 11610.05 Resp: 11610.05 [multiMatchCost=0.00] HA Join HA cost: 11610.05 resc: 11610.05 resc_io: 11594.00 resc_cpu: 519275111 resp: 11610.05 resp_io: 11594.00 resp_cpu: 519275111 Best:: JoinMethod: NestedLoop Cost: 2.00 Degree: 1 Resp: 2.00 Card: 15.18 Bytes: 155 *************** Now joining: SETDETAILS[SD]#0 *************** NL Join Outer table: Card: 15.18 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 155 Access path analysis for SETDETAILS Inner table: SETDETAILS Alias: SD Access Path: TableScan NL Join: Cost: 13948.55 Resp: 13948.55 Degree: 1 Cost_io: 13861.00 Cost_cpu: 2832525250 Resp_io: 13861.00 Resp_cpu: 2832525250 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: 3578.00 resc_cpu: 244871642 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: 5380.35 Resp: 5380.35 Degree: 1 Cost_io: 5369.00 Cost_cpu: 367314288 Resp_io: 5369.00 Resp_cpu: 367314288 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_IX resc_io: 405.00 resc_cpu: 76014876 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: 613.02 Resp: 613.02 Degree: 1 Cost_io: 609.50 Cost_cpu: 114029139 Resp_io: 609.50 Resp_cpu: 114029139 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (AllEqJoinGuess) Index: SETDETAILS_PID_IX resc_io: 1.00 resc_cpu: 8464 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: 3.50 Resp: 3.50 Degree: 1 Cost_io: 3.50 Cost_cpu: 19522 Resp_io: 3.50 Resp_cpu: 19522 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_PK resc_io: 331499.00 resc_cpu: 2580141368 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: 497370.13 Resp: 497370.13 Degree: 1 Cost_io: 497250.50 Cost_cpu: 3870218877 Resp_io: 497250.50 Resp_cpu: 3870218877 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_SETID1_IX resc_io: 7249.00 resc_cpu: 271014448 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: 10888.07 Resp: 10888.07 Degree: 1 Cost_io: 10875.50 Cost_cpu: 406528497 Resp_io: 10875.50 Resp_cpu: 406528497 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: 18771 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: 3.50 Resp: 3.50 Degree: 1 Cost_io: 3.50 Cost_cpu: 34982 Resp_io: 3.50 Resp_cpu: 34982 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_STAT_IX resc_io: 3577.00 resc_cpu: 244864521 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: 5378.85 Resp: 5378.85 Degree: 1 Cost_io: 5367.50 Cost_cpu: 367303606 Resp_io: 5367.50 Resp_cpu: 367303606 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 3.50 resc: 3.50 resc_io: 3.50 resc_cpu: 19522 resp: 3.50 resp_io: 3.50 resc_cpu: 19522 ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] = 1278471 SETDETAILS[SD] = 390896 Join selectivity using 1 ColGroups: 0.003014 (sel1 = 0.000000, sel2 = 0.000000) Join Card: 1.084306 = = outer (15.180769) * inner (23.695906) * sel (0.003014) Join Card - Rounded: 1 Computed: 1.08 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 11872.29 card 1188973.61 bytes: 155 deg: 1 resp: 11872.29 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: 26348 Row size: 181 Total Rows: 1188974 Initial runs: 6 Merge passes: 1 IO Cost / pass: 14274 Total IO sort cost: 40622 Total CPU sort cost: 1762283928 Total Temp space used: 499508000 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: 52550.76 Resp: 52550.76 [multiMatchCost=0.00] SM Join SM cost: 52550.76 resc: 52550.76 resc_io: 52478.90 resc_cpu: 2324941615 resp: 52550.76 resp_io: 52478.90 resp_cpu: 2324941615 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 11872.29 card 1188973.61 bytes: 155 deg: 1 resp: 11872.29 Inner table: SETDETAILS Alias: SD resc: 1.00 card: 23.70 bytes: 38 deg: 1 resp: 1.00 using dmeth: 2 #groups: 1 Cost per ptn: 9393.09 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 24239 probefrag: 1 ppasses: 1 Hash join: Resc: 21266.38 Resp: 21266.38 [multiMatchCost=0.00] 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: 2.00 card: 15.18 bytes: 155 deg: 1 resp: 2.00 using dmeth: 2 #groups: 1 Cost per ptn: 0.50 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1 probefrag: 1 ppasses: 1 Hash join: Resc: 3.50 Resp: 3.50 [multiMatchCost=0.00] HA Join HA cost: 3.50 swapped resc: 3.50 resc_io: 3.00 resc_cpu: 16192428 resp: 3.50 resp_io: 3.00 resp_cpu: 16192428 First K Rows: copy A one plan, tab=SETDETAILS Best:: JoinMethod: Hash Cost: 3.50 Degree: 1 Resp: 3.50 Card: 1.08 Bytes: 348 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 1.08 Cost: 3.50 Resp: 3.50 Degree: 1 Bytes: 348 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 2719.35 Resp: 2719.35 Degree: 1 Cost_io: 2705.00 Cost_cpu: 464393946 Resp_io: 2705.00 Resp_cpu: 464393946 kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (UniqueScan) Index: F4FEEITEM_PK resc_io: 1.00 resc_cpu: 9201 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: 4.50 Resp: 4.50 Degree: 1 Cost_io: 4.00 Cost_cpu: 16193348 Resp_io: 4.00 Resp_cpu: 16193348 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 (AllEqUnique) Index: F4FEEITEM_PK resc_io: 1.00 resc_cpu: 9201 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: 4.50 Resp: 4.50 Degree: 1 Cost_io: 4.00 Cost_cpu: 16193348 Resp_io: 4.00 Resp_cpu: 16193348 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 9545.00 resc_cpu: 492230665 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00 ***** End Logdef Adjustment ****** NL Join : Cost: 959.52 Resp: 959.52 Degree: 1 Cost_io: 957.50 Cost_cpu: 65415494 Resp_io: 957.50 Resp_cpu: 65415494 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 4.50 resc: 4.50 resc_io: 4.00 resc_cpu: 16193348 resp: 4.50 resp_io: 4.00 resc_cpu: 16193348 ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] = 192 F4FEEITEM[F4] = 1291140 Join selectivity using 1 ColGroups: 0.000293 (sel1 = 0.000000, sel2 = 0.000000) Join Card: 109.514994 = = outer (1.084306) * inner (344923.766664) * sel (0.000293) Join Card - Rounded: 110 Computed: 109.51 Outer table: SETDETAILS Alias: SD resc: 11877.47 card 90789.89 bytes: 193 deg: 1 resp: 11877.47 Inner table: F4FEEITEM Alias: F4 resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05 using dmeth: 2 #groups: 1 SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 2479 Row size: 223 Total Rows: 90790 Initial runs: 2 Merge passes: 1 IO Cost / pass: 1344 Total IO sort cost: 3823 Total CPU sort cost: 160772130 Total Temp space used: 38151000 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: 32953.73 Resp: 32953.73 [multiMatchCost=0.00] SM Join SM cost: 32953.73 resc: 32953.73 resc_io: 32880.40 resc_cpu: 2372333225 resp: 32953.73 resp_io: 32880.40 resp_cpu: 2372333225 Outer table: SETDETAILS Alias: SD resc: 11877.47 card 90789.89 bytes: 193 deg: 1 resp: 11877.47 Inner table: F4FEEITEM Alias: F4 resc: 1107.45 card: 344923.77 bytes: 42 deg: 1 resp: 1107.45 using dmeth: 2 #groups: 1 Cost per ptn: 1763.63 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 2272 probefrag: 2274 ppasses: 1 Hash join: Resc: 14748.55 Resp: 14748.55 [multiMatchCost=0.00] HA Join HA cost: 14748.55 resc: 14748.55 resc_io: 14722.80 resc_cpu: 833107904 resp: 14748.55 resp_io: 14722.80 resp_cpu: 833107904 ORDER BY sort First K Rows: switch to Amode plans Join order aborted: cost > best plan cost *********************** ****** Recost for ORDER BY (using index) ************ First K Rows: switch to Kmode plans Access path analysis for F4INVOICE *************************************** SINGLE TABLE ACCESS PATH (First K Rows) Single Table Cardinality Estimation for F4INVOICE[FINV] Table: F4INVOICE Alias: FINV Card: Original: 4.000000 Rounded: 3 Computed: 3.25 Non Adjusted: 3.25 Access Path: TableScan Cost: 2.00 Resp: 2.00 Degree: 0 Cost_io: 2.00 Cost_cpu: 8341 Resp_io: 2.00 Resp_cpu: 8341 Access Path: index (RangeScan) Index: F4INVOICE_DATE_IX resc_io: 4.00 resc_cpu: 30446 ix_sel: 0.813255 ix_sel_with_filters: 0.813255 Cost: 1.00 Resp: 1.00 Degree: 1 Best:: AccessPath: IndexRange Index: F4INVOICE_DATE_IX Cost: 1.00 Degree: 1 Resp: 1.00 Card: 3.25 Bytes: 24 Join order[9]: F4INVOICE[FINV]#1 X4FEEITEM_INVOICE[XFI]#2 SETDETAILS [SD]#0 F4FEEITEM[F4]#3 *************** Now joining: X4FEEITEM_INVOICE[XFI]#2 *************** NL Join Outer table: Card: 3.25 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 24 Access path analysis for X4FEEITEM_INVOICE Inner table: X4FEEITEM_INVOICE Alias: XFI Access Path: TableScan NL Join: Cost: 20256.17 Resp: 20256.17 Degree: 1 Cost_io: 20150.00 Cost_cpu: 3434729666 Resp_io: 20150.00 Resp_cpu: 3434729666 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"."INVOICE_NBR"="XFI"."INVOICE_NBR" OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (AllEqJoinGuess) Index: X4FEEITEM_INVOICE_NBR_IX resc_io: 1.00 resc_cpu: 12601 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: 2.00 Resp: 2.00 Degree: 1 Cost_io: 2.00 Cost_cpu: 6825 Resp_io: 2.00 Resp_cpu: 6825 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_PK resc_io: 1180.00 resc_cpu: 246636529 ix_sel: 0.954312 ix_sel_with_filters: 0.000004 ***** 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: 357.29 Resp: 357.29 Degree: 1 Cost_io: 355.00 Cost_cpu: 73994003 Resp_io: 355.00 Resp_cpu: 73994003 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 2.00 resc: 2.00 resc_io: 2.00 resc_cpu: 6825 resp: 2.00 resp_io: 2.00 resc_cpu: 6825 Join Card: 15.180769 = = outer (3.253022) * inner (1159013.473636) * sel (0.000004) Join Card - Rounded: 15 Computed: 15.18 Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 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: 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 SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 22528 Row size: 155 Total Rows: 1187146 Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204 Total IO sort cost: 34732 Total CPU sort cost: 1666431753 Total Temp space used: 442065000 SM join: Resc: 39945.10 Resp: 39945.10 [multiMatchCost=0.00] SM Join SM cost: 39945.10 resc: 39945.10 resc_io: 39880.90 resc_cpu: 2077107735 resp: 39945.10 resp_io: 39880.90 resp_cpu: 2077107735 Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 Inner table: X4FEEITEM_INVOICE Alias: XFI resc: 2967.37 card: 1159013.47 bytes: 131 deg: 1 resp: 2967.37 using dmeth: 2 #groups: 1 Cost per ptn: 8267.52 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 20232 ppasses: 1 Hash join: Resc: 11610.05 Resp: 11610.05 [multiMatchCost=0.00] HA Join HA cost: 11610.05 resc: 11610.05 resc_io: 11594.00 resc_cpu: 519275111 resp: 11610.05 resp_io: 11594.00 resp_cpu: 519275111 Best:: JoinMethod: NestedLoop Cost: 2.00 Degree: 1 Resp: 2.00 Card: 15.18 Bytes: 155 *************** Now joining: SETDETAILS[SD]#0 *************** NL Join Outer table: Card: 15.18 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 155 Access path analysis for SETDETAILS Inner table: SETDETAILS Alias: SD Access Path: TableScan NL Join: Cost: 13948.55 Resp: 13948.55 Degree: 1 Cost_io: 13861.00 Cost_cpu: 2832525250 Resp_io: 13861.00 Resp_cpu: 2832525250 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: 3578.00 resc_cpu: 244871642 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: 5380.35 Resp: 5380.35 Degree: 1 Cost_io: 5369.00 Cost_cpu: 367314288 Resp_io: 5369.00 Resp_cpu: 367314288 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_IX resc_io: 405.00 resc_cpu: 76014876 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: 613.02 Resp: 613.02 Degree: 1 Cost_io: 609.50 Cost_cpu: 114029139 Resp_io: 609.50 Resp_cpu: 114029139 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (AllEqJoinGuess) Index: SETDETAILS_PID_IX resc_io: 1.00 resc_cpu: 8464 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: 3.50 Resp: 3.50 Degree: 1 Cost_io: 3.50 Cost_cpu: 19522 Resp_io: 3.50 Resp_cpu: 19522 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_PK resc_io: 331499.00 resc_cpu: 2580141368 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: 497370.13 Resp: 497370.13 Degree: 1 Cost_io: 497250.50 Cost_cpu: 3870218877 Resp_io: 497250.50 Resp_cpu: 3870218877 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_SETID1_IX resc_io: 7249.00 resc_cpu: 271014448 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: 10888.07 Resp: 10888.07 Degree: 1 Cost_io: 10875.50 Cost_cpu: 406528497 Resp_io: 10875.50 Resp_cpu: 406528497 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: 18771
From: lsllcm on 4 Nov 2009 07:04 part-5 ========================================== *************** Now joining: F4INVOICE[FINV]#1 *************** NL Join Outer table: Card: 1.06 Cost: 2.50 Resp: 2.50 Degree: 1 Bytes: 300 Access path analysis for F4INVOICE Inner table: F4INVOICE Alias: FINV Access Path: TableScan NL Join: Cost: 801.56 Resp: 801.56 Degree: 1 Cost_io: 798.00 Cost_cpu: 115210413 Resp_io: 798.00 Resp_cpu: 115210413 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.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: 3.50 Resp: 3.50 Degree: 1 Cost_io: 3.00 Cost_cpu: 16190776 Resp_io: 3.00 Resp_cpu: 16190776 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: 3052.00 resc_cpu: 120046275 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: 308.07 Resp: 308.07 Degree: 1 Cost_io: 307.20 Cost_cpu: 28194557 Resp_io: 307.20 Resp_cpu: 28194557 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: F4INVOICE_BATCH_NBR_IX resc_io: 2454.00 resc_cpu: 114871354 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: 248.26 Resp: 248.26 Degree: 1 Cost_io: 247.40 Cost_cpu: 27677065 Resp_io: 247.40 Resp_cpu: 27677065 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: F4INVOICE_DATE_IX resc_io: 2999.00 resc_cpu: 119206769 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: 302.77 Resp: 302.77 Degree: 1 Cost_io: 301.90 Cost_cpu: 28110607 Resp_io: 301.90 Resp_cpu: 28110607 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: 3.50 Resp: 3.50 Degree: 1 Cost_io: 3.00 Cost_cpu: 16190776 Resp_io: 3.00 Resp_cpu: 16190776 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 3.50 resc: 3.50 resc_io: 3.00 resc_cpu: 16190776 resp: 3.50 resp_io: 3.00 resc_cpu: 16190776 Join Card: 1.061187 = = outer (1.061185) * inner (200076.276865) * sel (0.000005) Join Card - Rounded: 1 Computed: 1.06 Outer table: SETDETAILS Alias: SD resc: 3044.54 card 90650.35 bytes: 169 deg: 1 resp: 3044.54 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: 2176 Row size: 196 Total Rows: 90650 Initial runs: 2 Merge passes: 1 IO Cost / pass: 1180 Total IO sort cost: 3356 Total CPU sort cost: 153197471 Total Temp space used: 33776000 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: 8527.50 Resp: 8527.50 [multiMatchCost=0.00] SM Join SM cost: 8527.50 resc: 8527.50 resc_io: 8505.90 resc_cpu: 698772155 resp: 8527.50 resp_io: 8505.90 resp_cpu: 698772155 Outer table: SETDETAILS Alias: SD resc: 3044.54 card 90650.35 bytes: 169 deg: 1 resp: 3044.54 Inner table: F4INVOICE Alias: FINV resc: 301.87 card: 200076.28 bytes: 24 deg: 1 resp: 301.87 using dmeth: 2 #groups: 1 Cost per ptn: 1118.52 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 2003 probefrag: 880 ppasses: 1 Hash join: Resc: 4464.93 Resp: 4464.93 [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: 2.50 card: 1.06 bytes: 300 deg: 1 resp: 2.50 using dmeth: 2 #groups: 1 Cost per ptn: 426.79 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 1 ppasses: 1 Hash join: Resc: 804.45 Resp: 804.45 [multiMatchCost=0.00] HA Join HA cost: 804.45 swapped resc: 804.45 resc_io: 801.70 resc_cpu: 88946889 resp: 804.45 resp_io: 801.70 resp_cpu: 88946889 Best:: JoinMethod: NestedLoop Cost: 3.50 Degree: 1 Resp: 3.50 Card: 1.06 Bytes: 324 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 1.06 Cost: 3.50 Resp: 3.50 Degree: 1 Bytes: 324 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 2692.22 Resp: 2692.22 Degree: 1 Cost_io: 2678.00 Cost_cpu: 459952872 Resp_io: 2678.00 Resp_cpu: 459952872 kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (UniqueScan) Index: F4FEEITEM_PK resc_io: 1.00 resc_cpu: 9201 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: 4.50 Resp: 4.50 Degree: 1 Cost_io: 4.00 Cost_cpu: 16191696 Resp_io: 4.00 Resp_cpu: 16191696 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 (AllEqUnique) Index: F4FEEITEM_PK resc_io: 1.00 resc_cpu: 9201 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: 4.50 Resp: 4.50 Degree: 1 Cost_io: 4.00 Cost_cpu: 16191696 Resp_io: 4.00 Resp_cpu: 16191696 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 9451.00 resc_cpu: 487362029 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00 ***** End Logdef Adjustment ****** NL Join : Cost: 950.11 Resp: 950.11 Degree: 1 Cost_io: 948.10 Cost_cpu: 64926979 Resp_io: 948.10 Resp_cpu: 64926979 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 4.50 resc: 4.50 resc_io: 4.00 resc_cpu: 16191696 resp: 4.50 resp_io: 4.00 resc_cpu: 16191696 ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] = 192 F4FEEITEM[F4] = 1291140 Join selectivity using 1 ColGroups: 0.000293 (sel1 = 0.000000, sel2 = 0.000000) Join Card: 106.118936 = = outer (1.061187) * inner (341509.085948) * sel (0.000293) Join Card - Rounded: 106 Computed: 106.12 Outer table: F4INVOICE Alias: FINV resc: 4622.37 card 112700.05 bytes: 193 deg: 1 resp: 4622.37 Inner table: F4FEEITEM Alias: F4 resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05 using dmeth: 2 #groups: 1 SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 3077 Row size: 223 Total Rows: 112700 Initial runs: 2 Merge passes: 1 IO Cost / pass: 1668 Total IO sort cost: 4745 Total CPU sort cost: 193340637 Total Temp space used: 46179000 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: 26621.64 Resp: 26621.64 [multiMatchCost=0.00] SM Join SM cost: 26621.64 resc: 26621.64 resc_io: 26556.40 resc_cpu: 2110644038 resp: 26621.64 resp_io: 26556.40 resp_cpu: 2110644038 Outer table: F4INVOICE Alias: FINV resc: 4622.37 card 112700.05 bytes: 193 deg: 1 resp: 4622.37 Inner table: F4FEEITEM Alias: F4 resc: 1096.54 card: 341509.09 bytes: 42 deg: 1 resp: 1096.54 using dmeth: 2 #groups: 1 Cost per ptn: 1967.78 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 2821 probefrag: 2252 ppasses: 1 Hash join: Resc: 7686.69 Resp: 7686.69 [multiMatchCost=0.00] Outer table: F4FEEITEM Alias: F4 resc: 3953.05 card 1231517.00 bytes: 42 deg: 1 resp: 3953.05 Inner table: F4INVOICE Alias: FINV resc: 3.50 card: 1.06 bytes: 324 deg: 1 resp: 3.50 using dmeth: 2 #groups: 1 Cost per ptn: 3151.24 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 8118 probefrag: 1 ppasses: 1 Hash join: Resc: 7107.79 Resp: 7107.79 [multiMatchCost=0.00] HA Join HA cost: 7107.79 swapped resc: 7107.79 resc_io: 7094.50 resc_cpu: 430001640 resp: 7107.79 resp_io: 7094.50 resp_cpu: 430001640 ORDER BY sort First K Rows: switch to Amode plans Join order aborted: cost > best plan cost *********************** Join order[13]: X4FEEITEM_INVOICE[XFI]#2 SETDETAILS[SD]#0 F4FEEITEM [F4]#3 F4INVOICE[FINV]#1 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 90650.35 Cost: 3044.54 Resp: 3044.54 Degree: 1 Bytes: 169 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 878430226.52 Resp: 878430226.52 Degree: 1 Cost_io: 873946468.20 Cost_cpu: 145061545340703 Resp_io: 873946468.20 Resp_cpu: 145061545340703 kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (UniqueScan) Index: F4FEEITEM_PK resc_io: 1.00 resc_cpu: 9201 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: 12112.12 Resp: 12112.12 Degree: 1 Cost_io: 12101.20 Cost_cpu: 353225765 Resp_io: 12101.20 Resp_cpu: 353225765 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 (AllEqUnique) Index: F4FEEITEM_PK resc_io: 1.00 resc_cpu: 9201 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: 12112.12 Resp: 12112.12 Degree: 1 Cost_io: 12101.20 Cost_cpu: 353225765 Resp_io: 12101.20 Resp_cpu: 353225765 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 34070.00 resc_cpu: 1757393371 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00 ***** End Logdef Adjustment ****** NL Join : Cost: 309340004.34 Resp: 309340004.34 Degree: 1 Cost_io: 308847586.20 Cost_cpu: 15931040721013 Resp_io: 308847586.20 Resp_cpu: 15931040721013 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 12112.12 resc: 12112.12 resc_io: 12101.20 resc_cpu: 353225765 resp: 12112.12 resp_io: 12101.20 resc_cpu: 353225765 ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] = 192 F4FEEITEM[F4] = 1291140 Join selectivity using 1 ColGroups: 0.000001 (sel1 = 0.000000, sel2 = 0.000000) Join Card: 90650.350585 = = outer (90650.350585) * inner (1231517.000000) * sel (0.000001) Join Card - Rounded: 90650 Computed: 90650.35 Outer table: SETDETAILS Alias: SD resc: 3044.54 card 90650.35 bytes: 169 deg: 1 resp: 3044.54 Inner table: F4FEEITEM Alias: F4 resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05 using dmeth: 2 #groups: 1 SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 2176 Row size: 196 Total Rows: 90650 Initial runs: 2 Merge passes: 1 IO Cost / pass: 1180 Total IO sort cost: 3356 Total CPU sort cost: 153197471 Total Temp space used: 33776000 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: 23653.57 Resp: 23653.57 [multiMatchCost=0.00] SM Join SM cost: 23653.57 resc: 23653.57 resc_io: 23592.70 resc_cpu: 1969196004 resp: 23653.57 resp_io: 23592.70 resp_cpu: 1969196004 Outer table: SETDETAILS Alias: SD resc: 3044.54 card 90650.35 bytes: 169 deg: 1 resp: 3044.54 Inner table: F4FEEITEM Alias: F4 resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05 using dmeth: 2 #groups: 1 Cost per ptn: 3927.82 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 2003 probefrag: 8118 ppasses: 1 Hash join: Resc: 10925.41 Resp: 10925.41 [multiMatchCost=0.00] HA Join HA cost: 10925.41 resc: 10925.41 resc_io: 10901.70 resc_cpu: 767001333 resp: 10925.41 resp_io: 10901.70 resp_cpu: 767001333 Best:: JoinMethod: Hash Cost: 10925.41 Degree: 1 Resp: 10925.41 Card: 90650.35 Bytes: 211 *************** Now joining: F4INVOICE[FINV]#1 *************** NL Join Outer table: Card: 90650.35 Cost: 10925.41 Resp: 10925.41 Degree: 1 Bytes: 211 Access path analysis for F4INVOICE Inner table: F4INVOICE Alias: FINV Access Path: TableScan NL Join: Cost: 89868951.76 Resp: 89868951.76 Degree: 1 Cost_io: 89524001.70 Cost_cpu: 11160054879589 Resp_io: 89524001.70 Resp_cpu: 11160054879589 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: 19992.78 Resp: 19992.78 Degree: 1 Cost_io: 19966.70 Cost_cpu: 843704287 Resp_io: 19966.70 Resp_cpu: 843704287 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: 34436285.40 Resp: 34436285.40 Degree: 1 Cost_io: 34394446.70 Cost_cpu: 1353593534338 Resp_io: 34394446.70 Resp_cpu: 1353593534338 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: 27699188.62 Resp: 27699188.62 Degree: 1 Cost_io: 27659151.70 Cost_cpu: 1295301233863 Resp_io: 27659151.70 Resp_cpu: 1295301233863 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: 33828635.66 Resp: 33828635.66 Degree: 1 Cost_io: 33787091.70 Cost_cpu: 1344058002097 Resp_io: 33787091.70 Resp_cpu: 1344058002097 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: 19992.78 Resp: 19992.78 Degree: 1 Cost_io: 19966.70 Cost_cpu: 843704287 Resp_io: 19966.70 Resp_cpu: 843704287 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 19992.78 resc: 19992.78 resc_io: 19966.70 resc_cpu: 843704287 resp: 19992.78 resp_io: 19966.70 resc_cpu: 843704287 Join Card: 112700.054052 = = outer (90650.350585) * inner (248742.292433) * sel (0.000005) Join Card - Rounded: 112700 Computed: 112700.05 Outer table: F4FEEITEM Alias: F4 resc: 10925.41 card 90650.35 bytes: 211 deg: 1 resp: 10925.41 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: 17211.76 Resp: 17211.76 [multiMatchCost=0.00] SM Join SM cost: 17211.76 resc: 17211.76 resc_io: 17174.40 resc_cpu: 1208788923 resp: 17211.76 resp_io: 17174.40 resp_cpu: 1208788923 Outer table: F4FEEITEM Alias: F4 resc: 10925.41 card 90650.35 bytes: 211 deg: 1 resp: 10925.41 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: 12683.47 Resp: 12683.47 [multiMatchCost=0.00] Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 Inner table: F4FEEITEM Alias: F4 resc: 10925.41 card: 90650.35 bytes: 211 deg: 1 resp: 10925.41 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: 12683.30 Resp: 12683.30 [multiMatchCost=0.07] HA Join HA cost: 12683.30 swapped resc: 12683.30 resc_io: 12656.40 resc_cpu: 870213841 resp: 12683.30 resp_io: 12656.40 resp_cpu: 870213841 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: 18413.76 Degree: 1 Resp: 18413.76 Card: 112700.05 Bytes: 235 *********************** Best so far: Table#: 2 cost: 3039.3702 card: 1187146.2750 bytes: 155516126 Table#: 0 cost: 3044.5398 card: 90650.3506 bytes: 15319850 Table#: 3 cost: 10925.4075 card: 90650.3506 bytes: 19127150 Table#: 1 cost: 18413.7571 card: 112700.0541 bytes: 26484500 *********************** First K Rows: K = 100.00, N = 112700.00 First K Rows: old pf = 0.0000118, new pf = 0.0009047 Access path analysis for X4FEEITEM_INVOICE *************************************** SINGLE TABLE ACCESS PATH (First K Rows) Single Table Cardinality Estimation for X4FEEITEM_INVOICE[XFI] ColGroup Usage:: PredCnt: 3 Matches Full: Partial: Table: X4FEEITEM_INVOICE Alias: XFI Card: Original: 1157.000000 Rounded: 1074 Computed: 1074.35 Non Adjusted: 1074.35 Access Path: TableScan Cost: 8.03 Resp: 8.03 Degree: 0 Cost_io: 8.00 Cost_cpu: 1056158 Resp_io: 8.00 Resp_cpu: 1056158 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_NBR_IX resc_io: 30.00 resc_cpu: 1239718 ix_sel: 0.954312 ix_sel_with_filters: 0.954312 Cost: 3.00 Resp: 3.00 Degree: 1 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_PK resc_io: 196.00 resc_cpu: 2421877 ix_sel: 0.954312 ix_sel_with_filters: 0.954312 Cost: 19.61 Resp: 19.61 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: X4FEEITEM_INVOICE_NBR_IX Cost: 3.00 Degree: 1 Resp: 3.00 Card: 1074.35 Bytes: 131 First K Rows: old pf = 0.2773070, new pf = 1.0000000 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: 1291140.000000 Rounded: 1231517 Computed: 1231517.00 Non Adjusted: 1231517.00 Access Path: TableScan Cost: 9692.31 Resp: 9692.31 Degree: 0 Cost_io: 9643.00 Cost_cpu: 1595464860 Resp_io: 9643.00 Resp_cpu: 1595464860 Access Path: index (RangeScan) Index: F4FEEITEM_PK resc_io: 502011.00 resc_cpu: 5089807126 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 Cost: 50216.83 Resp: 50216.83 Degree: 1 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 39475.00 resc_cpu: 1795884754 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 Cost: 3953.05 Resp: 3953.05 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: F4FEEITEM_POS_IX Cost: 3953.05 Degree: 1 Resp: 3953.05 Card: 1231517.00 Bytes: 366 First K Rows: old pf = 0.8043506, new pf = 1.0000000 Access path analysis for F4INVOICE *************************************** SINGLE TABLE ACCESS PATH (First K Rows) Single Table Cardinality Estimation for F4INVOICE[FINV] Table: F4INVOICE Alias: FINV Card: Original: 305860.000000 Rounded: 248742 Computed: 248742.29 Non Adjusted: 248742.29 Access Path: TableScan Cost: 992.77 Resp: 992.77 Degree: 0 Cost_io: 989.00 Cost_cpu: 121960650 Resp_io: 989.00 Resp_cpu: 121960650 Access Path: index (RangeScan) Index: F4INVOICE_BATCH_DATE_IX resc_io: 4748.00 resc_cpu: 156037217 ix_sel: 0.813255 ix_sel_with_filters: 0.813255 Cost: 475.28 Resp: 475.28 Degree: 1 Access Path: index (RangeScan) Index: F4INVOICE_BATCH_NBR_IX resc_io: 3747.00 resc_cpu: 147769406 ix_sel: 0.813255 ix_sel_with_filters: 0.813255 Cost: 375.16 Resp: 375.16 Degree: 1 Access Path: index (RangeScan) Index: F4INVOICE_DATE_IX resc_io: 4643.00 resc_cpu: 154714696 ix_sel: 0.813255 ix_sel_with_filters: 0.813255 Cost: 464.78 Resp: 464.78 Degree: 1 Access Path: index (RangeScan) Index: F4INVOICE_PK resc_io: 89716.00 resc_cpu: 761518341 ix_sel: 0.813255 ix_sel_with_filters: 0.813255 Cost: 8973.95 Resp: 8973.95 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: F4INVOICE_BATCH_NBR_IX Cost: 375.16 Degree: 1 Resp: 375.16 Card: 248742.29 Bytes: 324 First K Rows: unchanged join prefix len = 1 Join order[13]: X4FEEITEM_INVOICE[XFI]#2 SETDETAILS[SD]#0 F4FEEITEM [F4]#3 F4INVOICE[FINV]#1 *************** Now joining: SETDETAILS[SD]#0 *************** NL Join Outer table: Card: 1074.35 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 131 Access path analysis for SETDETAILS Inner table: SETDETAILS Alias: SD Access Path: TableScan NL Join: Cost: 998447.68 Resp: 998447.68 Degree: 1 Cost_io: 992179.00 Cost_cpu: 202808443220 Resp_io: 992179.00 Resp_cpu: 202808443220 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: 3578.00 resc_cpu: 244871642 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: 385093.10 Resp: 385093.10 Degree: 1 Cost_io: 384280.20 Cost_cpu: 26299338340 Resp_io: 384280.20 Resp_cpu: 26299338340 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_IX resc_io: 405.00 resc_cpu: 76014876 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: 43752.35 Resp: 43752.35 Degree: 1 Cost_io: 43500.00 Cost_cpu: 8164121674 Resp_io: 43500.00 Resp_cpu: 8164121674 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (AllEqJoinGuess) Index: SETDETAILS_PID_IX resc_io: 1.00 resc_cpu: 8464 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: 110.43 Resp: 110.43 Degree: 1 Cost_io: 110.40 Cost_cpu: 1033051 Resp_io: 110.40 Resp_cpu: 1033051 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_PK resc_io: 331499.00 resc_cpu: 2580141368 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: 35611560.81 Resp: 35611560.81 Degree: 1 Cost_io: 35602995.60 Cost_cpu: 277107306938 Resp_io: 35602995.60 Resp_cpu: 277107306938 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_SETID1_IX resc_io: 7249.00 resc_cpu: 271014448 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: 779445.28 Resp: 779445.28 Degree: 1 Cost_io: 778545.60 Cost_cpu: 29107075730 Resp_io: 778545.60 Resp_cpu: 29107075730 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: 18771 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: 110.47 Resp: 110.47 Degree: 1 Cost_io: 110.40 Cost_cpu: 2140024 Resp_io: 110.40 Resp_cpu: 2140024 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: SETDETAILS_STAT_IX resc_io: 3577.00 resc_cpu: 244864521 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: 384985.67 Resp: 384985.67 Degree: 1 Cost_io: 384172.80 Cost_cpu: 26298573497 Resp_io: 384172.80 Resp_cpu: 26298573497 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 110.43 resc: 110.43 resc_io: 110.40 resc_cpu: 1033051 resp: 110.43 resp_io: 110.40 resc_cpu: 1033051 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: 82.037415 = = outer (1074.352285) * inner (25.332653) * sel (0.003014) Join Card - Rounded: 82 Computed: 82.04 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 3039.37 card 1187146.28 bytes: 131 deg: 1 resp: 3039.37 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: 22528 Row size: 155 Total Rows: 1187146 Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204 Total IO sort cost: 34732 Total CPU sort cost: 1666431753 Total Temp space used: 442065000 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: 37824.88 Resp: 37824.88 [multiMatchCost=0.00] SM Join SM cost: 37824.88 resc: 37824.88 resc_io: 37768.20 resc_cpu: 1833709679 resp: 37824.88 resp_io: 37768.20 resp_cpu: 1833709679 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 3039.37 card 1187146.28 bytes: 131 deg: 1 resp: 3039.37 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: 8031.63 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 20723 probefrag: 1 ppasses: 1 Hash join: Resc: 11072.00 Resp: 11072.00 [multiMatchCost=0.00] 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: 3.00 card: 1074.35 bytes: 131 deg: 1 resp: 3.00 using dmeth: 2 #groups: 1 Cost per ptn: 0.50 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1 probefrag: 19 ppasses: 1 Hash join: Resc: 4.51 Resp: 4.51 [multiMatchCost=0.00] HA Join HA cost: 4.51 swapped resc: 4.51 resc_io: 4.00 resc_cpu: 16415474 resp: 4.51 resp_io: 4.00 resp_cpu: 16415474 First K Rows: copy A one plan, tab=SETDETAILS Join order aborted: cost > best plan cost *********************** Join order[14]: X4FEEITEM_INVOICE[XFI]#2 F4INVOICE[FINV]#1 SETDETAILS[SD]#0 F4FEEITEM[F4]#3 *************** Now joining: F4INVOICE[FINV]#1 *************** NL Join Outer table: Card: 1187146.28 Cost: 3039.37 Resp: 3039.37 Degree: 1 Bytes: 131 Access path analysis for F4INVOICE Inner table: F4INVOICE Alias: FINV Access Path: TableScan NL Join: Cost: 1176777382.55 Resp: 1176777382.55 Degree: 1 Cost_io: 1172260247.20 Cost_cpu: 146141381110383 Resp_io: 1172260247.20 Resp_cpu: 146141381110383 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: 121785.02 Resp: 121785.02 Degree: 1 Cost_io: 121749.80 Cost_cpu: 1139412473 Resp_io: 121749.80 Resp_cpu: 1139412473 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: 450835123.34 Resp: 450835123.34 Degree: 1 Cost_io: 450287513.00 Cost_cpu: 17716655681044 Resp_io: 450287513.00 Resp_cpu: 17716655681044 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: 362606579.59 Resp: 362606579.59 Degree: 1 Cost_io: 362082565.20 Cost_cpu: 16953263829534 Resp_io: 362082565.20 Resp_cpu: 16953263829534 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: 442877385.28 Resp: 442877385.28 Degree: 1 Cost_io: 442333634.80 Cost_cpu: 17591779024034 Resp_io: 442333634.80 Resp_cpu: 17591779024034 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: 121785.02 Resp: 121785.02 Degree: 1 Cost_io: 121749.80 Cost_cpu: 1139412473 Resp_io: 121749.80 Resp_cpu: 1139412473 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 121785.02 resc: 121785.02 resc_io: 121749.80 resc_cpu: 1139412473 resp: 121785.02 resp_io: 121749.80 resc_cpu: 1139412473 Join Card: 1188973.610473 = = outer (1187146.275001) * inner (248742.292433) * sel (0.000004) Join Card - Rounded: 1188974 Computed: 1188973.61 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 3039.37 card 1187146.28 bytes: 131 deg: 1 resp: 3039.37 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: 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: 5161.60 Resp: 5161.60 [multiMatchCost=0.01] SM Join SM cost: 5161.60 resc: 5161.60 resc_io: 5148.90 resc_cpu: 410858781 resp: 5161.60 resp_io: 5148.90 resp_cpu: 410858781 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 3039.37 card 1187146.28 bytes: 131 deg: 1 resp: 3039.37 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: 8456.31 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 20723 probefrag: 1094 ppasses: 1 Hash join: Resc: 11873.74 Resp: 11873.74 [multiMatchCost=2.91] Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 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: 8457.76 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 20723 ppasses: 1 Hash join: Resc: 11872.29 Resp: 11872.29 [multiMatchCost=0.01] HA Join HA cost: 11872.29 swapped resc: 11872.29 resc_io: 11855.90 resc_cpu: 530295770 resp: 11872.29 resp_io: 11855.90 resp_cpu: 530295770 Best:: JoinMethod: SortMerge Cost: 5161.60 Degree: 1 Resp: 5161.60 Card: 1188973.61 Bytes: 155 *************** Now joining: SETDETAILS[SD]#0 *************** NL Join Outer table: Card: 1188973.61 Cost: 5161.60 Resp: 5161.60 Degree: 1 Bytes: 155 Access path analysis for SETDETAILS Inner table: SETDETAILS Alias: SD Access Path: TableScan NL Join: Cost: 1181486176.63 Resp: 1181486176.63 Degree: 1 Cost_io: 1174067434.90 Cost_cpu: 240016089733865 Resp_io: 1174067434.90 Resp_cpu: 240016089733865 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: 455511910.93 Resp: 455511910.93 Degree: 1 Cost_io: 454549909.10 Cost_cpu: 31123326020795 Resp_io: 454549909.10 Resp_cpu: 31123326020795 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: 51786380.99 Resp: 51786380.99 Degree: 1 Cost_io: 51487723.10 Cost_cpu: 9662379724384 Resp_io: 51487723.10 Resp_cpu: 9662379724384 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: 124090.12 Resp: 124090.12 Degree: 1 Cost_io: 124046.30 Cost_cpu: 1417610063 Resp_io: 124046.30 Resp_cpu: 1417610063 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: 42145478090.36 Resp: 42145478090.36 Degree: 1 Cost_io: 42135341350.50 Cost_cpu: 327950581423239 Resp_io: 42135341350.50 Resp_cpu: 327950581423239 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: 922405852.98 Resp: 922405852.98 Degree: 1 Cost_io: 921341101.50 Cost_cpu: 34447551490000 Resp_io: 921341101.50 Resp_cpu: 34447551490000 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: 124131.29 Resp: 124131.29 Degree: 1 Cost_io: 124046.30 Cost_cpu: 2749741852 Resp_io: 124046.30 Resp_cpu: 2749741852 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: 455392987.36 Resp: 455392987.36 Degree: 1 Cost_io: 454431011.70 Cost_cpu: 31122479300095 Resp_io: 454431011.70 Resp_cpu: 31122479300095 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 124090.12 resc: 124090.12 resc_io: 124046.30 resc_cpu: 1417610063 resp: 124090.12 resp_io: 124046.30 resc_cpu: 1417610063 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: 90789.885708 = = outer (1188973.610473) * inner (25.332653) * sel (0.003014) Join Card - Rounded: 90790 Computed: 90789.89 Outer table: F4INVOICE Alias: FINV resc: 5161.60 card 1188973.61 bytes: 155 deg: 1 resp: 5161.60 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: 26348 Row size: 181 Total Rows: 1188974 Initial runs: 6 Merge passes: 1 IO Cost / pass: 14274 Total IO sort cost: 40622 Total CPU sort cost: 1762283928 Total Temp space used: 405857000 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: 45840.07 Resp: 45840.07 [multiMatchCost=0.00] SM Join SM cost: 45840.07 resc: 45840.07 resc_io: 45771.90 resc_cpu: 2205504626 resp: 45840.07 resp_io: 45771.90 resp_cpu: 2205504626 Outer table: F4INVOICE Alias: FINV resc: 5161.60 card 1188973.61 bytes: 155 deg: 1 resp: 5161.60 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: 9393.09 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 24239 probefrag: 1 ppasses: 1 Hash join: Resc: 14555.97 Resp: 14555.97 [multiMatchCost=0.28] Outer table: SETDETAILS Alias: SD resc: 1.00 card 25.33 bytes: 38 deg: 1 resp: 1.00 Inner table: F4INVOICE Alias: FINV resc: 5161.60 card: 1188973.61 bytes: 155 deg: 1 resp: 5161.60 using dmeth: 2 #groups: 1 Cost per ptn: 4.18 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1 probefrag: 24239 ppasses: 1 Hash join: Resc: 5166.77 Resp: 5166.77 [multiMatchCost=0.00] HA Join HA cost: 5166.77 swapped resc: 5166.77 resc_io: 5149.90 resc_cpu: 545940284 resp: 5166.77 resp_io: 5149.90 resp_cpu: 545940284 Best:: JoinMethod: Hash Cost: 5166.77 Degree: 1 Resp: 5166.77 Card: 90789.89 Bytes: 193 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 90789.89 Cost: 5166.77 Resp: 5166.77 Degree: 1 Bytes: 193 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 879788993.47 Resp: 879788993.47 Degree: 1 Cost_io: 875298301.90 Cost_cpu: 145285854324231 Resp_io: 875298301.90 Resp_cpu: 145285854324231 kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (UniqueScan) Index: F4FEEITEM_PK resc_io: 1.00 resc_cpu: 9201 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: 14248.36 Resp: 14248.36 Degree: 1 Cost_io: 14228.90 Cost_cpu: 629480158 Resp_io: 14228.90 Resp_cpu: 629480158 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 (AllEqUnique) Index: F4FEEITEM_PK resc_io: 1.00 resc_cpu: 9201 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: 14248.36 Resp: 14248.36 Degree: 1 Cost_io: 14228.90 Cost_cpu: 629480158 Resp_io: 14228.90 Resp_cpu: 629480158 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 34070.00 resc_cpu: 1757393371 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00 ***** End Logdef Adjustment ****** NL Join : Cost: 309819867.05 Resp: 309819867.05 Degree: 1 Cost_io: 309326679.90 Cost_cpu: 15955920353777 Resp_io: 309326679.90 Resp_cpu: 15955920353777 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 14248.36 resc: 14248.36 resc_io: 14228.90 resc_cpu: 629480158 resp: 14248.36 resp_io: 14228.90 resc_cpu: 629480158 ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] = 192 F4FEEITEM[F4] = 1291140 Join selectivity using 1 ColGroups: 0.000293 (sel1 = 0.000000, sel2 = 0.000000) Join Card: 32739848.219760 = = outer (90789.885708) * inner (1231517.000000) * sel (0.000293) Join Card - Rounded: 32739848 Computed: 32739848.22 Outer table: SETDETAILS Alias: SD resc: 5166.77 card 90789.89 bytes: 193 deg: 1 resp: 5166.77 Inner table: F4FEEITEM Alias: F4 resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05 using dmeth: 2 #groups: 1 SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 2479 Row size: 223 Total Rows: 90790 Initial runs: 2 Merge passes: 1 IO Cost / pass: 1344 Total IO sort cost: 3823 Total CPU sort cost: 160772130 Total Temp space used: 38151000 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: 26340.43 Resp: 26340.43 [multiMatchCost=97.39] SM Join SM cost: 26340.43 resc: 26340.43 resc_io: 26173.40 resc_cpu: 5403729337 resp: 26340.43 resp_io: 26173.40 resp_cpu: 5403729337 Outer table: SETDETAILS Alias: SD resc: 5166.77 card 90789.89 bytes: 193 deg: 1 resp: 5166.77 Inner table: F4FEEITEM Alias: F4 resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05 using dmeth: 2 #groups: 1 Cost per ptn: 4032.85 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 2272 probefrag: 8118 ppasses: 1 Hash join: Resc: 13250.07 Resp: 13250.07 [multiMatchCost=97.39] HA Join HA cost: 13250.07 resc: 13250.07 resc_io: 13120.40 resc_cpu: 4195084566 resp: 13250.07 resp_io: 13120.40 resp_cpu: 4195084566 ORDER BY sort SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 1078235 Row size: 269 Total Rows: 32739848 Initial runs: 210 Merge passes: 1 IO Cost / pass: 584046 Total IO sort cost: 1662281 Total CPU sort cost: 63409659445 Total Temp space used: 8651785000 Best:: JoinMethod: Hash Cost: 1677491.02 Degree: 1 Resp: 1677491.02 Card: 32739848.22 Bytes: 235 *********************** Best so far: Table#: 2 cost: 3039.3702 card: 1187146.2750 bytes: 155516126 Table#: 1 cost: 5161.5994 card: 1188973.6105 bytes: 184290970 Table#: 0 cost: 5166.7747 card: 90789.8857 bytes: 17522470 Table#: 3 cost: 1677491.0188 card: 32739848.2198 bytes: 7693864280 *********************** First K Rows: K = 100.00, N = 32739848.00 First K Rows: old pf = 0.0009047, new pf = 0.0000118 Access path analysis for X4FEEITEM_INVOICE *************************************** SINGLE TABLE ACCESS PATH (First K Rows) Single Table Cardinality Estimation for X4FEEITEM_INVOICE[XFI] ColGroup Usage:: PredCnt: 3 Matches Full: Partial: Table: X4FEEITEM_INVOICE Alias: XFI Card: Original: 16.000000 Rounded: 15 Computed: 14.86 Non Adjusted: 14.86 Access Path: TableScan Cost: 2.00 Resp: 2.00 Degree: 0 Cost_io: 2.00 Cost_cpu: 19468 Resp_io: 2.00 Resp_cpu: 19468 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_NBR_IX resc_io: 4.00 resc_cpu: 43272 ix_sel: 0.954312 ix_sel_with_filters: 0.954312 Cost: 1.00 Resp: 1.00 Degree: 1 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_PK resc_io: 6.00 resc_cpu: 57515 ix_sel: 0.954312 ix_sel_with_filters: 0.954312 Cost: 1.00 Resp: 1.00 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: X4FEEITEM_INVOICE_NBR_IX Cost: 1.00 Degree: 1 Resp: 1.00 Card: 14.86 Bytes: 131 First K Rows: old pf = 1.0000000, new pf = 0.9354201 Access path analysis for SETDETAILS *************************************** SINGLE TABLE ACCESS PATH (First K Rows) Single Table Cardinality Estimation for SETDETAILS[SD] 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 ****** Table: SETDETAILS Alias: SD Card: Original: 365652.000000 Rounded: 24 Computed: 23.70 Non Adjusted: 23.70 Access Path: TableScan Cost: 930.59 Resp: 930.59 Degree: 0 Cost_io: 926.00 Cost_cpu: 148615537 Resp_io: 926.00 Resp_cpu: 148615537 Access Path: index (RangeScan) Index: SETDETAILS_ADDR_IX resc_io: 5096.00 resc_cpu: 255681988 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 510.39 Resp: 510.39 Degree: 1 Access Path: index (RangeScan) Index: SETDETAILS_IX resc_io: 13725.00 resc_cpu: 317132894 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 1373.48 Resp: 1373.48 Degree: 1 Access Path: index (RangeScan) Index: SETDETAILS_PID_IX resc_io: 349898.00 resc_cpu: 2711168743 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 34998.18 Resp: 34998.18 Degree: 1 Access Path: index (RangeScan) Index: SETDETAILS_PK resc_io: 333029.00 resc_cpu: 2591037172 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 33310.91 Resp: 33310.91 Degree: 1 Access Path: index (RangeScan) Index: SETDETAILS_SETID1_IX resc_io: 8638.00 resc_cpu: 280906129 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 864.67 Resp: 864.67 Degree: 1 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 (AllEqRange) Index: SETDETAILS_SETID_IX resc_io: 4.00 resc_cpu: 39286 ix_sel: 0.000065 ix_sel_with_filters: 0.000065 Cost: 1.00 Resp: 1.00 Degree: 1 Access Path: index (RangeScan) Index: SETDETAILS_STAT_IX resc_io: 5095.00 resc_cpu: 255674867 ix_sel: 0.999999 ix_sel_with_filters: 0.999999 Cost: 510.29 Resp: 510.29 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: SETDETAILS_SETID_IX Cost: 1.00 Degree: 1 Resp: 1.00 Card: 23.70 Bytes: 300 First K Rows: old pf = 1.0000000, new pf = 0.2800801 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: 361623.000000 Rounded: 344924 Computed: 344923.77 Non Adjusted: 344923.77 Access Path: TableScan Cost: 2715.81 Resp: 2715.81 Degree: 0 Cost_io: 2702.00 Cost_cpu: 446865598 Resp_io: 2702.00 Resp_cpu: 446865598 Access Path: index (RangeScan) Index: F4FEEITEM_PK resc_io: 140606.00 resc_cpu: 1425573713 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 Cost: 14065.01 Resp: 14065.01 Degree: 1 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 11059.00 resc_cpu: 503012525 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 Cost: 1107.45 Resp: 1107.45 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: F4FEEITEM_POS_IX Cost: 1107.45 Degree: 1 Resp: 1107.45 Card: 344923.77 Bytes: 42 First K Rows: unchanged join prefix len = 1 Join order[14]: X4FEEITEM_INVOICE[XFI]#2 F4INVOICE[FINV]#1 SETDETAILS[SD]#0 F4FEEITEM[F4]#3 *************** Now joining: F4INVOICE[FINV]#1 *************** NL Join Outer table: Card: 14.86 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 131 Access path analysis for F4INVOICE Inner table: F4INVOICE Alias: FINV Access Path: TableScan NL Join: Cost: 14872.08 Resp: 14872.08 Degree: 1 Cost_io: 14815.00 Cost_cpu: 1846549481 Resp_io: 14815.00 Resp_cpu: 1846549481 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: 2.50 Resp: 2.50 Degree: 1 Cost_io: 2.50 Cost_cpu: 17019 Resp_io: 2.50 Resp_cpu: 17019 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: 5697.42 Resp: 5697.42 Degree: 1 Cost_io: 5690.50 Cost_cpu: 223858690 Resp_io: 5690.50 Resp_cpu: 223858690 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: 4582.62 Resp: 4582.62 Degree: 1 Cost_io: 4576.00 Cost_cpu: 214212970 Resp_io: 4576.00 Resp_cpu: 214212970 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: 5596.87 Resp: 5596.87 Degree: 1 Cost_io: 5590.00 Cost_cpu: 222280830 Resp_io: 5590.00 Resp_cpu: 222280830 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: 2.50 Resp: 2.50 Degree: 1 Cost_io: 2.50 Cost_cpu: 17019 Resp_io: 2.50 Resp_cpu: 17019 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 2.50 resc: 2.50 resc_io: 2.50 resc_cpu: 17019 resp: 2.50 resp_io: 2.50 resc_cpu: 17019 Join Card: 14.879945 = = outer (14.857076) * inner (248742.292433) * sel (0.000004) Join Card - Rounded: 15 Computed: 14.88 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 1.00 card 14.86 bytes: 131 deg: 1 resp: 1.00 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: 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: 2123.22 Resp: 2123.22 [multiMatchCost=0.00] SM Join SM cost: 2123.22 resc: 2123.22 resc_io: 2114.70 resc_cpu: 275764300 resp: 2123.22 resp_io: 2114.70 resp_cpu: 275764300 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 3039.37 card 1187146.28 bytes: 131 deg: 1 resp: 3039.37 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: 8456.31 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 20723 probefrag: 1094 ppasses: 1 Hash join: Resc: 11870.84 Resp: 11870.84 [multiMatchCost=0.00] Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 Inner table: X4FEEITEM_INVOICE Alias: XFI resc: 1.00 card: 14.86 bytes: 131 deg: 1 resp: 1.00 using dmeth: 2 #groups: 1 Cost per ptn: 426.79 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 1 ppasses: 1 Hash join: Resc: 802.95 Resp: 802.95 [multiMatchCost=0.00] HA Join HA cost: 802.95 swapped resc: 802.95 resc_io: 800.70 resc_cpu: 72764086 resp: 802.95 resp_io: 800.70 resp_cpu: 72764086 Best:: JoinMethod: NestedLoop Cost:
From: lsllcm on 4 Nov 2009 07:04 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(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "FINV"@"SEL $1" ("F4INVOICE"."SERV_PROV_CODE" "F4INVOICE"."INVOICE_DATE")) INDEX_RS_ASC(@"SEL$1" "XFI"@"SEL $1" ("X4FEEITEM_INVOICE"."SERV_PROV_CODE" "X4FEEITEM_INVOICE"."INVOICE_NBR")) INDEX_RS_ASC(@"SEL$1" "SD"@"SEL $1" ("SETDETAILS"."SERV_PROV_CODE" "SETDETAILS"."B1_PER_ID1" "SETDETAILS"."B1_PER_ID2" "SETDETAILS"."B1_PER_ID3")) INDEX(@"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(@"SEL$1" "FINV"@"SEL$1" "XFI"@"SEL$1" "SD"@"SEL$1" "F4"@"SEL$1") USE_NL(@"SEL$1" "XFI"@"SEL$1") USE_NL(@"SEL$1" "SD"@"SEL$1") USE_NL(@"SEL$1" "F4"@"SEL$1") NLJ_BATCHING(@"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 ======================
From: Charles Hooper on 4 Nov 2009 17:26 On Nov 4, 7:01 am, lsllcm <lsl...(a)gmail.com> wrote: > The trace file is too long, I use three parts. (Snip) > *************************************** > PARAMETERS USED BY THE OPTIMIZER > ******************************** > ************************************* > PARAMETERS WITH ALTERED VALUES > ****************************** > Compilation Environment Dump > optimizer_features_enable = 11.1.0.6 > optimizer_mode = first_rows_100 > optimizer_index_cost_adj = 10 > optimizer_index_caching = 90 > _optimizer_cost_based_transformation = off > Bug Fix Control Environment I agree with Randolf's points. I attempted to run through the trace file, but I believe that there are sections of the trace file that are missing - at least from what I am able to see. Just a couple questions: Why is "_optimizer_cost_based_transformation = off" specified? Why is "optimizer_mode = first_rows_100" specified? Why is "optimizer_index_cost_adj = 10" specified? Why is "optimizer_index_caching = 90" specified? The system statistics show the following: ----------------------------- SYSTEM STATISTICS INFORMATION ----------------------------- Using NOWORKLOAD Stats CPUSPEEDNW: 2696 millions instructions/sec (default is 100) IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) MBRC: -1 blocks (default is 8) You might try collecting system statistics during a busy time period. To collect the system statistics with a 60 minute monitoring period, enter the following in SQL*Plus (the SQL*Plus command prompt will return immediately): EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60) The SETDETAILS table should probably be one of the first tables accessed, if not the first. There are a couple interesting statistics showing up in the calculations: SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for SETDETAILS[SD] Column (#17): NewDensity:0.000065, OldDensity:0.002105 BktCnt:254, PopBktCnt: 194, PopValCnt:19, NDV:3664 Column (#3): NewDensity:0.000063, OldDensity:0.001961 BktCnt:254, PopBktCnt: 196, PopValCnt:20, NDV:3665 ColGroup (#8, Index) SETDETAILS_IX Col#: 1 3 4 5 6 CorStregth: 11963.23 ColGroup (#2, Index) SETDETAILS_PID_IX Col#: 1 4 5 6 CorStregth: 7.07 ColGroup (#1, Index) SETDETAILS_SETID1_IX Col#: 1 3 CorStregth: 1.00 ColGroup (#3, Index) SETDETAILS_SETID_IX Col#: 1 17 CorStregth: 1.00 ColGroup (#5, Index) SETDETAILS_STAT_IX Col#: 1 15 CorStregth: -1.00 ColGroup (#7, Index) SETDETAILS_PK Col#: 1 2 CorStregth: -1.00 ColGroup (#6, Index) SETDETAILS_PAR_IX Col#: 12 13 CorStregth: -1.00 ColGroup (#4, Index) SETDETAILS_ADDR_IX Col#: 1 14 CorStregth: -1.00 ColGroup Usage:: PredCnt: 2 Matches Full: Partial: ***** Virtual column Adjustment ****** Column name SYS_NC00017$ cost_cpu 150.00 cost_io 179769313486231570814527423731704356798070567525844996598917476803157260780 028538760589558632766878171540458953514382464234321326889464182768467546703 537516986049910576551282076245490090389328944075868508455133942304583236903 222948165808559332123348274797826204144723168738177180919299881250404026184 124858368.00 Is SYS_NC00017$ the virtual column for a function based index, maybe on UPPER("SET_ID")? I wonder if Oracle was able to calculate positive infinity correctly? :-) -- Oracle only tried 19 join orders, ending with this: (newjo-stop-1) k:0, spcnt:0, perm:19, maxperm:1000 ********************************* Number of join permutations tried: 19 With 4 tables involved, there are 4! (24) possible join orders for the tables, so I am not sure if Oracle aborted the join orders early? -- Oracle found that the join order 9 was the lowest cost per this output in the trace file: 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 That join order is: Join order[9]: F4INVOICE[FINV]#1 X4FEEITEM_INVOICE[XFI]#2 SETDETAILS [SD]#0 F4FEEITEM[F4]#3 .... SM Join SM cost: 32953.73 resc: 32953.73 resc_io: 32880.40 resc_cpu: 2372333225 resp: 32953.73 resp_io: 32880.40 resp_cpu: 2372333225 Outer table: SETDETAILS Alias: SD resc: 11877.47 card 90789.89 bytes: 193 deg: 1 resp: 11877.47 Inner table: F4FEEITEM Alias: F4 resc: 1107.45 card: 344923.77 bytes: 42 deg: 1 resp: 1107.45 using dmeth: 2 #groups: 1 Cost per ptn: 1763.63 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 2272 probefrag: 2274 ppasses: 1 Hash join: Resc: 14748.55 Resp: 14748.55 [multiMatchCost=0.00] HA Join HA cost: 14748.55 resc: 14748.55 resc_io: 14722.80 resc_cpu: 833107904 resp: 14748.55 resp_io: 14722.80 resp_cpu: 833107904 Join order aborted: cost > best plan cost Without the FIRST_ROWS_100 optimizer mode, Oracle should have selected a different plan (if I am not missing sections of the 10053 trace between the join order start and the rejection of the join order due to the cost of the join order before applying the adjustment for the FIRST_ROWS_100 optimizer mode to avoid the sort). -- Suggestions (initially just at the session level): Gather statistics on all objects in the schema, including virtual columns (also specify NO_INVALIDATE=>FALSE) At the session level, Set optimizer_mode = ALL_ROWS At the session level, Set optimizer_index_cost_adj = 90 At the session level, Set optimizer_index_caching = 10 At the session level, Set _optimizer_cost_based_transformation = on Run the SQL statement - did the plan change, and did the execution time change? If not, are you able to modify the query to add a LEADING hint? If the plan is better, wait until the database instance is very busy, then start the system statistics gathering process (with DBMS_STATS.GATHER_SYSTEM_STATS). Consider setting the _optimizer_cost_based_transformation, optimizer_index_caching, optimizer_index_cost_adj, and optimizer_mode back to the default values at the system level: Set optimizer_mode = ALL_ROWS Set optimizer_index_cost_adj = 100 Set optimizer_index_caching = 0 Set _optimizer_cost_based_transformation = on - Randolf is better at reading 10053 trace files than I am, so he might see something else that I missed in the file. - Randolf: regarding the optimizer bug that was fixed in 11.2.0.1 which was related to ROWNUM and incorrect cardinality estimates, I was unable to force 11.2.0.1 to reproduce the incorrect cardinality estimates by changing the OPTIMIZER_FEATURES_ENABLE parameter to match a lower release number. So, I guess that some fixes are buried deep in the code. I even tried disabling some of the bug fixes without success. This seems to imply that changing the OPTIMIZER_FEATURES_ENABLE to an older version may not always reproduce the old optimizer behavior, which is a bit unfortunate. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: joel garry on 4 Nov 2009 19:56
On Nov 4, 2:26 pm, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > - > > Randolf: regarding the optimizer bug that was fixed in 11.2.0.1 which > was related to ROWNUM and incorrect cardinality estimates, I was > unable to force 11.2.0.1 to reproduce the incorrect cardinality > estimates by changing the OPTIMIZER_FEATURES_ENABLE parameter to match > a lower release number. So, I guess that some fixes are buried deep > in the code. I even tried disabling some of the bug fixes without > success. This seems to imply that changing the > OPTIMIZER_FEATURES_ENABLE to an older version may not always reproduce > the old optimizer behavior, which is a bit unfortunate. > Yes, Jonathan blogged about an example of this in "FBI problem" ( http://jonathanlewis.wordpress.com/2007/11/18/fbi-problem/ ) But it's still usually worth a try, if just to narrow possibilities. Also, just to clearly emphasize to the OP, it is recommended in a lot of places not to set OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING, at least until testing shows they help - it's likely a mistake to keep the settings from earlier versions. See http://richardfoote.wordpress.com/2009/09/01/optimizer_index_caching-parameter/ for some idea of what to look for. jg -- @home.com is bogus. http://www.newscientist.com/article/mg20427321.000-clever-fools-why-a-high-iq-doesnt-mean-youre-smart.html?full=true |