꿈꾸는 개발자, DBA 커뮤니티 구루비

아래 order by 속도문제 전체쿼리입니다.

by woni_rang [Oracle Tuning] [2013.04.26 13:30:56]

  WITH REPORT_TB AS
   (SELECT IM.IF_ID
, (SELECT DOMAIN_NAME FROM TB_DOMAIN WHERE DOMAIN_ID = IM.IF_DOMAIN) AS IF_DOMAIN_NM
, IM.IF_NAME
, CASE
   WHEN IM.SEND_ID = '1000000437' THEN IM.SEND_MODULE
   WHEN IM.RECV_ID = '1000000437' THEN IM.RECV_MODULE
   END MODULE_NM
, IM.ERP_IF_ID
, IM.IF_INFORMATION
, CASE
   WHEN IM.SEND_ID = '1000000437' THEN 'OUT'
WHEN IM.RECV_ID = '1000000437' THEN 'IN'
END IN_OUT
   , IM.IF_TYPE AS IF_TYPE_CD
   , (SELECT COMMON_CODE FROM COMMON_CDLST_CODE WHERE COMMON_LARGE_CLASS_CODE = 'AC023' AND COMMON_SMALL_CLASS_CODE = IM.IF_TYPE) AS IF_TYPE
   , CASE
WHEN IM.SEND_ID = '1000000437' AND IM.RECV_ID = '1000000201' THEN 'Y'
WHEN IM.SEND_ID = '1000000201' AND IM.RECV_ID = '1000000437' THEN 'Y'
ELSE 'N'
END EDI_FLAG
   , IM.SEND_SYS_ID
   , (SELECT KOR_NAME FROM SYS_INFO WHERE SYSTEM_ID = IM.SEND_ID) AS SEND_NM
   , (SELECT WM_CONCAT(DISTINCT SERVER_NAME) FROM SERVER_SW_INFO WHERE SYSTEM_ID = IM.SEND_ID AND SID_NAME = IM.SEND_SID) AS SEND_SERVER_NM
   , IM.SEND_SID
   , (SELECT WM_CONCAT(DISTINCT SERVER_IP) FROM SERVER_SW_INFO WHERE SYSTEM_ID = IM.SEND_ID AND SID_NAME = IM.SEND_SID) AS SEND_SERVER_IP
   , (SELECT B.EMPLOYEE_NAME||' '||B.POSITION_NAME
    FROM CONT_INFO A, USER_MST B
   WHERE A.SYS_CONTACT_TYPE_CODE = '03'
AND A.SYS_CONTACT_ID = B.USER_ID
AND A.SYSTEM_ID = IM.SEND_ID
AND ROWNUM = 1) AS SEND_CONT_USER
   , IM.SEND_OBJ
   , IM.RECV_ID
   , (SELECT KOR_NAME FROM SYS_INFO WHERE SYSTEM_ID = IM.RECV_ID) AS RECV_SYS_NM
   , (SELECT WM_CONCAT(DISTINCT SERVER_NAME) FROM SERVER_SW_INFO WHERE SYSTEM_ID = IM.RECV_ID AND SID_NAME = IM.RECV_SID) AS RECV_SERVER_NM
   , IM.RECV_SID
   , (SELECT WM_CONCAT(DISTINCT SERVER_IP) FROM SERVER_SW_INFO WHERE SYSTEM_ID = IM.RECV_ID AND SID_NAME = IM.RECV_SID) AS RECV_SERVER_IP
   , (SELECT B.EMPLOYEE_NAME||' '||B.POSITION_NAME
    FROM CONT_INFO A, USER_MST B
   WHERE A.SYS_CONTACT_TYPE_CODE = '03'
AND A.SYS_CONTACT_ID = B.USER_ID
AND A.SYSTEM_ID = IM.RECV_ID
AND ROWNUM = 1) AS RECV_CONT_USER
   , IM.RECV_OBJ
    FROM TB_IF_MASTER IM)

SELECT IF_ID
    , IF_DOMAIN_NM
    , IF_NAME
, MODULE_NM
, ERP_IF_ID
, IF_INFORMATION
, IN_OUT
, IF_TYPE
, EDI_FLAG
, SEND_SYS_ID
, SEND_SYS_NM
, SEND_SERVER_NM
, SEND_SID
, SEND_SERVER_IP
, SEND_CONT_USER
, SEND_OBJ
, RECV_SYS_ID
, RECV_SYS_NM
, RECV_SERVER_NM
, RECV_SID
, RECV_SERVER_IP
, RECV_CONT_USER
, RECV_OBJ
   FROM REPORT_TB
  ORDER BY 1 DESC

전체쿼리입니다...
마농님께서 알려주신 1,2번은 제가 잘못한건지 어쩐건지 별 차이가 없었습니다...흑..
어떻게 튜닝하면 될까요...?
다듬는다고 다듬었는데 보시기 불편하실 수 있겠네요..

