Post

<오라클 성능 고도화 원리와 해법2> Ch04-03 뷰 Merging

Posted Updated
By Mijong Go 11 min read

오라클 성능 고도화 원리와 해법2 - Ch04-03 뷰 Merging

(1) 뷰 Merging 이란?

1
2
3
4
5

< 쿼리1 >
SELECT *
FROM (SELECT * FROM emp WHERE job = 'SALESMAN') a
   , (SELECT * FROM dept WHERE loc = 'CHICAGO') b
WHERE a.deptno = b.deptno;

위 <쿼리1>과 같이 습관적으로 인라인 뷰를 많이 사용하는 사람들을 종종 볼 수 있는데, 그렇게 작성하는 것이 더 읽기 편하기 때문이다. 서브쿼리도 마찬가지다. 서브쿼리로 표현하면 아무래도 좀 더 직관적으로 읽힌다.

그런데 사람의 눈으로 볼 때는 쿼리를 블록화하는 것이 더 읽기 편할지 모르지만 최적화를 수행하는 옵티마이저의 시각에서는 더 불편하다. 그런 탓에 옵티마이저는 가급적 쿼리 블록을 풀어내려는 습성을 갖고 있다.

1
2
3
4
5
6

< 쿼리2 >
SELECT *
FROM emp a, dept b
WHERE a.deptno = b.deptno
AND a.job = 'SALESMAN'
AND b.loc = 'CHICAGO';

따라서 위에서 본 <쿼리1>의 뷰 쿼리 블록은 액세스 쿼리 블록(뷰를 참조하는 쿼리 블록)과의 머지(merge) 과정을 거쳐 다른 형태로 변환되는데, 이를 ‘뷰 Merging’이라고 한다. 이처럼 뷰 Merging을 거친 쿼리라야 옵티마이저가 더 다양한 액세스 경로를 조사 대상으로 삼을 수 있게 된다. 이 기능을 제어하는 힌트로는 merge nomerge가 있다.

<쿼리1>처럼 뷰를 사용하면 쿼리 성능이 더 느려진다는 속설이 있는데, 과연 그럴까? <쿼리1>과 <쿼리2>는 뷰 Merging을 통해 100% 같은 실행 계획으로써 수행되며, 쿼리 성능에 하등 차이가 없다.

뷰 Merging과 다음 절에서 설명하는 조건절(Predicate) Pushing이 불가능한 형태의 뷰를 사용했을 때 성능이 느려지기도 하므로 뷰 때문에 쿼리 성능이 느려진다는 말도 전혀 틀린 말은 아니다. 하지만 막연한 경험치를 가지고 뷰 사용을 꺼리는 것보다는 옵티마이저의 쿼리 변환 원리를 정확히 이해함으로써 적절한 때에 뷰를 사용할 수 있어야 한다.

(2) 단순뷰(Simple View) Merging

조건절과 조인문만을 포함하는 단순뷰(Simple View)는 no merge 힌트를 사용하지 않는 한 언제든 Merging이 일어난다. 반면 group by 절이나 distinct 연산을 포함하는 복합뷰(Complex View)는 파라미터 설정(complex_view_merging=true/9부터는 기본값이 true) 또는 힌트 사용에 의해서만 뷰 Merging이 가능하다. 또한 집합(se) 연산자, connect by, rownum 등을 포함하는 복합뷰(Non-mergeable Views)는 아예 뷰 Merging이 불가능하다.

(3) 복합뷰(Complex View) Merging

아래 항목을 포함하는 복합뷰(Complex View)는 - complex_view_merging 파라미터를 true로 설정할 때만 Merging이 일어난다.

  • group by 절
  • select-list에 distinct 연산자 포함

8i에서는 complex_view_merging 파라미터의 기본값이 false이므로 기본적으로 복합뷰 Merging이 작동하지 않는다. 복합뷰 Merging을 원할 때는 merge 힌트를 사용해야만 한다.

9i부터는 이 파라미터가 기본적으로 true로 설정돼 있으므로 동일한 결과가 보장되는 한 복합뷰 Merging이 항상 일어난다. 뷰를 Merging하면 더 나은 실행 계획을 생산할 가능성이 높다고 믿기 때문에 그렇게 하는 것이며, 휴리스틱(Heuristic 또는 Rule-based) 쿼리 변환의 전형이라고 할 수 있다. 이를 막고자 할 때는 no_merge 힌트를 사용한다. (뷰 안에 rownum을 넣어도 된다.)

10g에서도 복합뷰 Merging을 일단 시도하지만, 원본 쿼리에 대해서도 비용을 같이 계산해 Merging했을 때의 비용이 더 낮을 때만 그것을 채택한다(~비용 기반 쿼리 변). 10g에서도 뷰 Merging을 강제하고 싶을 때는 merge 힌트를 사용하면 된다.

