CREATE OR REPLACE PROCEDURE SIGMAWAY_DEV.SSM_USR_IF

IS

CURSOR LS_USR IS
        SELECT   USR1.EMPNO        AS V_EMPNO
         , USR1.EMP_HNAME    AS V_USERNM
               , CASE USR2.MAIL_ID when NULL THEN NULL
                 ELSE LOWER(USR2.MAIL_ID) || '@lscable.com'
                 END V_EMAIL
         , USR1.DEPT_CODE    AS V_DEPTCD
         , USR1.DUTY_CODE    AS V_DUTYCD
               ,CASE WHEN NVL(USR1.JIKNM_CODE, '') = '2300'
                          OR
                            NVL(USR1.JIKNM_CODE, '') = '2050'
                          OR
                            NVL(USR1.JIKNM_CODE, '') = '2040'
                          OR
                            NVL(USR1.JIKNM_CODE, '') = '3030'
                          OR
                            NVL(USR1.JIKNM_CODE, '') = '3040'
                          OR
                            NVL(USR1.JIKNM_CODE, '') = '3050'
                          THEN '2220'
                 ELSE NVL(USR1.JIKNM_CODE, '')
                 END  V_JIKGBCD
               , USR2.OFFICE_TEL   AS V_PHONENO
               , USR2.HAND_PHONE   AS V_HPNO
               , USR1.JIKMU_CODE   AS V_JIKMUCD
               , USR1.OFFICE_CODE  AS V_OFFICE_CODE
               , trim(USR1.COMPANY_CODE) AS V_COMPANY_CODE
          FROM TB_INA01@HRLSC.REGRESS.RDBMS.DEV.US.ORACLE.COM USR1  left outer join TB_INA02@HRLSC.REGRESS.RDBMS.DEV.US.ORACLE.COM USR2 on USR1.EMPNO = USR2.EMPNO
                                     inner join TB_INZ49@HRLSC.REGRESS.RDBMS.DEV.US.ORACLE.COM DEPT on USR1.DEPT_CODE  = DEPT.DEPT_CODE
         WHERE USR1.HT_CODE   IN ('1', '2')
           AND USR1.SAMU_CODE IN ('11', '12', '21', '31', '41', '46', '47', '51', '71', '81', '22', '23')
         UNION ALL
        SELECT  MUSR1.EMPNO        AS V_EMPNO
         , MUSR1.EMP_HNAME    AS V_USERNM
               , CASE MUSR2.MAIL_ID when NULL THEN NULL
                 ELSE LOWER(MUSR2.MAIL_ID) || '@lscable.com'
                 END V_EMAIL
         , MUSR1.DEPT_CODE    AS V_DEPTCD
         , MUSR1.DUTY_CODE    AS V_DUTYCD
               ,CASE WHEN NVL(MUSR1.JIKNM_CODE, '') = '2300'
                          OR
                            NVL(MUSR1.JIKNM_CODE, '') = '2050'
                          OR
                            NVL(MUSR1.JIKNM_CODE, '') = '2040'
                          OR
                            NVL(MUSR1.JIKNM_CODE, '') = '3030'
                          OR
                            NVL(MUSR1.JIKNM_CODE, '') = '3040'
                          OR
                            NVL(MUSR1.JIKNM_CODE, '') = '3050'
                          THEN '2220'
                 ELSE NVL(MUSR1.JIKNM_CODE, '')
                 END  V_JIKGBCD
               , MUSR2.OFFICE_TEL   AS V_PHONENO
               , MUSR2.HAND_PHONE   AS V_HPNO
               , MUSR1.JIKMU_CODE   AS V_JIKMUCD
               , MUSR1.OFFICE_CODE  AS V_OFFICE_CODE
               , trim(MUSR1.COMPANY_CODE) AS V_COMPANY_CODE
          FROM TB_INA01@HRLSM.REGRESS.RDBMS.DEV.US.ORACLE.COM MUSR1  left outer join TB_INA02@HRLSM.REGRESS.RDBMS.DEV.US.ORACLE.COM MUSR2 on MUSR1.EMPNO = MUSR2.EMPNO
                                     inner join TB_INZ49@HRLSM.REGRESS.RDBMS.DEV.US.ORACLE.COM MDEPT on MUSR1.DEPT_CODE  = MDEPT.DEPT_CODE
         WHERE MUSR1.HT_CODE   IN ('1', '2')
           AND MUSR1.SAMU_CODE IN ('11', '12', '21', '31', '41', '46', '47', '51', '71', '81', '22', '23');

BEGIN

DELETE FROM SSM_LS_USER;

FOR V_USR IN LS_USR
  LOOP
INSERT INTO SSM_LS_USER
              (
                 V_EMPNO
               , V_USERNM
               , V_DEPTCD
               , V_EMAIL
               , V_PHONENO
               , V_HPNO
               , V_DUTYCD
               , V_JIKGBCD
               , V_JIKMUCD
               , V_OFFICE_CODE
               , V_DIVISION_CODE
               , V_COMPANY_CODE
               , V_REG_USERID
               , V_REG_DTM
               , V_UPDATE_USERID
               , V_UPDATE_DTM
              )
       VALUES (
                 V_USR.V_EMPNO
               , V_USR.V_USERNM
               , V_USR.V_DEPTCD
               , V_USR.V_EMAIL
               , V_USR.V_PHONENO
               , V_USR.V_HPNO
               , V_USR.V_DUTYCD
               , V_USR.V_JIKGBCD
               , V_USR.V_JIKMUCD
               , V_USR.V_OFFICE_CODE
               , ''
               , V_USR.V_COMPANY_CODE
               , 'Procedure'
               , TO_CHAR(SYSDATE, 'yyyymmddhh24miss')
               , 'Procedure'
               , TO_CHAR(SYSDATE, 'yyyymmddhh24miss')
              );
    END LOOP;

  COMMIT;

END SSM_USR_IF;

이런 식으로 MSSQL에 들어 있는 데이터를 DB Link를 사용해서 커서에 넣었다가 오라클에 있는 테이블로 인서트 하는

프로시져를 작성하였습니다.

헌데 이 프로시져가 돌아가는데 20분이 넘게 걸려서 어떤 부분이 시간을 이렇게 오래 걸리게 하는 건지 궁금하여 선배님들께 질문 드립니다.

원인을 파악 할 수 없어서 조언 좀 부탁드리겠습니다. 감사합니다