오라클 성능 고도화 원리와 해법 II (2012년)
기타쿼리변환
- IS NOT NULL 조건추가
- 필터 조건 추가
- 조건절 비교순서
조인컬럼에 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
IS NOT NULL 테스트
-- 1. 테스트를 위한 emp 테이블 복제
SQL> create table t_emp as select * from emp , (select rownum no from dual connect by level <=1000);
테이블이 생성되었습니다.
-- 2. 테스트를 위한 null값 업데이트
SQL> update t_emp set deptno = null;
14000 행이 갱신되었습니다.
SQL> commit;
커밋이 완료되었습니다.
-- 3. 인덱스 생성
SQL> create index t_emp_idx on t_emp(sal);
인덱스가 생성되었습니다.
-- (통계생성전)
-- 4.조회
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;
Execution Plan
----------------------------------------------------------
Plan hash value: 3232964574
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 10359 (1)| 00:02:05 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
| 2 | NESTED LOOPS | | 1 | 52 | 10359 (1)| 00:02:05 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_EMP | 9535 | 363K| 810 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | T_EMP_IDX | 9535 | | 27 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."SAL"<=2900)
6 - access("D"."DEPTNO"="E"."DEPTNO")
-위의 predacate 정보를 볼때 아직 옵티마이저에의해 추가된 필터 조건은 없다.
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=0 us cost=11050 size=52 card=1)
10000 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=12783 us cost=809 size=399243 card=10237)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=3417 us cost=27 size=0 card=10237)
0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us cost=1 size=13 card=1)
********************************************************************************
- 실제로 위와 같이 t_emp_idx 인덱스를 스캔하면서 10,000번의 테이블 엑세스가 발생하였다.
- 여기서 t_emp 테이블에서 10,000개 레코드를 읽었지만 dept 테이블과의 조인 엑세스가 전혀 발생하지 않은것에 주목하자
이는 is null 조건을 따로 기술하지 않더라도 읽은 값이 null 일때는 조인 덱세스를 하지 않는 다는 뜻이며 이는 매우 중요한 사실이다.
(만약 버퍼 pinning 효과 때문이라면 적어도 dept_pk 인덱스를 두번은 읽었을것이다)
-이해할 수 없는 일이지만 Inner 테이블을 Full Table Scan 으로 엑세스 할때에는 아래처럼 조인 엑세스가 발생한다.
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 Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.016 0.012 0 72 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.078 0.079 0 70841 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.094 0.092 0 70913 0 1
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=70841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=70841 pr=0 pw=0 time=0 us cost=14691 size=52 card=1)
10000 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=16200 us cost=809 size=399243 card=10237)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=5442 us cost=27 size=0 card=10237)
0 TABLE ACCESS FULL DEPT (cr=70000 pr=0 pw=0 time=0 us cost=1 size=13 card=1)
********************************************************************************
-드라이빙 테이블에서 읽은 값이 null 일때도 상황에 따라 조인 엑세스가 일어날 수 있다는 뜻인데,
아예 e.deptno is not null 조건을 명시적으로 추가해 준다면 염려할 필요가 없다.
-다행히 컬럼 통계를 수집하고 나면 옵티마이저가 그런 조건절을 자동적으로 추가해 준다.
단 조인컬럼의 null 값 비중이 5%이상일 때에만 이 기능이 작동한다.
- 아래와 같이 통계정보 수집후 다시 수행해보자
begin
dbms_stats.gather_table_stats(user, 't_emp'
, method_opt=>'for all columns', no_invalidate=>false);
end;
/
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 Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.016 0.006 0 841 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.016 0.007 0 841 0 1
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=0 us cost=807 size=34 card=1)
0 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=0 us cost=804 size=21 card=1)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=3417 us cost=22 size=0 card=10001)
0 TABLE ACCESS FULL DEPT (cr=0 pr=0 pw=0 time=0 us cost=3 size=13 card=1)
********************************************************************************
-dept 테이블을 10,000번 Full Scan하면서 발생하던 70,000개의 블록 I/O가 사라졌다.
-아래와 같이 예상 실행계획을 보면 is not null 조건이 추가된것을 확인할 수 있다
Execution Plan
----------------------------------------------------------
Plan hash value: 1319716540
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 807 (1)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
| 2 | NESTED LOOPS | | 1 | 34 | 807 (1)| 00:00:10 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1 | 21 | 804 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | T_EMP_IDX | 10001 | | 22 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | 13 | 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개로 확연히 주는것을 확인할 수 있다
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 Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.069 27 23 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.071 27 23 0 1
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=23 pr=27 pw=0 time=0 us)
0 NESTED LOOPS (cr=23 pr=27 pw=0 time=0 us)
0 NESTED LOOPS (cr=23 pr=27 pw=0 time=0 us cost=24 size=34 card=1)
0 TABLE ACCESS BY INDEX ROWID T_EMP (cr=23 pr=27 pw=0 time=0 us cost=24 size=21 card=1)
0 INDEX RANGE SCAN T_EMP_IDX (cr=23 pr=27 pw=0 time=0 us cost=24 size=0 card=1)
0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
********************************************************************************
- 이처럼 조인 컬럼에 is not null 조건을 추가해 주면 NL조인 뿐만 아니라 해시조인,소트머지조인 시에도 효과를 발휘한다.
- 우선 해시조인을 위해 build Input을 읽어 해시 맵을 만들때 적은 메모리를 사용한다.
- Probe Input 을 읽을 때도 null값인 레코드를 제외함으로써 해시 맵 탐색 횟수를 줄일 수 있다
- 양쪽 모두 null 값 비중이 클수록 효과도 커진다.
- 소트머지 할때도 양쪽 테이블에서 조인 컬럼이 null인 레코드를 제외한다면 소트 및 비교 연산 횟수를 줄일 수 있다.
- 이런 여러가지 사실에 비추어볼 때 조인 컬럼에 대한 is not null 조건을 추가한다고 손해볼 일은 전혀 없다.
- 그런데도 옵티마이저는 null값에 비중이 5%가 넘을때만 이런 쿼리변환을 시행한다.
- 따라서 필요하다면 옵티마이저 기능에 의존하지 말고 사용자가 직접 위와 같은 조건절을 추가해줌으로써 불필요한 엑세스를 줄일 수 있다.
- 그리고 조인 컬럼에 null값 비중이 많을때 임의의 default(0,'x') 값 등으로 채우는 방식을 설계하면 조인성능을 떨어뜨릴수 있다는 사실도 기억하기 바란다.
필터 조건 추가
- 애아래와 같이 바인드 변수로 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>
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 | 37 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 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
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets --블록 I/O전혀없음
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
- 위의 Filter Predicate 정보를 확인
- 실행계획 상으로는 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.1.7로 바꾸고 테스트 해보면 아래와 같이 불필요한 I/O를 수행한다.
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 | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 1 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets --불필요한 I/O 발생
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
-통계정보 생성후 8.1.7
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
7 consistent gets -- 불필요한 I/O 발생
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
-9.2.0 변경
SQL> alter session set optimizer_features_enable='9.2.0';
세션이 변경되었습니다.
SQL> select * from emp where sal between :mn and :mx;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2 |
|* 1 | FILTER | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 32 | 2 |
-----------------------------------------------------------
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
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
조건절 비교 순서
| A | ... | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ... |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | ... | 990 | 991 | 992 | 993 | 994 | 995 | 996 | 997 | 998 | 999 | 1000 | 1001 | ... |
-- 위 데이터를 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
ADN 도서명 LIKE :book_nm||'%'
- 이에 옵티마이저는 테이블 전체를 스켄하거나 인덱스를 수평적으로 스캔할 때의 filter 조건식을 평가할때 선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다.(인덱스 수직적 탐색을 위한 조건절에는 영향 없음)
- 이런 쿼리 변환이 작동하려면 9i, 10g 를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써 CPU Costing 모델을 활성화 해야한다.
| 옵티마이저모드 | 조건절비교순서 | RBO | where절에 기술된 반대순서로 |
|---|---|---|---|
| CBO(I/O Costing 모드) | where 절에 기술된 순서대로 | ||
| CBO(CPU Costing 모드) | 비교 연산해야할 일량을 고려해 옵티마이저가 결정. 선택도가 낮은 조건식부터 평가 |
-테스트테이블 생성 T
SQL> create table t
2 nologging
3 as
4 select 1 a, rownum b from dual connect by level <= 1000000 ;
테이블이 생성되었습니다.
- 통계정보 생성
SQL> exec dbms_stats.gather_table_stats('SCOTT', 't', no_invalidate=>false);
PL/SQL 처리가 정상적으로 완료되었습니다.
10g는 기본적으로 CPU Costing 모드가 활성화된 상태이다
-a와 b 컬럼에 대한 조건식을 서로 바꿔가며 테스트해도 선택도가 낮은 b 컬럼이 항상 먼저 처리되는것을 확인할 수 있다
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 | 445 (10)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 445 (10)| 00:00:06 |
--------------------------------------------------------------------------
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 | 445 (10)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 445 (10)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1000 AND "A"=1)
-ordered_predicates 힌트를 사용하여 CPU Consting 모드에서의 조건절 비교순서 제어
-옵티마이저의 판단을 무시하고 아래의 힌트를 썼더니 예상비용이 늘어난것을 확인할 수 있다.
-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 | 453 (12)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 453 (12)| 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)
또는 _optimizer_cost_model 파라미터를 'IO'로 설정하거나 아래와 같이 no_cpu_costing 힌트를 사용해 IO 비용 모델로
변경해도 IN-List 뒤쪽부터 실행한다.
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3295
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.