Science of Database

"멀티 컬럼으로 Range 파티션을 할 경우 Where 절에 파티션 선두 컬럼에 해당하는 조건만 주어도 Partition Pruning이 수행된다"

위의 말이 사실일까? 어디서 흘러나온 말인지 모르겠으나 위의 경우는 Partition Pruning이 제대로 되지 않는다. 함정이 기다리고 있기 때문이다.

아래의 스크립트를 실행해서 직접 증명해보자.          

 

환경: Oracle 10.2.0.4

       4 Node RAC

 

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  day_num VARCHAR2(2), 

  inst_id NUMBER(1),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

  PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

  PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

  PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

  PARTITION pt_1_4 VALUES LESS THAN ('1', 5),

  PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

  PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

  PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

  PARTITION pt_2_4 VALUES LESS THAN ('2', 5),

  PARTITION pt_3_1 VALUES LESS THAN ('3', 2),

  PARTITION pt_3_2 VALUES LESS THAN ('3', 3),

  PARTITION pt_3_3 VALUES LESS THAN ('3', 4),

  PARTITION pt_3_4 VALUES LESS THAN ('3', 5),  

  PARTITION pt_4_1 VALUES LESS THAN ('4', 2),

  PARTITION pt_4_2 VALUES LESS THAN ('4', 3),

  PARTITION pt_4_3 VALUES LESS THAN ('4', 4),

  PARTITION pt_4_4 VALUES LESS THAN ('4', 5), 

  PARTITION pt_5_1 VALUES LESS THAN ('5', 2),

  PARTITION pt_5_2 VALUES LESS THAN ('5', 3),

  PARTITION pt_5_3 VALUES LESS THAN ('5', 4),

  PARTITION pt_5_4 VALUES LESS THAN ('5', 5),     

  PARTITION pt_6_1 VALUES LESS THAN ('6', 2),

  PARTITION pt_6_2 VALUES LESS THAN ('6', 3),

  PARTITION pt_6_3 VALUES LESS THAN ('6', 4),

  PARTITION pt_6_4 VALUES LESS THAN ('6', 5),   

  PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

  PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

  PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

  PARTITION pt_7_4 VALUES LESS THAN ('7', 5)

);

 

Table created.

 

먼저 실습을 진행할 테이블을 생성하였다. day_num 컬럼은 1~7 로 구성되어 있다. 모든 일자 데이터를 7등분 하기 위함이다. inst_id 컬럼은 RAC에서 Instance 번호이며 1~4로 구성된다. gc buffer busy등을 줄이기 위한 용도로 자주 사용된다.

 

이제 테스트용 데이터를 insert 하자.

 

INSERT INTO t

SELECT rownum AS id,

       trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4) AS d1,

       to_char(trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4),'d') AS day_num,

       1+mod(rownum,4) AS inst_id,

       dbms_random.string('p',255) AS pad

FROM dual

CONNECT BY level <= 10000

ORDER BY dbms_random.value;

 

10000 rows created.

 

commit;

 

BEGIN

  dbms_stats.gather_table_stats(

    ownname          => user,

    tabname          => 'T',

    estimate_percent => 100,

    method_opt       => 'for all columns size skewonly',

    cascade          => TRUE

  );

END;

/

 

PL/SQL procedure successfully completed.

 

각 파티션에 데이터가 골고루 Insert 되었는지 살펴보자.

SELECT partition_name, partition_position, num_rows

 FROM user_tab_partitions

 WHERE table_name = 'T'

 ORDER BY partition_position;

 

PARTITION_NAME                 PARTITION_POSITION   NUM_ROWS

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

PT_1_1                                          1        356

PT_1_2                                          2        358

PT_1_3                                          3        355

PT_1_4                                          4        355

PT_2_1                                          5        364

PT_2_2                                          6        364

PT_2_3                                          7        362

PT_2_4                                          8        362

PT_3_1                                          9        358

PT_3_2                                         10        355

PT_3_3                                         11        355

PT_3_4                                         12        356

PT_4_1                                         13        357

PT_4_2                                         14        355

PT_4_3                                         15        355

PT_4_4                                         16        358

PT_5_1                                         17        355

PT_5_2                                         18        355

PT_5_3                                         19        357

PT_5_4                                         20        358

PT_6_1                                         21        355

PT_6_2                                         22        355

PT_6_3                                         23        358

PT_6_4                                         24        356

PT_7_1                                         25        355

PT_7_2                                         26        358

PT_7_3                                         27        358

PT_7_4                                         28        355

 

28 rows selected.

모든 파티션에 건수가 골고루 분배되었다. 그럼 이제 파티션의 선두 컬럼 조건만 있을 경우 Partition Pruning이 수행되는지 알아보자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3';

 

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

| Id  | Operation                 | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

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

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |     115 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    13 |   1424 |     115 |

|*  3 |    TABLE ACCESS FULL      | T    |      5 |     9 |    13 |   1424 |     115 |

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

불필요한 파티션을 액세스 한다
Pstart와 Pstop 항목을 보면 9번째 파티션부터 13번째 파티션까지 Scan 되었다.

Partition Pruning이 잘된 것처럼 보이지만 자세히 보면 DAY_NUM이 3인 파티션은 4개뿐인데 5개의 파티션을 액세스 하였다.

 

어떻게 된 것인가?

DAY_NUM이 3인 파티션은 4개 이지만 inst_id 컬럼에 어떤 값이 있을지 알 수 없으므로 13번째 파티션을 액세스 할 수 밖에 없는 것이다. 다시 말하면 DAY_NUM이 3이면서 inst_id가 6인 데이터는 13번째 파티션에 들어갈 수 있으므로 불필요한 Scan이 일어나는 것이다.

 

물론 아래와 같이 DAY_NUM 조건과 INST_ID 조건을 같이 준다면 이런 현상은 발생하지 않는다.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3'

   AND INST_ID = 2;

  

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

| Id  | Operation               | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

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

|   1 |  SORT AGGREGATE         |      |      1 |       |       |      1 |      23 |

|   2 |   PARTITION RANGE SINGLE|      |      1 |    10 |    10 |    355 |      23 |

|*  3 |    TABLE ACCESS FULL    | T    |      1 |    10 |    10 |    355 |      23 |

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

정확히 10번째 파티션만 액세스 하였다.

만약 INST_ID 조건을 줄 수 없는 경우라면?

모든 경우에 INST_ID 조건을 줄 수는 없을 것이다. 예를 들면 전체를 처리해야 하는 경우는 INST_ID 조건을 줄 수 없을 것이다. 이때 어떻게 하면 비효율을 없앨 수 있겠는가?

해결방법1

특정 inst_id 조건을 주지 못할 때는 inst_id 의 범위를 주어 불필요한 파티션 scan을 방지한다.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3'

   AND INST_ID BETWEEN 1 AND 4;

 

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

| Id  | Operation                 | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

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

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |      92 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    12 |   1424 |      92 |

|*  3 |    TABLE ACCESS FULL      | T    |      4 |     9 |    12 |   1424 |      92 |

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

정확히 4개의 파티션만 액세스 하여 비효율이 사라졌다..

 

해결방법2

조건을 주지 못할 때는 4번째 inst_id 파티션을 maxvalue로 바꾼다.

 

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  day_num VARCHAR2(2), 

  inst_id NUMBER(1),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

  PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

  PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

  PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

  PARTITION pt_1_4 VALUES LESS THAN ('1', maxvalue),

  PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

  PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

  PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

  PARTITION pt_2_4 VALUES LESS THAN ('2', maxvalue),

  ...중간생략

  PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

  PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

  PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

  PARTITION pt_7_4 VALUES LESS THAN ('7', maxvalue)

);

 

위에서 실행한 insert문과 dbms_stats을 여기서 다시 실행하고 아래의 SQL을 수행하자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3';

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

| Id  | Operation                 | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

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

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |      92 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    12 |   1424 |      92 |

|*  3 |    TABLE ACCESS FULL      | T    |      4 |     9 |    12 |   1424 |      92 |

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

 

정확히 4개의 파티션만 액세스 하였다. 비효율이 없어진 것이다.

 

결론

이제 maxvalue의 진정한 의미를 알겠는가?

단순히 마지막 값이라는 의미 이외에 Partition Pruning에 영향을 끼치며 이것은 성능과 직결된다.

또한 멀티 컬럼으로 Range 파티션을 할 경우 where 조건에 따라 불필요한 파티션을 액세스 하므로 주의해야 한다.  

 

PS

위의 해결책 1,2 를 통해 문제를 해결하지 않으면 해당 select문의 성능저하뿐만 아니라 해당 테이블을 사용하는 DML문의 성능이 저하되는 끔찍한 현상이 발생할 수 있다. 다음시간에 이 문제에 대하여 논의 해보자.

Dummy Table(흔히 Copy_t 라고 불림) 대신에 9i 이후부터는 Connect By level 문을 사용하곤 한다. 하지만 조심하지 않으면 해당 SQL이 종료되지 않는 장애를 만나게 된다. 오늘은 Connect By level 문을 오용하는 사례와 해결책을 제시하고자 한다.

상황
업무팀에서 새로운 SQL을 작성하고 컴파일하여 운영 시스템에 반영되었다. 문제의 SQL이 실행되자 너무 오래걸려서 Time Out이 발생하였다. 아래는 상황을 최대한 간단히 표현하여 테스트를 수행하기 위한 스크립트 이다.

결과:
HIRE THIS        GAP
---- ---- ----------
2006 2010          4
2003 2010          7
2002 2010          8
1999 2010         11
1997 2010         13

5 rows selected.

업무요건
위의 SQL의 결과에서 나타난 GAP만큼 가상의 ROW를 생성하여야 한다. 즉 입사년도가 2006년인 사람은 ROW가 4개로 되어야 하고 2003년인 사람은 ROW가 7개가 되어야 한다. 전체적으로 43건이 나와야 한다. 아래에 원하는 답이 있다.
 
원하는 답
HIRE THIS        GAP        NUM
---- ---- ---------- ----------
2003 2010          7          7
2003 2010          7          6
2003 2010          7          5
2003 2010          7          4
2003 2010          7          3
2003 2010          7          2
2003 2010          7          1
2006 2010          4          4
2006 2010          4          3
2006 2010          4          2
2006 2010          4          1
....중간생략

문제의 SQL
아래의 SQL은 gap을 Connect By Level 절에 적용시킨 것이다. 아래처럼 SQL을 작성한다면 지옥을 경험할 수 있다.

위의 SQL은 전체건을 Fetch하려면 10분이 걸려도 끝나지 않았다. 시간이 너무 오래 걸리므로 아래처럼 COUNT 로 바꿔서 실행해 보았다.

       
결과:
  COUNT(*)
----------
   3773280

버그인가?
무려 370만건 이상의 건수가 나왔다. 이상하지 않은가? 건수의 예측도 할 수 없었다. 건수와 관련해서 일정한 규칙도 존재하지 않았다. 버그인지 아닌지 알 수 없지만 결론적으로 위의 SQL처럼 사용하면 안된다는 것을 알 수 있다. 아래는 Count에 대한 실행통계인데 무려 44초나 걸렸다.

-------------------------------------------------------------------------------
| Id  | Operation                      | Name | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |      |      1 |00:00:44.39 |       3 |
|   2 |   VIEW                         |      |   3773K|00:00:45.28 |       3 |
|   3 |    CONNECT BY WITHOUT FILTERING|      |   3773K|00:00:41.51 |       3 |
|   4 |     TABLE ACCESS FULL          | HIRE |      5 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------


해결방법

 select /*+ gather_plan_statistics leading(hire) */
        hire_date,
        to_char(sysdate, 'YYYY') this_year,
        to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
        num
   from hire,
        (select level as num
           from dual
        connect by level <= 40  --> 충분한 값을 주어야 한다.
        ) b
  where num <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date);  

Connect By 절을 인라인뷰로 만들어 Dummy 테이블처럼 사용하였다. 주의 사항은 connect by level <= 40 에서 숫자값을 충분히 주어야 한다. GAP 중에 가장 큰것이 13 이므로 넉넉히 40을 주었다.    
 
결과:
HIRE THIS        GAP        NUM
---- ---- ---------- ----------
1997 2010         13         13
1997 2010         13         12
1997 2010         13         11
.....중간생략
2006 2010          4          4
2006 2010          4          3
2006 2010          4          2
2006 2010          4          1

43 rows selected.  

정상적으로 원하는 결과가 나왔고 성능도 이상적이다.

-------------------------------------------------------------------------------------------
| Id  | Operation                       | Name | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN                     |      |     43 |00:00:00.01 |       3 |          |
|   2 |   SORT JOIN                     |      |      5 |00:00:00.01 |       3 | 2048  (0)|
|   3 |    TABLE ACCESS FULL            | HIRE |      5 |00:00:00.01 |       3 |          |
|*  4 |   SORT JOIN                     |      |     43 |00:00:00.01 |       0 | 2048  (0)|
|   5 |    VIEW                         |      |     40 |00:00:00.01 |       0 |          |
|   6 |     CONNECT BY WITHOUT FILTERING|      |     40 |00:00:00.01 |       0 |          |
|   7 |      FAST DUAL                  |      |      1 |00:00:00.01 |       0 |          |
-------------------------------------------------------------------------------------------
 