by 우리집아찌 [2013.04.26 13:37:50]

WITH 묶으셨네요. 왜 그러신건지.

자세히는 모르겠으나 저렇게 WITH 묶어서 쿼리를 던지면 느려지는 경우가 종종 있었습니다.

by woni_rang [2013.04.26 14:43:05]

아래 select에서 간단하게 글자 자르고 할게 있어서요 with써서 묶었습니다.

by 채용근 [2013.04.26 13:44:57]

WITH 로 묶는다고 느려지거나 그러진 않아요 View 머징이 되느냐 안되느냐인데

그것은 MATERIALIZE 나 INLINE 힌트로 제어 가능하구요.

IF_ID에 인덱스가 걸려있다면 인덱스 DESCENDIG이 되는지 확인해보셔야 합니다.

by 우리집아찌 [2013.04.26 13:47:24]

그렇군요.. 공부가 부족해서요..

by woni_rang [2013.04.26 14:51:56]

밑에 글에 마농님이 알려주신
/*+ INDEX(TB_IF_MASTER TB_IF_MST_IDX1) */
방법 말씀이신가요?

by 마농 [2013.04.26 13:53:32]

-- 2. 정렬을 최소화 하고 정렬후에 가공작업에 대한 쿼리
-- 변경전 --
SELECT 지지고볶고
  FROM tb_if_master im
 ORDER BY 1 DESC
;
-- 변경후 --
SELECT 지지고볶고
  FROM (-- 필요한 최소한의 컬럼만으로 정렬
        SELECT if_id
             , if_name
             , if_type
             , recv_id
             , recv_obj
             , recv_sid
             , send_id
             , send_obj
             , send_sid
             , send_sys_id
             , erp_if_id
             , if_information
             , if_domain
             , send_module
             , recv_module
          FROM tb_if_master
         ORDER BY 1 DESC
        ) im
;

by woni_rang [2013.04.26 14:47:15]

2번째 방법은 많이 빨라지진않고 아주 미세하게 빨라지네요..;;

by 마농 [2013.04.26 14:55:55]

데이터 건수가 어찌 되나요?
실행계획은 확인 가능한가요?
각 서브쿼리는 인덱스를 타나요?

by woni_rang [2013.04.26 15:04:39]

tb_if_master에는 3만건이 안되구요
각 서브쿼리에 들어가는 테이블에도 하나만 30만건이고 나머진 1만건 이하입니다.
그리고 index 걸어놨구요...
실행계획은 실력이 미천한지라 어떻게 확인하는지 몰라서요...

by woni_rang [2013.04.26 15:15:50]

현재는 속도가 4~5초대로 나오고있습니다. 아까는 한번씩 15초도 나오던데 왜그런지 모르겟네요..

그리고 pl/sql에서 F5번이 실행계획보는건가요..어떻게 읽어야하는건지 모르겠네요..휴;;;

by 마농 [2013.04.26 15:18:29]

스칼라서브쿼리부분 다 주석처리 하고 돌려보세요.
빨라지나요? 그렇다면 하나씩 주석 풀면서 속도 체크하세요.

by woni_rang [2013.04.26 15:26:05]

네 확인해봤는데요
WM_CONCAT으로 로우합치는 부분 4개가 속도를 거의 다 차지하네요.
그부분만 주석처리하면 0.7초대로 떨어집니다.

by 아린 [2013.04.26 15:41:20]

SERVER_SW_INFO 테이블의 index 는 어떻게 구성되어 있나요?

by woni_rang [2013.04.26 15:47:50]

검색조건인 system_id와 sid_name으로 했습니다.

by 마농 [2013.04.26 15:44:55]

-- 조인 형태로 바꿔보세요.
WITH sw_info AS
(
SELECT system_id, sid_name
     , wm_concat(DISTINCT server_name) server_nm
     , wm_concat(distinct server_ip  ) server_ip
  FROM server_sw_info
 GROUP BY system_id, sid_name
)
SELECT im.if_id
     , 어쩌구저쩌구
     , s.server_nm AS send_server_nm
     , s.server_ip AS send_server_ip
     , r.server_nm AS recv_server_nm
     , r.server_ip AS recv_server_ip
  FROM tb_if_master im
     , sw_info r
     , sw_info s
 WHERE im.recv_id  = r.system_id(+)
   AND im.recv_sid = r.sid_name (+)
   AND im.send_id  = s.system_id(+)
   AND im.send_sid = s.sid_name (+)
 ORDER BY im.if_id DESC
;

by woni_rang [2013.04.26 16:05:55]

ㄷㄷㄷ
0.9 ~ 1.1초대로 떨어졌습니다...
정말 감사합니다. 오늘도 덕분에 많이 배웠습니다.!!
정말 감사합니다(__)

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변