오라클 성능 고도화 원리와 해법 II (2010년)
조건절 Pushing
- 옵티마이져가 뷰를 액세스하는 쿼리를 최적화하는 단계
- View Merging
? 쿼리내부의 인라인뷰/뷰를 풀어 메인쿼리와 같은 레벨의 쿼리블록으로 만드는 쿼리 변환기능이다. (작은 단위의 쿼리블록보다 Merging/Unest된 경우 쿼리 최적화를 위한 다양한 cost계산을 할 수 있기 때문) - Push Predicate
? 해당 뷰를 참조하는 쿼리블록의 조건절을 뷰안의 쿼리로 제공(push)하는 기능임
- View Merging
- 뷰머징이 실패할 경우
- 복합 뷰(Complex View) Merging기능이 비활성화
- 사용자가 No_merge힌트를 사용한 경우
- 뷰안에 Rownum Psedo컬럼이 있는 경우(조건절 Pushing도 되지 않음.)
- 분석함수를 사용한 경우(조건절 Pushing도 되지 않음.)
- 비용기반 쿼리 변환이 작동해 No Merging을 선택한 경우
- Non-mergeable Views : 뷰 Merging이 시행되면 부정확한 결과가 나올 경우?
- 조건절 Pushing의 종류
- 조건절(Predicate) Pushdown
? 쿼리블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣는 것을 말함 - 조건절(Predicate) Pullup
? 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내와서 다른 쿼리블록에 Pushdown하는데 사용( Predicate Move Around) - 조인조건(Join Predicate) Pushdown
? NL조인 수행 중에 드라이빙테이블에서 읽은 값을 건건이 Inner쪽으로 밀어 넣는 것을 말함
- 조건절(Predicate) Pushdown
관련 힌트와 파라미터 :
- /*+ push_pred(table_name/alias) */
- /*+ no_push_pred(table_name/alias) */
- /*+ opt_param('_optimizer_push_pred_cost_based', 'false') */
- /*+ opt_param('_push_join_predicate', 'false') */
- /*+ opt_param('_push_join_union_view', 'false') */
- /*+ opt_param('_push_join_union_view2', 'false') */
{note:title=Be Careful}
- 조건절Pushdown/Pullup은 항상 더 나은 성능을 보장함
- 조인조건Pushdown은 NL조인을 전제로함, NL의 특성상 성능저하될 수 있어 제어힌트 제공
- 조인조건Pushdown은 NL조인을 전제로 하기 때문에 굳이 use_nl힌트를 줄 필요는 없다.
- 9i에서는 push_pred와 use_nl힌트를 함께 사용할 때 pushdown기능이 작동하지 않을 수 있다.
{note}
조건절 Pushdown
Group By 절을 포함한 뷰에 대한 Pushdown
================================================================
primary key : dept_pk on dept(deptno);
index : emp_deptno_idx on emp(deptno);
? 단순한 Group By 뷰에 조건절이 파고든 예
? 뷰 내부에 조건절이 없었지만 Predicate정보에 보면 access("DEPTNO"=30)로 조건이 뷰 안에서 실행된 것을 확인할 수 있다.
orcl:WOONG >
1 select deptno, avg_sal
2 from (select deptno, avg(sal) avg_sal from emp group by deptno) a
3 where deptno = 30;
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1032861127
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 |
| 1 | SORT GROUP BY NOSORT | | 1 | 7 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=30)
Group By절이 포함되어 있는 뷰에 대한 조인을 했을 경우의 예
뷰 merging이 되지 않도록 no_merge힌트로 제어한 후 테스트함
단순한 Group By 뷰에 파고든 예와 같이 뷰안에서 조건이 수행된 것을 확인할 수 있다.
orcl:WOONG >
1 select /*+ no_merge(a) */
2 b.deptno, b.dname, a.avg_sal
3 from (select deptno, avg(sal) avg_sal from emp group by deptno) a
4 , dept b
5 where a.deptno = b.deptno
6 and b.deptno = 30;
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1855526360
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 |
| 1 | NESTED LOOPS | | 1 | 28 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | |
| 4 | VIEW | | 1 | 15 |
| 5 | SORT GROUP BY | | 1 | 10 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 50 |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."DEPTNO"=30)
7 - access("DEPTNO"=30)
실제로는 아래처럼 '조건절 이행'이 먼저 일어났다.
조인조건에 의해서 내부적으로 조건절이 추가되어 집합A로 파고들 수 있었기 때문이다.
orcl:WOONG >
1 select /*+ no_merge(a) */
2 b.deptno, b.dname, a.avg_sal
3 from (select deptno, avg(sal) avg_sal from emp group by deptno) a
4 , dept b
5 where a.deptno = b.deptno
6 and b.deptno = 30
7 and a.deptno = 30;
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1855526360
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 |
| 1 | NESTED LOOPS | | 1 | 39 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | |
| 4 | VIEW | | 1 | 26 |
| 5 | SORT GROUP BY | | 1 | 10 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 50 |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."DEPTNO"=30)
7 - access("DEPTNO"=30)
결국 조인컬럼이 가공되어 조건절 이행이 일어날 수 없다면 Pushdown은 일어날 수 없다.
orcl:WOONG >
1 select /*+ no_merge(a) */
2 b.deptno, b.dname, a.avg_sal
3 from (select deptno||'' deptno
4 , avg(sal) avg_sal from emp group by deptno) a
5 , dept b
6 where a.deptno = b.deptno
7 and b.deptno = 30;
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4150857296
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53 |
| 1 | NESTED LOOPS | | 1 | 53 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 16 |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | |
| 4 | VIEW | | 1 | 37 |
| 5 | SORT GROUP BY | | 1 | 7 |
|* 6 | TABLE ACCESS FULL | EMP | 1 | 7 |
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."DEPTNO"=30)
6 - filter(TO_NUMBER(TO_CHAR("DEPTNO")||'')=30)
================================================================
Union 집합연산자를 포함한 Pushdown
================================================================
primary key : dept_pk on dept(deptno);
index : emp_x1 on emp(deptno, job);
Union/Minus같은 집합연산자를 포함하는 뷰는 뷰 Merging이 되지 않으므로 Pushdown으로만 최적화 될 수 있다.
뷰 merging이 되지 않도록 no_merge힌트로 제어한 후 테스트함
뷰내부의 조건절은 job만 존재하였으나 메인쿼리의 조건(v.deptno = 30)이 파고들어 뷰 안에서 조건에 따른 결합인덱스(emp_x1)를 이용한 처리를 한 것을 확인할 수 있다.
orcl:WOONG >
1 select *
2 from (select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate
3 from emp
4 where job = 'CLERK'
5 union all
6 select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate
7 from emp
8 where job = 'SALESMAN' ) v
9 where v.deptno = 30;
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3488565791
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 148 |
| 1 | VIEW | | 2 | 148 |
| 2 | UNION-ALL | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 |
|* 4 | INDEX RANGE SCAN | EMP_X1 | 2 | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 |
|* 6 | INDEX RANGE SCAN | EMP_X1 | 2 | |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=30 AND "JOB"='CLERK')
6 - access("DEPTNO"=30 AND "JOB"='SALESMAN')
Union/Minus같은 집합연산자를 포함하는 뷰는 뷰 Merging이 되지 않으므로 Pushdown으로만 최적화 될 수 있다.
뷰 merging이 되지 않도록 no_merge힌트로 제어한 후 테스트함
뷰내부의 조건절은 job만 존재하였으나 메인쿼리의 조건(v.deptno = 30)이 파고들어 뷰 안에서 조건에 따른 결합인덱스(emp_x1)를 이용한 처리를 한 것을 확인할 수 있다.
orcl:WOONG >
1 select /*+ ordered use_nl(e) */ d.dname, e.*
2 from dept d
3 ,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from emp
4 where job = 'CLERK'
5 union all
6 select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp
7 where job = 'SALESMAN' ) e
8 where e.deptno = d.deptno
9 and d.deptno = 30;
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1945841114
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 174 |
| 1 | NESTED LOOPS | | 2 | 174 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | |
| 4 | VIEW | | 2 | 148 |
| 5 | UNION-ALL | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 |
|* 7 | INDEX RANGE SCAN | EMP_X1 | 2 | |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 |
|* 9 | INDEX RANGE SCAN | EMP_X1 | 2 | |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=30)
7 - access("DEPTNO"=30 AND "JOB"='CLERK')
9 - access("DEPTNO"=30 AND "JOB"='SALESMAN')
================================================================
조건절 Pullup
================================================================
primary key : dept_pk on dept(deptno);
index : emp_deptno_idx on emp(deptno);
orcl:WOONG >
1 select * from
2 (select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
3 ,(select deptno, min(sal), max(sal) from emp group by deptno) e2
4 where e1.deptno = e2.deptno ;
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1076936357
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 |
|* 1 | HASH JOIN | | 1 | 69 |
| 2 | VIEW | | 1 | 28 |
| 3 | HASH GROUP BY | | 1 | 10 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 50 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | |
| 6 | VIEW | | 3 | 123 |
| 7 | HASH GROUP BY | | 3 | 30 |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 50 |
|* 9 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPTNO"="E2"."DEPTNO")
5 - access("DEPTNO"=10)
9 - access("DEPTNO"=10)
Predicate Information을 보면 두개의 인라인뷰 중에 where deptno = 10 조건을 가진 뷰는 하나지만 다른 한쪽 인라인뷰에도 조건이 파고들어 처리된 것을 확인할 수 있다.
마치 아래와 같은 쿼리로 실행된 것 처럼.
select * from
(select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
,(select deptno, min(sal), max(sal) from emp where deptno = 10 group by deptno) e2
where e1.deptno = e2.deptno ;
================================================================
조인조건 Pushdown
인라인뷰 e에는 d.deptno에 대한 참조가 없음에도 실행계획에는 조인조건이 들어간 것과 같은 Pushdown효과를 보이고 있다.
실행계획에 VIEW PUSHED PREDICATE 오퍼레이션을 확인할 수 있다.
orcl:WOONG >
1 select /*+ no_merge(e) push_pred(e) */ *
2 from dept d, (select empno, ename, deptno from emp) e
3 where e.deptno(+) = d.deptno
4 and d.loc = 'CHICAGO';
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3116586712
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 220 |
| 1 | NESTED LOOPS OUTER | | 4 | 220 |
|* 2 | TABLE ACCESS FULL | DEPT | 1 | 20 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 35 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 80 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D"."LOC"='CHICAGO')
5 - access("DEPTNO"="D"."DEPTNO")
Group By 절을 포함한 뷰에 대한 Pushdown
===============================================================
10g이전에는 Group By를 포함하는 뷰에 대한 조인조건 pushdown기능이 지원되지 않는다.(?)
11g부터 가능함
아래 예제처럼 Outer관계라면 스칼라쿼리로 변환할 수 있으며 이때 여러 컬럼을 참조한다면 Type오브젝트를 사용하거나 Substr함수로 잘라서 사용할 수 있다.
orcl:WOONG >
1 select /*+ leading(d) use_nl(e) no_merge(e) push_pred(e) index(e (deptno)) */
2 d.deptno, d.dname, e.avg_sal
3 from dept d
4 , (select deptno, avg(sal) avg_sal from emp group by deptno) e
5 where e.deptno = d.deptno;
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1993021132
----------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 |
| 1 | NESTED LOOPS | | 3 | 117 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 52 |
|* 3 | VIEW | | 1 | 26 |
| 4 | SORT GROUP BY | | 3 | 21 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 98 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."DEPTNO"="D"."DEPTNO")
===============================================================
Union 집합연산자를 포함한 Pushdown
===============================================================
뷰내부의 조건절은 job만 존재하였으나 메인쿼리의 조건(d.loc = 'CHICAGO')이 파고들어 뷰 안에서 조건에 따른 결합인덱스(emp_x1)를 이용한 처리를 한 것을 확인할 수 있다.
orcl:WOONG >
1 select /*+ push_pred(e) */ d.dname, e.*
2 from dept d
3 ,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from emp
4 where job = 'CLERK'
5 union all
6 select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp
7 where job = 'SALESMAN' ) e
8 where e.deptno = d.deptno
9 and d.loc = 'CHICAGO';
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4023361524
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 200 |
| 1 | NESTED LOOPS | | 2 | 200 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 |
|* 3 | INDEX RANGE SCAN | DEPT_IDX | 1 | |
| 4 | VIEW | | 1 | 80 |
| 5 | UNION ALL PUSHED PREDICATE | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 36 |
|* 7 | INDEX RANGE SCAN | EMP_IDX | 2 | |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 36 |
|* 9 | INDEX RANGE SCAN | EMP_IDX | 2 | |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."LOC"='CHICAGO')
7 - access("DEPTNO"="D"."DEPTNO" AND "JOB"='CLERK')
9 - access("DEPTNO"="D"."DEPTNO" AND "JOB"='SALESMAN')
===============================================================
Outer 조인 뷰에 대한 조인조건 Pushdown
- Outer 조인에서 Inner쪽 집합이 뷰 쿼리일 때 테이블개수에 따라 옵티마이져는 2가지 방법 중 하나를 선택한다.
- 뷰 안에서 참조하는 테이블이 단 하나일 때, 뷰 머징을 시도한다.
- 뷰 안에서 참조하는 테이블이 2개 이상일 때 조인조건식을 Pushdown하려 한다.
- 뷰 내에서 참조하는 테이블 중 하나라도 no_merge로 뷰머징을 제한하면 Pushdown하려 한다.
===============================================================
orcl:WOONG >
1 select /*+ push_pred(b) */
2 a.empno, a.ename, a.sal, a.hiredate, b.deptno, b.dname, b.loc, a.job
3 from emp a
4 ,(select e.empno, d.deptno, d.dname, d.loc
5 from emp e, dept d
6 where d.deptno = e.deptno
7 and e.sal >= 1000
8 and d.loc in ( 'CHICAGO', 'NEW YORK' ) ) b
9 where b.empno(+) = a.empno
10 and a.hiredate >= to_date('19810901', 'yyyymmdd');
경 과: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3468508478
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 952 |
| 1 | NESTED LOOPS OUTER | | 14 | 952 |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | 14 | 476 |
|* 3 | INDEX RANGE SCAN | EMP_HIREDATE_IDX | 14 | |
| 4 | VIEW PUSHED PREDICATE | | 1 | 34 |
| 5 | NESTED LOOPS | | 1 | 35 |
|* 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 |
|* 7 | INDEX UNIQUE SCAN | EMP_PK | 1 | |
|* 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 2 | 40 |
|* 9 | INDEX UNIQUE SCAN | DEPT_PK | 1 | |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."HIREDATE">=TO_DATE('1981-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
6 - filter("E"."SAL">=1000)
7 - access("E"."EMPNO"="A"."EMPNO")
8 - filter("D"."LOC"='CHICAGO' OR "D"."LOC"='NEW YORK')
9 - access("D"."DEPTNO"="E"."DEPTNO")
===============================================================
문서에 대하여
- 최초작성자 : 이지웅
- 최초작성일 : 2010년 05월 14일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*
"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3228
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.