주의사항
만약 위의 SQL처럼 인라인뷰를 사용한다고 해도 Nested Loop 조인으로 풀리고 Connect By문을 사용한 인라인뷰가 후행집합이 된다면 선행집합의 건수만큼 반복해서 Connect By문이 수행되므로 조심해야 한다.

결론
Dummy 테이블을 대신하는 Connect By Level을 사용할 때 주의하지 않으면 SQL이 종료되지 않는다. 이를 방지하려면 반드시 DUAL과 함께 사용해야 하며 인라인뷰를 만들어서 사용해야 된다. 만약 예전처럼 Dummy 테이블을 사용했다면 이런 성능저하는 발생하지 않을 것이다. 아무리 새롭고 좋은것도 오용한다면 결과가 어떻게 되는지 잘 보여주는 예제이다.

3 부작의 마지막 편
첫 번째, Oracle Data Access Pattern을 정복하라
두 번째, Data Access Pattern중의 파티션에 관련된 Partition Access Pattern 에 이어서 마지막 편이다.

지난 글에서 Data Access Pattern 과 Join Method 이 두 가지는 기본 중에 기본이라고 하였다.
또한 이 두 가지를 정복한다면 SQL 튜닝중의 많은 부분을 커버할 수 있다고 하였다.
튜닝에서 이것보다 중요한 것이 있을까? 이것들 중에 하나라도 빠트린다면 제대로 된 튜닝을 할 수 없다.

단순 분류 5가지
데이터의 연결방법은 단순히 분류해 보면 다음과 같다.

1.Nested Loop Join
2.Sort Merge Join
3.Hash Join
4.Outer Join
5.Using Subquery

세분화
하지만 이것으로는 부족하다. Join Method를 좀더 자세히 나타내면 다음과 같다.

01. Nested Loop Join
02. Sort Merge Join
03. Hash Join
04. Cartesian Join (혹은 Cross Join)
05. Sub Query-In,
06. Sub Query-Any
07. Sub Query-All
08. Sub Query-Exists
09. Subquery Factoring
10. Semi Join-Nested Loop
11. Semi Join-Sort Merge
12. Semi Join-Hash
13. Semi Join-Hash Join Right
14. Anti Join-Nested Loop
15. Anti Join-Sort Merge
16. Anti Join-Hash
17. Anti Join-Hash Join Right
18. Index Join
19. Outer Join-Full
20. Outer Join-Nested Loop
21. Outer Join-Sort Merge
22. Outer Join-Hash
23. Outer Join-Hash Join Right
24. Partition Outer Join
25. Star Query Transformation
 
극한의 세분화
물론 여기서 더 세분화 시킬 수 있다. 예를 들면 Nested Loop Join은 아래와 같이 분류할 수 있다.

Full(선행집합)-Unique (후행집합)
Full(선행집합)-Range (후행집합)
Range(선행집합)-Range (후행집합)
Unique(선행집합)-Unique (후행집합)
....중간생략

이런 방법으로 Sort Merge Join과 Hash Join까지 계속 나열한다면 아마 끝이 없을 것이다.

단 한 줄도 놓치지 마라 
아래의 첨부파일에는 Nested Loop Join도 위와 같은 방법으로 가능한 세분화 하였다. 따라서 이 파일에 담긴 Join method는 25가지가 넘는다. 오늘 이야기하는 조인방법들은 튜닝을 하려면 반드시 정복해야 할 주제이니 꼼꼼히 보기 바란다.

PS
Star Join은 Star Query Transformation이 나온 후로 설 땅을 잃었으므로 나타내지 않았다.

필자는 예전에 Recursive SQL이라는 글을 통하여 IBM DB2나 Microsoft의 SQL Serve에서 사용하는 Recursive With문을 소개한적이 있다. 시간이 흘러 이제는 Oracle11gR2에서 Recursive With문을 사용할 수 있게 되었다. 오라클에서도 DB2나 SQL Serve처럼 ANSI SQL을 사용할 수 있게 된 것이다.

 

타 DBMS에 능숙한 사람들에게는 환영할만한 일이나 오라클을 사용한 사람들은 별로 달가워하지 않는 것 같다. 왜냐하면 기존의 오라클 사용자들은 Connect By의 기능이 워낙 막강하였으므로 굳이 ANSI SQL을 사용할 필요가 없다고 생각하기 때문이다.

 

하지만 과연 그럴까? 모든 기능은 용도가 있다.

 

이 글을 보고 알 수 있는 사항은 다음과 같다.

 

1. Connect By와 Recursive With의 문법 비교

2. Connect By와 Recursive With의 기능 비교

3. Connect By와 Recursive With의 성능 비교

4. 1, 2, 3번을 통하여 Connect By와 Recursive With의 장단점 파악

 

1) 문법 관점에서 Connect By VS Recursive With

 

 SELECT employee_id, first_name, manager_id, LEVEL

   FROM employee

  START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id;

 

EMPLOYEE_ID FIRST_NAME           MANAGER_ID      LEVEL

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

        100 Steven                                   1

        101 Neena                       100          2

        108 Nancy                       101          3

        109 Daniel                      108          4

        110 John                        108          4

...중간생략

        202 Pat                         201          3

 

107 rows selected.

 

 

WITH recursive(employee_id, name, manager_id, recursive_level) AS

(   SELECT employee_id, first_name, manager_id, 1 recursive_level

      FROM employee

     WHERE manager_id IS NULL                  --> START WITH 절에 해당함

    UNION ALL

    SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

      FROM employee e, recursive

     WHERE e.manager_id = recursive.employee_id --> CONNECT BY 절에 해당함

)

SELECT *

  FROM recursive;

 

 

EMPLOYEE_ID NAME                 MANAGER_ID RECURSIVE_LEVEL

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

        100 Steven                                        1

        201 Michael                     100               2

        101 Neena                       100               2

        102 Lex                         100               2

        114 Den                         100               2

...중간생략

        107 Diana                       103               4

 

107 rows selected.

 

SQL문이 길어졌다. 하지만 가독성은?

위의 예제에서 볼 수 있듯이 Recursive With 문은 Connect By에 비하여 가독성이 좋아졌다. 왜냐하면 시작조건과 찾아가는 조건이 Union All로 분기되어 있으므로 SQL이 조금 길어지긴 하였으나 해석하는데 전혀 어려운 점이 없기 때문이다. 또한 SQL이 분리되어 있으므로 각각의 성능 최적화도 쉽게 할 수 있다. 주의 사항이 있다. Recursive With 문에서는 LEVEL을 사용할 수 없지만 위의 경우처럼 숫자 초기값을 지정하고 거기에 1을 계속 더해가면 같은 기능을 사용할 수 있다. 또 하나의 주의사항은 Sort의 순서가 다르다는 것인데 Order By 기능을 설명하는 부분에서 자세히 다루어진다.

 

무한루프 발생 테스트

먼저 무한루프에 만들기 위하여 TOP LEVEL의 manager를 조작하고 Connect By와 Recursive With문을 각각 실행시켜보자.

 

UPDATE employee

   SET manager_id = 101

 WHERE employee_id = 100;

 

commit;

 

 SELECT employee_id, first_name, manager_id, LEVEL

   FROM employee

  START WITH manager_id = 100

CONNECT BY PRIOR employee_id = manager_id;

 

ORA-01436: CONNECT BY loop in user data

 

WITH recursive(employee_id, name, manager_id, recursive_level) AS

(   SELECT employee_id, first_name, manager_id, 1 recursive_level

      FROM employee

     WHERE manager_id = 100

    UNION ALL

    SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

      FROM employee e, recursive

     WHERE e.manager_id = recursive.employee_id

)

SELECT *

  FROM recursive;

 

ORA-32044: cycle detected while executing recursive WITH query

 

위에서 보는 것처럼 에러코드가 서로 다르다. 기존의 Connect By문은 이러한 무한루프를 성공적으로 제거하는 기능이 존재했다. 아래가 그 기능을 구현한 SQL이다.

 

 

SELECT employee_id,

        manager_id,

        CONNECT_BY_ISCYCLE AS iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id ;

 

107 rows selected.

 

Connect By절에 NOCYCLE을 명시하면 무한루프를 방지할 수 있다. 뿐만 아니라 CONNECT_BY_ISCYCLE 기능을 사용하면 무한루프를 발생시키는 Row를 발견할 수 있다. 또한 Oracle 9i와 10g를 거치면서 기능이 막강해져서 아래와 같이 사용할 수 있게 되었다.

 

SELECT  employee_id,

        manager_id,

        LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') as name_tree,

        CONNECT_BY_ROOT last_name as root_name,

        CONNECT_BY_ISLEAF as isleaf,

        CONNECT_BY_ISCYCLE as iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id ;

 

107 rows selected.

 

위의 기능 중에 하나라도 모르는 것이 있다면 Hierarchical Queries를 참조하라

 

2) 기능 관점에서 Connect By VS Recursive With

위에서 보았던 막강한 기능들을 Recursive With에서 모두 사용할 수 있는지 아닌지는 매우 중요하다. 왜냐하면 기능의 사용 가능 유무에 의해서 Recursive With의 존재가치가 판가름 날 것이기 때문이다. 위의 예제에서 사용한 모든 기능들을 Recursive With문에서 사용할 수 있으면 좋겠지만 아쉽게도 위에서 굵게 표시된 모든 예약어와 함수 등을 사용할 수 없다. 하지만 Recursive With문에서는 모든 기능을 예약어나 함수가 아닌 수동으로 재연할 수 있다. 그것도 아주 간편하게 말이다. 손 맛을 느끼게 해주는 SQL이다. 아래를 보자.

 

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

    FROM employee

   WHERE employee_id = 100

  UNION ALL

  SELECT e.employee_id, e.manager_id,

         recursive.name_tree||'-'||e.last_name   --> SYS_CONNECT_BY_PATH  함수에 해당함

    FROM employee e, recursive

   WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX         --> ORDER SIBLINGS BY 기능에 해당함

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"   --> CONNECT_BY_ISCYCLE 기능에 해당함

SELECT employee_id, manager_id, name_tree,

       regexp_substr(name_tree, '[^-]*' ) root_name, --> CONNECT_BY_ROOT 기능에 해당함

       decode( regexp_count(lead(name_tree) OVER(ORDER BY IDX), name_tree), 0, 1, null, 1, 0) isleaf,

       --> CONNECT_BY_ISLEAF 기능에 해당함

       iscycle

FROM recursive;

 

102 rows selected.

 

이것 봐라?

너무나 쉽게 기능을 구현하였다. 이 정도라면 기존의 Connect By 사용자도 “어라 이것 봐라?” 라고 이야기 할 것이다. 기능을 살펴보자. 먼저 SEARCH DEPTH FIRST BY 기능은 ORDER SIBLINGS BY 기능에 해당한다. 물론 employee_id 컬럼에 ASC나 DESC를 사용할 수 있다. SET IDX라는 기능은 Sort된 순서대로 일련번호를 생성하는 기능이다. 이것은 ROWNUM과 기능이 비슷하지만 다른 점은 정렬된 순서대로 채번된다는 것이다. 주의사항은 SEARCH DEPTH FIRST 기능을 사용하지 않고 보통의 Order By 구문을 사용하게 되면 Tree 구조가 유지되지 않는다는 것이다. 이런 관점은 Connect By의 ORDER SIBLINGS BY 기능과 같다.

CYCLE manager_id SET iscycle TO "1" DEFAULT "0" 라고 선언하면 manager_id의 값이 잘못되어 무한루프가 발생될 때 식별할 수 있다. 이때 iscycle을 가상의 컬럼으로 이용하게 된다. 주의사항은 iscycle의 값은 숫자가 될 수 없으며 1 Byte 문자만 가능하다. “1” 과 “0”을 사용한 이유도 여기에 있다. regexp_substr를 사용한 이유는 문자를 첫번째 ‘-‘ 까지 잘라내야 root_name을 구할 수 있기 때문이다. Regular Expression에 대하여 자세히 알고 싶은 독자는 오라클 Regular Expressions 완전정복을 참조하라. regexp_count는 버전 11g에 추가된 기능이다. 이 기능은 다음처럼 사용할 수 있다.

 

ex) regexp_count(text1, text2) :

text1에 text2가 포함된 횟수를 Return한다. 예를 들면 regexp_count(‘yahoo.co.kr’, ‘o’) 라고 했다면 o 가 3번 포함되어 있으므로 3이 Return 된다.  

 

 

건수가 차이 난다

하지만 문제가 있다. 결과건수가 차이가 난다. Connect By는 결과가 107건이며 Recursive With문은 102건을 Return 하였다. 이 5건의 차이는 무엇인가? 아래는 차이의 분석을 위하여 두 SQL의 결과값 중에서 필요한 부분만 표로 정리한 것이다.

 

