12. 기타 쿼리 변환

조회 수 2701 추천 수 0 2011.06.13 19:23:09
실천하자 *.136.168.206


12. 기타 쿼리 변환


(1) 조인 컬럼에 IS NOT NULL 조건 추가

  • 조인문 처리 시, 조인 컬럼에 null인 데이터는 조인 엑세스가 불필요함

  ∵ 어차피 조인에 실패하기 때문

  • 조인컬럼에 is not null 추가 

  ☞ 불필요한 테이블 엑세스 및 조인 시도를 줄일 수 있어 쿼리 성능 향상 도움



  테스트


    ## 테스트 조건 ##   - emp 테이블 1000번 복제한 t_emp 테이블 생성


create table t_emp

as

select * 

from   scott.emp

    , (select rownum no from dual connect by level <= 1000);


update t_emp set deptno = null;


create index t_emp_idx on t_emp(sal);



    ## 테스트 1 ##   -   통계정보가 없이 Index Scan

  • 내용

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;


Execution Plan

--------------------------------------------------------------------------------------------

| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |           |     1 |    52 | 11026   (1)| 00:02:13 |

|   1 |  SORT AGGREGATE                |           |     1 |    52 |            |          |

|   2 |   NESTED LOOPS                 |           |       |       |            |          |

|   3 |    NESTED LOOPS                |           |     1 |    52 | 11026   (1)| 00:02:13 |

|   4 |     TABLE ACCESS BY INDEX ROWID| T_EMP     | 10213 |   388K|   809   (1)| 00:00:10 |

|*  5 |      INDEX RANGE SCAN          | T_EMP_IDX | 10213 |       |    27   (0)| 00:00:01 |

|*  6 |     INDEX UNIQUE SCAN          | PK_DEPT   |     1 |       |     0   (0)| 00:00:01 |

|   7 |    TABLE ACCESS BY INDEX ROWID | DEPT      |     1 |    13 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("E"."SAL"<=2900)

   6 - access("D"."DEPTNO"="E"."DEPTNO")


Statistics

----------------------------------------------------------

        247  recursive calls

          0  db block gets

        952  consistent gets

         27  physical reads

          0  redo size

  • 결과

Predicate 정보

    - 옵티마이저에 의해 추가 필터 조건 없음


실행계획

    - t_emp 테이블 인덱스 스캔 발생으로 10000 rows 

    - dept 테이블과 조인 액세스 발생하지 않음 (cr = 0)


※ is null 조건을 따로 기술하지 않아도 읽은 값이 null 일 때 조인 액세스 하지 않음 !!



    ## 테스트 2 ##   -   통계정보가 없이 Inner Table  Full Table Scan

  • 내용

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;


Execution Plan

-------------------------------------------------------------------------------------------

| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |           |     1 |    52 | 14659   (1)| 00:02:56 |

|   1 |  SORT AGGREGATE               |           |     1 |    52 |            |          |

|   2 |   NESTED LOOPS                |           |     1 |    52 | 14659   (1)| 00:02:56 |

|   3 |    TABLE ACCESS BY INDEX ROWID| T_EMP     | 10213 |   388K|   809   (1)| 00:00:10 |

|*  4 |     INDEX RANGE SCAN          | T_EMP_IDX | 10213 |       |    27   (0)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL          | DEPT      |     1 |    13 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("E"."SAL"<=2900)

   5 - filter("D"."DEPTNO"="E"."DEPTNO")


Statistics

----------------------------------------------------------

          7  recursive calls

          0  db block gets

      70912  consistent gets

          6  physical reads

          0  redo size


  • 결과

※ is null 조건을 따로 기술하지 않을 시, 조인 액세스 발생 !!

  • 드라이빙 테이블에서 읽은 값이 null 이여도 상황에 따라 조인 액세스 발생 할 수 있다는 뜻
  • ☞ e.deptno is no null 조건 명시적 추가 시 염려할 필요 없음



    ## 테스트 3 ##   -   통계정보가 수집 후 Inner Table  Full Table Scan

  • 내용

SQL> begin

  2    dbms_stats.gather_table_stats(user, 't_emp'

  3         , method_opt=>'for all columns', no_invalidate=>false);

  4  end;

  5  /

PL/SQL 처리가 정상적으로 완료되었습니다.


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;


Execution Plan

-------------------------------------------------------------------------------------------

| 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")


Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        841  consistent gets

          0  physical reads

          0  redo size


  • 결과

