꿈꾸는 개발자, DBA 커뮤니티 구루비

서두 내용

옵티마이저는 주어진 SQL을 최적화하기 위해 Query를 재 구성하는데 이 때 Rule Base 기반으로 하는 RBQT(Rule Base Query Transformation)와 CBQT(Cost Base Query Transformation)이 있다.
9i까지는 이 RBQT를 사용였고 10g에 넘어와서는 CBQT를 사용하고 있으며 이에 대한 파라미터는 '_optimizer_cost_based_transformation'(Value : on, off)가 있다.

Query Transformation Life Cycle

Query Transformation에 대한 내부 구현코드의 생명주기(Life Cycle)을 보면 일반적으로 아래와 같은 패턴을 가지고 있다.

Close Beta	구현된 내부 코드가 이미 존재하고, 파라미터 또는 비공식 힌트를 사용하여 Query Transformation이 일어나게끔 할 수 있음.
Open Beta	내부 코드가 기본값으로 활성화되지만 Cost-Based는 아님. 따라서 항상 변환이 일어남.
Release	옵티마이저가 원래 SQL과 변환된 SQL의 양쪽 비용 모두를 계산해서 더 비용이 낮은 쪽을 선택함. 힌트는 deprecatted됨.

I. 시작하면서

아래에서 오라클 버전 별 실행계획을 비교하여 어떤 차이가 있는지 살펴보자.

1. 테스트 테이블 생성


-- 총 20,000명의 사원이 여섯 개 부서로 나뉘도록 테이블을 생성함.
-- 각 사원은 서로 다른 식별자(EMP_NO)를 가지며, 급여(SALARY)도 서로 중복되지 않는다.
CREATE TABLE EMP(DEPT_NO    NOT NULL,
                 SAL,
                 EMP_NO		NOT NULL,
                 PADDING,
                 CONSTRAINT E_PK PRIMARY KEY(EMP_NO))
AS
WITH GENERATOR AS (SELECT ROWNUM ID
                   FROM   ALL_OBJECTS
                   WHERE  ROWNUM <= 1000)
SELECT /*+ ORDERED USE_NL(V2) */
       MOD(ROWNUM, 6),
       ROWNUM,
       ROWNUM,
       RPAD('X', 60)
FROM   GENERATOR V1,
       GENERATOR V2
WHERE  ROWNUM <= 20000
;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => USER,
                                  TABNAME           => 'EMP',
                                  CASCADE           => TRUE,
                                  ESTIMATE_PERCENT  => NULL,
                                  METHOD_OPT        =>'FOR ALL COLUMNS SIZE 1');
END;
/

2. 오라클 버전 별 실행계획 비교

8i version


ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '8.1.7';

EXPLAIN PLAN FOR
SELECT OUTER.*
FROM   EMP OUTER
WHERE  OUTER.SAL > (SELECT /*+ NO_UNNEST */
                           AVG(INNER.SAL)
                    FROM   EMP INNER
                    WHERE  INNER.DEPT_NO = OUTER.DEPT_NO);