Connect By 사용			Recursive With 사용
EMP_ID	NAME_TREE	IS CYCLE	EMP_ID	NAME_TREE	IS CYCLE
101	King-Kochhar	1	101	King-Kochhar	0
출력되지 않음			100	King-Kochhar-King	1
204	King-Kochhar-Baer	0	204	King-Kochhar-Baer	1
108	King-Kochhar-Greenberg	0	108	King-Kochhar-Greenberg	1
110	King-Kochhar-Greenberg-Chen	0	출력되지 않음
109	King-Kochhar-Greenberg-Faviet	0
113	King-Kochhar-Greenberg-Popp	0
111	King-Kochhar-Greenberg-Sciarra	0
112	King-Kochhar-Greenberg-Urman	0
205	King-Kochhar-Higgins	0	205	King-Kochhar-Higgins	1
206	King-Kochhar-Higgins-Gietz	0	출력되지 않음
203	King-Kochhar-Mavris	0	203	King-Kochhar-Mavris	1
200	King-Kochhar-Whalen	0	200	King-Kochhar-Whalen	1

 

어떤 차이가 있나?

결론을 이야기하면 Connect By의 NoCycle, IsCycle 기능과 Recursive With의 Cycle및 IsCycle 기능과는 차이가 있다.즉 각각의 기능이 다르므로 용도를 구분할 줄 알아야 한다는 뜻이다.

 

Connect By의 NoCycle 기능은 King과 Kochhar의 관계가 반복되는 것을 Skip하고 표시된다. 즉 직원번호 204의 name_tree 항목을 보면 King-Kochhar-Baer 라고 되어 있지만 사실은 King-Kochhar- King-Kochhar - King-Kochhar ....무한반복... Baer 처럼 표시할 수 있다. 하지만 Connect By + NoCycle을 사용하면 무한반복을 Skip하고 한번만 나타낸다. 또한 IsCycle 도 반복이 시작되기 직전의 데이터를 식별해주는 기능을 한다.

 

Connect By + NoCycle을 사용하면 직원번호 101의 데이터는 Cycle 로 표시되는데 사실은 아직 Cycle이 아니다. 왜냐하면 오른쪽의 Recursive With를 사용한 경우를 보면 직원번호 100번의 데이터는 King-Kochhar-King인데 King이 두 번 반복 된 것을 볼 수 있고 이 경우가 첫 번째 반복되는 Cycle 이기 때문이다. Recursive With의 IsCycle 항목에는 이런 관점이 잘 반영되어 나타난다. 이 데이터는 Connect By + NoCycle을 사용하면 볼 수 없다.

 

마지막으로 Connect By를 사용하면 데이터가 출력되지만 Recursive With를 사용하면 출력되지 않는 Row가 있다. 그 이유는 Connect By는 관계의 반복만 제거하고 마지막까지 데이터를 찾아가지만 Recursive With는 반복되는 첫 번째 데이터만 찾고 거기서 멈춘다. 즉 Connect By를 사용하면 King-Kochhar-Greenberg-Chen로 데이터의 끝까지 찾아가지만 Recursive With를 사용하면 King-Kochhar-Greenberg 여기서 멈춘다. 위의 표를 유심히 비교해 보기 바란다. 지금까지 문법과 여러 가지 기능의 차이를 알아보았다.

 

3) 성능관점에서 Connect By VS Recursive With

이제 가장 중요한 성능을 비교해보자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

        employee_id,

        manager_id,

        LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') name_tree,

        CONNECT_BY_ROOT last_name root_name,

        CONNECT_BY_ISLEAF isleaf,

        TO_CHAR(CONNECT_BY_ISCYCLE) iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id;

 

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

| Id  | Operation                     | Name           | A-Rows | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT              |                |    107 |      16 |          |

|*  1 |  CONNECT BY WITH FILTERING    |                |    107 |      16 | 2048  (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE       |      1 |       2 |          |

|*  3 |    INDEX UNIQUE SCAN          | EMP_EMP_ID_PK  |      1 |       1 |          |

|   4 |   NESTED LOOPS                |                |    106 |      14 |          |

|   5 |    CONNECT BY PUMP            |                |    107 |       0 |          |

|   6 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE       |    106 |      14 |          |

|*  7 |     INDEX RANGE SCAN          | EMP_MANAGER_IX |    106 |       5 |          |

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

Predicate Information (identified by operation id):

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

   1 - access("MANAGER_ID"=PRIOR NULL)

   3 - access("EMPLOYEE_ID"=100)

   7 - access("connect$_by$_pump$_002"."PRIOR employee_id "="MANAGER_ID")

 

 

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

    FROM employee

   WHERE employee_id = 100

  UNION ALL

  SELECT e.employee_id, e.manager_id,

         recursive.name_tree||'-'||e.last_name

    FROM employee e, recursive

   WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"

SELECT /*+ GATHER_PLAN_STATISTICS */

       employee_id, manager_id, name_tree,

       regexp_substr(name_tree, '[^-]*' ) root_name,

 decode(regexp_count(lead(name_tree) OVER(ORDER BY IDX), name_tree), 0, 1, null, 1, 0) isleaf,

       iscycle

  FROM recursive;

 

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

| Id  | Operation                                | Name           | A-Rows | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT                         |                |    107 |      16 |          |

|   1 |  WINDOW BUFFER                           |                |    107 |      16 | 8192  (0)|

|   2 |   VIEW                                   |                |    107 |      16 |          |

|   3 |    UNION ALL (RECURSIVE WITH) DEPTH FIRST|                |    107 |      16 |          |

|   4 |     TABLE ACCESS BY INDEX ROWID          | EMPLOYEE       |      1 |       2 |          |

|*  5 |      INDEX UNIQUE SCAN                   | EMP_EMP_ID_PK  |      1 |       1 |          |

|   6 |     NESTED LOOPS                         |                |    106 |      14 |          |

|   7 |      NESTED LOOPS                        |                |    106 |       5 |          |

|   8 |       RECURSIVE WITH PUMP                |                |    107 |       0 |          |

|*  9 |       INDEX RANGE SCAN                   | EMP_MANAGER_IX |    106 |       5 |          |

|  10 |      TABLE ACCESS BY INDEX ROWID         | EMPLOYEE       |    106 |       9 |          |

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

Predicate Information (identified by operation id):

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

   5 - access("EMPLOYEE_ID"=100)

   9 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

 

IsLeaf 기능을 사용하려면 Connect By가 유리하다

Scan한 블럭수는 16으로 동일하다. 하지만 PGA 사용량이 4배나 차이 난다. 하지만 이것은 Connect By와 Recursive With의 성능 차이가 아니라 분석함수 lead의 사용 유무에 의한 차이이다. 즉 IsLeaf 기능이 필요하다면 기존의 Connect By를 사용하는 것이 유리할 것이다. 하지만 Connect By나 Recursive With 자체의 성능만 비교한다면 결과가 달라진다.

 

Connect By와 Recursive With에서 IsLeaf 항목을 빼고 다시 실행 해보자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

        employee_id,

        manager_id,

        LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') name_tree,

        CONNECT_BY_ROOT last_name root_name,

TO_CHAR(CONNECT_BY_ISCYCLE) iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id;

 

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

| Id  | Operation                     | Name           | A-Rows | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT              |                |    107 |      16 |          |

|*  1 |  CONNECT BY WITH FILTERING    |                |    107 |      16 | 2048  (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE       |      1 |       2 |          |

|*  3 |    INDEX UNIQUE SCAN          | EMP_EMP_ID_PK  |      1 |       1 |          |

|   4 |   NESTED LOOPS                |                |    106 |      14 |          |

|   5 |    CONNECT BY PUMP            |                |    107 |       0 |          |

|   6 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE       |    106 |      14 |          |

|*  7 |     INDEX RANGE SCAN          | EMP_MANAGER_IX |    106 |       5 |          |

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

Predicate Information (identified by operation id):

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

   1 - access("MANAGER_ID"=PRIOR NULL)

   3 - access("EMPLOYEE_ID"=100)

   7 - access("connect$_by$_pump$_002"."PRIOR employee_id "="MANAGER_ID")

 

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

    FROM employee

   WHERE employee_id = 100

  UNION ALL

  SELECT e.employee_id, e.manager_id,

         recursive.name_tree||'-'||e.last_name

    FROM employee e, recursive

   WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"

SELECT /*+ GATHER_PLAN_STATISTICS */

       employee_id, manager_id, name_tree,

       regexp_substr(name_tree, '[^-]*' ) root_name,

       iscycle

  FROM recursive;

 

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

| Id  | Operation                               | Name           | A-Rows | Buffers |

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

|   0 | SELECT STATEMENT                        |                |    107 |      16 |

|   1 |  VIEW                                   |                |    107 |      16 |

|   2 |   UNION ALL (RECURSIVE WITH) DEPTH FIRST|                |    107 |      16 |

|   3 |    TABLE ACCESS BY INDEX ROWID          | EMPLOYEE       |      1 |       2 |

|*  4 |     INDEX UNIQUE SCAN                   | EMP_EMP_ID_PK  |      1 |       1 |

|   5 |    NESTED LOOPS                         |                |    106 |      14 |

|   6 |     NESTED LOOPS                        |                |    106 |       5 |

|   7 |      RECURSIVE WITH PUMP                |                |    107 |       0 |

|*  8 |      INDEX RANGE SCAN                   | EMP_MANAGER_IX |    106 |       5 |

|   9 |     TABLE ACCESS BY INDEX ROWID         | EMPLOYEE       |    106 |       9 |

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

Predicate Information (identified by operation id):

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

   4 - access("EMPLOYEE_ID"=100)

   8 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

 

일반적인 경우 Recursive With가 유리함

보았는가? 상황이 역전되어 Recursive With를 사용하면 PGA를 전혀 사용하지 않는다. 따라서 IsLeaf를 사용하지 않는 일반적인 경우 Recursive With가 유리함을 알 수 있다.

 

대용량 배치일 경우 Recursive With를 주의하라

하지만 이것이 끝이 아니다. 대용량 배치일 경우는 상황이 다시 반전된다.

 

WITH recursive(employee_id, name, manager_id, recursive_level) AS

(   SELECT employee_id, first_name, manager_id, 1 recursive_level

      FROM employee

     WHERE  manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

      FROM employee e, recursive

     WHERE e.manager_id = recursive.employee_id

)

SELECT /*+ GATHER_PLAN_STATISTICS */  *

  FROM recursive;

 

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

| Id  | Operation                                 | Name           | A-Rows | Buffers |

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

|   0 | SELECT STATEMENT                          |                |    107 |      22 |

|   1 |  VIEW                                     |                |    107 |      22 |

|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|                |    107 |      22 |

|*  3 |    TABLE ACCESS FULL                      | EMPLOYEE       |      1 |       8 |

|   4 |    NESTED LOOPS                           |                |    106 |      14 |

|   5 |     NESTED LOOPS                          |                |    106 |       5 |

|   6 |      RECURSIVE WITH PUMP                  |                |    107 |       0 |

|*  7 |      INDEX RANGE SCAN                     | EMP_MANAGER_IX |    106 |       5 |

|   8 |     TABLE ACCESS BY INDEX ROWID           | EMPLOYEE       |    106 |       9 |

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

Predicate Information (identified by operation id):

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

   3 - filter("MANAGER_ID" IS NULL)

   7 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

 

대용량 배치일 경우 Recursive With는 비효율이 발견되었다. manager_id IS NULL 조건 때문에 EMPLOYEE 테이블을 Full Scan 하였지만 Union ALL 아래의 SQL에서 또다시 EMPLOYEE 테이블을 Scan하고 있다. 동일한 블록을 두 번 Scan한 셈이다. 하지만 아래를 보라.

 

SELECT /*+ GATHER_PLAN_STATISTICS */ employee_id, first_name, manager_id, LEVEL

   FROM employee

  START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id;

 

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

| Id  | Operation                               | Name     | A-Rows | Buffers |

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

|   0 | SELECT STATEMENT                        |          |    107 |       7 |

|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|          |    107 |       7 |

|   2 |   TABLE ACCESS FULL                     | EMPLOYEE |    107 |       7 |

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

Predicate Information (identified by operation id):

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

   1 - access("MANAGER_ID"=PRIOR NULL)

       filter("MANAGER_ID" IS NULL)

 

Connect By를 사용하면 Union을 사용하지 않으므로 Full Table Scan 한 번으로 끝낼 수 있다. Scan한 블럭수는 세 배 이상 차이가 난다.

 

Connect By VS Recursive With 의 결론

1) 문법적인 측면에서 거의 차이가 없다. Recursive With가 조금 길어지기는 하지만 Union All로 분리되어 있기 때문에 오히려 가독성과 성능 최적화가 쉽게 될 수 있다.

 

2) 기능면에서도 거의 차이가 없다. Connect By의 모든 기능이 구현 가능하다. 다만 Nocycle 기능과 IsCycle 기능의 용도가 서로 다르므로 구분해서 사용하면 된다.

 

