조인튜닝 - 서브쿼리 조인

1. 서브쿼리 변환이 필요한 이유

• 옵티마이저는 비용(Cost)를 평가하고 실행계획 생성에 앞서 사용자로부터 전달받은 SQL을 최적화에
  유리한 형태료 변환하는 작업, 쿼리변환부터 진행
• 쿼리변환 (Query Transformation)은 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는것.
• 오라클의 세가지 서브쿼리
  1. 인라인 뷰 : FROM 절에 사용한 서브쿼리
  2. 중첩된 서브쿼리 : 결과집합을 한정하기 위해 WHERE절에 사용한 서브쿼리
  3. 스칼라 서브쿼리 : 한 레코드(ROW)당 정확히 하나의 값을 반환하는 서브쿼리, 주로 SELECT 절에 사용하지만, 몇가지 예외사항 제외하면 컬럼이 올 수 있는 대부분의 위치에 사용
  
• 이들 서브쿼리를 참조하는 메인쿼리도 하나의 쿼리 블록이며, 옵티마이저는 쿼리 블록 단위로 최적화를 수행

2. 서브쿼리와 조인

• 메인쿼리와 서브쿼리 간에는 부모와 자식이라는 종속적이고 계층적인 관계가 존재
• 서브쿼리는 메인쿼리에 종속되므로, 단독으로 실행할 수 없다.
• 메인쿼리 건수만큼 값을 받아 반복적으로 필터링하는 방식으로 실행해야 한다

  2.1 필터 오퍼레이션

• 서브쿼리를 필터방식으로 처리할때 no_unnest힌트를 사용
• no_unnest는 서브쿼리를 풀어내지말고 그대로 수행하는 힌트
• Filter 오퍼레이션은 기본적으로 NL조인과 처리 루틴이 같다.
• Filter와 NL조인 차이점
  • 필터는 메인쿼리의 한로우가 서브쿼리의 한 로우와 조인에 성공하는순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속처리.
  • 필터는 캐싱기능을 갖는다. 서브쿼리 입력값에 따른 반환값을 캐싱
    서브쿼리 수행전 항상 캐시부터확인, 캐시에서 true/false 여부를 확인할 수 있다면 서브쿼리를 수행하지않아도 되므로 성능을 높이는데 큰 도움이 된다.
    • 캐싱은 쿼리단위로 이루어짐, 쿼리시작시 PGA메모리에 공간을 할당하고 쿼리를 수행하며 공간을 채워나가고, 쿼리를 마치는순간 공간을 반환
  • 필터 서브쿼리는 일반 NL조인과 달리 메인쿼리에 종속되므로 조인순서가 고정, 항상메인쿼리가 드라이빙집합

  2.2 서브쿼리 Unnesting

• 서브쿼리를 그대로 두면 필터 방식을 사용할 수 밖에 없지만, Unnesting하고 나면 일반 조인문처럼 다양한 최적화 기법 사용 가능.
• NL세미조인은 기본적으로 NL조인과 같은 프로세스, 조인에 성공하는 순간 진행을 멈추고 메인쿼리의 다음 로우를 처리한다는 점만 다르다.

  서브쿼리를 Unnesting하는 이유

  -필터방식은 항상 메인쿼리가 드라이빙 집합이지만, Unnesting된 서브쿼리는 메인 쿼리 집합보다 먼저 처리될 수 있다.
  • leading힌트를 통해 서브쿼리 우선 처리가능

    select /+ leading(거래@subq) use_nl(c) /
    c.고객번호, c.고객명
    from 고객 c
    where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
    and exists(
                 select /+ qb_name(subq) unnest / 'x'
                 from 거래
                 where 고객번호 = c.고객번호
                 and 거래일시 >= trunc(sysdate, 'mm'))

• 해시세미조인을 위한 hint hash_sj
• 서브쿼리를 Unnesting해서 메인쿼리와 같은 레벨로 만들면, 다양한 조인메소드를 선택할 수 있고, 조인순서도 마음껏 정할 수 있다.
• 옵티마이저는 많은 조인 테크닉을 가지기 때문에 조인 형태로 변환했을때 필터 오퍼레이션보다 좋은 실행경로를 찾을 가능성이 높아진다.
• 서브쿼리 UNNESTING금지 힌트 : no_unnest
  
  

ROWNUM - 잘쓰면 약, 잘못 쓰면 독

• EXISTS에 ROWNUM조건사용은 의미의 중복
• 옵티마이징 기능을 사용하지 못하게 막는 부작용이 있다
• exists 서브쿼리에 rownum을 넣으면 Unneesting못하게 막는다.

서브쿼리 Pushing

• Unnesting되지않은 서브 쿼리는 항상 필터방식으로 처리되며, 대개 실행계획 맨마지막단계 처리
• 만약 서브쿼리 필터링을 먼저 처리함으로써 조인단계로 넘어가는 로우수를 크게 줄일수있으면 성능은 그만큼 향상
• 서브쿼리 필터링을 먼저처리하게 하려면 push_subq/no_push_subq 힌트를 사용
• 서브쿼리 필터링을 가능한 앞 단계에서 처리하도록 강제하는 기능
• Unnesting되지 않은 서브쿼리에만 작동, Unnesting되는순간 push_subq힌트는 무용지물
• push_subq는 no_unnest와 같이 기술하는것이 올바른 방법
• 서브쿼리 필터링을 가능한 나중에 처리하게 하려면 no_unnest와 no_push_subq를 함께사용

3. 뷰(View)와 조인

