3 Replies Latest reply: Dec 11, 2012 12:16 AM by Nikolay Savvinov RSS

    Outer Join Performance Issue

    KODS
      Dear All,

      Please help me in tuning the below query as it is including the outer join and is going for full table scans.

      Query :
      SELECT
      T27.CONFLICT_ID,
      T27.LAST_UPD,
      T27.CREATED,
      T27.LAST_UPD_BY,
      T27.CREATED_BY,
      T27.MODIFICATION_NUM,
      T27.ROW_ID,
      T24.ATTRIB_39,
      T27.REMIT_ADDR_ORG_ID,
      T27.REMIT_ORG_EXT_ID,
      T16.NAME,
      T25.ACCNT_TYPE_CD,
      T27.RECAL_TAX_SRV_FLG,
      T27.PROJ_ID,
      T8.PROJ_NUM,
      T8.BU_ID,
      T5.NAME,
      T8.INTEGRATION_ID,
      T12.CURCY_CD,
      T25.PR_BL_ADDR_ID,
      T25.URL,
      T27.TTL_INVC_AMT,
      T27.INVC_TYPE_CD,
      T27.TTL_PD_AMT,
      T3.CG_ASSSET_ID,
      T17.ASSET_NUM,
      T27.VENDR_INVOICE_NUM,
      T4.TOT_QTY_SHIP,
      T4.TOT_EXTND_PRICE,
      T27.ACCNT_ID,
      T25.INTEGRATION_ID,
      T25.NAME,
      T25.BU_ID,
      T25.AVAIL_CREDIT_AMT,
      T10.NAME,
      T27.AGREEMENT_ID,
      T4.TOT_EXTND_TAX,
      T27.STMT_SOURCE_CD,
      T4.SRC_INVLOC_ID,
      T27.STATUS_CD,
      T4.TOT_QTY_BONUS,
      T27.X_DEPOSIT_AMT,
      T27.COMMENTS,
      T27.INVC_FULLY_PAID_DT,
      T26.SEQ_NUM,
      T27.ELEMENT_ID,
      T26.INSCLM_ID,
      T14.INSCLAIM_NUM,
      T27.BL_PER_ID,
      T27.INS_CLAIM_ID,
      T27.FN_ACCNT_ID,
      T27.CUSTOMER_REF_NUM,
      T27.TTL_NONREC_AMT,
      T25.OU_NUM,
      T24.ATTRIB_39,
      T27.AMT_CURCY_CD,
      T2.CCNUM_ENCRPKEY_REF,
      T18.PR_DEPOSIT_ID,
      T19.DISCNT_RULE_CD,
      T27.ORDER_ID,
      T4.STATUS_CHG_FLG,
      T25.MAIN_PH_NUM,
      T25.MAIN_FAX_PH_NUM,
      T27.DELINQUENT_FLG,
      T15.LOGIN,
      T25.PR_POSTN_ID,
      T4.ORDER_NUM,
      T22.ADDR,
      T22.ZIPCODE,
      T27.INVC_NUM,
      T27.INVC_DT,
      T22.COUNTRY,
      T22.CITY,
      T27.BL_ADDR_ID,
      T23.NAME,
      T27.POSTED_DT,
      T20.NAME,
      T27.BL_PERIOD_ID,
      T27.GOODS_DLVRD_TS,
      T23.NET_DAYS,
      T27.PAYMENT_TERM_ID,
      T23.DUE_DT,
      T27.DUE_DT,
      T27.VOID_REASON_TEXT,
      T27.DEPT_CD,
      T24.ATTRIB_60,
      T24.ATTRIB_28,
      T21.AMT,
      T1.STATE,
      T1.ADDR,
      T1.ADDR_LINE_2,
      T1.COUNTRY,
      T1.CITY,
      T1.ZIPCODE,
      T11.LOGIN,
      T21.ROW_ID,
      T9.ROW_ID,
      T1.ROW_ID,
      T13.ROW_ID,
      T7.ROW_ID
      FROM
      SIEBEL.S_ADDR_PER T1,
      SIEBEL.S_PTY_PAY_PRFL T2,
      SIEBEL.S_INVLOC T3,
      SIEBEL.S_ORDER T4,
      SIEBEL.S_ORG_EXT T5,
      SIEBEL.S_POSTN T6,
      SIEBEL.S_PARTY T7,
      SIEBEL.S_PROJ T8,
      SIEBEL.S_CON_ADDR T9,
      SIEBEL.S_ORG_EXT T10,
      SIEBEL.S_USER T11,
      SIEBEL.S_DOC_QUOTE T12,
      SIEBEL.S_ACCNT_POSTN T13,
      SIEBEL.S_INS_CLAIM T14,
      SIEBEL.S_USER T15,
      SIEBEL.S_ORG_EXT T16,
      SIEBEL.S_ASSET T17,
      SIEBEL.S_ORDER_TNTX T18,
      SIEBEL.S_ORG_EXT_TNTX T19,
      SIEBEL.S_PERIOD T20,
      SIEBEL.S_DEPOSIT_TNT T21,
      SIEBEL.S_ADDR_PER T22,
      SIEBEL.S_PAYMENT_TERM T23,
      SIEBEL.S_ORG_EXT_X T24,
      SIEBEL.S_ORG_EXT T25,
      SIEBEL.S_INSCLM_ELMNT T26,
      SIEBEL.S_INVOICE T27
      WHERE
      T25.BU_ID = T10.PAR_ROW_ID (+) AND
      T26.INSCLM_ID = T14.ROW_ID (+) AND
      T27.ELEMENT_ID = T26.ROW_ID (+) AND
      T27.LAST_UPD_BY = T15.PAR_ROW_ID (+) AND
      T4.QUOTE_ID = T12.ROW_ID (+) AND
      T3.CG_ASSSET_ID = T17.ROW_ID (+) AND
      T27.BL_ADDR_ID = T22.ROW_ID (+) AND
      T8.BU_ID = T5.PAR_ROW_ID (+) AND
      T27.PER_PAY_PRFL_ID = T2.ROW_ID (+) AND
      T27.REMIT_ORG_EXT_ID = T16.PAR_ROW_ID (+) AND
      T27.PROJ_ID = T8.ROW_ID (+) AND
      T27.BL_PERIOD_ID = T20.ROW_ID (+) AND
      T27.PAYMENT_TERM_ID = T23.ROW_ID (+) AND
      T12.BU_ID = T19.PAR_ROW_ID (+) AND
      T27.ACCNT_ID = T25.PAR_ROW_ID (+) AND
      T27.ORDER_ID = T18.ROW_ID (+) AND
      T4.SRC_INVLOC_ID = T3.ROW_ID (+) AND
      T27.ORDER_ID = T4.ROW_ID (+) AND
      T27.ACCNT_ID = T24.PAR_ROW_ID (+) AND
      T18.PR_DEPOSIT_ID = T21.ROW_ID (+) AND
      T27.BL_ADDR_ID = T9.ADDR_PER_ID (+) AND
      T27.ACCNT_ID = T9.ACCNT_ID (+) AND
      T27.BL_ADDR_ID = T1.ROW_ID (+) AND
      T25.PR_POSTN_ID = T13.POSITION_ID (+) AND
      T25.ROW_ID = T13.OU_EXT_ID (+) AND
      T13.POSITION_ID = T7.ROW_ID (+) AND
      T13.POSITION_ID = T6.PAR_ROW_ID (+) AND
      T6.PR_EMP_ID = T11.PAR_ROW_ID (+) AND
      (T27.INVC_TYPE_CD = :1) AND
      (T27.DEPT_CD = :2);


      Explan Plan Output :
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 3132260827

      ---------------------------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ---------------------------------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 1958 | 1896K (11)| 00:50:46 |
      | 1 | NESTED LOOPS OUTER | | 1 | 1958 | 1896K (11)| 00:50:46 |
      | 2 | NESTED LOOPS OUTER | | 1 | 1922 | 1896K (11)| 00:50:46 |
      | 3 | NESTED LOOPS OUTER | | 1 | 1885 | 1896K (11)| 00:50:46 |
      | 4 | NESTED LOOPS OUTER | | 1 | 1861 | 1896K (11)| 00:50:46 |
      | 5 | NESTED LOOPS OUTER | | 1 | 1849 | 1896K (11)| 00:50:46 |
      | 6 | NESTED LOOPS OUTER | | 1 | 1817 | 1896K (11)| 00:50:46 |
      | 7 | NESTED LOOPS OUTER | | 1 | 1792 | 1896K (11)| 00:50:46 |
      | 8 | NESTED LOOPS OUTER | | 1 | 1771 | 1896K (11)| 00:50:46 |
      | 9 | NESTED LOOPS OUTER | | 1 | 1739 | 1896K (11)| 00:50:46 |
      | 10 | NESTED LOOPS OUTER | | 1 | 1483 | 1896K (11)| 00:50:46 |
      | 11 | NESTED LOOPS OUTER | | 1 | 1451 | 1896K (11)| 00:50:46 |
      | 12 | NESTED LOOPS OUTER | | 1 | 1419 | 1896K (11)| 00:50:46 |
      | 13 | NESTED LOOPS OUTER | | 1 | 1361 | 1896K (11)| 00:50:46 |
      | 14 | NESTED LOOPS OUTER | | 1 | 1276 | 1896K (11)| 00:50:46 |
      | 15 | NESTED LOOPS OUTER | | 1 | 1202 | 1896K (11)| 00:50:46 |
      | 16 | NESTED LOOPS OUTER | | 1 | 1108 | 1896K (11)| 00:50:46 |
      | 17 | NESTED LOOPS OUTER | | 1 | 1087 | 1896K (11)| 00:50:46 |
      | 18 | NESTED LOOPS OUTER | | 1 | 1040 | 1896K (11)| 00:50:46 |
      | 19 | NESTED LOOPS OUTER | | 1 | 939 | 1896K (11)| 00:50:46 |
      | 20 | NESTED LOOPS OUTER | | 1 | 894 | 1896K (11)| 00:50:46 |
      | 21 | NESTED LOOPS OUTER | | 1 | 868 | 1896K (11)| 00:50:46 |
      | 22 | NESTED LOOPS OUTER | | 1 | 843 | 1896K (11)| 00:50:46 |
      | 23 | NESTED LOOPS OUTER | | 1 | 824 | 1896K (11)| 00:50:46 |
      | 24 | NESTED LOOPS OUTER | | 1 | 690 | 1896K (11)| 00:50:46 |
      | 25 | NESTED LOOPS OUTER | | 1 | 613 | 1896K (11)| 00:50:46 |
      | 26 | NESTED LOOPS OUTER | | 1 | 457 | 1896K (11)| 00:50:46 |
      |* 27 | TABLE ACCESS FULL | S_INVOICE | 1 | 269 | 1896K (11)| 00:50:46 |
      | 28 | TABLE ACCESS BY INDEX ROWID| S_PROJ | 1 | 188 | 1 (0)| 00:00:01 |
      |* 29 | INDEX UNIQUE SCAN | S_PROJ_P1 | 1 | | 1 (0)| 00:00:01 |
      | 30 | TABLE ACCESS BY INDEX ROWID | S_PAYMENT_TERM | 1 | 156 | 1 (0)| 00:00:01 |
      |* 31 | INDEX UNIQUE SCAN | S_PAYMENT_TERM_P1 | 1 | | 1 (0)| 00:00:01 |
      | 32 | TABLE ACCESS BY INDEX ROWID | S_INSCLM_ELMNT | 1 | 77 | 1 (0)| 00:00:01 |
      |* 33 | INDEX UNIQUE SCAN | S_INSCLM_ELMNT_P1 | 1 | | 1 (0)| 00:00:01 |
      | 34 | TABLE ACCESS BY INDEX ROWID | S_INS_CLAIM | 1 | 134 | 1 (0)| 00:00:01 |
      |* 35 | INDEX UNIQUE SCAN | S_INS_CLAIM_P1 | 1 | | 1 (0)| 00:00:01 |
      | 36 | TABLE ACCESS BY INDEX ROWID | S_PERIOD | 1 | 19 | 1 (0)| 00:00:01 |
      |* 37 | INDEX UNIQUE SCAN | S_PERIOD_P1 | 1 | | 1 (0)| 00:00:01 |
      | 38 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 25 | 2 (0)| 00:00:01 |
      |* 39 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
      | 40 | TABLE ACCESS BY INDEX ROWID | S_ORDER_TNTX | 1 | 26 | 2 (0)| 00:00:01 |
      |* 41 | INDEX UNIQUE SCAN | S_ORDER_TNTX_P1 | 1 | | 1 (0)| 00:00:01 |
      | 42 | TABLE ACCESS BY INDEX ROWID | S_DEPOSIT_TNT | 1 | 45 | 1 (0)| 00:00:01 |
      |* 43 | INDEX UNIQUE SCAN | S_DEPOSIT_TNT_P1 | 1 | | 1 (0)| 00:00:01 |
      | 44 | TABLE ACCESS BY INDEX ROWID | S_ORDER | 1 | 101 | 2 (0)| 00:00:01 |
      |* 45 | INDEX UNIQUE SCAN | S_ORDER_P1 | 1 | | 1 (0)| 00:00:01 |
      | 46 | TABLE ACCESS BY INDEX ROWID | S_INVLOC | 1 | 47 | 1 (0)| 00:00:01 |
      |* 47 | INDEX UNIQUE SCAN | S_INVLOC_P1 | 1 | | 1 (0)| 00:00:01 |
      | 48 | TABLE ACCESS BY INDEX ROWID | S_DOC_QUOTE | 1 | 21 | 1 (0)| 00:00:01 |
      |* 49 | INDEX UNIQUE SCAN | S_DOC_QUOTE_P1 | 1 | | 1 (0)| 00:00:01 |
      | 50 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT_TNTX | 1 | 94 | 1 (0)| 00:00:01 |
      |* 51 | INDEX RANGE SCAN | S_ORG_EXT_TNTX_U1 | 1 | | 1 (0)| 00:00:01 |
      | 52 | TABLE ACCESS BY INDEX ROWID | S_PTY_PAY_PRFL | 1 | 74 | 1 (0)| 00:00:01 |
      |* 53 | INDEX UNIQUE SCAN | S_PTY_PAY_PRFL_P1 | 1 | | 1 (0)| 00:00:01 |
      | 54 | TABLE ACCESS BY INDEX ROWID | S_ADDR_PER | 1 | 85 | 2 (0)| 00:00:01 |
      |* 55 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 1 | | 1 (0)| 00:00:01 |
      | 56 | TABLE ACCESS BY INDEX ROWID | S_ADDR_PER | 1 | 58 | 1 (0)| 00:00:01 |
      |* 57 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 1 | | 1 (0)| 00:00:01 |
      | 58 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 32 | 1 (0)| 00:00:01 |
      |* 59 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
      | 60 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 32 | 1 (0)| 00:00:01 |
      |* 61 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
      | 62 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 256 | 2 (0)| 00:00:01 |
      |* 63 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
      | 64 | TABLE ACCESS BY INDEX ROWID | S_ACCNT_POSTN | 1 | 32 | 3 (0)| 00:00:01 |
      |* 65 | INDEX RANGE SCAN | S_ACCNT_POSTN_U1 | 1 | | 2 (0)| 00:00:01 |
      | 66 | TABLE ACCESS BY INDEX ROWID | S_POSTN | 1 | 21 | 1 (0)| 00:00:01 |
      |* 67 | INDEX UNIQUE SCAN | S_POSTN_U2 | 1 | | 1 (0)| 00:00:01 |
      | 68 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 25 | 2 (0)| 00:00:01 |
      |* 69 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
      | 70 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 32 | 2 (0)| 00:00:01 |
      |* 71 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
      |* 72 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 12 | 1 (0)| 00:00:01 |
      | 73 | TABLE ACCESS BY INDEX ROWID | S_ASSET | 1 | 24 | 2 (0)| 00:00:01 |
      |* 74 | INDEX UNIQUE SCAN | S_ASSET_P1 | 1 | | 2 (0)| 00:00:01 |
      | 75 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT_X | 1 | 37 | 2 (0)| 00:00:01 |
      |* 76 | INDEX RANGE SCAN | S_ORG_EXT_X_U1 | 1 | | 2 (0)| 00:00:01 |
      | 77 | TABLE ACCESS BY INDEX ROWID | S_CON_ADDR | 1 | 36 | 3 (0)| 00:00:01 |
      |* 78 | INDEX RANGE SCAN | S_CON_ADDR_U1 | 1 | | 2 (0)| 00:00:01 |
      ---------------------------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      27 - filter("T27"."DEPT_CD"=:2 AND "T27"."INVC_TYPE_CD"=:1)
      29 - access("T27"."PROJ_ID"="T8"."ROW_ID"(+))
      31 - access("T27"."PAYMENT_TERM_ID"="T23"."ROW_ID"(+))
      33 - access("T27"."ELEMENT_ID"="T26"."ROW_ID"(+))
      35 - access("T26"."INSCLM_ID"="T14"."ROW_ID"(+))
      37 - access("T27"."BL_PERIOD_ID"="T20"."ROW_ID"(+))
      39 - access("T27"."LAST_UPD_BY"="T15"."PAR_ROW_ID"(+))
      41 - access("T27"."ORDER_ID"="T18"."ROW_ID"(+))
      43 - access("T18"."PR_DEPOSIT_ID"="T21"."ROW_ID"(+))
      45 - access("T27"."ORDER_ID"="T4"."ROW_ID"(+))
      47 - access("T4"."SRC_INVLOC_ID"="T3"."ROW_ID"(+))
      49 - access("T4"."QUOTE_ID"="T12"."ROW_ID"(+))
      51 - access("T12"."BU_ID"="T19"."PAR_ROW_ID"(+))
      53 - access("T27"."PER_PAY_PRFL_ID"="T2"."ROW_ID"(+))
      55 - access("T27"."BL_ADDR_ID"="T1"."ROW_ID"(+))
      57 - access("T27"."BL_ADDR_ID"="T22"."ROW_ID"(+))
      59 - access("T8"."BU_ID"="T5"."PAR_ROW_ID"(+))
      61 - access("T27"."REMIT_ORG_EXT_ID"="T16"."PAR_ROW_ID"(+))
      63 - access("T27"."ACCNT_ID"="T25"."PAR_ROW_ID"(+))
      65 - access("T25"."ROW_ID"="T13"."OU_EXT_ID"(+) AND "T25"."PR_POSTN_ID"="T13"."POSITION_ID"(+))
      67 - access("T13"."POSITION_ID"="T6"."PAR_ROW_ID"(+))
      69 - access("T6"."PR_EMP_ID"="T11"."PAR_ROW_ID"(+))
      71 - access("T25"."BU_ID"="T10"."PAR_ROW_ID"(+))
      72 - access("T13"."POSITION_ID"="T7"."ROW_ID"(+))
      74 - access("T3"."CG_ASSSET_ID"="T17"."ROW_ID"(+))
      76 - access("T27"."ACCNT_ID"="T24"."PAR_ROW_ID"(+))
      78 - access("T27"."BL_ADDR_ID"="T9"."ADDR_PER_ID"(+) AND "T27"."ACCNT_ID"="T9"."ACCNT_ID"(+))
      filter("T27"."ACCNT_ID"="T9"."ACCNT_ID"(+))

      117 rows selected.

      We are using 10.2.0.3 oracle version.