참고로, 앞절에서 설명한 서브쿼리 Unnesting도 뷰 Merging과 똑같은 진화 과정을 거쳐 비용 기반 쿼리 변환 방식으로 작동하게 되었다.

_complex view_merging 파라미터를 true로 설정하더라도 아래 항목들을 포함하는 복합뷰는 Merging될 수 없다(-> Non-mergeable Views).

  • 집합(se) 연산자(union, union all, intersect, minus)
  • connect by 절
  • ROWNUM pseudo 컬럼
  • select-list에 집계함수(avg, count, max, min, sum) 사용: group by 없이 전체를 집계하는 경우를 말함
  • 분석함수(Analytic Function)

(4) 비용기반 쿼리 변환의 필요성

9i에서 복합뷰(Complex View)를 무조건 Merging하도록 구현한 것은, 옵티마이저 개발팀의 경험과 테스트 결과에 비추어볼 때 보편적으로 더 나은 성능을 보였기 때문일 것이다. 그런데 다른 쿼리 변환은 대개 더 나은 성능을 제공하지만, 복합뷰 Merging은 그렇지 못할 때가 많다.

예를 들어, 앞서 보았던 쿼리를 뷰 Merging했을 때의 성패는 10c = ‘CHICAGO’ 조건에 달렸다. 이 조건에 의해 선택된 deptno가 emp테이블에서 많은 비중을 차지한다면 오히려 Table Full Scan을 감수하더라도 group by로 먼저 집합을 줄이고 나서 조인하는 편이 더 나을 것이다. 9i 환경에서 튜닝할 때, no merge 힌트를 사용하거나 뷰 안에 rownum을 넣어주는 튜닝 기법을 자주 사용하게 되는 이유가 여기에 있다.

그래서 10g부터는 비용 기반 쿼리 변환 방식으로 전환하게 되었고, 이 기능을 제어하기 위한 파라미터가 _optimizer_cast_based_transformation 이다. 설정 가능한 값으로는 아래 5가지가 있다.

  • on
  • off
  • exhaustive
  • linear
  • iterative

비용 기반 서브쿼리 Unnesting도 이 파라미터에 의해 영향을 받는다. 참고로, 바로 다음절에서 설명하는 조건절(Predicate) Pushing도 비용 기반 쿼리 변환 방식으로 전환되었지만 이 기능은 별도의 파라미터(optimizer_push_pred_cost_based)로 제어된다. 그리고 오라클 10.2.0.2에서 _optimizer_connect_by_cost_based 파라미터가 추가된 것을 통해 connect by도 비용 기반 하에 쿼리 변환이 일어나게 되었음을 알 수 있다.

비용 기반 쿼리 변환이 휴리스틱 쿼리 변환보다 고급 기능이긴 하지만 파싱 과정에서 더 많은 일을 수행해야만 한다. 약간의 하드 파싱 부하를 감수하더라도 더 나은 실행 계획을 얻으려는 것이므로 이들파라미터를 off 시키는 것은 바람직하지 않다.

각 쿼리 변환마다 제어할 수 있는 힌트가 따로 있고, 필요하다면 Opt paraml힌트(10gR2부터 제공)를 이용해 아래와 같이 쿼리 레벨에서 파라미터를 변경할 수도 있다.

1

select /*+ opt param('.optimizer_push_pred_cost_based', 'false') */ from ...

실제로 10g에서 조인조건(Join Predicate) Pushdown(4절에서 설명) 기능이 비용 기반 쿼리 변환으로 바뀌면서 쿼리 성능이 느려지는 경우가 자주 발생한다. 이때는 문제가 되는 쿼리 레벨에서 위와 같이 힌트를 이용해 파라미터를 false로 변경하면 된다.

(5) Merging 되지 않은 뷰의 처리 방식

뷰 Merging을 시행했을 때 오히려 비용이 더 증가한다고 판단되거나(10g부터) 부정확한 결과 집합이 만들어질 가능성이 있을 때 옵티마이저는 뷰 Merging을 포기한다. 어떤 이유에서건 뷰 Merging이 이뤄지지 않았을 땐 2차적으로 조건절 Pushing(다음절에서 설명함)을 시도한다. 하지만 이마저도 실패한다면 뷰 쿼리 블록을 개별적으로 최적화하고, 거기서 생성된 서브플랜(Plan)을 전체 실행 계획을 생성하는 데 사용한다. 실제 쿼리를 수행할 때도 뷰 쿼리의 수행 결과를 액세스 쿼리에 전달하는 방식을 사용한다.

Database, SQL Tuning
SQLP
This post is licensed under CC BY 4.0 by the author.