3) 성능면에서는 Sort 기능을 사용해도 PGA를 전혀 사용하지 않는 Recursive With가 일반적으로 유리하다. 하지만 IsLeaf 기능을 구현하려면 분석함수를 사용해야 하기 때문에 Connect By가 유리할 수 있다. 또한 Scan할 범위가 넓은 경우 Start With 조건을 Full Table Scan으로 유도하면 같은 블럭을 반복해서 Scan하지 않는 Connect By가 유리하다.

 

결국 각 기능들의 허와 실을 제대로 파악하고 성능 이슈를 최소화 한다면 모두가 웃을 수 있을 것이다.
모두가 웃는 그날까지......  

지난시간의 DEUI라는 기능에 이어서 이번시간에는 그 사촌격인 DE에 대해서 논의해보자.

DE (Distinct Elimination)란 무엇인가
DE는 Unique한 집합일 경우 불필요한 Distinct를 제거하는 기능이다. 이렇게 함으로써 Sort와 중복제거 등의 부하가 많은 작업을 수행하지 않을 수 있다. 이 기능은 Oracle 11g에서 추가되었다. 이제 DE가 어떻게 수행되는지 알아보자.

 

----------------------------------------+-----------------------------------+

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

----------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT    |           |       |       |     3 |           |

| 1   |  NESTED LOOPS       |           |    27 |   270 |     3 |  00:00:01 |

| 2   |   TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     3 |  00:00:01 |

| 3   |   INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |

----------------------------------------+-----------------------------------+

Predicate Information:

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

3 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

Unique를 보장하는 컬럼이 있으면 Distinct가 필요없다
실행계획에서 Distinct에 해당하는 Operation인 Sort Unique 혹은 Hash Unique가 사라졌다. 이유는 Transformer가 위의 SQL을 분석해서 Distinct가 없어도 Unique 함을 알았기 때문이다. Select 절에 있는 d.department_id와 l.location_id는 From 절에 있는 두 테이블의 PK 이다.  따라서 Distinct는 당연히 필요 없음으로 Logical Optimizer가 삭제한 것이다.

아래는 DE와 관련된 10053 Event의 Trace 내용이다.
 

OBYE:   Considering Order-by Elimination from view SEL$1 (#0)
***************************

Order-by elimination (OBYE)

***************************

OBYE:     OBYE bypassed: no order by to eliminate.

Eliminated SELECT DISTINCT from query block SEL$1 (#0)

…이후생략

10053 Trace 상에서는 DE가 OBYE 자리에서 발생하였다. 이것은 OBYE 수행여부를 체크할 때 DE의 수행여부를 같이 체크하므로 DE가 OBYE 자리에서 발생되는 것 같다.

함정에 주의할 것
Unique 하다고 해서 항상 DE가 발생될까? 그렇지 않다. 아래의 SQL을 보자.

SELECT distinct d.department_id, d.location_id

  FROM department d, location l

 WHERE d.location_id = l.location_id ;

위의 SQL은 location_id를 department 테이블의 컬럼으로 대체하였다는 점을 제외하면 최초의 SQL과 완전히 같다.
-----------------------------------------+-----------------------------------+

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

-----------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT     |           |       |       |     4 |           |

| 1   |  HASH UNIQUE         |           |    27 |   270 |     4 |  00:00:01 |

| 2   |   NESTED LOOPS       |           |    27 |   270 |     3 |  00:00:01 |

| 3   |    TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     3 |  00:00:01 |

| 4   |    INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |

-----------------------------------------+-----------------------------------+

Predicate Information:

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

4 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

 

Unique를 보장하는 모든 테이블의 컬럼이 Select 절에 사용되어야
컬럼 하나만이 바뀌었을 뿐인데 실행계획에 Hash Unique가 생긴 것이다. 여기서 알 수 있는 것은 From 절에 나열된 모든 테이블의 PK 컬럼 혹은 Unique 컬럼이 Select 절에 나와야 Distinct가 제거된다는 사실이다. 이것은 아직 DE 기능이 완성되지 않은 것을 의미한다. 논리적으로는 Select 절에 d.location_id를 사용하거나 l.location_id를 사용해도 같기 때문에 DE가 발생 해야 하지만 아직 이런 기능이 없다는 것이 아쉽다.

DE 기능을 Control 하는 파라미터는 _optimizer_distinct_elimination이며 Default로 True 이다. 하지만 이 파라미터로는 DEUI 기능을 Control 할수 없다. 한가지 주의사항은 DE가 버전 10gR2(10.2.0.4)에서도 수행된다는 점이다. 다만 _optimizer_distinct_elimination 파라미터가 없다는 것이 11g와 다른 점이다.

결론
만약 이런 일이 대용량 테이블에서 발생한다면 결과는 심각한 성능저하로 나타날 수 있으므로 조인된 컬럼을 사용할 것인지 아니면 참조되는 컬럼을 사용할 것인지 아주 신중히 결정해야 한다.

성능저하가 예상되는 부분
예를들면 서브쿼리가 Unnesting되어 Distinct가 자동으로 추가된 인라인 뷰에 CVM이 발생하면 인라인뷰가 해체되므로 전체집합에 대해서 Sort Unique 혹은 Hash Unique가 발생된다. 전체집합이 대용량이라면 성능은 심각하게 저하될 것이다. 이 사실은 어떤 컬럼을 Select 절에서 사용할 것인지 아주 신중히 결정해야 하며 Merge 힌트를 얼마나 조심스럽게 사용해야 하는지를 잘 나타내 주고 있다.

PS
이렇게 나가다간 아마 책을 출판할 필요가 없을듯 하다.^^ 하지만 책보다는 블로그가 우선이다.

지인으로부터 아래와 같은 질문을 받았다.

 

질문 : Predicate Information을 참조하려면 DBMS_XPLAN 패키지를 사용해야만 합니까?

       저희 개발자들은 DBMS_XPLAN 패키지를 사용할 권한이 없습니다.
       따라서 오렌지나 TOAD에서 간단히 볼 수 있는 방법이 필요합니다. 가능 합니까?

 

답변 : 볼 수 있습니다. 단 PLAN_TABLE을 볼수 있는 권한은 있어야 합니다.

요청: 그건 있습니다. 방법을 블로그에 올려주시면 나머지 사람들도 볼수 있겠네요. 올려주시죠.

이렇게 해서 이글을 작성 하게 되었다. 이런 질문을 받았다는 것은 2가지 의미로 해석할 수 있다. 첫번째, 의외로 오렌지나 TOAD의 기능을 모르는 사람이 많이 있을 수 있다는 의미다. 두번째, 튜닝에 필요한 권한이 개발자에게 없다는 안타까움 이다. 이 정책은 매우 아쉬운 선택이며 앞으로 개선되기를 기대해본다. 하지만 수정이 필요한 법이나 악법도 법이므로 수정되기 전까진 따라야 한다.

Predicate Information과 관련된 가장 흔한 오류는 10046 이벤트 + tkprof를 사용하면 Predicate Information을 볼수 있다고 착각 하는 것이다. 절대 볼수 없다. 

Predicate Information이 뭐지?
Predicate Information이란 인덱스 scan 시의 컬럼 액세스 정보, 조인정보, filter 정보를 각 Opreation 단위로 나타낸 것이다. 아래의 예제를 보자.
 

explain plan for

SELECT /*+ LEADING(e) USE_NL(d) */

       e.employee_id, e.first_name, e.last_name, e.email, e.salary

  FROM employee e, department d

 WHERE e.department_id = d.department_id

   AND e.job_id = 'SH_CLERK';

  

select * from table(dbms_xplan.display);  

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

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

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

|   0 | SELECT STATEMENT            |            |    20 |   860 |     3   (0)|

|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |    20 |   860 |     3   (0)|

|*  2 |   INDEX RANGE SCAN          | EMP_JOB_IX |    20 |       |     1   (0)|

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

 

Predicate Information (identified by operation id):

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

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL) --> FILER 정보가 출력됨

   2 - access("E"."JOB_ID"='SH_CLERK')         --> INDEX SCAN 정보 혹은 JOIN 정보가 출력됨

 

FILTER와 ACCESS 정보는 중요하다

위에서 출력된 Predicate Information을 보면 FILTER 와 INDEX SCAN 정보를 정확히 볼 수 있다. 특히 인덱스가 여러 개의 컬럼으로 구성된 경우 몇 번째 컬럼까지 액세스 되었는지 보려면 Predicate Information이 필수적인 것이다. 예를 들어 인덱스가 COL1 + COL2 + COL3로 되어 있는데 Predicate Information에서 INDEX를 SCAN에 사용된 컬럼이 하나뿐이고 COL2와 COL3는 테이블의 FILTER로 풀린다면 성능에 문제가 될 수 있다. 따라서 Predicate Information을 확인 하는 것은 매우 중요한 것이다.

 

문제는 이처럼 중요한 정보를 DBMS_XPLAN 패키지를 사용하지 않고 'TOAD나 오렌지에서 어떻게 볼수 있냐' 이다.

지금부터 따라 해보기 바란다.

 

1. TOAD에서 Predicate Information 보기

먼저 토드 화면에서 EXPLAIN PLAN을 실행한다. EXPLAIN PLAN은 구급차 아이콘을 누르면 된다.

그러면 위와 같은 화면이 출력될 것이다. 위의 화면에서는 Predicate Information가 없다. 지금부터 Predicate Information을 추가해보자.

먼저 TOAD 화면의 하단(Explain Plan) 탭에서 오른쪽 버튼을 클릭한다. 연이어 Adjust Content를 선택한다.

그러면 위와 같은 화면이 뜨는데 여기서 Access Predicates와 Filter Predicates의 Visible 항목을 체크하고 OK를 클릭한다.

위와 같이 TOAD에서 Access Predicates와 Filter Predicates가 깔끔하게 출력되었다.

2. 오렌지에서 Predicate Information 보기

오렌지를 사용한다면 PLAN TOOL에서 Show Plan버튼을 클릭하거나 Function 키 F5를 누르고 하단의 세번째 탭을 클릭하면 아래의 그림처럼 Predicate Information을 볼 수 있다.

확인하는 습관이 필요해
이로써 어디서든 무엇을 사용하든 버튼 클릭 만으로 Predicate Information을 볼 수 있게 되었다. 이제부터 Predicate Information을 애용하기 바란다. 특히 Index Scan 시에 몇번째 컬럼까지 이용하였는지 확인하는 습관이 필요하다.

PS :
필자는 TOAD나 오렌지의 제조사나 판매사와는 상관없는 사람이다. 단지 가끔 이용할 뿐...

지난 2009년 10월달에 Oracle Data Access Pattern을 정복하라 라는 글을 통하여 데이터의 접근방법에 대하여 알아보았다. 오늘은 파티션 데이터의 접근방법에 대하여 알아보자. 필자가 이글을 올리는 이유는 실행계획에 Partition Access Pattern 이 나오지만 해석을 못하는 사람이 많이 있기 때문이다. 오늘 이글을 읽고 이해한다면 파티션에 어떻게 접근하는지 접근하는 방법은 어떤것인지 모두 알수 있다.

기본적인 Partition의 종류는 3가지이다.

1.RANGE
2.LIST
3.HASH

하지만 위의 3가지를 엑세스 패턴으로 나누고자 한다면 매우 종류가 많아진다.

이번 내용은 난위도가 있으므로  'Interleaving은 선수조건이 필요하다' 단락을 읽고 이해가 가는 사람만 보기 바란다. 필자의 글을 꾸준히 구독한 독자라면 어렵지 않게 볼수 있을 것이다.

전략적 결혼
사극을 보면 권력가들이 자신의 세력를 확장하기 위해 자신의 딸을 왕에게 추천하여 결혼시키는 것을 심심치 않게 볼수 있다. 자신의 목표를 이루기 위한 전단계의 포석이라고 할수 있다. 딸이 왕자를 순산하고 그 왕자가 차세대 왕이 된다면 자신은 최고의 권력을 가지는것과 마찬가지 이다. CBQT가 수행될 때에도 이러한 전략적 결혼과 같은것이 존재한다는 사실을 알고 있는가? CBQT를 최적화 하기 위해서 Transformation 간의 관계는 필수적이다. 오라클에서는 Interleaving이라는 기법을 사용하고 있다.

Interleaving은 선수조건이 필요하다
하나의 Transformation(T2) 을 수행하기 위해서 다른 Transformation(T1)이 반드시 먼저 실행되어야 하는 경우가 있다. 이럴 때 Interleaving 기법을 이용하여 T1이 수행되면 마치 기다렸다는 듯이 T2가 실행된다. 대표적인 경우가 CSU(Complex Subquery Unnesting)가 실행되면 연이어 CVM(Complex View Merging)혹은 JPPD(Join Predicate Push Down)가 실행되는 경우이다.

서브쿼리가 Unnesting 되면 초기상태는 인라인뷰로 생성된다. 인라인뷰를 제거하고 정상적인 조인으로 바꾸는 기능이 CVM 이라는 것은 이미 알것이다. 즉 CVM을 시작하기 위해서는 CSU라는 전단계의 포석이 반드시 이루어 져야만 한다. 이 규칙은 JPPD에도 똑같이 적용된다.

숫자표기법의 이해
Interleaving 에 대한 10053 Trace 내용을 분석하려면 숫자 표기법을 알아야 한다. 예를 들어 Complex Subquery가 하나 있는 SQL은 아래와 같이 0 과 1 로 Unnesting 상태를 나타낼수 있다.

CSU(1) : 서브쿼리가 Unnesting 됨
CSU(0) : 서브쿼리가 Unnesting 되지 않음.

만약 서브쿼리가 2개(SUBQ2, SUBQ1)라면 아래처럼 표현된다.
CSU(1,1) : 모든 서브쿼리가 Unnesting 됨
CSU(1,0) : SUBQ2만 Unnesting 됨
CSU(0,1) : SUBQ1만 Unnesting 됨
CSU(0,0) : 모든 서브쿼리가 Unnesting 되지 않음.

이러한 표기법은 CSU 뿐만 아니라 CVM, JPPD 등에도 똑같이 적용되며 10053 Trace에서 자주 나타나므로 반드시 알아두어야 한다.

Interleaving의 용도
Interleaving은 주로 CSU 수행시의 비용계산 오류를 줄이는 용도로 사용한다. 예를 들어 CSU(0)이 최저 Cost로 선택되었다면 JPPD의 입장에서는 전혀 다른 결과를 가져올 수 있다. CSU(0)이 아닌 CSU(1) 상태에서 JPPD을 적용하는 것이 최저 Cost가 될 수 있다. 따라서 Interleaving은 CBQT간에 최저 Cost를 구하기 위해 대화를 하는 기능이라고 할 수 있다. Interleaving 기능이 존재함으로써 CSU + CVM(혹은 JPPD)을 모두 고려한 최적의 Cost를 구 수 있다. 이제 아래의 SQL을 보자.

SELECT /*+ QB_NAME(MAIN_VIEW) */ 

       e1.employee_id, e1.manager_id, e1.salary

  FROM employee e1

 WHERE e1.department_id = 10

     and (e1.manager_id, e1.salary) in (select /*+ QB_NAME(SUBQ) */

                                             e2.manager_id,  max(e2.salary) 

                                        from employee e2

                                       group by e2.manager_id )

     and rownum = 1

 

위의 SQL에 해당하는 10053 Trace의 내용을 미리 예상해보자. 먼저 서브쿼리에 Group By를 사용하였으므로 CSU가 발생할 것이다. 연이어 CSU의 과정 중에 Interleaving 이 발생하여 CVM및 JPPD의 Cost가 같이 고려될 것이다. 하지만 위의 SQL은 조건절에 Rownum을 사용하였으므로 CVM이 발생할 수 없다. 따라서 JPPD만 고려될 것이다. 이제 우리가 예상한 내용이 맞는지 확인 해보자. 아래의 Trace 내용은 필요한 부분만 발췌하여 요약한 것이다.

 

*****************************

Cost-Based Subquery Unnesting
*****************************
SU: Using search type: exhaustive

SU: Starting iteration 1, state space = (2) : (1)

  Cost: 6.0035  Degree: 1  Card: 1.0000  Bytes: 41

 

SU: Considering interleaved complex view merging

CVM:     CVM bypassed: Outer QBc referencing ROWNUM.

  Cost: 5.0035  Degree: 1  Card: 1.0000  Bytes: 41

SU: Interleaved cost better than best so far.
SU: Finished interleaved complex view merging

SU: Considering interleaved join pred push down

JPPD: Using search type: linear

JPPD: Considering join predicate push-down

JPPD: Starting iteration 1, state space = (2) : (0)

  Cost: 6.0035  Degree: 1  Card: 1.0000  Bytes: 41

 

JPPD: Starting iteration 2, state space = (2) : (1)

  Cost: 4.0010  Degree: 1  Card: 1.0000  Bytes: 30

 

JPPD: Selected interleaved query.

SU: Finished interleaved join pred push down

SU: Updated best state, Cost = 4.00

 

SU: Starting iteration 2, state space = (2) : (0)

  Cost: 6.0037  Degree: 1  Card: 1.0000  Bytes: 15

 

SU: Not update best state, Cost = 6.00

SU: Will unnest subquery SUBQ (#2)

SU: Reconstructing original query from best state.

우리의 예상대로 먼저 CSU가 발생되었다. 연이어 Interleaving 기능에 의해 CVM이 고려되고 있지만 Rownum 제약사항 때문에 CVM이 발생되지 못한다.(CVM bypassed 부분 참조) 하지만 불완전 Costing(CVM을 제외한 최적화)은 계속 진행된다.

CVM과 JPPD 는 동시에 고려된다
CVM 과정이 끝나면 JPPD의 Costing 과정이 진행된다. Interleaving이 CVM뿐만 아니라 JPPD도 동시에 고려하고 있음을 알 수 있다. JPPD를 적용한 Iteration(JPPD: Starting iteration 2, state space = (2) : (1) 부분 참조) 의 Cost가 4.0010으로 최적임을 알 수 있다. 이로서 모든 Interleaving이 완료되었다.( SU: Finished interleaved join pred push down 부분 참조) 마지막으로 CSU를 수행하지 않는 Iteration의 Cost를 구하고 모든 CSU과정을 마치게 된다.

CSU의 최저 Cost는 CSU + CVM + JPPD를 모두 고려한것
한가지 주의해야 할 사항은 4.0010은 JPPD의 Cost가 되는 것이 아니라 CSU의 Cost 이다. SU: Updated best state, Cost = 4.00 부분을 보면 이러한 사실을 알 수 있다. 즉 CSU의 Cost는 단순히 CSU 자체의 Cost만 고려하는 것이 아니라 CSU + CVM + JPPD를 모두 고려한 후 최적의 Cost를 갖는 경우를 선택하는 것을 알 수 있다. Interleaving 기능으로 인하여 더욱 정확한 Cost를 구하는 것이 가능한 것이다.

PS
1)위의 내용은 내년초 출간될 책의 일부분을 발췌한 것이다.
2)아이러니 하게도 맨처음에 설명한 전략적 결혼과 같은 비근대적인 방법을 21세기의 우리사회에서도 가끔 볼수 있다.