@XPLAN3;
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1000 | 72000 |    35 |
|*  1 |  FILTER             |      |       |       |       |
|   2 |   TABLE ACCESS FULL | EMP  |  1000 | 72000 |    35 |
|   3 |   SORT AGGREGATE    |      |     1 |     8 |       |
|*  4 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    35 |
------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$2" "INNER"@"SEL$2")
      FULL(@"SEL$1" "OUTER"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      OPT_PARAM('_fast_full_scan_enabled' 'false')
      OPTIMIZER_FEATURES_ENABLE('8.1.7')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */
              AVG("INNER"."SAL")
              FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
   4 - filter("INNER"."DEPT_NO"=:B1)

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

9i version



ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '9.2.0.8';

EXPLAIN PLAN FOR
SELECT OUTER.*
FROM   EMP OUTER
WHERE  OUTER.SAL > (SELECT /*+ NO_UNNEST */
                          AVG(INNER.SAL)
                   FROM   EMP INNER
                   WHERE  INNER.DEPT_NO = OUTER.DEPT_NO);

@XPLAN3;
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1000 | 72000 |    72 |
|*  1 |  FILTER             |      |       |       |       |
|   2 |   TABLE ACCESS FULL | EMP  |  1000 | 72000 |    36 |
|   3 |   SORT AGGREGATE    |      |     1 |     8 |       |
|*  4 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    36 |
------------------------------------------------------------

Outline Data
-------------

 /*+
     BEGIN_OUTLINE_DATA
     FULL(@"SEL$2" "INNER"@"SEL$2")
     FULL(@"SEL$1" "OUTER"@"SEL$1")
     OUTLINE_LEAF(@"SEL$1")
     OUTLINE_LEAF(@"SEL$2")
     OPT_PARAM('_optim_peek_user_binds' 'false')
     OPT_PARAM('_fast_full_scan_enabled' 'false')
     OPT_PARAM('_b_tree_bitmap_plans' 'false')
     OPTIMIZER_FEATURES_ENABLE('9.2.0.8')
     IGNORE_OPTIM_EMBEDDED_HINTS
     END_OUTLINE_DATA
 */

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

  1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */
             AVG("INNER"."SAL")
             FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
  4 - filter("INNER"."DEPT_NO"=:B1)

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

10g version



ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';

EXPLAIN PLAN FOR
SELECT OUTER.*
FROM   EMP OUTER
WHERE  OUTER.SAL > (SELECT /*+ NO_UNNEST */
                           AVG(INNER.SAL)
                    FROM   EMP INNER
                    WHERE  INNER.DEPT_NO = OUTER.DEPT_NO);

@XPLAN3;
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   167 | 12024 |   452   (1)| 00:00:06 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  | 20000 |  1406K|    65   (2)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  4 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    65   (2)| 00:00:01 |
----------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$2" "INNER"@"SEL$2")
      FULL(@"SEL$1" "OUTER"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_fast_full_scan_enabled' 'false')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */
              AVG("INNER"."SAL")
              FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
   4 - filter("INNER"."DEPT_NO"=:B1)

1) 8i, 9i에서 Driving Table인 EMP의 Cardinality 값을 1,000으로 계산했는데 그 이유는?

그 이유는 아직 서브쿼리가 결과가 알려지지 않은 상태이기 때문에 'salary > :bind_variable'로 계산을 하여 5%의 룰을 적용하였고 이로 인해 '20000 / 20 = 1000'의 값을 리턴하였다.

2) 그렇다면 10g에서의 Driving Table인 EMP의 Cardinality 값이 20,000인 이유는?

우선 COST 값을 보면 최종 결과에서 452가 나왔고 EMP 테이블을 Driving할 때는 65값이 나왔는데 이를 나누어 보면 '452 / 65 = 6.9(약 7)'의 결과가 나오는 것을 알 수 있다.
이는 맨 처음 Driving Table을 읽은 것을 제외하면 6번을 읽었다는 얘기인데 EMP 테이블의 부서 숫자와 거의 일치한다.
즉 10g 부터는 서브쿼리의 내용을 바인드 변수로 처리하는게 아니라 서브쿼리의 결과 값을 IN-Memory 참조 테이블에 캐싱하는 메커니즘을 사용할 것을 알고 단지 여섯 번만 서브쿼리를 수행하는 보다 현실적인 로직으로 처리하는 것을 알 수 있다.
추가적으로 서브쿼리의 Cardinality 값인 3,333은 EMP 테이블 전체 로우수 '20,000 / 6'인 값과 같다.

3) 마지막으로 8i, 9i에서 최종 예측 Cardinality 값이 1,000인데 비해 10g가 167인 이유는?

8i, 9i는 Base Cardinality에 5%의 룰을 단순히 적용하였지만 10g는 5%의 룰에 서브쿼리의 개수를 나누어 개산하였기 때문이다.
'20000 * 0.05 / 6 = 166.6(약 167)'