오라클 성능 고도화 원리와 해법 II (2012년)
- 뷰 Merging 이란?
- 단순 뷰(Simple View) Merging
- 복합 뷰(Complex View) Merging
- 비용기반 쿼리 변환의 필요성
- Merging 되지 않은 뷰의 처리방식
뷰 Merging 이란?
- 사람의 눈으로 볼 때는 쿼리를 블록화하는 것이 더 읽기 편하지만 최적화를 수행하는 옵티마이저의 시각에서는 더 불편하다. 그러므로 옵티마이저는 쿼리 블록을 풀어내려는 습성을 갖는다.
- 힌트 : merge, no_merge
단순 뷰(Simple View) Merging
- 조건절과 조인문만을 포함하는 단순 뷰는 no_merge 힌트를 사용하지 않는 한 언제든 Merging이 일어난다.
-- 뷰 생성
create or replace view emp_salesman
as
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp
where job = 'SALESMAN';
-- 단순 뷰와 조인
select 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
;
-- 뷰 Merging 하지 않고 그대로 최적화시 실행계획
SQL> explain plan for
2 select /*+ no_merge */
3 e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
4 from emp_salesman e, dept d
5 where d.deptno = e.deptno
6 and e.sal >= 1500
7 ;
해석되었습니다.
경 과: 00:00:00.00
SQL> @plan
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3251065420
-------------------------------------------------------------------------------------------------
| 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 RANGE SCAN | DEPT_DEPTNO_IDX | 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")
20 개의 행이 선택되었습니다.
- no_merge 힌트를 줬는데도 뷰가 merge 되어서 나옴 ???
- no_merge 되었다면 emp 테이블 액세스 후 view 오퍼레이션이 있어야 됨.
- 뷰 Merging 했을 때의 실행계획은 위와 같음
-- 뷰 Merging 작동시 변환된 쿼리 모습 예상
select e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
from emp e, dept d
where d.deptno = e.deptno
and e.job = 'SALESMAN'
and e.sal >= 1500
복합 뷰(Complex View) Merging
- group by 절
- distinct 연산 포함
- 복합 뷰는 _complex_view_merging 파라미터가 true 일때만 Merging이 일어남.
버전별 파라미터 값
| 8i | 기본값이 false. 복합 뷰 Merging 원할 때는 merge 힌트 사용해야 됨. |
| 9i | 기본값이 true. 동일 결과 보장시 항상 복합 뷰 Merging이 일어남. |
| 10g | 일단 복합 뷰 Merging 시도. 원본 쿼리 비용도 같이 계산해서 Merging 했을 때의 비용이 더 낮을 때만 변환. 비용기반 쿼리 변환. |
- _complex_view_merging 파라미터 값과 상관없이 아래 항목은 복합 뷰 Merging 불가
- 집합(set) 연산자(union, union all, intersect, minus)
- connect by 절
- ROWNUM pseudo 컬럼
- 집계함수(avg, count, max, min, sum) 사용 - group by 없이 전체 집계하는 경우
- 분석함수
-- 복합 뷰를 포함한 쿼리 - 뷰 Merging 함
SQL> explain plan for
2 select d.dname, avg_sal_dept
3 from dept d
4 ,(select /*+ merge */ deptno, avg(sal) avg_sal_dept
5 from emp
6 group by deptno) e
7 where d.deptno = e.deptno
8 and d.loc = 'CHICAGO'
9 ;
해석되었습니다.
경 과: 00:00:00.12
SQL> @plan
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1182506179
-----------------------------------------------------------------------------------------
| 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 | 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")
-- 뷰 Merging 하면 아래와 같은 형태가 됨. 실행계획이 같음을 볼 수 있음.
select d.dname, avg(sal)
from dept d, emp e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
group by d.rowid, d.dname
;
-----------------------------------------------------------------------------------------
| 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 | 5 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
위 쿼리가 뷰 Merging 을 통해서 얻을 수 있는 이점
- dept.loc = 'CHICAGO' 인 데이터만 선택해서 조인하고, 조인에 성공한 집합만 group by 한다는데 있음.
- 뷰 Merging 을 하지 않는다면 emp 테이블의 모든 데이터를 group by 해서 조인하고 loc = 'CHICAGO' 조건을 필터링하게 되므로 emp 테이블을 스캔하는 과정에서 불필요한 레코드 액세스가 많이 발생됨.
비용기반 쿼리 변환의 필요성
- 9i에서 무조건 Merging 되도록 되어 있었지만 10g에서는 비용기반으로 동작하게 되는데 이는 복합 뷰 merging이 성능이 더 안 좋을 때가 많기 때문이다.
- loc = 'CHICAGO' 조건에 의해 선택된 deptno 가 emp 테이블에서 많다면 오히려 Table Full Scan 을 감수하더라도 group by 로 먼저 집합을 줄이고 조인하는 편이 나을 수 있다.
- 비용기반 쿼리 변환 방식을 제어하기 위한 파라미터 : _optimizer_cost_based_transformation
Merging 되지 않은 뷰의 처리방식
- 뷰 Merging 시도
(실패)> 조건절 Pushing 시도(실패)> 뷰 쿼리 블록을 개별적으로 최적화
-- 뷰 Merge 되었을 때 SQL 트레이스
select /*+ leading(e) use_nl(d) */
*
from dept d
,(select * from emp) e
where e.deptno = d.deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 14 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
14 NESTED LOOPS (cr=14 pr=0 pw=0 time=0 us)
14 NESTED LOOPS (cr=12 pr=0 pw=0 time=32 us cost=17 size=798 card=14)
14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=6 us cost=3 size=518 card=14)
14 INDEX RANGE SCAN DEPT_DEPTNO_IDX (cr=4 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 91722)
14 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=0 us cost=1 size=20 card=1)
-- no_merge 힌트 사용했을 때 SQL 트레이스
select /*+ leading(e) use_nl(d) */
*
from dept d
,(select /*+ NO_MERGE */ * from emp) e
where e.deptno = d.deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 14 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
14 NESTED LOOPS (cr=14 pr=0 pw=0 time=0 us)
14 NESTED LOOPS (cr=12 pr=0 pw=0 time=39 us cost=17 size=1498 card=14)
14 VIEW (cr=8 pr=0 pw=0 time=8 us cost=3 size=1218 card=14)
14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=5 us cost=3 size=518 card=14)
14 INDEX RANGE SCAN DEPT_DEPTNO_IDX (cr=4 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 91722)
14 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=0 us cost=1 size=20 card=1)
- 실행계획에 "VIEW" 라고 표시되지만 중간집합을 생성하는 것은 아니므로 부분범위 처리가 가능함.
-- 뷰가 NL 조인에서 Inner 테이블로 액세스될 때
select /*+ leading(d) use_nl(e) */
*
from dept d
,(select /*+ NO_MERGE */ * from emp) e
where e.deptno = d.deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.02 4 37 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 4 37 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
14 NESTED LOOPS (cr=37 pr=4 pw=4 time=0 us cost=15 size=1498 card=14)
4 TABLE ACCESS FULL DEPT (cr=8 pr=4 pw=4 time=3 us cost=3 size=80 card=4)
14 VIEW (cr=29 pr=0 pw=0 time=3 us cost=3 size=348 card=4)
56 TABLE ACCESS FULL EMP (cr=29 pr=0 pw=0 time=2 us cost=3 size=518 card=14)
- VIEW 처리 단계에서 중간 집합 생성하지 않음. 드라이빙 테이블 dept에서 읽은 건수만큼 emp 테이블에 대한 Full Scan 반복.
- emp 테이블을 읽은 블록 개수(=29)와 출력된 결과 건수(4*14=56)으로 알 수 있음.
-- 인라인 뷰에 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
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 15 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 15 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
14 NESTED LOOPS (cr=15 pr=0 pw=0 time=0 us cost=19 size=1498 card=14)
4 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=10 us cost=3 size=80 card=4)
14 VIEW (cr=7 pr=0 pw=0 time=2 us cost=4 size=348 card=4)
56 SORT ORDER BY (cr=7 pr=0 pw=0 time=2 us cost=4 size=518 card=14)
14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=1 us cost=3 size=518 card=14)
- 건수(=14), 블록 I/O(=7) 를 보면 emp 테이블을 한번만 Full Scan 했고, 소트 수행 후 PGA에 저장된 중간집합을 반복 액세스한 것을 알 수 있음.
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3286
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.