비용기반의 오라클 원리 (2009년)
쿼리변환 - 시작하면서 0 0 31,743

by 구루비스터디 쿼리 변환 Query Transformation [2023.09.23]


서두 내용

  • 옵티마이저는 주어진 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)'
"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4421

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입