다른 테이블은 인덱스가 없나요?

TB_MP_AL_LM_MSTR , TB_MP_AL_LM_MCHTMSTR 

TB_MP_AL_LM_MSTR:PK(TUP_ID)

TB_MP_AL_LM_MCHTMSTR :  PK(TUP_ID,MCHT_ID)

입니다.

전체 데이터를 다 가지고 와야하나요?

건수 많으시면 정렬시 문제 생기는거 같습니다.

페이징은 쓰시지않나요?

월데이타는 40만건 정도이고 TB_MP_AL_LM_MCHTMSTR 코드 데이타가 120만건정도 됩니다.

페이징처리는 합니다.

코드 테이블에 조인조건 인덱스가 다 있으면 leading , use_nl, index 힌트를 다 주셔서 명시적으로 플랜 고정 해보시면 되지 않을까요

정렬빼도 마찬가지로 느립니다.

제가 알고 싶은것은 plan 보시면 코드값에 대한 명칭을 가져올 경우

120만건에 대한 코드명을 가저올경우 full 스캔하지 않고 index로 가져올수 있는지 입니다.

힌트 말씀 하셨는데 구체적으로 코드테이블에 힌트를 어떻게 적용해야 하나요. 방법 알려주시면 감사하겠습니다.

SELECT /*+ leading(a) use_nl(b) index(b, 인덱스 이름) use_nl(c) index(c, 인덱스 이름) use_nl(d) index(d, 인덱스 이름) use_nl(e) index(e, 인덱스 이름) */

SUBSTR(A.APV_DT,1,6)      

...

메인 쿼리에서 group by 이후 건수가 많이 줄어들면 그룹바이 한 것 까지 인라인 뷰로 만들고 그 뒤에 밖에서 코드테이블들 조인해보세요

드라이빙 순서가 잘못된거 같습니다.

WITH V_TB_MP_AP_PS_UGMCHTGRPTOT AS
    (SELECT /*+ materialize */  <= 이부분 변경
        APV_DT                        AS APV_DT
       ,RVG_TUP_ID                    AS RVG_TUP_ID    
       ,RVG_MCHT_GRP_ID               AS RVG_MCHT_GRP_ID
       ,UG_TUP_ID                     AS UG_TUP_ID
       ,UG_MCHT_GRP_ID                AS UG_MCHT_GRP_ID
       ,TR_DS_C                       AS TR_DS_C
       ,TR_TP_C                       AS TR_TP_C
       ,TR_RSN_C                      AS TR_RSN_C
       ,SUM(NVL(TR_CN, 0))            AS CN
       ,SUM(NVL(TR_PNT, 0))           AS PNT    
    FROM TB_MP_AP_PS_UGMCHTGRPTOT A
    WHERE 1=1
      AND APV_DT BETWEEN '20190101' AND '20190230'
    GROUP BY
        APV_DT
       ,RVG_TUP_ID
       ,RVG_MCHT_GRP_ID
       ,UG_TUP_ID
       ,UG_MCHT_GRP_ID
       ,TR_DS_C
       ,TR_TP_C
       ,TR_RSN_C)

......

    SELECT /*+ LEADING(A) */ <= 이부분 변경
        SUBSTR(A.APV_DT,1,6)            AS APV_YM
       , A.RVG_TUP_ID                      AS RVG_TUP_ID    -- 코드1
       , B.TUP_NM                          AS RVG_TUP_NM        -- 명칭1
       , A.RVG_MCHT_GRP_ID                 AS RVG_MCHT_GRP_ID   -- 코드2
       , C.NCHT_NM                         AS RVG_MCHT_GRP_NM   -- 명칭2
       , A.UG_TUP_ID                       AS UG_TUP_ID         -- 코드3
       , D.TUP_NM                          AS UG_TUP_NM         -- 명칭3
       , A.UG_MCHT_GRP_ID                  AS UG_MCHT_GRP_ID    -- 코드4
       , E.NCHT_NM                         AS UG_MCHT_GRP_NM    -- 명칭4
       , A.TR_DS_C                         AS TR_DS_C
       , A.TR_TP_C                         AS TR_TP_C
       , A.TR_RSN_C                        AS TR_RSN_C
       , SUM(A.CN)                         AS CN
       , SUM(A.PNT)                        AS PNT
    FROM V_TB_MP_AP_PS_UGMCHTGRPTOT A
       , TB_MP_AL_LM_MSTR B 
       , TB_MP_AL_LM_MCHTMSTR C
       , TB_MP_AL_LM_MSTR D 
       , TB_MP_AL_LM_MCHTMSTR E