※ e.deptno is no null 조건이 옵티마이저에 의해 Predicate 정보에 추가

  • Inner Table(dept)의 블록 I/O 없어짐
  • t_emp 테이블을 액세스 하면서 발생한 블록 I/O는 통계정보 수집전과 동일 

  ∵ in not null 조건을 필터링하기위해 테이블 방문



    ## 테스트 4 ##   -   인덱스에 null 값이 있는 조인 컬럼 추가

  • 내용

SQL> drop index t_emp_idx;

인덱스가 삭제되었습니다.


SQL> create index t_emp_idx on t_emp(sal, deptno);

인덱스가 생성되었습니다.


SQL> select /*+ ordered use_nl(d) index(e t_emp_idx) */

  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;


Execution Plan

--------------------------------------------------------------------------------------------

| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |           |     1 |    34 |    24   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE                |           |     1 |    34 |            |          |

|   2 |   NESTED LOOPS                 |           |       |       |            |          |

|   3 |    NESTED LOOPS                |           |     1 |    34 |    24   (0)| 00:00:01 |

|   4 |     TABLE ACCESS BY INDEX ROWID| T_EMP     |     1 |    21 |    24   (0)| 00:00:01 |

|*  5 |      INDEX RANGE SCAN          | T_EMP_IDX |     1 |       |    24   (0)| 00:00:01 |

|*  6 |     INDEX UNIQUE SCAN          | PK_DEPT   |     1 |       |     0   (0)| 00:00:01 |

|   7 |    TABLE ACCESS BY INDEX ROWID | DEPT      |     1 |    13 |     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("E"."SAL"<=2900)

       filter("E"."DEPTNO" IS NOT NULL)

   6 - access("D"."DEPTNO"="E"."DEPTNO")


Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

         23  consistent gets

          0  physical reads

          0  redo size


  • 결과

※  t_emp 테이블 블록 I/O 감소 


  • 조인 컬럼 is not null 추가시 효과

- NL 조인 블록I/O 엑세스 줄어드는 효과

- 해시조인 : Build Input 읽어 해시 맵 생성 시, 메모리 사용 감소

           Probe Input 읽을 시, null 값 레코드 제외로 해시 맵 탐색 횟수 감소

- 소트머지조인 : 양쪽 테이블에서 조인 컬럼이 null인 레코드를 제외 시 소트 및 비교 연산 횟수 감소


  • 옵티마이져 null값 비중이 5%를 넘을 때만 쿼리 변환 시행

          ☞ 필요시, 옵티마이저 기능에 의존하지 말고 사용자가 직접 조건 추가하여 불필요한 엑세스 감소

  • 조인 컬럼에 null 값 비중이 많을 때 Default 값('o','x' 등) 채우는 방식으로 설계 시, 조인 성능을 떨어뜨릴 수 있음



(2) 필터 조건 추가


select * from emp

where sal between :mx and :mn;


    ☞ :mx보다 :mn 변수에 더 큰 값을 입력한다면 쿼리 결과는 공집합

  • 8i 까지 공집합을 나타내는 쿼리를 실제 수행한 후에 공집합 결과 출력
  • 9i 이후 옵티마이저가 임의로 필터 조건식 추가하여 위와 같은 비합리적 상황 방지


    ## 바인드 변수 필터 테스트 ##

SQL> var mx number;

SQL> var mn number;

SQL> exec :mx := 100;

SQL> exec :mn := 5000;


# 11g

SQL> select * from emp where sal between :mn and :mx;

선택된 레코드가 없습니다.


Execution Plan

--------------------------------------------------------------------------------------------

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |             |     2 |    76 |     2   (0)| 00:00:01 |

