5 Replies Latest reply: Oct 3, 2013 6:16 AM by user12075536123 RSS

    Slow performing insert

    985871

      Hi Friends ,

       

      I am using 11.2.0.3 with 2 rac node.

       

      The below insert query is running very slow . Can you please help .

       

      insert /*+ APPEND */ into WCDS.I$_CDS_INVENTORY ( SERVICE_ID, ALTERNATIVE_ID, 
      ASSET_CREATED_DT, BILLING_ACCOUNT_ID, BILLING_ACCOUNT_NUMBER, 
      BILLING_ACCOUNT_NAME, COMPANY_ID, COMPANY_NAME, INTEGRATION_ID, PRODUCT_ID, 
      PRODUCT_SCODE, PRODUCT_NAME, INV_STATUS, FROM_NAD_KEY, TO_NAD_KEY, FROM_ADDRESS, 
      TO_ADDRESS, FROM_POST_CODE, TO_POST_CODE, PAR_INV_ID, SOURCE_SYSTEM_KEY, 
      SOURCE_SYSTEM_ID, WATERMARK, SOURCE_LAST_UPD , IND_UPDATE ) select 
      C1_SERVICE_ID, C2_ALTERNATIVE_ID, C3_ASSET_CREATED_DT, C4_BILLING_ACCOUNT_ID, 
      C5_BILLING_ACCOUNT_NUMBER, C6_BILLING_ACCOUNT_NAME, CDS_COMPANY.COMPANY_ID, 
      C7_COMPANY_NAME, C8_INTEGRATION_ID, CDS_PRODUCT.PRODUCT_ID, C9_PRODUCT_SCODE, 
      C10_PRODUCT_NAME, C11_INV_STATUS, C12_FROM_NAD_KEY, C13_TO_NAD_KEY, 
      C14_FROM_ADDRESS, C15_TO_ADDRESS, C16_FROM_POST_CODE, C17_TO_POST_CODE, 
      C18_PAR_INV_ID, C19_SOURCE_SYSTEM_KEY, 1, 394806325, C20_SOURCE_LAST_UPD, 'I' 
      IND_UPDATE from WCDS.CDS_COMPANY CDS_COMPANY, WCDS.CDS_PRODUCT CD S_PRODUCT, 
      WCDS.C$_0CDS_INVENTORY where (1=1) And 
      (C9_PRODUCT_SCODE=CDS_PRODUCT.PRODUCT_SCODE) AND 
      (C21_ASSET_OWNER_ACCNT_ID=CDS_COMPANY.SOURCE_SYSTEM_KEY)
      

       

      here is the explain plan

      \

      ------------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|

      ------------------------------------------------------------------------------------------------------------------------

      |   0 | INSERT STATEMENT              |                        |       |       |     9 (100)|          |        |      |

      |   1 |  LOAD AS SELECT               |                        |       |       |            |          |        |      |

      |   2 |   NESTED LOOPS                |                        |       |       |            |          |        |      |

      |   3 |    NESTED LOOPS               |                        |     1 |  3899 |     9  (12)| 00:00:01 |        |      |

      |   4 |     HASH JOIN                 |                        |     1 |  3882 |     6  (17)| 00:00:01 |        |      |

      |   5 |      REMOTE                   | C$_0CDS_INVENTORY      |     1 |  3871 |     2   (0)| 00:00:01 | WCDS_~ | R->S |

      |   6 |      TABLE ACCESS FULL        | CDS_PRODUCT            |    74 |   814 |     3   (0)| 00:00:01 |        |      |

      |   7 |     INDEX RANGE SCAN          | SOURCE_KEY_COMP_UNIQUE |     1 |       |     2   (0)| 00:00:01 |        |      |

      |   8 |    TABLE ACCESS BY INDEX ROWID| CDS_COMPANY            |     1 |    17 |     3   (0)| 00:00:01 |        |      |

      ------------------------------------------------------------------------------------------------------------------------

      \

      Could you please help me here to reduce the time taken which is at present approx 4 mins.

       

      Regards,

      DBApps