행위

뷰 튜닝

DB CAFE

1 뷰 머징(View Merging)

1.1 뷰 머징 이란?

옵티마이저는 최적화 쿼리 수행을 위해 서브 쿼리블록을 풀어서 메인 쿼리와 결합(MERGE) 하려는 특성이 있음

  • SQL 원본
SELECT * 
  FROM ( SELECT * FROM EMP WHERE JOB = 'SALESMAN' ) A
     , ( SELECT * FROM DEPT WHERE LOC = 'CHICAGO' ) B
 WHERE A.DEPTNO = B.DEPTNO;
  • 서브쿼리나 인라인 뷰처럼 쿼리를 블록화 할 시, 가독성이 더 좋기 때문에 습관적으로 사용
  • View Merging 으로 오라클 내부에서 아래 형태로 SQL 변환
SELECT *  
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
   AND A.JOB = 'SALESMAN'
   AND B.LOC = 'CHICAGO';
  • View Merging 이유 : 옵티마이저가 더 다양한 액세스 경로를 조사대상으로 삼을 수 있음

1.2 View Merging 제어 힌트

  1. merge
  2. no_merge

1.3 단순 뷰(Simple View) Merging

  1. 가능한 조건
    1. 조건절과 조인문만을 포함하는 단순 뷰(Simple View)일 경우, no_merge 힌트를 사용하지 않는 한 언제든 Merging 발생
    2. group by, distinct 연산을 포함하는 복합뷰(Complex View)는 파라미터 설정 또는 힌트 사용에 의해서만 뷰 Merging 가능
  2. 불가능한 조건
    1. 집합 연산자, connect by, rownum 등을 포함한 복합 뷰(Non-mergeable Views)는 뷰 Merging 불가능
-- Simple View 예제 
create or replace view emp_salesman as
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from   emp
where job = 'SALESMAN';

1.3.1 Simple View 뷰 No Merging 최적화

