Oracle_HINT, USE HASH로 성능 끌어올리기

얼마전 속도가 오래 걸리는 쿼리 튜닝 요청을 받고, 메뉴얼데로 튜닝해서 돌려봤는데 엄청난 속도차이를 경험했다. 아래 코드는 예시로 학과를 조회하는데 학과별로 학생수와 학과시험에서 50점 이하인 학생 수를 함께 조회하는 쿼리를 예로 들어보자.

SELECT A.DEPTID
  , A.DEPTNAME
  , (SELECT COUNT(*) 
      FROM tSTUDENT -- 학생테이블
      WHERE DEPTID = A.DEPTID
    ) AS TOTALCNT  -- 학과학생수
  , (SELECT COUNT(*) 
      FROM tSTUDENT TS -- 학생테이블
        INNER JOIN vTESTRESULT VT ON VT.STUDENTID = TS.STUDENTID AND VT.SCORE < 50 -- 시험결과 VIEW
      WHERE TS.DEPTID = A.DEPTID
    ) AS UNDER50CNT -- 학과시험 50점 이하 학생수
  FROM tDEPT A --학과 테이블
 

위와 같이 쿼리를 짰었다. 시험 결과 VIEW 자체가 복잡하게 조인 되어있어 시간을 많이 잡아먹는다. 그래서 위의 쿼리 수행 속도가 10초를 넘기는 말도 안되는 속도가 나왔다. 메인쿼리 결과값 만큼 서브쿼리가 반복 수행(50점 아래 학생수를 뽑는 서브쿼리가 문제가됨)되면서 오래걸림… 튜닝이 필요했다.

SELECT /*+ leading (A B C ) USE_HASH( B C ) */  
  A.DEPTID
  , A.DEPTNAME
  , B.TOTALCNT
  , C.UNDER50CNT
  FROM tDEPT A --학과 테이블
    , (SELECT DEPTID
          , COUNT(*) AS TOTALCNT
        FROM tSTUDENT
        GROUP BY DEPTID
      ) B
    , (SELECT TS.DEPTID
          , COUNT(*) AS UNDER50CNT
        FROM tSTUDENT TS, vTESTRESULT VT
        WHERE TS.STUDENTID = VT.STUDENTID
          AND VT.SCORE < 50
        GROUP BY DEPTID
      ) C
  WHERE A.DEPTID = B.DEPTID(+)
    AND A.DEPTID = C.DEPTID(+)
 

위처럼 튜닝하니 1초만에 수행됬다. 문제가 되는 쿼리의 서브쿼리를 Outer join으로 변환하고 Hash-join처리하여 속도를 감소시킨다.

hint 사용 설명

• leading( A B C) : join 할때 먼저 읽는 테이블을 지정. A -> B -> C로 읽어 드린다는 것을 힌트로 지정
• USE_HASH( B C) : 해쉬조인 유도(공부가 필요할듯… 아직 잘 개념이 안잡힌다)

어떻게 속도가 빨라졌을까?

• 위의 쿼리에서 인라인뷰를 통해 중간 집합을 구성하게 되는데, 인덱스가 존재한다고 해도 인덱스를 통해 탐색하지 못한다.(임시테이블)
• 해쉬조인은 비용이 가장 많이 드는 조인이지만 인덱스 존재유무에 영향을 받지않고 최고의 성능을 낼수있다.
• 오라클 고급 수준은 되야 적재적소에 잘 활용 할수 있을 듯 하다… 아직 공부가 많이 필요한 튜닝..ㅜㅜ