오라클 성능 고도화 원리와 해법 II (2010년)
지금까지 설명한 것 외에도 이름이 밝혀지지 않음 많은 쿼리변환이 작동하고 있다.
그 중 몇가지 중요한 쿼리 변환만을 소개하고자 한다.
(1) 조인 컬럼에 IS NOT NULL 조건 추가
select count(e.empno), count(d.dname)
from emp e, dept d
where d.deptno = e.deptno
and sal <= 2900
- 위와 같은 조인문을 처리 할 때 조인 컬럼 deptno가 null인 데이터는 조인 엑세스가 불필요 (어차피 조인에 실패하기 때문)
- 따라서 아래와 같이 필터조건을 추가해 주면 불필요한 테이블 엑세스 및 조인 시도를 줄일수 있어 쿼리 성능 향상에 도움이 된다.
select count(e.empno), count(d.dname)
from emp e, dept d
where d.deptno = e.deptno
and sal <= 2900
and e.deptno is not null
and d.deptno is not null
테스트를 위해 emp테이블을 1,000번 복제한 t_emp테이블을 만들어 보자
SQL> update t_emp set deptno = null;
14000 행이 갱신되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> create index t_emp_idx on t_emp(sal);
인덱스가 생성되었습니다.
14건 갖는 emp 테이블을 1,000번 복제했으므로 총 건수 14,000이고, 모든 레코드의 deptno를 null값으로 갱신했다.
아래 쿼리를 sal <= 2900 조건으로 드라이빙하려고 emp테이블 sal컬럼에 인덱스를 생성했고,
아직 테이블 및 컬럼 통계는 생성되지 않은 상태이다.
SQL> select /*+ ordered use_nl(d) index(e t_emp_idx) index(d dept_pk) */
2 count(e.empno), count(d.dname)
3 from t_emp e, dept d
4 where d.deptno = e.deptno
5 and e.sal <= 2900;
COUNT(E.EMPNO) COUNT(D.DNAME)
-------------- --------------
0 0
Execution Plan
----------------------------------------------------------
Plan hash value: 3628534116
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 815 (1)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 48 | | |
| 2 | NESTED LOOPS | | 1 | 48 | 815 (1)| 00:00:10 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_EMP | 10930 | 277K| 809 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | T_EMP_IDX | 10930 | | 27 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 22 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."SAL"<=2900)
6 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
226 recursive calls
0 db block gets
956 consistent gets
27 physical reads
0 redo size
495 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
위 Predicate 정보를 볼 때 아직 옵티아미저에 의해 추가된 필터 조건은 없다.
t_emp 테이블을 만든 원본 테이블 emp에는 sal <= 2900인 사원 레코드가 10개다
emp 테이블을 1,000번 복제했으므로 t_emp 테이블에는 sal <= 2900인 사원 레코드가 10,000개 존재할 것이다.
실제 수행해 보면, 아래와 같이 t_emp_idx 인덱스를 스캔하면서 10,000번의 테이블을 액세스가 발생하는 것을 알 수 있다.
select /*+ ordered use_nl(d) index(e t_emp_idx) index(d dept_pk) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.02 0 4 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.40 0.40 0 1682 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.40 0.43 0 1686 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=841 pr=0 pw=0 time=201571 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=201554 us)
10000 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=130039 us)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=30269 us)(object id 45120)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=157233 us)
0 INDEX UNIQUE SCAN DEPT_PK (cr=0 pr=0 pw=0 time=62982 us)(object id 45118)
여기에서 t_emp 테이블에서 10,000개 레코드를 읽었지만 dept테이블과의 조인 액세스가 전혀 발생하지 않은 것 (cr=0)에 주목하자
is null 조건을 따로 기술하지 않더라도 읽은 값이 null일때는 조인 액세스를 하지 않는다는 뜻이며, 이는 매우 중요한 사실이
아닐수 없다. (만약 버퍼 Pinning 효과 때문이라면 적어도 dept_pk 인덱스를 두 번은 읽었을 것이다)
이해할수 없는 일이지만 Inner테이블을 Full Table Scan액세스할 때 는 아래처럼 조인 액세스가 발생하며, 11g에서 테스트하더라도
마찬가지다 (10,000번 탐색했으므로 한 번 액세스할 때마다 3개 블록을 읽었다.)
select /*+ ordered use_nl(d) index(e t_emp_idx) full(d) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.15 0.15 0 30841 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.15 0.15 0 30845 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=30841 pr=0 pw=0 time=155087 us)
0 NESTED LOOPS (cr=30841 pr=0 pw=0 time=155067 us)
10000 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=140043 us)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=30260 us)(object id 45120)
0 TABLE ACCESS FULL DEPT (cr=30000 pr=0 pw=0 time=110048 us)
드라이빙 테이블에서 읽은 값이 null일 때도 상황에 따라 조인 액세스가 일어날 수 있다는 뜻인데,
아예 e.deptno is not null 조건을 명시적으로 추가해 준다면 염려할 필요가 없다.
다행히, 컬럼 통계를 수집하고 나면 옵티마이저가 그런 조건절을 자동으로 추가해 준다.
단 조인컬럼의 null값 비중이 5% 이상 일때만 이 기능이 작동한다.
아래와 같이 통계정보를 생성하고 다시 수행해보자.
SQL> begin
2 dbms_stats.gather_table_stats(user, 't_emp'
3 , method_opt=>'for all columns', no_invalidate=>false);
4 end;
5 /
select /*+ ordered use_nl(d) index(e t_emp_idx) full(d) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 0 841 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 0 843 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=841 pr=0 pw=0 time=9518 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=9483 us)
0 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=9468 us)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=30265 us)(object id 45120)
0 TABLE ACCESS FULL DEPT (cr=0 pr=0 pw=0 time=0 us)
dept 테이블을 10,000번 Full Scan하면서 발생하던 30,000개의 블록 I/O가 사라졌다.
예상실행계획과 함께 출력되는 Predicate정보를 보면 옵티마이저에 의해 e.deptno is not null조건이 추가되었음을 알 수 있다.
SQL> select /*+ ordered use_nl(d) index(e t_emp_idx) full(d) */
2 count(e.empno), count(d.dname)
3 from t_emp e, dept d
4 where d.deptno = e.deptno
5 and e.sal <= 2900
6 /
COUNT(E.EMPNO) COUNT(D.DNAME)
-------------- --------------
0 0
Execution Plan
----------------------------------------------------------
Plan hash value: 1319716540
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 807 (1)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
| 2 | NESTED LOOPS | | 1 | 39 | 807 (1)| 00:00:10 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1 | 17 | 804 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | T_EMP_IDX | 10001 | | 22 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | 22 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."DEPTNO" IS NOT NULL)
4 - access("E"."SAL"<=2900)
5 - filter("D"."DEPTNO"="E"."DEPTNO")
t_emp 테이블을 액세스하면서 발생한 블록I/O는 통계정보를 수집하기 전과 똑같이 841개다.
추가된 is not null 조건을 필터링하면서 어차피 테이블을 방문해야 하기 때문이다
아래와 같이 t_emp_idx 인덱스에 deptno 컬럼을 추가하고 다시 수행하면 블록 I/O가 841에서 23로 확연히 준다
SQL> drop index t_emp_idx;
인덱스가 삭제되었습니다.
SQL> create index t_emp_idx on t_emp(sal, deptno);
인덱스가 생성되었습니다.
select /*+ ordered use_nl(d) index(e t_emp_idx) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 2 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.02 27 46 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.01 0.03 27 48 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=23 pr=27 pw=0 time=23105 us)
0 NESTED LOOPS (cr=23 pr=27 pw=0 time=23082 us)
0 INDEX RANGE SCAN T_EMP_IDX (cr=23 pr=27 pw=0 time=23072 us)(object id 45121)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN DEPT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 45118)
이 처럼 조인 컬럼에 is not null 조건을 추가해 주면 NL조인 뿐만아니라 해시조인, 소트머지조인 시에도 효과를 발휘한다.
우선, 해시조인을 위해 Build Input을 읽어 해시 맵을 만들 때 더 적은 메모리를 사용한다.
Probe Input을 읽을 때도 null값인 레코드를 제외함으로써 해시 맵 탐색 횟수를 줄일수 있다.
양쪽 모두 null 값 비중이 클수록 효과도 커진다
소트 머지 조인할 때도 양쪽 테이블에서 조인컬럼이 null인 레코드를 제외한다면 소트 및 비교 연산 횟수를 줄일수 있다.
이런 여러가지 사실을 비추어 볼 때, 조인 컬럼에 대한 is not null조건을 추가한다고 손해 볼일은 전혀 없다.
그런데도 옵티마이저는 null값 비중이 50%를 넘을 때만 이런 쿼리변환을 시행한다.
따라서 필요하다면 옵티마이저 기능에 의존하지 말고 사용자가 직접 위와 같은 조건을 추가해줌으로써 불필요한 액세스를 줄일수 있다.
그리고 조인 컬럼에 null값이 비중이 많을때 임의의 Default값(0,'x'등)으로 채우는 방식으로 설계하면 조인성능을 떨어뜨릴수있다는 사실도 기억하기 바란다.
(2) 필터 조건 추가
아래와 같이 바인드 변수로 between 검색하는 쿼리가 있다고 하자
쿼리르 수행할때 사용자가 :mx보다 :mn변수에 더 큰 값을 입력한다면 쿼리 결과는 공집합이다
select * from emp
where sal between :mn an :mx
사전에 두 값을 비교해알 수 있음에도 쿼리를 수행하고서야 공집합을 출력한다면 매우 비합리적이다.
잦은 일은 아니겠지만 최대용량 테이블을 조회하면서 사용자가 값을 거꾸로 입력하는 경우를 상상해 보라.
그럴 경우 8i까지는 사용자가 한참을 기다려야만 했다. 9i부터는 이를 방지하기 위해 옵티마이저가 임의로 필터 조건식을 추가한다.
아래 실행계획에서 1번 오퍼레이션 단계에 사용된 Filter Predicate 정보를 확인하기 바란다.
SQL> variable mn number;
SQL> variable mx number;
SQL>
SQL> begin
2 :mn := 5000;
3 :mx := 100;
4 end;
5 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> set autotrace traceonly;
SQL> select * from emp
2 where sal between :mn and :mx;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:MN)<=TO_NUMBER(:MX))
2 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
373 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
위 에는 :mn 에 5000, :mx에 100을 입력하고 실제 수행했을때의 결과인데, 블록 I/O가 전혀 발생하지 않는 것을 볼수 있다.
실행계획 상으로는 Table Full Scan을 수행하고 나서 필터 처리가 일어나는 것 같지만 실제로는 Table Full Scan 자체를 생략한 것이다.
바인드 변수 대신 상수 값으로 조회할 때도 filter조건이 추가되는데, 아래와 같은 9i 와 10g에서 조금 다르게 처리하고 있습니다.
- 9i: filter (5000 <= 100)
- 10g이상: filter (NULL IS NOT NULL)
9i에서 오브젝트 통계가 없으면 RBO모드로 작동해 위와 같은 쿼리변환이 일어나지 않는다.
10g는 통계정보가 없어도 항상 CBO모드로 작동하므로 쿼리변환이 잘 일어나지만 optimizer_features_enable 파라미터를 8.17로
바꾸고 테스트해 보면 아래와 같이 불필요한 I/O를 수행한다.
/**
테스트 환경 정보
**/
SQL> variable mn number;
SQL> variable mx number;
SQL>
SQL> begin
2 :mn := 5000;
3 :mx := 100;
4 end;
5 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> set autotrace traceonly;
/**
- optimizer_features_enable = '9.2.0.1'
- 통계정보 없음
**/
SQL> alter session set optimizer_features_enable = '9.0.1';
세션이 변경되었습니다.
SQL> select * from emp
2 where sal between :mn and :mx;
선택된 레코드가 없습니다.
보
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"<=TO_NUMBER(:MX) AND "SAL">=TO_NUMBER(:MN))
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
373 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
/**
- optimizer_features_enable = '9.2.0.1'
- analyze table (통계정보 있음)
**/
SQL> analyze table emp compute statistics;
테이블이 분석되었습니다.
SQL> select * from emp
2 where sal between :mn and :mx;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 1 |
|* 1 | FILTER | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 32 | 1 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:MN)<=TO_NUMBER(:MX))
2 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
373 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
/**
- optimizer_features_enable = '8.1.7'
- 통계정보 없음
**/
SQL> alter session set optimizer_features_enable = '8.1.7';
세션이 변경되었습니다.
SQL> select * from emp
2 where sal between :mn and :mx;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"<=TO_NUMBER(:MX) AND "SAL">=TO_NUMBER(:MN))
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
373 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
/**
- optimizer_features_enable = '8.1.7'
- analyze table (통계정보 있음)
**/
SQL> analyze table emp compute statistics;
테이블이 분석되었습니다.
SQL> select * from emp
2 where sal between :mn and :mx;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 1 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 32 | 1 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
373 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
(3) 조건절 비교 순서
위 데이터를 아래 SQL문으로 검색하면 B 컬럼에 대한 조건식을 먼저 평가하는 것이 유리하다.
왜냐하면, 대부분 레코드가 B = 1000 조건을 만족하지 않아 A컬럼에 대한 비교 연산을 수행하지 않아도 되기 때문이다.
SELECT * FROM T
WHERE A = 1
AND B = 1000;
반대로 A=1 조건식을 먼저 평가한다면 A 컬럼이 대부분 1이어서 B컬럼에 대한 비교 연산자까지 그 만큼 수행해야 하므로
CPU사용량이 늘어날 것이다.
아래와 같은 조건절을 처리할 때도 부등호(>) 조건을 먼저 평가하느냐 like 조건을 먼저 평가하느냐에 따라 일량에 차이가 생긴다.
select /* + full(도서) */ 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where 도서명 > :last_book_nm
and 도서명 like :book_nm || '%'
이에 옵티마이저는, 테이블 전체를 스캔하거나 인덱스를 수평적으로 스캔할 때의 Filter조건식(Filter Predicates)을 평가할때
선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다 (인덱스 수직적 탐색을 위한 조건절에 영향 없음)
이런 쿼리 변환이 작동하려면 9i, 10g를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써 CPU Costing모델을 활성화해야 한다
I/O Costing모델에서는 where절에 기술된 순서대로 조건식 비교가 일어난다.
RBO 모드에서는 where절에 기술된 반대 순서로 조건식 비교가 일어난다.
정리하면 아래 표와 같다.
| 옵티마이저 모드 | 조건절 비교 순서 |
| RBO | where절에 기술된 반대 순서로 |
| CBO (I/O Costing 모드 | where절에 기술된 순서로 |
| CBO (CPU Costing 모드 | 비교 연산해야 할 일량을 고려해 옵티마이저가 결정. 선택도가 낮은 조건식부터 평가 |
테스틀 위해 아래와 같이 t 테이블을 생성하고, 통계정보를 생성하였다.
10g 환경이므로 기본적으로 CPU Costing모드가 활성화된 상태이다.
SQL> create table t
2 nologging
3 as
4 select 1 a, rownum b from dual connect by level <= 1000000 ;
테이블이 생성되었습니다.
아래와 같이 a와 b컬럼에 대한 조건식을 서로 바꿔 가면서 테스트 해 보았지만 선택도가 낮은 b컬럼이 항상 먼저 처리되는 것을
볼 수 있다.
SQL> exec dbms_stats.gather_table_stats(user, 't', no_invalidate=>false);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> set autotrace traceonly exp;
SQL> select * from t
2 where a = 1
3 and b = 1000 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 417 (4)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 417 (4)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1000 AND "A"=1)
SQL> select * from t
2 where b = 1000
3 and a = 1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 417 (4)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 417 (4)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1000 AND "A"=1)
아래와 같이 ordered_predicates 힌트를 사용하면 CPU Costing모드에서도 조건절 비교 순서를 제어할 수 있다.
이 힌트를 사용하면 where절에 기술된 순서대로 비교가 일어난다.
옵티마이저의 판단을 무시하고 아래와 같이 힌트를 썼더니 예상 비용이 417에서 419으로 증가한 사살에 주목하기 바란다.
I/O뿐만 아니라 CPU연산 시간까지 비용 계산식에 포함되고 있음을 알 수 있다.
SQL> select /*+ ORDERED_PREDICATES */ * from t
2 where a = 1
3 and b = 1000 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 419 (4)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 419 (4)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1 AND "B"=1000)
아래와 같이 9i에서 시스템 통계를 지우거나 10g에서 I/O비용 모델로 전환한 상태에서 같은 테스트를 수행해 보면,
where절에 기술된 순서대로 조건 비교가 일어난다.
SQL> alter session set "_optimizer_cost_model" = io;
세션이 변경되었습니다.
SQL> select * from t
2 where a = 1
3 and b = 1000 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 178 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 178 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1 AND "B"=1000)
Note
-----
- cpu costing is off (consider enabling it)
SQL> select * from t
2 where b = 1000
3 and a = 1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 178 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 178 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1000 AND "A"=1)
Note
-----
- cpu costing is off (consider enabling it)
아래와 같이 RBO 모드로 바꾼 상태에서 테스트해 보면, where절에 기술된 반대 순서로 조건 비교가 일어난다.
SQL> alter session set optimizer_mode = rule;
세션이 변경되었습니다.
SQL> select * from t
2 where a = 1
3 and b = 1000 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1000 AND "A"=1)
Note
-----
- rule based optimizer used (consider using cbo)
SQL> select * from t
2 where b = 1000
3 and a = 1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1 AND "B"=1000)
Note
-----
- rule based optimizer used (consider using cbo)
ordered_predicates 힌트의 또 다른 용도
10g에서 OR 또는 IN-List조건에 대한 OR-Expansion이 일어날 때 실행순서를 제어할 목적으로 ordered_predicates힌트를 사용할수 있다.
예를 들어 9i까지는 I/O비용모델, CPU 비용모델을 불문하고 IN-List를 OR-Expansion(=Concatenation) 방식으로 처리할 때 뒤쪽에 있는
값을 먼저 실행한다. 하지만 10g CPU비용 모델 하에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행한다.
실제 그런지 10g에서 테스트해보자.
7절에서 설명한 것처럼 10g에서 같은 컬럼에 대한 OR 또는 IN-List 조건에 OR-Expansion이 작동하도록 하려면 use_concat 힌트에
아래와 같은 인자를 사용해야 한다.
SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) */ *
2 from emp e
3 where deptno in (10, 30) ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7934 MILLER CLERK 7782 82/01/23 1300 10
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
Execution Plan
----------------------------------------------------------
Plan hash value: 809118877
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 333 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 3 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 222 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
5 - access("DEPTNO"=30)
30을 IN-List 뒤쪽에 기술했음에도, Predicate정보를 보면 통계정보 상 카디널리티가 낮은 10이 위쪽으로 올라가는 것을 볼수 있다.
실제 수행해 봐도 10이 먼저 출력된다.
아래와 같이 ordered_predicates 힌트를 사용하면 9i이전 버전처럼 IN-List 뒤쪽에 있는 값을 먼저 실행한다.
SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) ordered_predicates */ *
2 from emp e
3 where deptno in (10, 30) ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7934 MILLER CLERK 7782 82/01/23 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 809118877
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 333 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 222 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 3 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=30)
5 - access("DEPTNO"=10)
{code
또는 _optimizer_cost_model 파라미터를 'IO'로 설정하거나 아래와 같이 no_cpu_costing 힌트를 사용해 IO 비용 모델로
변경해도 IN-List 뒤쪽부터 실행한다.
{code:sql}
SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) no_cpu_costing */ *
2 from emp e
3 where deptno in (10, 30) ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7934 MILLER CLERK 7782 82/01/23 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 809118877
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 333 | 4 |
| 1 | CONCATENATION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 222 | 2 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 3 | | 1 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=30)
5 - access("DEPTNO"=10)
Note
-----
- cpu costing is off (consider enabling it)
퀴즈풀이
쿼리변환에 대한 설명을 마치기 전에, 2절 뒷부분에 퀴즈로 제시했던 10개의 실행계획을 같이 재현해 보자.
퀴즈를 위해 사용한 두 테이블은 scott계정에 있는 dept와 emp테이블 이며, PK는 각각 아래와 같이 구성되어 있다.
dept.dept_pk : deptno
emp.emp_pk : empno
1
<인덱스 구성>
dept_x01 : dname + deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest no_push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN'
8 and sal > 1000)
9 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3269917256
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
3 - access("DNAME"='SALES')
4 - filter("SAL">1000)
5 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
메인쿼리에서 dname = 'SALES' 조건을 위해 사용된 dept_x01 인덱스에 deptno컬럼이 포함돼 있으므로 dept테이블을 액세스하기 전
인덱스 레벨에서 서브쿼리 필터링이 가능하다.
하지만 no_push_subq 힌트를 사용했기 때문에 테이블 액세스 이후에 필터링이 이루어지고 있다.
(dept_x01 인덱스에 deptno 컬럼이 없으며 no_push_subq 힌트 없이도 실행계획이 수립된다)
2
<인덱스 구성>
dept_x01 : dname + deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest no_push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN')
8 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2128684681
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | 11 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1))
3 - access("DNAME"='SALES')
4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
인덱스 상황과 힌트, 모두 1번과 똑같지만 서브쿼리에서 sal > 1000 조건을 제거했기 때문에 테이블 액세스 없이 emp_x01인덱스만으로
서브쿼리 필터링이 이루어지고 있다. (dept_x01 인덱스에 deptno 컬럼이 없으면 no_push_subq 힌트 없이도 같은 실행계획이 수립된다)
3
<인덱스 구성>
dept_x01 : dname + deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN'
8 and sal >= 1000)
9 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3410011884
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DNAME"='SALES')
filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
"JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">=1000))
3 - filter("SAL">=1000)
4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
1번과 쿼리뿐만 아니라 인덱스 상황까지 동일하지만 push_subq 힌트를 사용했다.
따라서 가능한 빠른 단계에서 서브쿼리를 실행하기 위해 dept_x01 인덱스를 액세스(2번 단계)하고 나서 곧바로 서브쿼리르 필터링(3~4단계)하고 있다.
서브쿼리에서 필터링되지 않는 레코드만 dept테이블을 액세스(1번 단계) 한다.
실행계획 각 단계의 Indent를 주의 깊게 관찰하기 바란다.
emp 테이블 필터 처리가 dept_x01 인덱스의 자식 노드로 표현되고 있다.
4
<인덱스 구성>
dept_x01 : dname + deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN')
8 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 166048891
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_X01 | 1 | 11 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DNAME"='SALES')
filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1))
3 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
3번과 인덱스 상황이 동일하고 힌트도 똑같지만 서브쿼리에서 sal > 1000 조건을 제거했기 때문
테이블 액세스없이 emp_x01 인덱스만으로 서브쿼리 필터링이 이루어지고 있습니다.
5
<인덱스 구성>
dept_x01 : dname
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN'
8 and sal > 1000)
9 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3234211606
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
2 - access("DNAME"='SALES')
3 - filter("SAL">1000)
4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
1번과 3번 에서는 dept_x01인덱스에 deptno 컬럼이 포함돼 있으므로 dept테이블을 액세스 하기 이전
인덱스 레벨에서 서브쿼리 필터링이 가능했다.
하지만 여기서는 dept_x01인덱스가 dname컬럼만으로 구성했기 때문에 인덱스 레벨에서 서브쿼리 필터링이 불가능하다.
따라서 push_subq 힌트를 이용하지 않는다면 1번과 같은 실행계획이 수립됐을 것입니다.
그런데 필요없는 push_subq 힌트를 의도적으로 사용했기 때문에 1번과 다른 형태의 실행계획이 나오게 된 것이다.
하지만 이것은 표현상의 문제일 때 실제 처리과정은 1번 실행계획과 다르지 않다.
1번 실행계획보다 emp테이블에 대한 필터처리가 한 칸 더 안쪽으로 들어가면서 dept테이블에 대한 자식노드로 표현된 것에 주목하기 바란다.
테이블 레벨 필터링이 분명하다.
인덱스 레벨에서 필터링이 이루어지는 3번 실행계획에서는 서브쿼리 필터링이 dept_x01 인덱스의 자식노드로 표현된 것과도 대비해 보기 바란다.
같은 쿼리 과정에 대해 1번과 5번 두 가지 펴현 방식이 다 필요한 이유는, 메인쿼리에서 제 3의 테이블과 조인이 있을때 조인전에 서브쿼리 필터링이
먼저 이루어지는 경우를 표현하기 위해서 일것 이다. 아래 실행계획을 참조하기 바란다.
6
<인덱스 구성>
dept_x01 : dname
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN')
8 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3152071781
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_X01 | 1 | 11 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1))
2 - access("DNAME"='SALES')
3 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
5번과 기본적으로 동일한 실행계힉이지만 서브쿼리 필터링을 위해 인덱스만 액세스하는 경우를 표현하기 위해 sal > 1000 조건을 제거 했다.
아래는 5번에서 예시한 것처럼 메인 쿼리가 또 다른 제3의 테이블과 조인이 있는 경우이며,
조인 전에 인덱스만으로 서브쿼리 필터링이 먼저 이루어지고 있다.
7
<인덱스 구성>
dept_x01 : dname
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ deptno
4 from emp e
5 where sal >= 5000
6 and job = 'PRESIDENT')
7 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1561374394
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ "DEPTNO" FROM "EMP"
"E" WHERE "SAL" IS NOT NULL AND "SAL">=5000 AND "JOB"='PRESIDENT'))
3 - filter("JOB"='PRESIDENT')
4 - access("SAL">=5000 AND "SAL" IS NOT NULL)
where 조건절에 사용된 서브쿼리가
1) 메인쿼리와 상관관계에 있지 않으면서(Non-Correlated, 서브쿼리에서 메인 쿼리르 참조하지 않음)
2) 단일 로우를 리턴(single-row subquery)하는 경우로서
아래와 같은 형태에 속한다.
select * from tab1 where key1 = (select avg(col1) from tab2);
select * from tab1 where (key1, key2) =
( select col1, col2 from tab2 where col3 >= 5000 and rownum = 1);
2절 (2)항 보충설명 (p468 '서브쿼리의 또 다른 최적화 기법')에서 설명한 바와 같이,
위와 같은 형태의 서브쿼리를 만나면 옵티마이저는 서브쿼리를 Fetch가 아닌 Execute시점에 먼저 수행해
그 결과 값을 메인 쿼리에 상수로 제공하는, 아래와 같은 방식으로 처리한다.
select * from tab1 where key1 = :value1;
select * from tab1 where (key1, key2) = (:vlaue1, :value2);
실제 그렇게 처리되는지는 아래 SQL트래이스 결과를 보면 확인할 수 있다.
select * from dept d
where deptno = (
select /*+ index(e emp_x01) */ deptno
from emp e
where sal >= 5000
and job = 'PRESIDENT')
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 2 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=89 us)
1 INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=70 us)(object id 45128)
1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=50 us)
1 INDEX RANGE SCAN EMP_X01 (cr=1 pr=0 pw=0 time=25 us)(object id 45139)
총 4블럭을 읽었는데, 그 중 2개 블록은 Execute 단계에서 읽었다.
emp테이블을 필터링하는 부분(실행계획 맨 아래쪽 두 단계)이 여기에 해당된다.
참고로 9i에서는 아래쪽 두 단계가 Row Source Operation에 표시되지 않는다.
조건절에서 서브쿼리를 'in'이 아닌 '='조건으로 비교한다는 것은 서브쿼리가 단일로우를 리턴하게 됨을 의미하고,
만약 이들 서브쿼리가 2개 이상의 로우를 리턴한다면 ORA-01427(single-row subquery returns more than one row)에러가 발생한다.
이를 방지하려고 아래처럼 서브쿼리에서 rownum = 1 같은 stopkey 조건절이 넣어주거나, select-list에 min, max, avg등 집계함수를
사용하는 경우가 일반적이다.
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ deptno
4 from emp e
5 where sal >= 5000
6 and job = 'PRESIDENT'
7 and rownum <= 1)
8 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3366355202
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ "DEPTNO" FROM "EMP"
"E" WHERE ROWNUM<=1 AND "SAL" IS NOT NULL AND "SAL">=5000 AND "JOB"='PRESIDENT'))
3 - filter(ROWNUM<=1)
4 - filter("JOB"='PRESIDENT')
5 - access("SAL">=5000 AND "SAL" IS NOT NULL)
7번 실행계획은 3번 Filter 실행계획과 서로 대비되도록 하려고 rownum =1 조건을 생략한 채 의도적으로 만들어 낸 것이다.
따라서 3번 실행계획과 헷갈리는 경우는 실제로 생기지 않을 것이다.
8
<인덱스 구성>
dept_x01 : deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ deptno
4 from emp e
5 where sal >= 5000 )
6 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2570959809
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_X01 | 1 | 7 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ "DEPTNO" FROM
"EMP" "E" WHERE "SAL" IS NOT NULL AND "SAL">=5000))
3 - access("SAL">=5000 AND "SAL" IS NOT NULL)
7번과 같은 상황이지만 서브쿼리에서 테이블 액세스가 발생하지 않는 경우를 보이고 있다.
4번 실행계획과 대비해보기 바란다.
7번과 마찬가지로 서브쿼리를 Fetch가 아닌 Execute 시점에 먼저 수행해 그 결과 값을 메인 쿼리애 상수로 제공하는 방식으로 처리한다.
실제 그렇게 처리되는지 아래 SQL트레이스 결과를 보면 알수 있다.
select * from dept d
where deptno = (
select /*+ index(e emp_x01) */ deptno
from emp e
where sal >= 5000 )
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 1 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=3 pr=0 pw=0 time=66 us)
1 INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=46 us)(object id 45128)
1 INDEX RANGE SCAN EMP_X01 (cr=1 pr=0 pw=0 time=25 us)(object id 45139)
총 3블록을 읽었는데, 그 중 1개 블록은 Execute 단계에서 읽었다.
emp테이블을 필터링하는 부분(실행계획 맨 아래쪽 한 단계)이 여기에 해당된다.
참고로 9i에서는 아래쪽 두 단계가 Row Source Operation에 표시되지 않는다.
7번과 마찬가지로 아래처럼 stopkey조건이 붙는 경우가 더 일반적 이다.
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ deptno
4 from emp e
5 where sal >= 5000
6 and rownum <= 1)
7 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 692795560
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | 7 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ "DEPTNO" FROM
"EMP" "E" WHERE ROWNUM<=1 AND "SAL" IS NOT NULL AND "SAL">=5000))
3 - filter(ROWNUM<=1)
4 - access("SAL">=5000 AND "SAL" IS NOT NULL)
9
<인덱스 구성>
dept_x01 : deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ max(deptno)
4 from emp e
5 where sal >= 5000
6 and job = 'PRESIDENT')
7 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 681784893
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 15 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ MAX("DEPTNO") FROM
"EMP" "E" WHERE "SAL" IS NOT NULL AND "SAL">=5000 AND "JOB"='PRESIDENT'))
4 - filter("JOB"='PRESIDENT')
5 - access("SAL">=5000 AND "SAL" IS NOT NULL)
7번 처럼 서브쿼리에서 stopkey조건을 사용하지 않는다면 여기처럼 max등의 집계함수를 사용하는 경우가 일반적이다.
이 실행계획도 3번 실행계획과 대비해 보기 바란다.
여기서도 max값을 구하는 서브쿼리가 Execute 단계에서 실행되는지 확인해 보자.
select * from dept d
where deptno = (
select /*+ index(e emp_x01) */ max(deptno)
from emp e
where sal >= 5000
and job = 'PRESIDENT')
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 2 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=114 us)
1 INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=94 us)(object id 45128)
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=68 us)
1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=46 us)
1 INDEX RANGE SCAN EMP_X01 (cr=1 pr=0 pw=0 time=25 us)(object id 45139)
9i에서는 아래쪽 세 단계가 Row Source Operation에 표시되지 않는다
10
<인덱스 구성>
dept_x01 : deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ max(deptno)
4 from emp e
5 where sal >= 5000 )
6 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1001032830
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 7 | | |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | 7 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ MAX("DEPTNO")
FROM "EMP" "E" WHERE "SAL" IS NOT NULL AND "SAL">=5000))
4 - access("SAL">=5000 AND "SAL" IS NOT NULL)
9번과 같은 상황이지만 서브쿼리에서 테이블 액세스가 발생하지 않는 경우를 보이고 있다.
4번 실행계획과 대비해 보기 바란다.
9i에서는 SQL트레이스 Row Source Operation에서 아래 세 단계가 Row Source Operations에 표시되지 않는다.
문서에 대하여
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*
"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3236
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.