• 최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰쿼리를 변환하징낳으면 뷰 쿼리블록을 독립적으로 최적화
• merge힌트는 뷰를 메인쿼리와 머징하는힌트, 뷰머징 방지는 no_merge

조인조건 Pushdown

• 조인조건 pushdown : 메인쿼리를 실행하면서 조인 조건절 값을 건건이 뷰안으로 밀어넣는기능
• 실행계획에 'VIEW PUSHED PREDICATE' 오퍼레이션을 통해 이 기능의 작동여부를 알 수 있다.
• GROUP BY 한 서브쿼리도 부분범위 처리가 가능
• 뷰를 독립실행할때처럼 당월 거래를 모두 읽지않아도되고 뷰를 머징할때처럼 조인에 성공한 전체집합을 GROUP BY 하지않아도된다.
• 힌트는 push_pred이며 옵티마이저가 뷰를 머징하면 작동하지않으니 no_merge와함께사용해야한다.

4. 스칼라 서브쿼리 조인

4.1 특징

• 함수의 경우 함수안의 SELECT 쿼리를 메인쿼리 건수만큼 '재귀적으로' 반복 실행
• 스칼라 서브쿼리는 메인쿼리 건수 만큼 DEPT테이블을 반복해서 읽는측면에서 함수와 비슷하지만, 함수처럼 '재귀적' 실행하는 구조는 아니다.
• 콘텍스트 스위칭 없이 메인쿼리와 서브쿼리를 한 몸체처럼 실행
• Outer조인문처럼 NL조인방식으로 실행, 조인에 실패한 레코드는 NULL로 출력하는점도 같다.
• Outer조인과 차이점은 처리과정에서 캐싱 작용이 일어난다는것.

4.2 스칼라 서브쿼리 캐싱 효과

• 스칼라서브쿼리 조인시 오라클은 조인횟수 최소화를위해 입력값과 출력 값을 내부 캐시에 저장해 둔다.
• 매 레코드 조인할때마다 일단 캐시에서 입력 값을 찾아보고 찾으면, 저장된 '출력 값'을 반환
• 캐시에서 찾지 못할 때만 조인을 수행, 결과는 버리지 않고 캐시에 저장
• 스칼라서브쿼리 입력값은, 그안에서 참조하는 메인쿼리의 컬럼 값
• 이러한 캐싱 매커니즘은 조인성능을 높이는데 큰 도움, 메인쿼리집합이 아무리커도 조인데이터 대부분 캐시에서 찾는다면 조인 수행횟수 최소화가능
• 캐싱은 쿼리 단위로 이루어진다. 쿼리 시작시 PGA메모리에 공간할당하고, 쿼리를 수행하면서 채워나가며, 쿼리를 마치는 순간 공간반환

  많이 사용되는 튜닝기법

• SELECT절 함수는 메인쿼리 결과만큼 반복 수행되는데 아래와같이 서브쿼리를 덧 씌우면 호출횟수를 최소화할수있다(캐싱효과)

  SELECT EMPNO, ENAME, SAL, HIREDATE
  ,(SELECT GET_DNAME(E.DEPTNO) FROM DUAL) DNAME
  FROM EMP E
  WHERE SAL >= 2000

4.3 스칼라 서브쿼리 캐싱 부작용

• 모든 캐시가 그렇듯, 캐시 공간은 늘 부족, 스칼라 서브쿼리에 사용하는 캐시도 매우 작은 메모리 공간
• 10g이후 입력과 출력 값 크기, _query_execution_cache_max_size 파라미터에 의해 사이즈 결정
• 스칼라 서브쿼리 캐싱효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을때 효과적
• 반대의 경우 캐시를 매번확인하는 비용때문에 오히려 성능이 나빠지고 cpu사용률만 높게만듬, 메모리도 더사용

스칼라 서브쿼리 캐싱이 성능에 도움을 주지못하는 경우

• 메인쿼리 집합이 매우 작은 경우
• 서브쿼리 캐싱은 쿼리단위로 이루어짐, 쿼리단위로 쓰고 버린다는 뜻
• 메인쿼리 집합이 클수록 재사용성이 높아 효과도 크다.
• 메인쿼리 집합이 작으면 캐시 재사용성도 낮다.

두개이상의 값 반환시.

• 프로세싱은 NL과 유사, 다른점은 캐싱효과
• 치명적약점은 두개 이상의 값을 반환할 수 없다는 제약
• 부분범위 처리 가능한 스칼라 서브쿼리의 장점을 이용하고싶을때 고민
• 한번읽을때 문자열을 모두 결합하고, 바깥쪽 액세스 쿼리에서 substr함수로 다시분리하는 방식 사용
  (반복적으로 같은테이블에 스칼라 서브쿼리 사용시 비효율)
• 두 개 이상의 값을 반환하고 싶을때, 인라인뷰를 사용하면 편하긴하다.
• But, 뷰사용시 (뷰가머징되지않을때) 전체데이터를 읽어야하거나, Group by때문에 부분처리가 안되는 문제가있다 인라인뷰사용시 장단점은(4절3항) 뷰와 조인참조
• 11g이후부터는 조인조건 Pushdown기능으로인해 인라인뷰를 마음편히 사용가능

스칼라 서브쿼리 Unnesting

• 스칼라 서브쿼리도 NL방식으로 조인하므로 캐싱효과가 크지않으면 랜덤 I/O부담 발생
• 병렬 쿼리에선 될수있으면 스칼라 서브쿼리를 사용하지않아야한다.
  (대량 데이터 처리하는 병렬쿼리는 해시 조인으로 처리해야 효과적)
• 12c부터 스칼라서브쿼리도 Unnesting가능 : 옵티마이저가 자동으로 쿼리변환