|*  1 |  FILTER                      |             |       |       |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP         |     2 |    76 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | EMP_SAL_IDX |     2 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(TO_NUMBER(:MN)<=TO_NUMBER(:MX))

   3 - access("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))


Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size


# 8i

SQL> alter session set optimizer_features_enable = '8.1.7';


SQL> select * from emp where sal between :mn and :mx;

선택된 레코드가 없습니다.


Execution Plan

----------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost  |

----------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    38 |     1 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     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

          0  physical reads

          0  redo size


    실행계획 상 Table Full Scan 수행 후 필터 처리가 일어날 것 같지만 쿼리 변환 발생 시 Table Full Scan 자체 생략

  • 9i  : 오브젝트 통계가 없으면 RBO 모드로 작동하여 쿼리 변환 발생하지 않음
  • 10g 이상 : 통계정보가 없어도 항상 CBO 모드로 작동하여 쿼리 변환 발생

※ 쿼리 변환이 발생하지 않을 경우 불필요한 I/O 발생 



    ## 상수 필터 테스트 ##

# 11g

SQL> select * from emp where sal between 5000 and 100;

선택된 레코드가 없습니다.


Execution Plan

--------------------------------------------------------------------------------------------

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |             |     1 |    38 |     0   (0)|          |

|*  1 |  FILTER                      |             |       |       |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP         |     1 |    38 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | EMP_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

   3 - access("SAL">=5000 AND "SAL"<=100)


Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size


# 8i

SQL> alter session set optimizer_features_enable = '8.1.7';


SQL> select * from emp where sal between 5000 and 100;

선택된 레코드가 없습니다.


Execution Plan

----------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost  |

----------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    38 |     1 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     1 |

----------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("SAL">=5000 AND "SAL"<=100)


Note

-----

   - cpu costing is off (consider enabling it)


Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          7  consistent gets

          0  physical reads

          0  redo size


  • 9i : filter (5000<=100)
  • 10g 이상 : filter (null is not null)



(3) 조건절 비교 순서


  A  

 ... 

1

11111

1

1

1

1

1

1

...

B

...

 990 

 991  992  993  994  995 

 996 

 997 

 998 

 999 

 1000 

 1001 

 ... 


위의 데이터를  " SELECT * FROM T WHERE A = 1 AND B = 1000; " 로 검색 시, B 컬럼에 대한 조건식을 먼저 평가하는 것이 유리


※ 옵티마이저는, 테이블 전체를 스캔하거나 인덱스를 수평적으로 스캔할 때의 Filter 조건식을 평가할 때 

    선택도가 낮은 컬럼을 먼저 처리하도록 순서 조정

        (단, 9i, 10g를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써 CPU Costing 모델 활성화 상태일 때)


     ▼ 옵티마이저 모드에 따른 조건절 비교 순서

옵티마이져 모드조건절 비교 순서
RBOwhere절에 기술된 반대 순서로
CBO (I/O Costing 모드)where절에 기술된 순서대로
CBO (CPU Costing 모드)비교 연산해야 할 일량을 고려해 옵티마이저가 결정. 선택도가 낮은 조건식부터 평가


     ## 테스트 조건 ## (11g)

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(user, 't', no_invalidate=>false);


SQL> set autotrace traceonly exp;


    ## 테스트 1 ( 10g 이상에서는 기본적으로 CPU Costing 모드 활성 상태 )

SQL> select * from t

  2  where  a = 1

  3  and    b = 1000 ;


Execution Plan

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     8 |   511   (3)| 00:00:07 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |     8 |   511   (3)| 00:00:07 |

--------------------------------------------------------------------------


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

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     8 |   511   (3)| 00:00:07 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |     8 |   511   (3)| 00:00:07 |

--------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("B"=1000 AND "A"=1)



    ## 테스트 2 ( ordered_predicates 힌트를 사용하여 CPU Costing 모드에서 조건절 비교 순서 제어 )

SQL> select /*+ ORDERED_PREDICATES */ * from t

  2  where  a = 1

  3  and    b = 1000 ;


Execution Plan

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     8 |   513   (3)| 00:00:07 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |     8 |   513   (3)| 00:00:07 |

--------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("A"=1 AND "B"=1000)


    where 절 기술된 순서대로 비교 발생

     ☞ 옵티마이저의 판단을 무시하고 실행하여 예상 비용 증가 발생


    ## 테스트 3 ( RBO 모드 ) - where 절에 기술된 반대 순서로 조건 비교 발생

SQL> alter session set optimizer_mode = rule;

세션이 변경되었습니다.


SQL> select * from t

  2  where  a = 1

  3  and    b = 1000 ;


Execution Plan

----------------------------------

| 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

----------------------------------

| 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 이후 부터 CPU 비용 모델 하에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행
  • OR 또는 IN-List 조건에 OR-Expansion 작동하기 위해 use_concat 힌트 사용
    ## 테스트 1 ## - 특별한 조건 없음

SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) */ *

  2  from   emp e

  3  where  deptno in (10, 30)  ;


Execution Plan

-----------------------------------------------------------------------------------------------

| 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" 이 먼저 출력


    ## 테스트 2 ## - ordered_predicates 힌트 사용

SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) ordered_predicates */ *

  2  from   emp e

  3  where  deptno in (10, 30)  ;


Execution Plan

-----------------------------------------------------------------------------------------------

| 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)


    ☞ ordered_predicates 힌트를 사용하여 뒤쪽부터 실행 가능하도록 제어함



    ## 테스트 3 ## - no_cpu_costing 힌트 사용

SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) no_cpu_costing */ *

  2  from   emp e

  3  where  deptno in (10, 30)  ;


Execution Plan

-------------------------------------------------------------------------------