아래는 약 3년전에 고객사 DBA 와 필자의 대화내용 이다.
물론 내용은 오라클 파티션 가이드 이다.

파티션 가이드? 구글에 있잖아!

이상하네? 가이드가 없네?
그 DBA 의 말이 일리가 있었다. 3년전 구글에서 조회 해보니 파티션 종류별 특성과 설명 뿐이었다. 이것은 놀라운 결과이다. 무수한 프로젝트에서 파티션을 적용해왔을 터
그런데 가이드가 없다니...

우리가 원하는 것은  "테이블을 어떠한 기준으로 무슨 종류의 파티션을 적용하느냐" 이다.
문제는 "각테이블을 어떤 기준으로 파티션을 적용하며 파티션의 종류는 무엇으로 할것인가?" 이다.  그런데 최근에 다시 파티션 가이드에 대해 구글에서 조회해본 결과 이런 성격의 자료가  전혀 없다. 물론 영문검색을 하면 오라클 메뉴얼이 나오긴 한다. 오라클 메뉴얼에도 명확한 기준은 없다. 또한 고객들이 원하는 것은 한글로된 문서이다.

가이드를 작성하는 방법
일반적으로 가이드라고 하는것은 아래와 같은 조건을 충족 해야한다.

위의 방법대로 Oracle 10g 파티션 가이드를 작성해보면 다음과 같이 한장으로 요약 할수 있다.

위의 로드맵을 한글로 표현하면 다음과 같다.


이상으로 2장짜리 Oracle 10g 파티션 가이드를 만들어 보았다. 한글로된 가이드를 필자가 작성하였고 그것을 보고 한 지인이 즉석에서 로드맵을 작성한 것 인데 필자가 그 로드맵의 일부분을 알기쉽게 수정한것이다.
아래는 이 가이드를 정리한 pdf 문서이다.
이 문서에는 추가적으로 파티션 적용시의 주의사항 까지 덤으로 있으니 반드시 다운 받아서 읽어 보기 바란다.
물론 문서를 배포시 출처를 밝히는 것은 기본이고 매너의 문제이다.





편집후기 :
가이드를 작성 하고 보니 한가지가 위에서 언급한 가이드를 작성하는 방법1 에 위배된다.  대용량 혹은 소용량 테이블이 그것인데 이 용어는 필자로 서도 어쩔수 없다. 테이블의 용량이 어디서 부터 대용량이고 어디서 부터 소용량인지 구분하는 기준은 존재하지 않기 때문이다.

한가지 화두가 있는데 물리 모델링시에 모델러가 파티셔닝을 해야할까? 아니면 이것을 모델링의 개념이 아닌 튜닝의 개념으로 보아서 튜너 혹은 DBA 가 해야할까? 여러분은 어떻게 생각 하는가?

오라클도 역사의 저편으로 사라지나?
HDB, NDB 등의 DBMS 는 아직까지 일부 시스템에서 사용하고 있으나 대세는 이미 RDB 로 넘어갔다.
RDBMS 는 90 대부터 약 20년간 꾸준히 사용되어 왔다. 하지만 오라클 같은 RDBMS 도 HDB 나 NDB 처럼 역사의 저편으로 갈날이 얼마 남지 않은것 같다. 미래에는 SQL 을 사용하며 오라클과 같은 RDBMS 를 사용하는것이 NDB 나 HDB 를 사용했던 것 처럼 구식이 되어버릴 것이다.

근거가 있냐고?
물론 있다. 아래의 2가지가 이미 실현되고 있기 때문이다. 필자는 아래의 2가지에 대해서는 전문가도 아니고 잘 알지도 못하지만 RDBMS 관련 일을 하는 사람으로서 이런 것들은 오라클과 같은 RDBMS 의 입장에서는 아주 위협적이라고 생각한다.

1. NoSQL 운동 


상세한 관련기사는 아래의 링크에서 확인 할수 있다.
출처 : "SQL 반대?" 부상하는 반 데이터베이스 운동


2. 구글의 Fusion Tables

기술 분석가인 스테판 아놀드에 따르면  “이제 우리에게 4차원 공간인 하이퍼큐브가 있고, 이 공간에서 새로운 제품과 시장을 창출할 수 있는 새로운 종류의 쿼리를 수행할 수 있다”며, “IBM이나 오라클, 마이크로소프트에겐 최고의 악몽이 시작되는 것이다. 구글은 데이터 스페이스를 자동으로 구축하고 새로운 종류의 쿼리를 도입할 계획”이라고 덧붙였다.
 
Fusion Tables 관련기사는 아래의 링크에서 확인해볼수 있다.
출처 : 구글, 클라우드 기반 신개념 데이터베이스 테스트 중


문제는 상업화및 활성화의 시기 라는것
프로젝트를 하면서 오라클 같은 RDBMS를 사용하고 SQL을 작성 하는것이 당연하다고 생각 할것이다.하지만 미래에는 이러한 생각이 달라질수도 있다. 위의 것들이 상업화및 대중화에 성공한다면 말이다. 하지만 최소한 5~10년간은 위의 일들을 걱정할필요는 없는듯 하다. 오라클의 경우 상업화에 성공하는데만 약 20년 가까이 걸렸기 때문이다. 아무리 구글이라 할지라도 2~3년 내에 DBMS 시장에서 주류에 편입 되진 못할것이다.

여러분들은 위의 2가지에 대해서  어떻게 생각하는가?
RDBMS 에게 위협적인가? 아니면 의례적인 Anti 운동으로 보는가?

글의 내용이 가슴에 와닫지 않는다는 독자가 있다. 필자의 잘못이다. 독자들을위하여 일부 내용을 댓글에 추가 하였으므로 반드시 확인하기 바란다. (2009.07.17)

대분분 튜닝을 해본 사람이면 USE_CONCAT 힌트를 잘 알고 있다고 생각할 것 이다. 하지만 문제는 얼마나 정확히 아는가가 중요하다. IN 이나 OR 조건이 있는 SQL에 USE_CONCAT 힌트를 사용하면 OR_Expansion(Union All 로 분리되는 쿼리변환) 이 발생한다는 것은 누구나 알것이다. 이것은 개발자들에 대한 일반적인 튜닝 가이드인것은 분명하다. 메뉴얼에는 분명히 이렇게 되어있다.

힌트 정확히 알기
이 힌트는 인자가 2개가 필요한데 튜닝을 전담하는 사람들까지도 이런 사실을 모르고 있으니 큰일이 아닐수 없다. 하지만 정확한 용법을 모르고 SQL을 튜닝을 하는 사람들을 비난할수는 없다. 그어떤 문서에도 USE_CONCAT의 용법이 자세히 나온 것이 없기 때문이다. 이럴경우 다양한 연구및 테스트를 진행 해보는수 밖에 없다. 아니면 SR 이라는 방법이 있기는 하다. 하지만 이러한 경우 SR 의 답변 성공률은 아주 낮다.

