오라클 성능 고도화 원리와 해법 II (2016년)
03.뷰 Merging
뷰머징이란
뷰머징
<쿼리1>
select *
from (select * from emp where job = 'SALESMAN') a
, (select * from dept where loc = 'CHICAGO') b
where a.deptno = b.deptno
위와 같은 쿼리는 사람이 보기에는 읽기 편하지만
최적화를 수행하는 옵티마이저에게는 좋지 않은 쿼리이다.
옵티마이저는 가급적 위와 같은 쿼리 블록을 아래 쿼리처럼 풀어내려는 습성이 있다.
<쿼리2>
select *
from emp a, dept b
where a.deptno = b.deptno
and a.job = 'SALESMAN'
and b.loc = 'CHICAGO'
<쿼리1>의 뷰 쿼리 블록은 액세스 쿼리 블록과의 머지 과정을 거쳐 <쿼리2>와 같은 형태로 변환되는데 이를 뷰머징이라고 한다.
이와 같이 쿼리가 풀어지면 옵티마이저는 더욱 다양한 액세스 경로를 탐색할 수 있게 된다.
단순 뷰(Simple View) Merging
- 조건절과 조인문만을 포함하는 단순 뷰는 no_merge 힌트를 사용하지 않는 한 언제든 merge가 일어난다.
예시
create or replace view emp_salesman
as
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp
where job = 'SALESMAN' ;
아래와 같은 쿼리를 뷰머징하지 않는다면
select /*+ no_merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
from emp_salesman e, dept d
where d.deptno = e.deptno
and e.sal >= 1500 ;
--------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | |
| 2 | NESTED LOOPS | | 1 |
| 3 | VIEW | EMP_SALESMAN | 1 | -> 뷰 그대로 사용
|* 4 | TABLE ACCESS FULL | EMP | 1 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
--------------------------------------------------------------
뷰머징을 해준다면..
select /*+ merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
from emp_salesman e, dept d
where d.deptno = e.deptno
and e.sal >= 1500 ;
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | |
| 2 | NESTED LOOPS | | 1 |
|* 3 | TABLE ACCESS FULL | EMP | 1 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
---------------------------------------------------------
복합 뷰(Complex View) Merging
- group by 절, select 절에 distinct 연산자를 포함하는 복합 뷰는 \_complex_view_merging 파라미터를 true로 설정할 때만 merging이 일어난다.
- \_complex_view_merging 파라메터 버전에 따른 변화
- 8i : default 값이 false \-> merge힌트를 사용해야 merge 시도한다.
- 9i이후 : default 값이 true \-> no_merge힌트를 사용해야 merge 안 한다.
예시
select d.dname, avg_sal_dept
from dept d
, (select deptno, avg(sal) avg_sal_dept
from emp
group by deptno) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
-----------------------------
| Id | Operation |
-----------------------------
| 0 | SELECT STATEMENT |
| 1 | HASH GROUP BY |
|* 2 | HASH JOIN |
|* 3 | TABLE ACCESS FULL|
| 4 | TABLE ACCESS FULL|
-----------------------------
-> 뷰를 해체하여 일반 조인으로 변경하였다.
select d.dname, avg_sal_dept
from dept d
, (select /*+no_merge*/deptno, avg(sal) avg_sal_dept
from emp
group by deptno) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
-------------------------------
| Id | Operation |
-------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
| 2 | JOIN FILTER CREATE |
|* 3 | TABLE ACCESS FULL |
| 4 | VIEW |
| 5 | HASH GROUP BY |
| 6 | JOIN FILTER USE |
|* 7 | TABLE ACCESS FULL|
-------------------------------
- 아래 항목을 포함할 때는 merge가 불가능하다.
- 집합 연산자(union, uion all, minus)
- connect by 절
- rownum pseudo 컬럼
- select-list에 집계 함수(avg, count, max, min, sum) 사용 : group by 없지 전체 집계하는 경우
- 분석함수
테스트 : 뷰머징 불가능 케이스
--집합연산의 경우
select d.dname
from dept d
, (select *
from emp
where job = 'MANAGER'
union all
select *
from emp
where job = 'SALESMAN') e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
------------------------------
| Id | Operation |
------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
|* 2 | TABLE ACCESS FULL |
| 3 | VIEW |
| 4 | UNION-ALL |
|* 5 | TABLE ACCESS FULL|
|* 6 | TABLE ACCESS FULL|
------------------------------
merge가 되지 않는다.
--connect by 사용한 경우
select d.dname
from dept d
, (select
from emp
start with mgr is null
connect by nocycle prior empno = mgr
) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
---------------------------------------------------
| Id | Operation |
---------------------------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
|* 2 | TABLE ACCESS FULL |
| 3 | VIEW |
|* 4 | CONNECT BY NO FILTERING WITH START-WITH|
| 5 | TABLE ACCESS FULL |
---------------------------------------------------
-->merge 안 됨
select d.dname
from dept d
, (select /*+merge*/*
from emp
start with mgr is null
connect by nocycle prior empno = mgr
) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
---------------------------------------------------
| Id | Operation |
---------------------------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
|* 2 | TABLE ACCESS FULL |
| 3 | VIEW |
|* 4 | CONNECT BY NO FILTERING WITH START-WITH|
| 5 | TABLE ACCESS FULL |
---------------------------------------------------
-->hint를 사용해도 안 됨
--rownum을 사용한 경우
select d.dname, e.empno
from dept d
, (select /*+merge*/rownum, deptno, empno
from emp) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | TABLE ACCESS FULL | DEPT |
| 3 | VIEW | |
| 4 | COUNT | |
| 5 | TABLE ACCESS FULL| EMP |
-------------------------------------
-->역시 안 됨
--전체 집계함수를 사용한 경우
select d.dname, avg_sal_dept
from dept d
, (select /*+merge*/ max(deptno) deptno, avg(sal) avg_sal_dept
from emp) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
--------------------------------------
| Id | Operation |
--------------------------------------
| 0 | SELECT STATEMENT |
| 1 | NESTED LOOPS |
| 2 | VIEW |
| 3 | SORT AGGREGATE |
| 4 | TABLE ACCESS FULL |
|* 5 | TABLE ACCESS BY INDEX ROWID|
|* 6 | INDEX UNIQUE SCAN |
--------------------------------------
--집계함수 사용한 경우
select d.dname, avg_sal_dept
from dept d
, (select /*+merge*/deptno, avg(sal) over(partition by empno) avg_sal_dept
from emp) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
------------------------------
| Id | Operation |
------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
|* 2 | TABLE ACCESS FULL |
| 3 | VIEW |
| 4 | WINDOW SORT |
| 5 | TABLE ACCESS FULL|
------------------------------
비용기반 쿼리 변환의 필요성
- 9i에서 뷰를 무조건 머징하였고, 대부분의 경우 뷰머징은 더 나은 성능을 보일 때가 많지만 그렇지 않은 경우도 있었다.
따라서 10g부터는 비용기반 쿼리 변환 방식으로 전환되었다. - 기능을 제어하기 위한 파라미터 \_optimizer_cost_based_transformation
- on
- off
- exhaustive
- linear
- iterative
- 조건절 pushing도 비용기반 쿼리 방식으로 변환 되었지만 파라미터는 다르다.(optimizer_push_pred_cost_based)
- opt_param 힌트를 통해 쿼리 레벨에서도 조정 가능하다.
select /*\+ opt_param('_optimizer_push_pred_cost_based','false') \*/ from ..
Merging 되지 않은 뷰의 처리 방식
- 뷰 Merging이 비용이 증가한다고 판단하거나 부정확한 결과 집합을 만들 가능성이 있으면
뷰머징을 포기하고, 조건절 Pushing을 시도한다. 이마저도 실패하면 개별 블록 최적화한다.
예시
-- 단순뷰였을 때 Merging 실행계획
select /*+ leading(e) use_nl(d) */ *
from dept d
, (select * from emp) e
where e.deptno = d.deptno
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | 14 |
| 2 | TABLE ACCESS FULL| EMP | 14 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 |
--------------------------------------------
-- no_merge문을 사용했을 때 실행계획
select /*+ leading(e) use_nl(d) */ *
from dept d
, (select /*+no_merge*/* from emp) e
where e.deptno = d.deptno
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | 14 |
| 2 | VIEW | | 14 |
| 3 | TABLE ACCESS FULL| EMP | 14 |
|* 4 | TABLE ACCESS FULL | DEPT | 1 |
---------------------------------------------
'VIEW'라고 표시되었다고 해서 중간집합을 만드는 것은 아니다!
위와 같은 실행계획에서도 부분범위 처리가 가능하다!
select /*+ leading(e) use_nl(d) */ *
from dept d
, (select /*+no_merge*/* from emp) e
where e.deptno = d.deptno
and rownum = 1
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
|* 1 | COUNT STOPKEY | | 1 | | 1 |
| 2 | NESTED LOOPS | | 1 | | 1 |
| 3 | NESTED LOOPS | | 1 | 1 | 1 |
| 4 | VIEW | | 1 | 1 | 1 |
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 1 |
|* 6 | INDEX UNIQUE SCAN | DEPT_PK | 1 | 1 | 1 |-->부분범위 처리하고 있음
| 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |
----------------------------------------------------------------------------
view라고 표시된 오퍼레이션 단계가 추가되었다고 해서 다음 단계로 넘어가기 전에 중간집합을 생성하는 것은 아님을 알 수 있다.
select /*+ leading(d) use_nl(e) */ *
from dept d
, (select /*+no_merge*/* from emp) e
where e.deptno = d.deptno
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 12 |
| 1 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 12 |
| 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 4 |00:00:00.01 | 3 |
|* 3 | VIEW | | 4 | 4 | 14 |00:00:00.01 | 9 |
| 4 | TABLE ACCESS FULL| EMP | 4 | 14 | 56 |00:00:00.01 | 9 |
--------------------------------------------------------------------------------------
뷰가 inner table로 액세스될 때도 view 처리 단계에서 중간 집합을 생성하지 않는다.
dept에서 읽은만큼 emp 테이블을 fullscan하고 있다.
(총 buffer = 9이고 출력 건수는 56)
inline view에 order by 를 추가해보자.
select /*+ leading(d) use_nl(e) */ *
from dept d
, (select /*+no_merge*/* from emp order by ename) e
where e.deptno = d.deptno
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 5 | | | |
| 1 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 5 | | | |
| 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 4 |00:00:00.01 | 3 | | | |
|* 3 | VIEW | | 4 | 4 | 14 |00:00:00.01 | 2 | | | |
| 4 | SORT ORDER BY | | 4 | 14 | 56 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 2 | | | |
------------------------------------------------------------------------------------------------------------------
inline view에 order by를 추가했더니..
emp 테이블을 액세스하는 단계에서 14개의 row만 접근하였고 buffer 사용도 9->2로 줄었다.
sort를 위해 memory 사용량은 늘어났다.
emp 테이블을 한번만 full scan했고 소트 수행 후 PGA에 저장된 중간집합을 반복 액세스한 것을 알 수 있다.
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3359
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.