SQL> select /*+ no_merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
  2  from   emp_salesman e, dept d
  3  where  d.deptno = e.deptno
  4  and    e.sal >= 1500 ;
 
      EMPNO ENAME      JOB              MGR        SAL DNAME
---------- ---------- --------- ---------- ---------- --------------
      7844 TURNER     SALESMAN        7698       1500 SALES
      7499 ALLEN      SALESMAN        7698       1600 SALES

Execution Plan
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     2 |   156 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |              |       |       |            |          |
|   2 |   NESTED LOOPS                 |              |     2 |   156 |     4   (0)| 00:00:01 |
|   3 |    VIEW                        | EMP_SALESMAN |     2 |   130 |     2   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| EMP          |     2 |    58 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | EMP_SAL_IDX  |     8 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN           | PK_DEPT      |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | DEPT         |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("JOB"='SALESMAN')
   5 - access("SAL">=1500)
   6 - access("D"."DEPTNO"="E"."DEPTNO")

1.3.2 Simple View 뷰 Merging 최적화

SQL> select /*+ merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
  2  from   emp_salesman e, dept d
  3  where  d.deptno = e.deptno
  4  and    e.sal >= 1500 ;

Execution Plan
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    84 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     2 |    84 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP         |     2 |    58 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX |     8 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT     |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT        |     1 |    13 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("JOB"='SALESMAN')
   4 - access("SAL">=1500)
   5 - access("D"."DEPTNO"="DEPTNO")

<일반 조인문>

SQL> select e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
  2  from   emp e, dept d
  3  where  d.deptno = e.deptno
  4  and    e.job = 'SALESMAN'
  5  and    e.sal >= 1500;
Execution Plan

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    84 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     2 |    84 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP         |     2 |    58 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX |     8 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT     |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT        |     1 |    13 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."JOB"='SALESMAN')
   4 - access("E"."SAL">=1500)
   5 - access("D"."DEPTNO"="E"."DEPTNO")
* 단순 뷰를 Merging 할 경우, 파라미터 or 힌트 설정을 하지 않을 경우 일반 조인문과 똑같은 형태로 변환 후 처리

1.4 복합 뷰(Complex View) Merging

  • group by절 , select-list에 distinct연산자 포함하는 복합 뷰
  • _complex_view_merging 파라미터 값이 true로 설정할 때만 Merging 발생
  • 10g에서는 복합 뷰 Merging을 일단 시도하지만, 원본 쿼리에 대해서도 비용을 같이 계산해 Merging했을 때의 비용이 더 낮을 때만 그것을 채택 (비용기반 쿼리 변환)
  • 10g 이전 _complex_view_merging 파라미터 기본 값 (8i : false, 9i : true)
  • complex_view_merging 파라미터를 true로 설정해도 Merging 될 수 없는 복합 뷰
    • 집합(set)연산자( union, union all, intersect, minus )
    • connect by절
    • ROWNUM pseudo 컬럼
    • select-list에 집계 함수(avg, count, max, min, sum)사용 : group by 없이 전체를 집계하는 경우를 말함
    • 분석 함수
    • 복합뷰를 포함한 쿼리 (뷰 머징 발생 시)
SQL> select d.dname, avg_sal_dept
  2  from   dept d
  3        ,(select deptno, avg(sal) avg_sal_dept from emp group by deptno) e
  4  where  d.deptno = e.deptno
  5  and    d.loc='CHICAGO';

DNAME          AVG_SAL_DEPT
-------------- ------------
SALES            1566.66667
 
Execution Plan
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     3 |    81 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY                |                |     3 |    81 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |                |       |       |            |          |
|   3 |    NESTED LOOPS               |                |     5 |   135 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | DEPT           |     1 |    20 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | EMP_IDX_DEPTNO |     5 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("D"."LOC"='CHICAGO')
   5 - access("D"."DEPTNO"="DEPTNO")



복합뷰를 일반 조인절로 변경한 쿼리

SQL> select d.dname,avg(sal)
  2  from   dept d,emp e
  3  where  d.deptno=e.deptno
  4  and    d.loc='CHICAGO'
  5  group by d.rowid,d.dname;

DNAME            AVG(SAL)
-------------- ----------
SALES          1566.66667
 
Execution Plan
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    27 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY                |                |     1 |    27 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |                |       |       |            |          |
|   3 |    NESTED LOOPS               |                |     5 |   135 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | DEPT           |     1 |    20 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | EMP_IDX_DEPTNO |     5 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("D"."LOC"='CHICAGO')
   5 - access("D"."DEPTNO"="E"."DEPTNO")


  • 뷰머징이 발생 할 경우
    • dept테이블에서 loc컬럼이 'CHICAGO'인 데이터를 먼저 필터링하고 조인, 조인대상 집합만 group by 실행
  • 뷰머징이 발생되지 않을 경우
    • emp 테이블의 모든 테이블을 group by 한 후 필터링하게 되면서 불필요한 레코드 엑세스 발생



1.5 비용기반 쿼리 변환의 필요성

9i : 복합 뷰를 무조건 머징 => 대부분 더 나은 성능 제공하지만 복합뷰 머징 시 그렇지 못할 때가 많음

 ☞ no_merge 힌트 등 뷰안에 rownum 을 넣어주는 튜닝 기법 활용

10g 이후 비용기반 쿼리 변환 방식으로 처리

  ☞ _optimizer_cost_based_transformation 파라미터 사용 → 설정값 5가지 (on, off, exhaustive, linear, iteraive)


on  : 적절한 것을 스스로 선택 exhaustive : cost가 가장 저렴한 것 선택 linear  : 순차적 비교 후 선택 literation : 변환이 수행 유무에 따른 cost를 비교하기 위한 경우의 수로 listeration 정의



opt_param 힌트 이용으로 쿼리 레벨에서 파라미터 변경가능 (10gR2부터 제공) p.496 참조


1.6 Merging 되지 않은 뷰의 처리방식

  1. 1단계 : 뷰머징 시행 시 오히려 비용이 증가된다고 판단(10g이후) 되거나, 부정확한 결과 집합 가능성이 있을 시 뷰머징 포기
  2. 2단계 : 뷰머징이 포기 할 경우 조건절 Pushing 시도 (다음절에서 자세히 다룸)
  3. 3단계 : 뷰 쿼리 블록을 개별적으로 최적화된 개별 플랜을 전체 실행계획에 반영 (즉, 뷰 쿼리 수행 결과를 엑세스 쿼리에 전달)
SQL> select /*+ leading(e) use_nl(d) */ *
  2  from   dept d
  3       ,(select /*+ NO_MERGE */ * from emp) e
  4  where  e.deptno = d.deptno;

14 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |  1498 |    17   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |    14 |  1498 |    17   (0)| 00:00:01 |
|   3 |    VIEW                      |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("E"."DEPTNO"="D"."DEPTNO")
     ※ 실행계획의 "VIEW" 로 표시된 오퍼레이션 단계가 추가 되었을 시, 실제로 다음 단계로 넘어가기 전 중간집합을 생성하는 것은 아님