그럼 이제부터 용법을 하나씩 살펴보자.
환경 : 오라클 11.1.0.6
인덱스 상황 : employees(manager_id) , departments(department_id)

OR_Expansion 이 발생하지 않는 상황
아래의 SQL 은 departments 테이블에서 department_id 컬럼에 대해서 IN 조건이 있고 employees 테이블에서 manager_id 컬럼에 대해서 IN 조건이 있다. 따라서 USE_CONCAT 힌트를 사용한다면 UNION ALL 로 구분될수 있는 SQL 이 4개(departments 2개, employees 2개)가 나올것이다.

---------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    19 |   798 |     6  (17)|
|   1 |  MERGE JOIN                  |             |    19 |   798 |     6  (17)|
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |    27 |   432 |     2   (0)|
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK1 |    27 |       |     1   (0)|
|*  4 |   FILTER                     |             |       |       |            |
|*  5 |    SORT JOIN                 |             |   107 |  2782 |     4  (25)|
|   6 |     TABLE ACCESS FULL        | EMPLOYEE    |   107 |  2782 |     3   (0)|
---------------------------------------------------------------------------------
 
Plan 을 보면 OR_Expansion 이 발생하지 않았는데 옵티마이져는 Union All 로 분리하지 않는것이 가장 비용이 저렴했다는 것을 알수 있다.

그럼이제 USE_CONCAT 힌트를 사용해보자.

------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |    29 |  1218 |     9  (12)|
|   1 |  CONCATENATION                  |                   |       |       |            |
|   2 |   MERGE JOIN                    |                   |    12 |   504 |     5  (20)|
|   3 |    TABLE ACCESS BY INDEX ROWID  | DEPARTMENT        |    27 |   432 |     2   (0)|
|   4 |     INDEX FULL SCAN             | DEPT_ID_PK1       |    27 |       |     1   (0)|
|*  5 |    SORT JOIN                    |                   |    12 |   312 |     3  (34)|
|   6 |     INLIST ITERATOR             |                   |       |       |            |
|   7 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |    12 |   312 |     2   (0)|
|*  8 |       INDEX RANGE SCAN          | EMP_MANAGER_IX    |    12 |       |     1   (0)|
|   9 |   NESTED LOOPS                  |                   |       |       |            |
|  10 |    NESTED LOOPS                 |                   |    17 |   714 |     4   (0)|
|  11 |     INLIST ITERATOR             |                   |       |       |            |
|  12 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |     2 |    32 |     2   (0)|
|* 13 |       INDEX UNIQUE SCAN         | DEPT_ID_PK1       |     2 |       |     1   (0)|
|* 14 |     INDEX RANGE SCAN            | EMP_DEPARTMENT_IX |    10 |       |     0   (0)|
|* 15 |    TABLE ACCESS BY INDEX ROWID  | EMPLOYEE          |     9 |   234 |     1   (0)|
------------------------------------------------------------------------------------------
 
위의 Plan 을 보면 Id 기준으로 2번에 CONCATENATION 이 발생하였으므로 Union ALL 로 분리가 된것이다.
즉 옵티마이져는 SQL 을 아래와 같이 변형시킨것이다.

Union All 로 분리된 아래쪽의 SQL 에서 LNNVL을 사용한 이유는 윗쪽의 SQL이 이미 e.manager_id IN (:v_manager1, :v_manager2) 조건을 만족하는 데이터에 대하여 SELECT 를 하였으므로 아래쪽에서 또다시 SELECT 되는것을 막기위함이다.

Inlist 에 대해서는 Union All 로 분리되지 않았다.
위의 PLAN 을 자세히 보면 e.manager_id IN (:v_manager1, :v_manager2) 조건에 대해서는 Union All 로 분리되지 않았다. d.department_id IN (:v_dept1, :v_dept2)  조건 또한 마찬가지이다. 하지만 이것은 전통적인 OR_Expansion 변환이 아니다. USE_CONCAT 힌트 대신에 RULE 힌트를 사용할 경우 Plan은 아래와 같다.
 
------------------------------------------------------------
| Id  | Operation                      | Name              |
------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |
|   1 |  CONCATENATION                 |                   |
|   2 |   NESTED LOOPS                 |                   |
|   3 |    NESTED LOOPS                |                   |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |
|*  5 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |
|*  6 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|   7 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|   8 |   NESTED LOOPS                 |                   |
|   9 |    NESTED LOOPS                |                   |
|  10 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |
|* 11 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |
|* 12 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|  13 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|  14 |   NESTED LOOPS                 |                   |
|  15 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|* 16 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|* 17 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |
|* 18 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |
|  19 |   NESTED LOOPS                 |                   |
|  20 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|* 21 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|* 22 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |
|* 23 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |
------------------------------------------------------------
 
RULE 힌트를 적용한 Plan은 우리가 예상한대로 UNION ALL 로 구분될수 있는 SQL이 4개(departments 2개, employees 2개)가 생성 되었다. 결국 CBO는 Inlist Plan을 사용할수 있는 경우에는 OR_Expansion 변환을 수행하지 않음을 알수 있다. 하지만 이렇게 결말을 내기에는 너무 싱겁다.

PLAN 고정 시키기
CBO 상황에서 조건절에 Inlist 가 있을 경우 항상 OR_Expansion 변환을 수행하지 않게 PLAN을 고정 하려면 어떻게 하면 될까?
그냥 /*+ USE_CONCAT*/ 이렇게만 사용하면 되는 걸까?
위의 질문들을 해결하려면 DBMS_XPLAN.DISPLAY_CURSOR 함수를 사용해서 Outline Data 정보를 보면 된다.
 

     
위의 SQL 에 대한 PLAN 은 USE_CONCAT 을 사용하였으므로 이미 수행한 SQL의 PLAN 과 동일하므로 생략하고  Outline Data만 보기로 하자.      
 
 Outline Data
-------------
 
  /*+
      ...중간생략
      USE_CONCAT(@"MAIN" 8)
      ...중간생략
  */
 
숫자 8의 의미
오라클이 내부적으로 USE_CONCAT 힌트에 인자 2개를 사용한것을 알수 있다. 첫번째 인자는 쿼리블럭명이고 두번째 인자인 8의 의미는 Inlist 를 사용할수 있는 경우에는 Union All 로 분리하지 말것을 강제하는 힌트이다.

자존심이 허락지 않는다.
여기서 한단계 더 나아가 보자. 이번에는 거꾸로 Inlist 를 사용한 경우에도 무조건 Union All 로 분리되게 할수 있을까?  RULE 힌트를 사용하라고? 그것은 언제 어떤버젼에서 없어질지 알수없는 아주 위험한 힌트이다.
또한 CBO 상황에서 이러한 힌트를 사용한다는 것은 자존심이 허락지 않는다.
아래의 SQL 을 보자.

숫자 1의 의미
USE_CONCAT 힌트에 숫자 1을 사용하였다. 이것이 의미하는 바는 가능한 경우 모두 Union All 로 분리하라는 뜻이다. 이제 Plan 을 보자.

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   1 |  CONCATENATION                 |                   |      3 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS                 |                   |      0 |00:00:00.01 |       1 |
|   3 |    NESTED LOOPS                |                   |      0 |00:00:00.01 |       1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      0 |00:00:00.01 |       1 |
|*  5 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |      0 |00:00:00.01 |       1 |
|*  6 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      0 |00:00:00.01 |       0 |
|   7 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      0 |00:00:00.01 |       0 |
|   8 |   NESTED LOOPS                 |                   |      0 |00:00:00.01 |       1 |
|   9 |    NESTED LOOPS                |                   |      0 |00:00:00.01 |       1 |
|  10 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      0 |00:00:00.01 |       1 |
|* 11 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |      0 |00:00:00.01 |       1 |
|* 12 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      0 |00:00:00.01 |       0 |
|  13 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      0 |00:00:00.01 |       0 |
|  14 |   NESTED LOOPS                 |                   |      2 |00:00:00.01 |       6 |
|  15 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      1 |00:00:00.01 |       2 |
|* 16 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      1 |00:00:00.01 |       1 |
|* 17 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |      2 |00:00:00.01 |       4 |
|* 18 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      2 |00:00:00.01 |       2 |
|  19 |   NESTED LOOPS                 |                   |      1 |00:00:00.01 |       4 |
|  20 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      1 |00:00:00.01 |       2 |
|* 21 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      1 |00:00:00.01 |       1 |
|* 22 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |      1 |00:00:00.01 |       2 |
|* 23 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------

인자를 1로 바꾸자 IN 조건 혹은 OR 조건에 대하여 모두 Union All 로 분리 되었다. 이제 모든 궁금증이 해소 되었다.

결론 :
인자없이 힌트를 USE_CONCAT(@MAIN)으로 사용 한다면 모든경우의 수를 다 고려하여 가장 비용이 적게드는 Plan 을 선택 할것이다. 심지어 USE_CONCAT 힌트를 사용 하였지만 분리되지 않는 경우가 있는데 이것은 힌트를 무시한 것이 아니라 옵티마이져 입장에서 비용계산을 기준으로 가장 저렴한 PLAN 을 선택한것이다. 만약 힌트를 사용하였지만 Union ALL 로 분리가 안되며 이것 때문에 성능이 문제가 된다면 USE_CONCAT 힌트의 숫자 인자(1혹은 8)를 활용하여 적절하게 튜닝할수 있어야 한다.

힌트를 제대로 아는 것이 얼마나 중요한지 가슴에 와 닿아야 할것이다. 생각해보라 의사들이 수술용 칼이나 마취용 주사 같은 것을 규정에 맞게 아주 정밀하고 세밀하게 사용하지 않고 대충 사용한다면 큰일이 날수 도 있을 것이다. 힌트도 마찬가지로 생각해야 한다.  

Science of DataBase가 운영을 시작한지 1년이 지났습니다.
정확히 2008년 6월 부터 시작했으니 이제 만으로 1년이 되었습니다.
블로그를 만들 당시의 의도는 데이터 모델링, 데이터 품질, 데이터 모델 패턴, Performance Tuning 등을 모두 다루려고 하였으나 어쩌다 보니 Performance Tuning 위주로 나가게 되었습니다. 프로젝트에서 이슈된 사항들 위주로 하다보니 그렇게 된것인데 추후에 모델링 프로젝트시 이슈가 생기면 데이터 모델링,  데이터 모델 패턴등도 올리도록 하겠습니다.

Science of DataBase를 방문해주시는 분들 중에는 이방면에서 유명한 분들도 많이 계십니다.

Optimizing Oracle Optimizer 의 저자 조동욱님

Transaction Internals in Oracle 10gR2 의 저자 김시연님

오라클 성능 고도화 원리와 해법 I 의 저자 조시형님

비용기반의 오라클 원리 의 역자 김정삼님

이분들은 아주 실력이 뛰어나고 성품 또한 아주 훌륭한 분들 이어서 본받을점이 많이 있습니다.
이분들과 때때로 토론과 논쟁을 하는 과정에서 오류를 발견하고 수정하여 블로그가 많이 발전한것 같습니다.
사실 개인적인 바램은 이런 고수 분들이 회사 차원을 떠나 사심없이 모여서 한국의 OAKTABLE NETWORK  를 만드는 것입니다.  이것이 가능 할지는 모르겠습니다. 물론 어렵겠지요. 한분 한분의 허락을 받아야 되기 때문 입니다.

Science of DataBase는 개인 블로그 입니다.
저는 개인공간에 회사 차원의 메세지를 올리는것을 무척 싫어합니다. 앞으로도 상업적인 메세지는 올리지 않도록 노력 하겠습니다. 하지만 지금 집필중인 튜닝 책은 예외로 할 작정입니다. 회사차원이라기 보다는 개인차원의 노력이 많이 필요하기 때문에 책이 나오면 블로그에 소개를 올릴려고 합니다.

사실 요즘 블로그 관리가 힘들어 졌습니다.
맡은 프로젝트 + 집필작업 + 블로그 관리 + 개인적인 Study 의 4중고(?)에 시달리고 있습니다. 하지만 방문해주시는 분들을 위해서라도 꾸준히 주평균 1회 정도 글을 올릴려고 노력 하고 있습니다. Science of DataBase는 다은 블로그와는 달리 화려하지도 않고 방문자수도 많지 않습니다. 하지만 방문해주신 모든 분들이 DataBase를 사랑하시는 분들입니다. 방문 해주시는 한분 한분이 저에게는 소중하신분들 이십니다.

작년 6월달에 하루 평균 방문자수 3명으로 시작하여 요즈음은 평일 평균 120 명정도로 꾸준히 방문자수가 늘고 있습니다. 블로그는 독자들과의 소통이 중요한데 혹시 Science of DataBase 에게 바라는 점,  아쉬운점, 개선사항 등이 있는 분은 주저하지 마시고 코멘트를 남겨주시기 바랍니다.
독자와 함께 만들어 가는 Blog야 말로 진정한 의사소통의 장소가 되지 않을까요?

