Post

<오라클 성능 고도화 원리와 해법2> Ch04-04 조건절 Pushing

Posted
By Mijong Go 13 min read

오라클 성능 고도화 원리와 해법2 - Ch04-04 조건절 Pushing

뷰를 액세스하는 쿼리를 최적화할 때 옵티마이저는 우선적으로 뷰 Merging을 고려한다. 하지만 아래와 같은 이유로 뷰 Merging에 실패할 수 있다.

• 복합뷰(ComplexView) Merging 기능이 비활성화됨 • 사용자가 no_merge 힌트 사용함 • Non-mergeable Views: 뷰 Merging 시행하면 부정확한 결과 가능성 • 비용기반 쿼리변환이 작동해 No Merging 선택(10g 이후)

어떤 이유에서건 뷰 Merging에 실패했을 때, 옵티마이저는 포기하지 않고 2차적으로 조건절(Predicate) Pushing을 시도한다. 이는 뷰를 참조하는 쿼리 블록의 조건을 뷰 쿼리 블록 안으로 Pushing하는 기능을 일컫는다.

조건절이 가능한 빨리 처리되도록 뷰 안으로 밀어넣는다면, 뷰 안에서의 처리 일량을 최소화함은 물론 리턴되는 결과 건수를 줄임으로써 다음 단계에서 처리해야 할 일량을 줄일 수 있다.

조건절 Pushing 종류

조건절 Pushing과 관련하여 오라클은 다음과 같은 기술을 사용한다.

• 조건절(Predicate) Pushdown: 쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어넣는 것 • 조건절(Predicate) Pullup: 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 Pushdown하는 데 사용함 (Predicate Move Around) • 조인 조건(Join Predicate) Pushdown: NL 조인 수행 중에 드라이빙 테이블에서 읽은 값을 건 건 이 Inner 쪽(= right side) 뷰 쿼리 블록 안으로 밀어넣는 것을 말함

관련 힌트와 파라미터

조건절 Pushdown과 Pullup은 항상 더 나은 성능을 보장하므로 별도의 힌트를 제공하지 않는다. 하지만, 조인 조건 Pushdown은 NL 조인을 전제로하기 때문에 성능이 더 나빠질 수도 있다. 따라서 오라클은 조인 조건 Pushdown을 제어할 수 있도록 push_pred와 mo_push_pred 힌트를 제공한다.

그러나 조인 조건 Pushdown 기능이 10g에서 비용 기반 쿼리 변환이 되면서 오히려 느려지는 현상이 종종 나타나고 있다. 이때는 문제가 되는 쿼리 레벨에서 아래와 같이 힌트를 이용해 파라미터를 false로 변경하면 된다.

1

select /*+ opt_param('_optimizer_push_pred_cost_based', 'false') */* from ...

10g에서 시스템 환경에 따라 이 기능이 문제를 일으켜 쿼리 결과가 틀리는 문제도 발생하는 것으로 보고되고 있는데, 그 때는 패치를 적용하거나 아래와 같이 시스템 레벨 변경이 불가피하다.

1

alter system set "_optimizer_push_pred_cost_based" = false;

Non-pushable View

뷰 안에 rownum을 사용하면 Non-mergeableView가 되는데, 동시에 Non-pushableView가 되는 사실도 기억하기 바란다. 왜냐하면, rownum은 집합을 출력하는 단계에서 실시간으로 부여되는 값인데, 조건 Pushing이 작동하면 기존에 없던 조건절이 생겨 같은 로우가 다른 값이 부여받을 수 있기 때문이다. 어떤 경우에도 옵티마이징 기법에 따라 쿼리 결과가 달라지는 일이 발생해선 안된다.

분석함수(Analytic Function)를 사용해도 Non-mergeable, Non-pushable View가 되므로 주의하기 바란다.

이제부터 조건절 Pushdown, 조건절 Pullup, 조인조건 Pushdown에 대해 자세히 살펴보자.

(1) 조건절 Pushdown

GROUP BY절을 포함한 뷰에 대한 조건절 Pushdown

복합뷰(ComplexView) Merging에 실패했을 때, 쿼리 블록 밖에 있는 조건 절을 쿼리 블록 안쪽에 밀어넣을 수 있다면 group by해야 할 데이터량을 줄일 수 있다. 상황에 따라서는 더 효과적인 인덱스 선택이 가능해지기도 한다.

여기서는 인라인뷰 자체적으로 사전에 deptno=30 조건을 적용해 데이터량을 줄이고, group by를 하고 나서 조인에 참여하였다. deptno=30 조건이 인라인뷰에 pushdown될 수 있었던 이유는, 다음 절에서 설명하는 ‘조건 이 행’이 먼저 일어났기 때문이다. b.deptno=30 조건이 조인 조건을 타고 a쪽에 전이됨으로써 아래와 같이 a.deptno=30 조건절이 내부적으로 생성된 것이다.

UNION 집합 연산자를 포함한 뷰에 대한 조건 Pushdown

union집합연산자를 포함한 뷰는 Non-mergeableView에 속하므로 복합뷰(ComplexVriew) Merging 기능을 활성화하더라도 뷰 Merging에 실패한다. 따라서 조건절 Pushing을 통해서만 최적화가 가능하다.