WITH 문에서 일 별로 집계하고, 다시 메인쿼리에서 월별로 집계를 하는 이유가 있나요?, WITH 문 제거하고, 메인 쿼리에서 월별로 집계할 순 없나요?

메인쿼리에서 월별로 집계 해도 마찬가지입니다.

임상준 님께서 알려 주신대로 운영에서 실행해보고 댓글 달겠습니다

감사합니다.

SELECT SUBSTR(T2.APV_DT,1,6)                    AS APV_YM
       , T2.RVG_TUP_ID                          AS RVG_TUP_ID    -- 코드1
       , (SELECT TUP_NM 
          FROM TB_MP_AL_LM_MSTR S1
          WHERE S1.TUP_ID = T2.RVG_TUP_ID)      AS RVG_TUP_NM        -- 명칭1
       , T2.RVG_MCHT_GRP_ID                     AS RVG_MCHT_GRP_ID   -- 코드2
       , (SELECT NCHT_NM
          FROM TB_MP_AL_LM_MCHTMSTR S2
          WHERE S2.TUP_ID = T2.RVG_TUP_ID
          AND S2.MCHT_ID = T2.RVG_MCHT_GRP_ID)  AS RVG_MCHT_GRP_NM   -- 명칭2
       , T2.UG_TUP_ID                           AS UG_TUP_ID         -- 코드3
       , (SELECT TUP_NM
          FROM TB_MP_AL_LM_MSTR S3
          WHERE S3.TUP_ID = T2.UG_TUP_ID)       AS UG_TUP_NM         -- 명칭3
       , T2.UG_MCHT_GRP_ID                      AS UG_MCHT_GRP_ID    -- 코드4
       , (SELECT NCHT_NM
          FROM TB_MP_AL_LM_MCHTMSTR S2
          WHERE E.TUP_ID = T2.UG_TUP_ID
          AND E.MCHT_ID = T2.UG_MCHT_GRP_ID)    AS UG_MCHT_GRP_NM    -- 명칭4
       , T2.TR_DS_C                             AS TR_DS_C
       , T2.TR_TP_C                             AS TR_TP_C
       , T2.TR_RSN_C                            AS TR_RSN_C
       , T2.CN                                  AS CN
       , T2.PNT                                 AS PNT
FROM (SELECT ROWNUM AS RN
           , T1.*
      FROM (SELECT APV_DT
                 , RVG_TUP_ID
                 , RVG_MCHT_GRP_ID
                 , UG_TUP_ID
                 , UG_MCHT_GRP_ID
                 , TR_DS_C
                 , TR_TP_C
                 , TR_RSN_C
                 , SUM(NVL(TR_CN, 0))
                 , SUM(NVL(TR_PNT, 0))
            FROM TB_MP_AP_PS_UGMCHTGRPTOT
            WHERE 1=1
            AND APV_DT BETWEEN '20190101' AND '20190230'
            GROUP BY APV_DT, RVG_TUP_ID, RVG_MCHT_GRP_ID, UG_TUP_ID, UG_MCHT_GRP_ID, TR_DS_C, TR_TP_C, TR_RSN_C
            ORDER BY APV_DT, RVG_TUP_ID, RVG_MCHT_GRP_ID, UG_TUP_ID, UG_MCHT_GRP_ID, TR_DS_C, TR_TP_C, TR_RSN_C
            ) T1
      WHERE ROWNUM <= :b1 --30
      ) T2
WHERE RN >= :b2 --1
;

혹시나 해서 말씀드려봅니다.

통계수집은 하셨는지요? 간혹 대량 데이터를 넣고, 통계수집을 안해서 이런 문제가 발생할 때가 있더라구요. 

비교되는 컬럼의 자료형이 일치하는지 확인해 보세요.