앞으로 더 좋은글로 보답 하겠습니다.
감사합니다.

오동규 배상

Query Transformer 의 냉대
  튜닝을 하는 많은 사람들이 PM 의 개념을 모른다는 결과가 나왔다. 정기모임 술자리에서 즉석으로 설문을 하였는데 결과는 충격적이었다. 참석자 10명은 DBA, 튜너, DB 컨설턴트 등등 DB 전문가들의 모임이라고 할수 있는데 단 한명도 아는사람이 없었다.

 필자가 충격적이라고 한 이유는 그모임의 많은 사람들이 왠만한 SQL 의 COST 를 계산할수 있는 내공을 가진 사람들이 었기 때문이다. 다행히 JPPD 나 VIEW MERGING, Unnesting 과 같이 튜닝 책에 소개 되는 간단한 변환들은 알고 있었다. 하지만 Query Transformer 가 푸배접을 받고 있다는 생각은 지울수가 없었다.
 
  Query Transformer 는 그 중요성이 옵티마이져의 50% 를 차지한다. 왜그럴까? 옵티마이져의 3대 Components 는 Query Transformer, Cost Estimator,  Plan Generator  이지만  이중에서 우리가 연구할수 있는 것은  Query Transformer 와  Cost Estimator 이며  Plan Generator 의 비밀은 오라클사의 DBMS 설계자/개발자만이 알수 있는 영역이기 때문이다. 또한 SQL 이 Transformer 에 의하여 변형되고 재작성 되기 때문에 성능에 직접적인 영향을 끼친다. 대부분의 경우 Query Transformation 이 발생하면 성능에 긍정적인 영향을 끼치지만 예외적으로 부정정인 영향을 줄수가 있으므로 가능한 Query Transformer에 대하여 상세히 알아야 한다.

어찌되었건 모임에서 PM 관련 내용을 블로그에 올리겠다는 약속을 하였다.

PM ( Predicate Move Around ) 이란?
 
인라인뷰가 여러 개 있고 각각의 where 절에 공통적인 조건들이 있다고 가정하자.
이럴경우에 모든 인라인뷰의 where 절에 똑 같은 조건들을 반복해서 사용해야 할까?
물론 그렇게 해야 하는 경우가 있지만 아래의 경우에는 그렇지 않음을 알수 있다..

SELECT /*+ qb_name (v_outer) */

       v1.*

  FROM (SELECT /*+ qb_name (IV1) no_merge */

               e1.*,  d1.location_id

          FROM employee e1, department d1

         WHERE e1.department_id = d1.department_id

            AND d1.department_id = 30

        ) v1,

       (SELECT   /*+ qb_name (IV2) no_merge */

                  d2.department_id, AVG (salary) avg_sal_dept

            FROM employee e2, department d2, loc l2

           WHERE e2.department_id = d2.department_id

             AND l2.location_id = d2.location_id

        GROUP BY d2.department_id

       ) v2

 WHERE v1.department_id = v2.department_id

    AND v1.salary > v2.avg_sal_dept  ;

 

 

----------------------------------------------------------+----------------------------------+

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

----------------------------------------------------------+----------------------------------+

| 0  | SELECT STATEMENT                |                  |       |       |     5 |          |

| 1  |  HASH JOIN                      |                  |     1 |   176 |     5 |  00:00:01|

| 2  |   VIEW                          |                  |     1 |    28 |     2 |  00:00:01|

| 3  |    HASH GROUP BY                |                  |     1 |    21 |     2 |  00:00:01|

| 4  |     NESTED LOOPS                |                  |       |       |       |          |

| 5  |      NESTED LOOPS               |                  |     6 |   126 |     2 |  00:00:01|

| 6  |       NESTED LOOPS              |                  |     1 |    14 |     1 |  00:00:01|

| 7  |        INDEX RANGE SCAN         | DEPT_IX_01       |     1 |    11 |     1 |  00:00:01|

| 8  |        INDEX UNIQUE SCAN        | LOC_ID_PK        |    23 |    69 |     0 |          |

| 9  |       INDEX RANGE SCAN          | EMP_DEPARTMENT_IX|     6 |       |     0 |          |

| 10 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     6 |    42 |     1 |  00:00:01|

| 11 |   VIEW                          |                  |     6 |   888 |     2 |  00:00:01|

| 12 |    NESTED LOOPS                 |                  |       |       |       |          |

| 13 |     NESTED LOOPS                |                  |     6 |   474 |     2 |  00:00:01|

| 14 |      INDEX RANGE SCAN           | DEPT_IX_01       |     1 |    11 |     1 |  00:00:01|

| 15 |      INDEX RANGE SCAN           | EMP_DEPARTMENT_IX|     6 |       |     0 |          |

| 16 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES        |     6 |   408 |     1 |  00:00:01|

----------------------------------------------------------+----------------------------------+

Predicate Information:

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

1 - access("V1"."DEPARTMENT_ID"="V2_DEPT"."DEPARTMENT_ID")

1 - filter("V1"."SALARY">"V2_DEPT"."AVG_SAL_DEPT")

7 - access("D2"."DEPARTMENT_ID"=30)

8 - access("L2"."LOCATION_ID"="D2"."LOCATION_ID")

9 - access("E2"."DEPARTMENT_ID"=30)

14 - access("D1"."DEPARTMENT_ID"=30)

15 - access("E1"."DEPARTMENT_ID"=30)

다른뷰에 조건이 추가되었다.

Predicate Information을 보면 두번째 뷰(v2 ) 에 "D2"."DEPARTMENT_ID"=30 조건과 "E2"."DEPARTMENT_ID"=30 조건이 파고들어 간 것을 알수 있다. 이 현상 때문에 D1 과 E1 에서 인덱스를 사용하였는데 결과는 성능면에서 아주 성공적이다. 그렇다면  오라클은 어떤 과정을 거쳐서 이작업을 진행하였을까?

10053 trace 정보를 보면 PM의 진행과정이 매우 상세하게 나와 있다.