(2) 조건절 Pullup

조건절을 쿼리 블록 안으로 밀어넣을 뿐만 아니라 안쪽에 있는 조건들을 바깥쪽으로 끄집어내기도 하는데, 이를 ‘조건절(Predicate) Pullup’이라고 한다. 그리고 그것을 다시 다른 쿼리 블록에 Pushdown하는데 사용한다(-> PredicateMoveAround).

(3) 조인 조건 Pushdown

‘조인조건(Join Predicate) Pushdown’은 말그대로 조인조건절을 뷰 쿼리 블록 안으로 밀어넣는 것으로서, NL 조인 수행 중에 드라이빙 테이블에서 읽은 조인 컬럼값을 Inner 쪽( right side) 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능이다.

조인 조건 Pushdown은 조건 절 Pushdown의 일종이지만 이를 따로 분류한 이유를 앞에서 잠시 언급하였다. 지금까지 본 조인문에서의 조건절 Pushdown은 상수 조건이 조인 조건을 타고 전이된 것을 Pushing하는 기능이었던 반면, 지금 설명하는 조인 조건 Pushdown은 조인을 수행하는 중에 드라이빙 집합에서 얻은 값을 뷰 쿼리 블록 안에 실시간으로 Pushing하는 기능이다.

조인 조건 Pushdown이 일어난 것을 확인하는 방법은, 실행 계획 상에 VIEW PUSHED PREDICATE 오퍼레이션이 나타나는 것을 통해서도 알 수 있다.

조인조건Pushdown을 제어하는 힌트로는 아래 두가지가 있다.

  • push_pred: 조인조건Pushdown을 유도한다.
  • no_push_pred: 조인조건Pushdown을 방지한다.

그리고 이를 제어하는 파라미터로는 아래 세가지가 있다.

  • _push_join_predicate: 뷰 Merging에 실패한 뷰 안쪽으로 조인조건을 Pushdown하는 기능을 활성화한다. union 또는 union all을 포함하는 Non-mergeable 뷰에 대해서는 아래 두 파라미터가 따로 제공된다.
  • _push_join_union_view: union all을 포함하는 Non-mergeable View 안쪽으로 조인조건을 Pushdown하는 기능을 활성화한다.
  • _push_join_union_view2: union을 포함하는 Non-mergeable View 안쪽으로 조인조건을 Pushdown하는 기능을 활성화한다.

위 항목은 10g 기준이며, 9i에서는 pushjoinunionview2 파라미터가 없다. 9i에서 union all을 포함한 뷰에 대한 조인조건Pushdown은 작동하지만 union에는 작동하지 않는다는 뜻이다.

GROUP BY절을 포함한 뷰에 대한 조인조건 Pushdown

가장 질문을 많이 받는 사항 중 하나인데, 아래처럼 group by를 포함하는 뷰에 대한 조인 조건 Pushdown 기능은 11g에 와서야 제공되기 시작했다.

집계함수가 두 개 이상일 때가 문제인데, 2장 6절에서 설명한 것처럼 필요한 컬럼값들을 모두 결합하고서 바깥쪽 액세스 쿼리에서 substr 함수로 다시 분리하거나, 오브젝트TYPE을 사용하는 방식을 고려해볼 수 있다.

UNION집합연산자를 포함한 뷰에 대한 조인조건 Pushdown

union 또는 union all을 포함한 뷰 쿼리 블록에 대한 조인 조건 Pushdown은 10g 이전부터 제공되던 기능이다. union all은 8.1.6부터, union은 10.1.0부터다.

9i에서 use_nl 힌트를 pushpred와 함께 사용하면 조인조건Pushdown 기능이 작동하지 않는 현상이 나타나므로 주의하기 바란다. 이때는 push_pred 힌트만을 사용해야하며, 조인조건Pushdown은 NL조인을 전제로 하므로 굳이 use_nl 힌트를 사용할 필요는 없다.

Outer 조인뷰에 대한 조인조건 Pushdown

Outer 조인에서 Inner 쪽 집합이 뷰 쿼리 블록일 때, 뷰 안에서 참조하는 테이블 개수에 따라 옵티마이져는 다음 2가지 방법 중 하나를 선택한다.

  1. 뷰 안에서 참조하는 테이블이 단 하나일 때, 뷰 Merging을 시도한다.
  2. 뷰 내에서 참조하는 테이블이 두 개 이상일 때, 조인조건식을 뷰 안쪽으로 Pushing하려고 시도한다.

뷰 안에서 참조하는 테이블이 단 하나일 때도 no_merge 힌트를 사용해 뷰 Merging를 방지하면 위와 같이 조인조건Pushdown이 작동한다.

참고로, 앞에서 설명한 UNION집합연산자를 포함한 뷰에 대한 조인조건Pushdown은 10g부터 비용기반으로 작동하기 시작했지만 지금 설명한 Outer 조인뷰에 대한 기능은 9i에서도 비용기반이었다. ‘GROUP BY절을 포함한 뷰에 대한 조인조건Pushdown’은 11g에서도 입증되면서부터 비용기반으로 작동한다.

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