| 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)


    ☞ _optimizer_cost_model 파라미터를 'I/O'로 설정, 또는 no_cpu_costing 힌트 사용으로 뒤쪽부터 실행가능하도록 제어 가능




  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 위충환 (실천하자)
  • 최초작성일: 2011년 06월 02 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^


profile

실천하자

2011.06.08 00:31:37
*.136.168.206

## Oracle 버전 별 실행계획 테스트 ##

SQL> set autotrace traceonly exp;
SQL>
SQL> alter session set optimizer_features_enable = '8.1.7';

세션이 변경되었습니다.

SQL>
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;

Execution Plan
----------------------------------------------------------
Plan hash value: 3232964574

---------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     1 |    34 |    25 |
|   1 |  SORT AGGREGATE               |           |     1 |    34 |       |
|   2 |   NESTED LOOPS                |           |     1 |    34 |    25 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_EMP     |     1 |    21 |    24 |
|*  4 |     INDEX RANGE SCAN          | T_EMP_IDX |     1 |       |    24 |
|   5 |    TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |    13 |     1 |
|*  6 |     INDEX UNIQUE SCAN         | PK_DEPT   |     1 |       |       |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."SAL"<=2900)
       filter("E"."DEPTNO" IS NOT NULL)
   6 - access("D"."DEPTNO"="E"."DEPTNO")

Note
-----
   - cpu costing is off (consider enabling it)

SQL>
SQL> alter session set optimizer_features_enable = '9.2.0.8';

세션이 변경되었습니다.

SQL>
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;

Execution Plan
----------------------------------------------------------
Plan hash value: 3232964574

---------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     1 |    34 |    24 |
|   1 |  SORT AGGREGATE               |           |     1 |    34 |       |
|   2 |   NESTED LOOPS                |           |     1 |    34 |    24 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_EMP     |     1 |    21 |    24 |
|*  4 |     INDEX RANGE SCAN          | T_EMP_IDX |     1 |       |    24 |
|   5 |    TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |    13 |       |
|*  6 |     INDEX UNIQUE SCAN         | PK_DEPT   |     1 |       |       |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."SAL"<=2900)
       filter("E"."DEPTNO" IS NOT NULL)
   6 - access("D"."DEPTNO"="E"."DEPTNO")

Note
-----
   - cpu costing is off (consider enabling it)

SQL>
SQL> alter session set optimizer_features_enable = '10.1.0.5';

세션이 변경되었습니다.

SQL>
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;

Execution Plan
----------------------------------------------------------
Plan hash value: 3232964574

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     1 |    34 |    24   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |           |     1 |    34 |            |          |
|   2 |   NESTED LOOPS                |           |     1 |    34 |    24   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_EMP     |     1 |    21 |    24   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T_EMP_IDX |     1 |       |    24   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |    13 |     0   (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)
       filter("E"."DEPTNO" IS NOT NULL)
   6 - access("D"."DEPTNO"="E"."DEPTNO")

SQL>
SQL> alter session set optimizer_features_enable = '10.2.0.5';

세션이 변경되었습니다.

SQL>
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;

Execution Plan
----------------------------------------------------------
Plan hash value: 3232964574

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     1 |    34 |    24   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |           |     1 |    34 |            |          |
|   2 |   NESTED LOOPS                |           |     1 |    34 |    24   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_EMP     |     1 |    21 |    24   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T_EMP_IDX |     1 |       |    24   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |    13 |     0   (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)
       filter("E"."DEPTNO" IS NOT NULL)
   6 - access("D"."DEPTNO"="E"."DEPTNO")

SQL>
SQL> alter session set optimizer_features_enable = '11.2.0.1';

세션이 변경되었습니다.

SQL>
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;

Execution Plan
----------------------------------------------------------
Plan hash value: 3687075479

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |     1 |    34 |    24   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                |           |     1 |    34 |            |          |
|   2 |   NESTED LOOPS                 |           |       |       |            |          |
|   3 |    NESTED LOOPS                |           |     1 |    34 |    24   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_EMP     |     1 |    21 |    24   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T_EMP_IDX |     1 |       |    24   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | PK_DEPT   |     1 |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID | DEPT      |     1 |    13 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."SAL"<=2900)
       filter("E"."DEPTNO" IS NOT NULL)
   6 - access("D"."DEPTNO"="E"."DEPTNO")


11g 이후에서 실행계획이 다른 버전과 차이나는 이유는?!!! -_a

글쓴이

2011.06.08 12:43:14
*.18.66.42

충환씨!

[세미나 및 실습자료]에 관련 자료 올려놨어요..

보시구.. 담에 보강 부탁해요..!!!

profile

실천하자

2011.06.13 19:23:09
*.18.78.25

좋은 자료 감사합니다~ ^ -^)b