PM: Considering predicate move-around in query block V_OUTER (#1)

**************************

Predicate Move-Around (PM)

**************************

PM:   Passed validity checks.

PM:   Pulled up predicate "V1"."DEPARTMENT_ID"=30

 from query block IV1 (#2) to query block V_OUTER (#1)

PM:   Pushed down predicate "D2"."DEPARTMENT_ID"=30

 from query block V_OUTER (#1) to query block IV2 (#3)

 

PM 은 순서가 중여하다.
10053 trace 내용을 분석하여 수행과정을 살펴보자.

1.       먼저 PM 이 수행될수 있는지 검사한다.

2.       V1 에서 WHERE 조건 d1.department_id = 30 를 바깥쪽 메인쿼리로 이동시킨다.

      이것을 Predicate pull up 이라고 한다.

3.       메인쿼리로 옮겨진 where 조건을 v2 에 복사한다.

이것을 Predicate Push down 이라고 한다.

4.  최종 결과에서 중복된 조건절이 존재하면 삭제한다..

 

V1, V2 가 메인쿼리(V_OUTER)에서 department_id 기준으로 조인되고 있기 때문에 조건절을 V_OUTER 로 빼낸 다음에 V2 에 조건절을 밀어 넣고 있다. 이것은 JPPD 기능과 유사한 면이 있지만 Predicate pull up 이 반드시 먼저 일어나야 한다는 점과 Hash 조인등에서도 PM 이 발생한다는 점에서 엄연히 다르다. 오라클은 여러분이 모르는 사이에 조건절을 이리 저리 옮겨 다니면서 SQL 의 최적화를 시도하고 있다.

 

PM 아무때나 발생하나?
그럼 PM 이 어떤 조건일 경우 발생하는지 짐작할수 있겠는가?

다음과 같은 조건일 경우 PM 이 발생된다.

1.       뷰(혹은 인라인뷰) 가 2개 혹은 그이상이 되어야 한다.(예제에서 V1, V2 가 있음)

2.       특정 인라인뷰내의 조건이 존재하고 뷰의 바깥쪽에서 조건을 사용한 컬럼으로 조인이 발생할 경우에 발생된다. 예제에서 는 d1.department_id = 30 으로  V1 내부에 조건이 존재하고 V_OUTER 에서 department_id 로 V2 와 조인을 하고 있다.

3.       VIEW MERGE 가 발생하지 않아야 한다. Merging 이 발생되면 PM 대신에 Transitive Predicates 가 발생된다. 

       4.   파라미터 _PRED_MOVE_AROUND 가 true 로 지정이 되어 있어야 한다.

결론 :
  제목에서 보듯이 PM 의 개념은 매우 간단하다. Where 조건을 다른뷰에 이동시키는 기능이며 Heuristic Transformatin 의 대표적인 예제이다.
  오늘 올린 글은 현재 집필중인 책의 내용인데 일부를 먼저 공개하기로 결정 하였다.

편집후기 :  JPPD 와 PM 이 헷갈린다는 보고가 들어왔다. 둘다 WHERE 조건이 PUSH 되는것이지만 가장 결정적이 차이점은 JPPD 는 Predicate pull up 기능이 없다는 것이다. 아주 명확하게 구분할수 있다.

 

오라클, 선마이크로시스템즈 인수 
  "세계 2위의 소프트웨어 업체인 오라클은 서버와 저장장치 등 하드웨어 사업부문에 진출, IBM과 휴렛패커드(HP)등과 경쟁하게 됐다. 이와 함께 오라클은 선마이크로가 보유하고 있는 '자바 프로그래밍' 언어 기술과 대용량 전용 컴퓨터 운영 체계(OS)인 '솔라리스'에 대한 소유권도 확보하게 된다. 선마이크로 측에 따르면 자바 언어는 전세계적으로 약 8억대의 PC에서 운영되고 있으며 600만의 개발자들이 있는 것으로 알려졌다."
<기사 출처 :아시아 경제>

더아상 DBMS 업체로 보기 힘들어
  OS, 미들웨어, 서버, 저장장치, 프로그래밍 언어...  이로서 IBM 이나 HP 와 3강체제가 확립되었고 MS 와는 프로그래밍 언어로서 대결하게 되었다. 프로그래밍 언어로서도 현재는 자바가 닷넷보다 우월한 위치에 있다.  이정도 되면 오라클이 더이상 DBMS 업체라고 부르기 힘들지 않을까?  

중요한것은 시너지 효과
  이제 오라클사 단독으로 프로젝트를 진행할수 있게 되었다. 미들웨어 업체나 OS 업체 , 저장장치 등등 의 타업체에게 도움을 받지 않아도 된다는 이야기다. 하지만 정작 중요한것은 ? 시너지 효과이다. 오라클이 시너지 효과를 발휘할지 두고 볼일이다.

육식 공룡이 아닌 상생의 공룡으로 거듭나길
  몸집은 거대해졌지만 주위의 모든것을 먹어치워버리는 육식공룡이 아닌 영화 아이스 에이지에 나오는 그런 친근한, 협력업체를 껴안을수 있는 다정다감한 공룡으로 다시태어나길 기대한다.


 <그림-영화 아이스 에이지의 포스터에서 발췌>

PRO * C 나 PL/SQL 에서 많은양의 데이터를 Cursor For Loop로  선언한후를 건건이 Update/Delete 등을 처리하는 경우가 많이 있다.
집합처리가 유리하겠지만 그것이 불가능한 경우나 집합처리가 가능하지만 SQL 이 너무 길어지는 경우는
유지보수의 관점에서 Cursor For Loop 를 사용해야 한다.
하지만 많은 개발자들이 일반적으로 Cursor For Loop 내에서 아직도 PK 인덱스나 Unique 인덱스를 사용하여
DML 문을 실행하고 있다.  
오늘은 Cursor for Loop 처리시 개발자들이 일반적으로 코딩하는 방식의 비효율성을 설명하고 Solution 을 제시한다.  
아래는 테스트용 테이블및 인덱스 생성 스크립트 이다.

1.아래는 Curso For Loop  를 사용하여  건건이 Update 하는 가장 흔하게 볼수있는 PL/SQL 패턴이다.

위 PL/SQL 의 ELAPSED TIME 은 11.80 초 이며 이수치는 10번을 실행하여 평균값을 취한것이다.

2.SOLUTION
  

위 PL/SQL 의 ELAPSED TIME 은 10.70 초 이며 이수치는 10번을 실행하여 평균값을 취한것이다.
물론 테스트를 정확히 하기위하여 각각의 테스트시에 buffer cache 를 flush 하였다.

PK 인덱스를 이용한 UPDATE 보다 Current of 를 사용한 Update 가 10% 정도 빠른걸 알수 있다.
그렇다면 10%의 차이는 어디서 오는가?

비밀은 v$sqlarea 에서 수행된 SQL 을 보면 알수 있다.

결과 :
---------------------------------------------------------------------------
UPDATE /*+ current_of_test */ T1 SET SCORE = SCORE + 0 WHERE ROWID = :B1

위의 결과에서 처럼 WHERE CURRENT OF 를 사용하면 ROWID 를 이용하여 Accsess 하는것을 알수 있다.
Rowid Access 는 오라클에서 가장 빠른 Access 방식이다.
주의할 점은 WHERE CURRENT OF 를 사용하려면  Main Cursor 가 반드시 SELECT FOR UPDATE 문이어야
한다는 것이다.
하지만 FOR UPDATE 가 없는 커서에서도 아래처럼 사용하면 같은 효과를 누릴수 있다.

결론:
다시한번 기본(SQL 및 PL/SQL)이 얼마나 중요한지 알수 있는 테스트 이다.
Where Current of 를 모르는 사람은 많이 없을것이다.
하지만 Where Current of 절을 사용하면 Index Access에 의한 부하를 없앨수 있다는 사실을 아는사람은
의외로 많지 않다.
여러분의 프로그램이 이런환경에서 PK 인덱스를 사용하도록 코딩이 되어 있다면 지금 당장 달려가서
프로그램을 수정하라고 말하고 싶다.

예전에 필자가 Relational Database Index Design and the Optimizers 라는 인덱스 관련된 책을 추천한바 있다.
오늘은 중급개발자로 부터 오라클을 잘사용할수 있도록 3종류의 책을 추천 해달라는 요청을 받았다.
"오라클을 7~8년 정도 사용하였지만 3~4년 전부터 SQL 실력이 늘지않는다." 가 추천을 요청한 사유이다.
3종류의 책은 다음과 같다.
1. SQL 을 자유자재로 구사할수 있는 능력을 기르는 책
   참고로 위의 책은 튜닝책을 이야기 하는것이 아니다.
   원하는 답을 내기위한 효율적인 SQL 활용법을 가르쳐주는 책을 이야기 하는것이다.  
2. SQL 튜닝책
3. PL/SQL 튜닝책

물론 단한권으로 1,2,3 번을 어느정도 기본적인 사항들을 커버하는 책이 있다.
바로 Tomas Kyte 의 Effective Oracle by Design 이다.
하지만 위의 책을 정복 한다고 해도 1,2,3 번중 어느것도 Master 가 될수는 없다.
왜냐하면 한권의 책으로는 깊이의 한계와 페이지수의 한계를 넘지못하는 것이다.
마찬가지로 국내서적중에서 깊이있는 책이 존재하지만 1,2,3번을 모두다 다루려고 하다보니 대부분 깊이
들어가지 못하고 소개정도에 그치고 있다.  

해당 개발자는 다행스럽게도 영문서적도 상관없다고 하였다.
또한 각권을 독파하면 바로 효과를 볼수 있는 책을 원했다.
다시 말하면 투자시간 대비 효율이 좋은 책을 원하는 것이다.
어떤책이 투자시간 대비 효율이 좋은 책인지 이해를 돕기위해 아래의 그림을 보자.













위의 곡선은 필자가 생각하는 실력 상승곡선이다.
검은 화살표 3개를 보면 조금만 노력을 하면 실력이 급격히 향상되는것을 알수있다.
이것은 초급 중급 단계에서는 조금만 노력해도 실력향상의 폭이 크다는 것을 나타낸다.
하지만 상대적으로 빨강화살표 부분부터는 노력을 많이해도 실력이 별로 늘지 않음을 알수 있다.
다시말하면 고급(고수의 세계)단계에서는 책 1~2 권 읽는다고 해서 실력이 그다지 늘지않는다.    
누구도 실력 상승곡선의 예외일수 없다.
오늘 추천하는 책 3권은 오라클 개발및 튜닝 관점에서 빨강화살표 부분까지 가장 빨리 도달할수있게
도와주는(효율이 좋은) 책이라 할수 있다.  
개발자들이 읽어야할 책과 DBA, 컨설턴트들이 보아야 할 책은 엄연히 다르고 생각한다.
개발자 입장에서 일단 후보가 될수있는 책을 생각하니 7~8권 정도 생각이 났지만 몇시간의 장고 끝에
아래의 3권을 추천 하였다.



각각의 장점을 설명하면 아래와 같다.

1.Mastering Oracle SQL 2nd Edition (저자:Sanjay Mishra & Alan Beauieu)
  말이 필요없는 SQL 활용의 대가인 Alan Beauieu 의 작품이다.
  중급개발자라면 1,2장은 넘기면서 볼수 있지만 3장부터는 신경을 써야한다.
  모든 스크립트는 온라인으로 Down Load가 가능하다.
  자세한 내용은 필자의 서평을 참조하기바란다.

2.SQL Tuning (저자:Dan Tow)
  Oracle 뿐아니라 DB2, SQL Server 의 SQL 튜닝을 모조리 커버하는 놀라운 책 이다.
  여러분이 만약 업무를 모르는 상태에서 SQL 튜닝 요청을 받았다고 가정하고 해당 SQL 의 FROM 절에
  테이블이 8~10 개정도 있다고 할때 조인순서및 조인방법을 쉽고 빠르게 확정할수 있겠는가?
  국내외를 막론하고 조인순서및 조인방법에 대하여 이책만큼 명확한 방법론을 제시하는 책은 없었다.
  Dan Tow 의 Diagram-Based 방법론을 익힌다면 SQL 튜닝에 대해서는 더이상 걱정이 없을것이다.
  Method-R 방법론을 집대성한 Cary Milsap 이 적극 추천한 책이기도 하다.

3.Mastering Oracle PL/SQL (저자:Cornor McDonald)
  PL/SQL 문법이나 활용서적이 아니라 튜닝 책이다.
  마지막 순간까지 이책과 Steven Feuerstein 의 Oracle PL/SQL Best Practices를 두고 고민 하였다.
  하지만  Steven Feuerstein 의 책은 100% PL/SQL 튜닝 책이 아니고 PL/SQL 의 효율적인 활용에 대해서
   50% 정도 언급을 하고 있기 때문에 제외 시켰다.
  Cornor McDonald의 책은 100% 튜닝관점에서 집필된 책이다.
  모든 소스 코드가 온라인에 공개되어 있으므로 튜닝과정을 따라가기가 매우쉽다.  
  예를 하나만 들면
 "같은 답을 내는 2가지의 PL/SQL 이 있고 구현방법이 다를때 redo 양이 적게 발생하는 것이 무엇인지?"
  등의 상당히 재미있는 주제가 많다.  
  기본(1,2번 책)을 익히고 이책을 정복한다면 이책의 제목처럼 PL/SQL 튜닝에 대해서는 Master 가 될것이다.

모든 중급개발자및 DBA들에게 위의 3가지 책을 자신있게 추천한다.
만약 컨설턴트들이 위의 책들을 보지 못했다면 실수라고 말하고 싶다.
이런 종류의 책이 국내에는 없다는점이 아쉬울 뿐이다.
최근에 환율이 장난이 아니고 난위도가 어느정도 있는책이므로 중간에 포기할 생각이라면 원서를
구입하지말고 E-Book 등을 활용하기 바란다.
참고로 3번책을 제외하면 E-Book 을 구할수 있다.(방법은 각자 알아서...)

편집후기 :
혹시 여러분들이 1,2,3 번에 대해여 다른책을 추천한다면?
물론 효율(실력 상승곡선)을 염두에 두어야 한다.
여러분들의 의견을 듣고 싶다.  

필자는 Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) 이라는 이전글에서 Right Deep Tree,
Left Deep Tree Plan 의 개념 및 튜닝에 관한 글을 작성하였는데 Bushy Tree Plan 에 관한 질문을 받았다.
질문은 "Bushy Tree Plan 은 무엇이며 어떨때 사용하는가?" 였다.
간단히 설명하면 T1 과 T2 를 조인하고 T3 와 T4 를 조인하여 2개의 결과 집합을 조인하는 방식이다.
실제로 Bushy Tree Plan SQL 을 실행시켜보자

테이블 생성 스크립트는 Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) 시 사용했던 스크립트와 동일하다.
먼저 지난번에 언급 되었던  Right Deep Tree PLAN 을 보자.

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.13 | 1212K (0)|
|*  2 |   TABLE ACCESS FULL  | T1   |      1 |     90 |00:00:00.04 |          |
|*  3 |   HASH JOIN          |      |      1 |  10000 |00:00:00.18 |   11M (0)|
|   4 |    TABLE ACCESS FULL | T2   |      1 |  10000 |00:00:00.03 |          |
|*  5 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   6 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

아래는 위의 SQL 을 Bushy Tree Plan 으로 유도하는 예제이다.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST'));

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.09 | 1190K (0)|
|   2 |   VIEW               |      |      1 |     90 |00:00:00.06 |          |
|*  3 |    HASH JOIN         |      |      1 |     90 |00:00:00.06 | 1191K (0)|
|*  4 |     TABLE ACCESS FULL| T1   |      1 |     90 |00:00:00.04 |          |
|   5 |     TABLE ACCESS FULL| T2   |      1 |  10000 |00:00:00.03 |          |
|   6 |   VIEW               |      |      1 |  10000 |00:00:00.10 |          |
|*  7 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   8 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   9 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

Right Deep Tree PLAN 과 비교해보면 메모리 사용량이 거의 절반수준으로 떨어졌다.

참고로 DBMS_XPLAN.DISPLAY_CURSOR 사용시 ADVANCED 를 명시하면 튜닝차원에서 필요한 모든정보가
빠짐없이 출력된다.
예를 들면 Outline Data, Query Block Name, Predicate Information, Column Projection Information
등이 모두 출력이 되나 여기서는 지면관게상 생략 하였다.

결론:
Bushy Tree Plan 의 원래의 활용도는 인라인뷰 2개를 만들고 각인라인뷰 내에서 조인이 발생하며
독자적인 똑똑한 FILTER 조건이 있을때 각각의 인라인뷰를 먼저 실행시키고 조인이 완료된 인라인뷰 끼리
다시 조인하는 것이 Bushy Tree Plan의 최적 활용 방안이다.
하지만 원래의 목적과는 상관없이 Driving 테이블에 훌륭한 Filter 조건이 있는경우 Nested Loop Join 이나
Hash Join 시에 Left Deep Tree Plan 으로 유도가 안될때(조인조건의 문제가 제일 많음)
Bushy Tree Plan 으로 유도하여야 한다.
오늘의 예제는 후자를 나타낸것이다.

이글을 쓰기전에 필자는 QUEST 사와 아무런 관계가 없음을 먼저 밝혀둔다.
이글의 목적은 개발자, DBA, 튜닝 컨설턴트 들이 이런 리포트들을 뽑기위하여 OEM(엔터프라이즈 매니져) 를
사용할수 없는경우 발을 동동 구르고 있다는 사실이다.
하지만 사실은 우리주위에 있는 가장흔한 쿼리툴(TOAD) 로도 쉽게 출력이 가능하다.
아래처럼 토드에서 Database --> Monitor --> ADDM/AWR 을 클릭하면
1.AWR(Automatic Workload Repository),
2.ADDM(AUTOMATIC DATABASE DIAGNOSTIC MONITOR),
3.ASH(Active Session History) 를 관리및 출력할수 있는 화면이 나온다.
참고로 AWR 리포트는 9i 이하버젼에서 사용한 Statspack 리포트의 진화된 버젼이라고 할수 있다.
 

아래는 AWR 의 관리화면이다.
Snapshot Interval 은 10분이며 최장 보관기간은 30 일 임을 알수 있다.
이화면에서 관리및 변경이 가능하다.

아래는 ASH 를 출력할수 있는 탭을 보여준다.
그림 FROM ~ TO 로 START 와 END TIME 을 지정하고 RAC 인경우 인스턴스를 지정하고 상단에 있는 연두색 버트늘 누르면 ASH 보고서가 쉽계 출력된다.

아래는 AWR 보고서 화면이다.
SNAPSHOT 구간을 선택하고 인스턴스를 선택하고 상단의 연두색 버튼을 누르면 쉽게 보고서가 출력된다.


아래는 ADDM 리포트이다.
AWR 리포트와 출력하는 방법이 같다.


이상으로서 거칠것 없이 AWR 과 ASH, ADDM 의 보고서를 출력 해보았다.
복잡한 절차없이 몇번의 클릭만으로 3종류의 보고서를 출력하였다.
이 3개의 보고서를 잘활용하면 특정구간대의 DBMS 차원의 성능진단및 Wait Event, TOP SQL,
TOP Object 등을 도출해 낼수 있다.
특히 ADDM 보고서에는 문제가 무엇이고 어떻게 조치해야 되는지 까지 조언을 해준다.
또한 ASH 나 AWR 의 보고서에는 TOP 이벤트를 발생시키는 SQL 을 찾아줄뿐 아니라
연관된 TOP OBJECT 까지 찾아준다.
예를 들면 특정 SQL 이 Index Contention 을 발생시킬경우에 관련 인덱스가 무엇인지 쉽게 찾을수 있다.
오늘 소개한것은 토드의 일부기능에 불과하다.
우리주위에 흔히 볼수 있는 쿼리툴도 찾아보면 멋진 기능들이 많이 숨어있다.
단지 어디에 뭐가 있는지 몰라서 사용하지 않을뿐.....