-대표적인 페이징 처리방법

-누적집계가 필요할 때 페이징(부분범위) 처리방법

-Pagination의 단점을 이용하는 방법

 

주의사항

이 글에서 사용되는 분석함수는 현재 row 까지의 누적집계(Cumulative total) 이다. 이와 반대로 전체집계(Grand Total)나 그룹집계(Sub total)는 부분범위처리를 할 수 없다. 왜냐하면 데이터를 모두 읽어야만 결과를 낼 수 있기 때문이다. 하지만 누적집계는 데이터가 sort 되어 있고, 이미 출력된 컬럼들의 값을 알 수 있다면 부분범위처리가 가능하다. 우리는 이점을 이용할 것이다.

 

상황

Time Out이 발생하여 개발자가 종이 한 장을 들고 급하게 뛰어왔다.

 

개발자: 페이징 처리를 했고, 최적의 인덱스도 존재하고, 그 인덱스를 잘 타는데도 Time Out이 발생합니다.

필자  : 그럴 리가요?

개발자: SQL입니다. 한번 봐주세요.

필자  : ….분석함수 때문에 전체 건을 읽고, 전체 건을 sort하는 군요. 페이징 처리방법을 약간 변경하면 됩니다.

개발자: 이 방법은 SQL 작성 가이드에 나온 방법입니다. 이 방법을 쓰지 않으면 사수에게 혼납니다.

필자  : 이 방법을 사용하지 말라는 이야기가 아니라, 분석함수의 위치만 옮기라는 이야기 입니다.

개발자: 그렇군요. 감사합니다.

 

이렇게 해서 장애상황은 없어졌다. 이후에 SQL 작성가이드에 페이징 처리시 누적집계가 있는 경우의 처리방법을 추가하였다고 한다.

 

Pagination SQL

개발자가 사용한 페이징 처리용 SQL은 아래와 같았다.

 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (         ) a --> 여기에 order by 가 포함된 SQL 을 넣는다.

         WHERE ROWNUM <= :v_max_row )

 WHERE rnum >= :v_min_row ;

        

 

인라인뷰 a SQL을 넣기만 하면 페이징 처리가 된다. 물론 조회시 정렬이 필요하다면 order by가 포함된 SQL을 넣어야 한다. 이 방법은 토마스 카이트가 제시하였다. 이 기법은 약간의 비효율이 있다. 첫 페이지에서는 최적이지만, 뒤쪽 페이지를 읽을 때는 이전 페이지의 데이터를 모두 scan 해야 한다.(화면에 출력되지는 않는다.) 하지만 경험적으로 볼 때 비효율이 크지 않다. 왜냐하면 우리가 구글이나 네이버로 검색을 할 때 통상적으로 앞쪽의 몇 페이지만 보고 검색을 끝내기 때문이다. 만약 네이버에서 트위터라는 단어로 검색을 했더니 5729 페이지가 나왔다고 치면, 대부분 첫 페이지 혹은 두 번째, 세 번째 페이지에서 찾고자 하는 정보를 볼 수 있을 것이다. 5729 페이지를 모두 넘겨본 사람은 거의 없을 것이다. (만약 있다면 존경스럽다.) 따라서 위의 방법을 사용한다고 해도 성능저하는 거의 발생하지 않는다.

 

그런데 인라인뷰 a에 포함될 SQL에 누적집계용 분석함수가 포함될 때는 위의 방법에 약간의 변형을 가해야 한다. 그렇지 않고 위의 방법을 그대로 사용하면 심각한 성능저하가 발생할 수 있다. 즉 분석함수가 존재한다면 위의 방법은 무늬만 페이징 처리가 되며 실제로는 전체범위를 처리하여 Time Out이 발생 할 수 있다. 이 글에서는 누적집계용 분석함수가 있는 경우에 기존방법의 문제점을 제시하고 효과인 페이징 처리방법에 대해 논의 한다.

 

테스트를 위해 테이블과 인덱스를 생성한다.

 

CREATE TABLE sales_t AS SELECT * FROM sales;

 

CREATE INDEX ix_prod ON sales_t (prod_id);

 

먼저 인라인뷰 a 에 들어갈 SQL을 보자.

 

SQL1

 

SELECT   /*+ INDEX(S IX_PROD) */

         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold,

         SUM (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

    FROM sales_t s

   WHERE s.prod_id = :v_prod_id  --> 30 대입

ORDER BY s.cust_id, s.channel_id, s.time_id ;

 

 

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

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

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

|   0 | SELECT STATEMENT             |         |      1 |  29282 |00:00:00.12 |     424 |          |

|   1 |  WINDOW SORT                 |         |      1 |  29282 |00:00:00.12 |     424 | 1621K (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID| SALES_T |      1 |  29282 |00:00:00.10 |     424 |          |

|*  3 |    INDEX RANGE SCAN          | IX_PROD |      1 |  29282 |00:00:00.03 |      60 |          |

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

 

고객별로 channel_idtime_id sort하여 누적합계를 구하는 SQL이다. 위의 SQL은 페이징 처리(부분범위 처리)가 되지 않은 것이다. 따라서 29282건이 결과로 출력되었고 424 블럭을 Scan 하였다. WINDOW SORT라는 operation이 존재하는 이유는 분석함수 때문이다. SQL order by가 있지만 별도의 SORT ORDER BY operation이 존재하지 않는다. 그 이유는 WINDOW SORT order by가 할 일을 대신해 주고 있기 때문이다. WINDOW SORT operation 때문에 PGA 1621K만큼 사용하였다.

 

이제 페이징 처리를 해보자. 먼저 차이를 보여주기 위하여 분석함수를 제거하고 페이징 처리를 하였다.

 

SQL2

 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT   /*+ INDEX(S IX_PROD) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) a

         WHERE ROWNUM <= :v_max_row --> 20 대입

       )

 WHERE rnum >= :v_min_row ;         --> 1 대입

 

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

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

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

|   0 | SELECT STATEMENT                |         |      1 |     20 |00:00:00.02 |     424 |          |

|*  1 |  VIEW                           |         |      1 |     20 |00:00:00.02 |     424 |          |

|*  2 |   COUNT STOPKEY                 |         |      1 |     20 |00:00:00.02 |     424 |          |

|   3 |    VIEW                         |         |      1 |     20 |00:00:00.02 |     424 |          |

|*  4 |     SORT ORDER BY STOPKEY       |         |      1 |     20 |00:00:00.02 |     424 | 2048  (0)|

|   5 |      TABLE ACCESS BY INDEX ROWID| SALES_T |      1 |  29282 |00:00:00.13 |     424 |          |

|*  6 |       INDEX RANGE SCAN          | IX_PROD |      1 |  29282 |00:00:00.03 |      60 |          |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("RNUM">=:V_MIN_ROW)

   2 - filter(ROWNUM<=:V_MAX_ROW)

   4 - filter(ROWNUM<=:V_MAX_ROW)

   6 - access("S"."PROD_ID"=:V_PROD_ID)

 

페이징 처리를 하였음에도 똑같이 전체 블록인 424 블럭을 scan 하였다. 그 이유는 전체 건을 읽어서 정렬작업을 해야 하기 때문이다. 반면에 PGA의 사용은 2048에 불과하다. 왜냐하면 부분범위를 처리할 때는 전체 건을 sort하는 것이 아니라, 20 row 짜리 배열을 만들고 그 배열만 관리하면 되기 때문이다. 자세한 내용은 관련 을 참조하라.

 

이제 분석함수를 추가해 보자.

 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT   /*+ INDEX(S IX_PROD) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold,

                         SUM(amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) a

         WHERE ROWNUM <= :v_max_row --> 20 대입

       )

 WHERE rnum >= :v_min_row ;         --> 1 대입

 

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

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

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

|   0 | SELECT STATEMENT                |         |      1 |     20 |00:00:00.03 |     424 |          |

|*  1 |  VIEW                           |         |      1 |     20 |00:00:00.03 |     424 |          |

|*  2 |   COUNT STOPKEY                 |         |      1 |     20 |00:00:00.03 |     424 |          |

|   3 |    VIEW                         |         |      1 |     20 |00:00:00.03 |     424 |          |

|   4 |     WINDOW SORT                 |         |      1 |     20 |00:00:00.03 |     424 | 1621K (0)|

|   5 |      TABLE ACCESS BY INDEX ROWID| SALES_T |      1 |  29282 |00:00:00.15 |     424 |          |

|*  6 |       INDEX RANGE SCAN          | IX_PROD |      1 |  29282 |00:00:00.03 |      60 |          |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("RNUM">=:V_MIN_ROW)

   2 - filter(ROWNUM<=:V_MAX_ROW)

   6 - access("S"."PROD_ID"=:V_PROD_ID)

 

성능저하의 원인은 분석함수

분석함수를 사용하자 PGA사용량이 급격히 늘었다. 분석함수가 없는 경우와 비교해보면 무려 791배나 차이가 난다. SQL1 PGA 사용량과 위 실행계획의 PGA 사용량을 비교해 보면 분석함수의 PGA 사용량은 페이징 처리를 하지 않았을 때와 똑같다. 즉 페이징 처리를 하였지만 분석함수의 영향으로 전체범위 처리가 되어버린 것이다. 바로 이점이 페이징 처리를 하였음에도 Time-Out이 발생하는 이유였다. 어떻게 하면 비효율을 제거할 수 있을까? 아래의 SQL이 정답이다.

 

SELECT *

  FROM (SELECT s.*, ROWNUM rnum,

               SUM (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

          FROM (SELECT   /*+ INDEX(S IX_PROD) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) s

         WHERE ROWNUM <= :v_max_row --> 20 대입

       )

 WHERE rnum >= :v_min_row ;         --> 1 대입

 

 

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

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

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

|   0 | SELECT STATEMENT                 |         |      1 |     20 |00:00:00.02 |     424 |          |

|*  1 |  VIEW                            |         |      1 |     20 |00:00:00.02 |     424 |          |

|   2 |   WINDOW BUFFER                  |         |      1 |     20 |00:00:00.02 |     424 | 2048  (0)|

|*  3 |    COUNT STOPKEY                 |         |      1 |     20 |00:00:00.02 |     424 |          |

|   4 |     VIEW                         |         |      1 |     20 |00:00:00.02 |     424 |          |

|*  5 |      SORT ORDER BY STOPKEY       |         |      1 |     20 |00:00:00.02 |     424 | 2048  (0)|

|   6 |       TABLE ACCESS BY INDEX ROWID| SALES_T |      1 |  29282 |00:00:00.14 |     424 |          |

|*  7 |        INDEX RANGE SCAN          | IX_PROD |      1 |  29282 |00:00:00.04 |      60 |          |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("RNUM">=:V_MIN_ROW)

   3 - filter(ROWNUM<=:V_MAX_ROW)

   5 - filter(ROWNUM<=:V_MAX_ROW)

   7 - access("S"."PROD_ID"=:V_PROD_ID)

 

분석함수는 인라인뷰 밖으로 빼라

분석함수를 뷰의 외부로 위치를 바꾸자 PGA를 거의 사용하지 않는다. 분석함수가 추가되었음에도 PGA 사용량이 분석함수를 사용하지 않은 경우(SQL2)와 비슷하다. 그 이유는 20건에 대해서만 분석함수가 실행되었기 때문이다. ID 2번에서 사용한 PGA SORT를 위한 것이 아니다. 왜냐하면 이미 인라인뷰 내에서 SORT가 되었으므로 같은 작업을 반복할 필요가 없기 때문이다. 이런 경우는 order by절의 컬럼과 분석함수 OVER절의 컬럼이 일치한 경우만 나타난다. 이에 따라 OperationWINDOW SORT가 아니라 WINDOW BUFFER로 바뀌었다. 20 row로 구성된 배열만 관리하면 된다. Order by 작업 또한 전체 건을 sort하지 않고 페이징 처리된 20건에 대해서 배열만 관리한 것이다.

 

절반의 성공

위의 실행계획이 best 인가 하면 그렇지는 않다. 왜냐하면 페이징 처리가 되지 않은 SQL1의 실행계획을 보면 29282건을 모두 읽었고, 페이징 처리가 된 위의 SQL 또한 마찬가지 이다. 다시 말해 위의 SQL은 결과적으로 20건만 출력되므로 비효율적인 전체범위를 처리한 것이다. PGA 사용(Sort)의 관점에서는 부분범위 처리가 되었지만 Block I/O의 관점에서는 전체범위를 처리하고 말았다.

 

이제 Block I/O 문제를 해결하기 위해 인덱스를 생성해보자.


CREATE UNIQUE INDEX PK_SALES_T ON SALES_T(PROD_ID, CUST_ID, CHANNEL_ID, TIME_ID);


이제 위의 인덱스를 이용하여 페이징 처리되지 않은 SQL을 실행해 보자.

 

SELECT /*+ INDEX(S PK_SALES_T) */

       s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold,

       SUM (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

  FROM sales_t s

 WHERE s.prod_id = :v_prod_id  --> 30 대입

 ORDER BY s.cust_id, s.channel_id, s.time_id ;

 

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

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

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

|   0 | SELECT STATEMENT             |            |      1 |  29282 |00:00:00.11 |   28337 |          |

|   1 |  WINDOW BUFFER               |            |      1 |  29282 |00:00:00.11 |   28337 | 1495K (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.12 |   28337 |          |

|*  3 |    INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.03 |     118 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("S"."PROD_ID"=:V_PROD_ID)

 

28337 블록을 scan 하였고 PGA 1495K나 사용하였다. WINDOW BUFFER operation을 본다면 전체 건을 sort한 것은 아니다. 하지만 배열(WINDOW)의 크기가 20건이 아니라 29282건이나 되므로 전체 건을 sort한 경우와 PGA 사용량이 비슷해져 버렸다. 전체 건을 sort SQL1 PGA 사용량이 1621K 이므로 비슷하다고 할 수 있다.

 

페이징 처리를 해도...

이런 현상은 페이징 처리를 해도 분석함수를 인라인뷰 외부로 이동하지 않으면 마찬가지로 발생한다. 아래의 SQL을 보자.

 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT   /*+ INDEX(S PK_SALES_T) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold,

                         SUM (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) a

         WHERE ROWNUM <= :v_max_row --> 20 대입

       )

 WHERE rnum >= :v_min_row ;         --> 1 대입

 

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

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

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

|   0 | SELECT STATEMENT                |            |      1 |     20 |00:00:00.04 |   28337 |          |

|*  1 |  VIEW                           |            |      1 |     20 |00:00:00.04 |   28337 |          |

|*  2 |   COUNT STOPKEY                 |            |      1 |     20 |00:00:00.04 |   28337 |          |

|   3 |    VIEW                         |            |      1 |     20 |00:00:00.04 |   28337 |          |

|   4 |     WINDOW BUFFER               |            |      1 |     20 |00:00:00.04 |   28337 | 1495K (0)|

|   5 |      TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.13 |   28337 |          |

|*  6 |       INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.03 |     118 |          |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("RNUM">=:V_MIN_ROW)

   2 - filter(ROWNUM<=:V_MAX_ROW)

   6 - access("S"."PROD_ID"=:V_PROD_ID)

 

부분범위 처리가 아니라 전체범위 처리이다

많은 이들이 착각하는 것이 위의 SQL이다. 다시 말해 “order by와 분석함수의 over절에 최적화된 인덱스를 생성하면 부분처리가 되겠지라고 생각한다. 하지만 사실은 이와 다르다. 인덱스의 영향으로 Plan상에 sort order by window sort operation이 없으므로 부분범위 처리가 된 것으로 판단하면 안 된다. 20건을 읽은 것이 아니라 전체 건인 29282건을 읽었으며 PGA 사용량도 전체 건을 sort했던 경우(SQL1)와 비슷하다.

 

이런 상황에서도 해결방법은 분석함수를 밖으로 빼는 것이다. 아래의 SQL을 보자.

 

SELECT *

  FROM (SELECT s.*, ROWNUM rnum,

               SUM (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

          FROM (SELECT   /*+ INDEX(S PK_SALES_T) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) s

         WHERE ROWNUM <= :v_max_row --> 20 대입

       )

 WHERE rnum >= :v_min_row ;         --> 1 대입

 

 

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

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

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

|   0 | SELECT STATEMENT                |            |      1 |     20 |00:00:00.01 |      23 |          |

|*  1 |  VIEW                           |            |      1 |     20 |00:00:00.01 |      23 |          |

|   2 |   WINDOW BUFFER                 |            |      1 |     20 |00:00:00.01 |      23 | 2048  (0)|

|*  3 |    COUNT STOPKEY                |            |      1 |     20 |00:00:00.01 |      23 |          |

|   4 |     VIEW                        |            |      1 |     20 |00:00:00.01 |      23 |          |

|   5 |      TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |     20 |00:00:00.01 |      23 |          |

|*  6 |       INDEX RANGE SCAN          | PK_SALES_T |      1 |     20 |00:00:00.01 |       3 |          |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("RNUM">=:V_MIN_ROW)

   3 - filter(ROWNUM<=:V_MAX_ROW)

   6 - access("S"."PROD_ID"=:V_PROD_ID)

 

정확히 20건에 대해서만 WINDOW BUFFER operation 이 발생하였다. 이에 따라 PGA 사용량도 최적이 되었다. 또한 Block I/O 관점에서도 최상이다. 28337 Block scan한 것이 아니라 고작 23 Block scan 하였다. 분석함수의 위치가 성능에 얼마나 큰 영향을 미치는지 알 수 있는 장면이다.

 

결론

페이징 처리가 되었음에도 Time Out이 발생한다면 누적집계용 분석함수를 의심해보아야 한다. 만약 분석함수가 존재한다면 인라인뷰 밖으로 빼야 한다. 그렇게 한다면 분석함수의 실행이 최소화되며 이에 따라 성능이 향상된다. 또한 order by와 분석함수에 최적화된 인덱스를 만든다면 전체 건을 읽지 않아도 되며 sort의 부하 또한 없어질 것이다. 다시 말해 비효율이 없는 페이징 처리가 가능하다.

 

원리는 따로 있다

이 글의 결론까지 보았음에도 한가지 의문점을 떠올리지 못한다면 핵심원리를 놓친 것이다. 의문점이란 분석함수를 인라인뷰 밖으로 빼도 답이 달라지지 않는가?” 이다. 분석함수를 인라인뷰 밖으로 빼는 방법이 가능한 이유가 뭐라고 생각하는가? 답을 보기 전에 잠시 이유를 생각해보기 바란다. 답은 아래에 있다.

 

답을 보려면 아래의 글을 마우스로 드래그 하시오

 

이 글의 처음에 언급했던 페이징 처리시 약간의 비효율 있다고 했는데 이것이 원리이다. Tomas Kyte가 제시한 pagination 방법을 사용하면 뒤쪽 페이지를 읽을 때는 이전 페이지의 데이터를 모두 scan 해야 만 한다. 이 비효율을 이용하는 것이 핵심이다. 왜냐하면 한 페이지의 누적집계를 구하려면 이전 페이지의 값들을 모두 알아야 하기 때문이다. 예를 들어 홍길동 고객의 실적이 1 페이지와 2 페이지에 걸쳐서 나온다고 할 때, 1 페이지 있는 홍길동의 실적과 2페이지에 있는 홍길동의 실적을 더해야만 2 페이지의 누적집계를 구할 수 있다. 그런데 위의 방법을 사용하면 분석함수를 인라인뷰 밖으로 빼더라도 이전 페이지의 값을 보존하기 때문에 누적집계의 값은 정확하다.

 

 페이징 처리시 누적집계용 분석함수를 인라인뷰 밖으로 빼라고 누군가에게 guide할 때 단점(비효율)을 장점으로 이용했음을 같이 알려주기 바란다. 그것이 원리이자 핵심이기 때문이다.

 

PS

즐거운 성탄절을 보내시기 바랍니다.

지난 1년간 이 블로그를 이용해 주셔서 감사합니다.


저작자 표시 비영리 동일 조건 변경 허락
신고

'Oracle > SQL Tuning' 카테고리의 다른 글

Sort 부하를 좌우하는 두 가지 원리  (9) 2011.03.29
SQL튜닝 방법론  (17) 2011.01.27
Pagination과 분석함수의 위험한 조합  (26) 2010.12.23
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (6) 2010.02.11
USE_CONCAT 힌트 제대로 알기  (5) 2009.07.17
Posted by extremedb

댓글을 달아 주세요

  1. Ejql 2010.12.23 18:32 신고  댓글주소  수정/삭제  댓글쓰기

    글 잘봤습니다. 튜닝업무를 하면서 분석함수가 존재하는 부분범위처리가 튜닝이 쉽지가 않았는데 한가지는 해결되는것 같습니다.
    감사합니다. -- 해피 크리스마스 되세요 --

  2. 똥꽃 2010.12.24 08:20 신고  댓글주소  수정/삭제  댓글쓰기

    멋진 크리스마스 선물이네요.

  3. 김시연 2010.12.27 08:27 신고  댓글주소  수정/삭제  댓글쓰기

    한해동안 블로그를 통해 새로운 지식을 많이 얻었습니다. 감사드리고요. ^^ 한해 마무리 잘해세요~

  4. 2010.12.27 16:10  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  5. 에너자이져 2010.12.28 16:01 신고  댓글주소  수정/삭제  댓글쓰기

    언제나 감동이 있습니다..
    한해 마무리 잘하시고.. 건강하세요^^

  6. 라튜니 2010.12.29 10:49 신고  댓글주소  수정/삭제  댓글쓰기

    올 한해는 거의 매일 동규님 블로그를 하루에도 몇 차례를 방문한거 같네요. 새로운 포스트에 대한 기대감에 더 자주 방문했던 거 같습니다. 항상 좋은 정보, 내용의 포스트를 올려 주셔서 감사합니다. 내년에도 올해처럼 변함없는 포스팅 부탁 드립니다.^^ 항상 건강하시고 좋은 일만 가득하길 기원합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.12.29 16:56 신고  댓글주소  수정/삭제

      말씀하신 것처럼 내년에도 블로그 활동을 계속 하겠습니다. 좋은 내용이 되어야 할 텐데 걱정이 됩니다. ^^ 새해 복 많이 받으시고 소원 성취하세요.
      감사합니다.

  7. 최상운 2010.12.29 15:05 신고  댓글주소  수정/삭제  댓글쓰기

    SQL 가이드에 paging 처리를 추가할려고 했는데, 참 유용한 글이었어요.
    늦었지만, 오수석도 Merry Christmas 하시고 Happy New Year 해요!!!!

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.12.29 16:58 신고  댓글주소  수정/삭제

      그렇군요. 다행입니다.
      저번에 회사 워크샵에서 최수석님을 뵙지 못해서 아쉽습니다. 언제 한번 얼굴 봅시다.
      올해 마무리 잘하시고 새해 소원성취하세요.

  8. feelie 2010.12.30 20:25 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보를 보고 한해를 마무리하니 기분이 참좋네요.
    일년동안 고생하셨구요.. 내년에도 좋은 내용 기대하겠습니다.

  9. 백면서생 2010.12.31 14:00 신고  댓글주소  수정/삭제  댓글쓰기

    감사드리고, 마무리 잘하시고 행복한 신묘년 되시길 바랍니다.

  10. 시그너스 2011.01.03 13:51 신고  댓글주소  수정/삭제  댓글쓰기

    이글을 읽고 감동받아서 책을 구매 했습니다

  11. 오라클완전초보 2011.01.10 13:46 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요.
    수석님의 글을 읽고 현재 진행중인 차세대 일부 SQL 에 대해서 테스트를 해봤는데
    테이블을 하나만 사용했을 경우는 수석님의 글처럼 PGA 를 현저히 적게 쓰는게 확인이 되었으나
    테이블을 2개이상 조인 했을 경우 analytic function 을 밖으로 빼는거와 기존처럼 사용하나
    동일한 결과값이 나왔습니다. 아직 테스트 진행중이라 정확히 어떠한 방식때문인지는 확인을 못했으나
    혹시 테이블이 n 개 일 경우에도 실적용 사례가 있으신지요?

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.10 14:34 신고  댓글주소  수정/삭제

      안녕하세요. 조인을 한다고 해서 부분범위 처리가 불가능 하지는 않습니다.
      혹시 아래의 경우가 아닌지 해당 SQL을 다시 점검 해보시기 바랍니다.
      1.nested loop join 이 아닌 hash 조인이나 sort merge 조인을 사용한다.

      2.적절한 인덱스가 없다. 위의 예제에서 보면 order by의 컬럼 순서와 일치해야 합니다.

      3.order by 절과 와 분석함수의 over절의 컬럼 순서가 일치 해야 합니다.

      4.전체건이 몇건인지? 페이징 처리를 했을때 20건이고 하지 않았을때 30건이라면 별로 효율이 나지 않습니다.

      다시한번 확인해보시기 바랍니다.

  12. 아삽 2011.04.07 16:13 신고  댓글주소  수정/삭제  댓글쓰기

    유익한 정보 감사히 읽었습니다.^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.04.07 16:54 신고  댓글주소  수정/삭제

      아삽님 오랜만입니다.

      더 좋은 해법이 있습니다.
      분석함수에 NOSORT + STOPKEY 을 적용하는 방법입니다. 아래 링크의 제일 마지막 부분을 보시면 됩니다.

      하지만 분석함수를 밖으로 빼는 위의 방법도 의미가 있습니다. NOSORT가 불가능 한 경우이거나 분석함수 두개가 각자 서로 다른 OPERATION을 사용한다면 위의 방법이 유일한 대안 입니다.

      http://scidb.tistory.com/entry/분석함수의-성능개선-그-결과는

      감사합니다.

  13. Favicon of http://www.topreplicawatchesstore.com/dolce-gabbana-watches-c-135.html BlogIcon idolreplicas 2011.09.20 12:41 신고  댓글주소  수정/삭제  댓글쓰기

    하지만 분석함수를 밖으로 빼는 위의 방법도 의미가 있습니다. NOSORT가 불가능 한 경우이거나 분석함수 두개가 각자 서로 다른 OPERATION을 사용한다면 위의 방법이 유일한 대안 입니다

  14. Favicon of http://www.hotefashion.com/ BlogIcon gucci hangbags 2011.09.20 12:41 신고  댓글주소  수정/삭제  댓글쓰기

    분석함수에 NOSORT + STOPKEY 을 적용하는 방법입니다. 아래 링크의 제일 마지막 부분을 보시면 됩니다.

-개발 방법론 VS 모델링 방법론

최근에 어느 모델러에게 “~개발 방법론 때문에 미치겠다.”는 말을 들었다. SI 프로젝트에 참여하면 여러 가지 방법론이 등장한다. 하지만 그것들은 개발 방법론 혹은 관리방법론이기 때문에 분석과 사고, 그리고 통찰이 중요한 모델링을 수행하는 데는 오히려 방해가 되는 경우가 있다. 왜냐하면 이런 방법론들은 복잡한 건물을 세우는 방법, 건물 내부를 채우는 방법, 그리고 최소의 인력으로 최소의 시간을 들여 프로젝트를 마치는 방법에 관심이 집중되어 있기 때문이다. 이런 방법들은 프로젝트의 일정이 진행되는 입장에서 보면 관리적이며 과학적인 방법이다. 하지만 데이터 모델링에는 그런 과학적 방법이 얼마나 보탬이 될지는 미지수 이다.

 

데이터의 품질은 어떻게 보장되나?

많은 이들이 데이터의 품질에 목을 매고 있지만 개발 방법론에 따라 데이터 품질이 좌지우지 되는 것은 아니다. 왜냐하면 많은 경우에 데이터의 품질이란 데이터 모델의 품질에 따라 좌우된다. 그러므로 각종 개발 방법론들이 데이터 모델의 품질을 향상시키는 쪽으로 방법론이 진화되었으면 한다. 진화가 그리 어려운 것은 아니다.

 

진화방법 1

모델러는 일이 어떻게 진행될지 명확히 설명해야 하며, 산출물을 제대로 작성하고, 일정을 지켜야 한다. 모델러들 또한 고집이 있다. 전문가이기 때문에 개발자에게 배우지 않으려는 특징이 있다. 개발자들에게 UML을 배울 수 있다면 배워야 한다. 항상 모델러가 선생님이 되어야 하는 것이 아니다. UML과 모델링 툴의 사용법과 표기법이 다르다고 해서 그 사상이나 엔티티의 내부 구조가 달라지는 것이 아니다. 그러므로 모델러들도 특정 모델링 Tool에만 집착하면 안되며 UML로 모델링 할 수 있는 능력을 키워야 한다. 다른 Tool이 있음에도 내가 사용법을 모른다고 해서 고객이 몇 백만 원 혹은 몇 천만 원 하는 특정 Tool을 사야만 할까?

 

진화방법 2

반면에 단위 일정 내에서는 모델러에게 특정 방법을 강요해서는 안 된다. 즉 인정 될 수 있는 범위 내에서는 자유를 주어야 한다는 이야기이다. 왜냐하면 그들만의 방법이 따로 있기 때문이다. 화가가 그림(ERD)을 그리는데 특정 방법론을 강요할 수 있을까? 또한 장인이 작품(모델)을 만드는데 대규모 공장의 논리를 강요할 수 있을까 


강요와 허용의 차이

많은 경우에 모델러를 PL(관리자)로 생각하기 때문에 강요가 발생한다. 모델러는 PM이나 PL이 아니다. 물론 한사람이 다하는 곳도 있지만 그것은 예외이다. 그림을 그리는 방법에 대해서는 완전한 자유를 주어야 한다. 이와 반대로 구조물의 단순성 및 명료성 자체를 논하는 과정(철학적 과정)이 아니라, 복잡한 구조물을 세울 때(개발진행과정)에는 과학적 개발 방법이 꼭 필요하다. 즉 모델러에게 프로젝트의 관리 및 개발방법을 무시해도 된다는 특권을 주라는 이야기가 아니다. 다만 그들에게 좋은 그림을 그릴 수 있도록 자유로운 사고와 통찰을 허하라. 그렇게 해야만 데이터 모델이 단순해지고 명료해진다. 그리고 무엇보다 중요한 데이터의 품질이 향상된다.

 

이런 생각을 하는 사람은 나뿐인가? 만약 그렇다면 100년 전에 생존했던 어느 철학자가 필자의 의견을 지지했던 것처럼 보이는 이유는 무엇일까?

우리 문명은 '진보'라는 단어를 특징으로 한다. 진보는 우리 문명이 가진 특징 가운데 하나이기보다는 그것의 형태이다. 전형적으로 볼 때 그것은 쌓아 올리는 것이다. 그것은 점점 더 복잡해지는 구조물을 세우는 데 매몰되어 있다. 또 명료성 조차도 목적 자체가 아니라 이 목적을 이루기 위한 수단으로서 추구된다. 내게는 그와 반대로 명료성, 명석성이 그 자체로서 귀중하다.

 

나는 건물을 쌓는 데는 관심이 없고, 지을 수 있는 건물의 기초를 명료하게 보는 데에 관심이 있다.

 

그러니 내가 겨누는 과녁은 과학자들의 것과 같지 않으며, 나의 사고방식은 그들의 것과 다르다.

 

-비트겐슈타인 <철학적 언급들>의 서문 초고

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/bluepupils BlogIcon 김기창 2011.01.27 11:55 신고  댓글주소  수정/삭제  댓글쓰기

    SQL이나 플랜이 없는 글을 보니 신선하네요.. ㅎㅎ

    언젠가는 모델링 방법론이 녹아 있는 개발 방법론을 볼 수 있을 거라 생각해요. 언젠가는...
    더 바라기는 개발 방법론이 녹아 있는 모델링 방법론이 지배했으면 하는데 쉽지 않을 거 같고...

    오동규님의 주장대로 철학적 과정을 정형화한다는 건 모순이 있죠.

    사실 모델링 과정보다는 결과를 검증하는 활동에 초점을 맞춰야 되는데, 관리하는 입장에서는 답답하다 보니 방해가 되는 방법들을 강요하는 거 같아요. 좋은 의도에서 과정을 간섭하는데 그게 결과에 나쁜 영향을 미치는 게 문제죠.

    현재로서는 모델러가 내공을 쌓을 수 밖에 없는 거 같아요.
    그래야 간섭도 줄어들고, 간섭 받고도 일정 수준 이상의 결과를 낼 수 있을 거에요.

    그 내공 중에 커다란 부분은 진화입니다. 끊임없는 진화... 정체돼 있는 것이 아닌... 정지하고 있는 사람은 뭐든 시작해야 합니다... ㅎㅎ

    진화라는 단어도 다시 생각하게 하는 좋은 글이네요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.27 15:52 신고  댓글주소  수정/삭제

      김 수석님 오랜만 입니다.
      실행계획이 없는 글은 많지 않은데 용케 찾아 내셨네요.^^
      진화는 중요한 항목임에 동의합니다. 개발방법론도 언젠가는 진화 되겠죠?

-동적인 조회조건에서 SQL 작성법
-
다양한 검색조건에서 SQL 튜닝방법

-쿼리변환의 부정적 측면 해결

 

아래는 신입사원과 김대리의 대화내용이다. 신입사원이 머리를 긁고 있다. 문제가 어려운 모양이다.

 

신입사원: 상황에 따라서 조회조건이 달라지는데 어떻게 처리하죠?

김대리: 각각의 상황에 대해 union all로 처리하고 서로 다른 SQL로 처리하면 되.

신입사원: 네 알겠습니다. (조금 후에) 김대리님, 그렇게 하면 SQL이 너무 길어서 복잡해져요.

          6가지의 조건이 상황에 따라 달라지기 때문이죠.  

김대리: 그럼 방법이 없지. Dynamic SQL로 작성해. Dynamic SQL을 쓰되 바인드 변수를 사용해야 돼.

신입사원: 그건 어떻게 사용하죠? 제가 Dynamic SQL 사용법을 몰라서 그럽니다.

김대리: 내가 조금 있다가 가르쳐 줄게.

신입사원: 감사합니다.

 

이런 상황에서는 Union all로 여러 개의 SQL을 작성하는 것 보다는 Dynamic SQL을 사용하는 것이 해결책이 될 수 있다. 또한 많은 사람들이 그렇게 하고 있다. 하지만 꼭 둘 중에 하나만 골라야 한다는 생각은 버려야 한다. 그렇지 않으면 Union all을 사용하여 SQL이 매우 길어지거나 C JAVA 언어의 도움을 받아 IF Then ELSE 로직으로 SQL을 동적으로 생성하는 불리함을 감수해야 한다. 따라서 이보다 더 쉽고 간단한 방법이 있다면 그것을 사용하면 된다.

 

환경

DBMS: Oracle11g R2

각 테이블의 PK 인덱스는 이미 존재하므로 추가적인 인덱스만 설명한다.

EMP_MGR_HR_DT_IX: employee( manager_id + hire_date )

EMP_DEPT_IX : employee( department_id )

EMP_JOB_IX : employee( job_id )

 

다양한 조회조건을 제외하면 SQL은 다음과 같이 단순하다.

 

SELECT e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

 

여기까지는 SQL이 쉽다. 하지만 여기서부터는 까다로운 요구사항 때문에 SQL에 분기가 발생한다. 원래는 6가지의 where 조건을 적용해야 하지만 지면관계상 요구사항은 네 가지로 한정한다.

 

업무 요구사항

l  네 가지 패턴으로 조회조건이 들어온다. 각각의 패턴들은 :v_delimit(구분자)로 식별이 가능하다.

l  패턴 1  :v_delimit = 1 인 경우는 j.job_id = :v_job 조건으로 조회한다.

l  패턴 2  :v_delimit = 2 인 경우는 e.manager_id = :v_emp AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to 조건으로 조회한다.

l  패턴 3  :v_delimit = 3 인 경우는 d.department_id = :v_dept 조건으로 조회한다.

l  패턴 4  :v_delimit = 4 인 경우는 l.location_id = :v_loc 조건으로 조회한다. 

l  모든 패턴 1~4 filter 조건 d.manager_id > 0 가 공통적으로 적용되어야 한다.

 

성능 요구사항

여기까지는 업무팀의 요구사항이지만 개발자의 요구사항도 있다. where 조건이 패턴에 따라 동적으로 변경되면서도 각 패턴의 실행계획을 튜너의 마음대로 조정할 수 있어야 한다. 즉 네 가지 패턴의 SQL에 대해 서로 다른 힌트를 사용할 수 있어야 한다.

 

이런 까다로운 요구사항을 보고 가장 먼저 떠올릴 수 있는 생각은 Union all로 분기하는 것이다. 하지만 이 방법은 SQL이 길어지므로 코딩량을 증가시킨다. 두 번째로 생각할 수 있는 방법은 Dynamic SQL을 사용하는 것이다. 하지만 이 경우는 Where 조건뿐만 아니라 Select 절도 동적으로 변경되어야 한다. 왜냐하면 구분자의 값에 따라 힌트를 동적으로 만들어야 하기 때문이다. 따라서 우리는 이런 방법들을 사용하지 않을 것이다.

아래의 SQL을 실행할 때는 구분자인
:v_delimit의 값에 1을 대입해야 한다. 즉 패턴 1의 경우이다. 따라서 :v_job :v_delimit를 제외한 나머지 변수 값은 모두 null이다.

 

SELECT /*+ USE_CONCAT */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND (   ( :v_delimit = 1 AND j.job_id = :v_job )               --> :v_delimit = 1 입력, :v_job = 'SA_MAN' 입력

        OR ( :v_delimit = 2 AND e.manager_id = :v_emp

                            AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to )

        OR ( :v_delimit = 3 AND d.department_id = :v_dept )

        OR ( :v_delimit = 4 AND l.location_id = :v_loc   )

       )

   AND d.manager_id > 0;

 

OR를 Union all로 바꿔서 생각한다면 이해가 빠를 것이다. 복잡한 요구사항을 만족하면서도 SQL이 매우 가벼워졌다. Union all을 사용한 경우와 SQL을 비교해 보기 바란다. 길이는 많이 짧아졌지만 Union all을 사용할 때와 성능상 동일하다. 다시 말해 실행시점에서 하나의 SQL 4개의 SQL로 분리될 것이다. (이를 OR-Expansion 이라 부른다) 이 정도 길이의 SQL 이라면 Union all로 구분하여 SQL을 각각 작성하는 방법이나 Dynamic SQL을 일부러 사용할 필요는 없다. 주의사항은 각 패턴 별로 적절한 인덱스가 있어야 한다는 것이다. 그렇지 않으면 구분자의 의미는 사라질 것이다. 이제 실행계획을 보자.

 

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

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

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

|   0 | SELECT STATEMENT                  |                  |      1 |      5 |00:00:00.03 |      19 |          |

|   1 |  CONCATENATION                    |                  |      1 |      5 |00:00:00.03 |      19 |          |

|*  2 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |          |

|*  3 |    HASH JOIN                      |                  |      0 |      0 |00:00:00.01 |       0 |  988K (0)|

|   4 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |          |

|   5 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |          |

|   6 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |          |

|   7 |        TABLE ACCESS BY INDEX ROWID| LOCATION         |      0 |      0 |00:00:00.01 |       0 |          |

|*  8 |         INDEX UNIQUE SCAN         | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|*  9 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |          |

|* 10 |         INDEX RANGE SCAN          | DEPT_LOCATION_IX |      0 |      0 |00:00:00.01 |       0 |          |

|* 11 |       INDEX RANGE SCAN            | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |          |

|  12 |      TABLE ACCESS BY INDEX ROWID  | EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |          |

|  13 |     TABLE ACCESS FULL             | JOB              |      0 |      0 |00:00:00.01 |       0 |          |

|* 14 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |          |

|* 15 |    HASH JOIN                      |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  16 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  17 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |          |

|* 18 |       TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |          |

|* 19 |        INDEX UNIQUE SCAN          | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |          |

|* 20 |       TABLE ACCESS BY INDEX ROWID | LOCATION         |      0 |      0 |00:00:00.01 |       0 |          |

|* 21 |        INDEX UNIQUE SCAN          | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|  22 |      TABLE ACCESS BY INDEX ROWID  | EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |          |

|* 23 |       INDEX RANGE SCAN            | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |          |

|  24 |     TABLE ACCESS FULL             | JOB              |      0 |      0 |00:00:00.01 |       0 |          |

|* 25 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |          |

|  26 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  27 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  28 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  29 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  30 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |          |

|* 31 |         INDEX RANGE SCAN          | EMP_MGR_HR_DT_IX |      0 |      0 |00:00:00.01 |       0 |          |

|  32 |        TABLE ACCESS BY INDEX ROWID| JOB              |      0 |      0 |00:00:00.01 |       0 |          |

|* 33 |         INDEX UNIQUE SCAN         | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|* 34 |       TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |          |

|* 35 |        INDEX UNIQUE SCAN          | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |          |

|* 36 |      INDEX UNIQUE SCAN            | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|* 37 |     TABLE ACCESS BY INDEX ROWID   | LOCATION         |      0 |      0 |00:00:00.01 |       0 |          |

|* 38 |   FILTER                          |                  |      1 |      5 |00:00:00.03 |      19 |          |

|* 39 |    HASH JOIN                      |                  |      1 |      5 |00:00:00.03 |      19 |  360K (0)|

|* 40 |     HASH JOIN                     |                  |      1 |      5 |00:00:00.01 |      11 |  385K (0)|

|  41 |      NESTED LOOPS                 |                  |      1 |      5 |00:00:00.01 |       4 |          |

|  42 |       TABLE ACCESS BY INDEX ROWID | JOB              |      1 |      1 |00:00:00.01 |       2 |          |

|* 43 |        INDEX UNIQUE SCAN          | JOB_ID_PK        |      1 |      1 |00:00:00.01 |       1 |          |

|* 44 |       TABLE ACCESS BY INDEX ROWID | EMPLOYEE         |      1 |      5 |00:00:00.01 |       2 |          |

|* 45 |        INDEX RANGE SCAN           | EMP_JOB_IX       |      1 |      5 |00:00:00.01 |       1 |          |

|* 46 |      TABLE ACCESS FULL            | DEPARTMENT       |      1 |     11 |00:00:00.01 |       7 |          |

|* 47 |     TABLE ACCESS FULL             | LOCATION         |      1 |     23 |00:00:00.03 |       8 |          |

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

 

4개의 SQL이 각각 다른 조건의 인덱스로 Driving 되었다. 실행계획도 최적이다. 다시 말해 :v_delimit = 1 이 아닌 경우의 SQL은 전혀 실행되지 않았다. 하지만 만약 Hash Join이 맘에 걸린다면 아래처럼 힌트를 추가할 수 있다. Global Hint를 사용하면 하나의 SQL에는 하나의 힌트만 사용한다는 제약을 극복할 수 있다.

 

SELECT /*+ USE_CONCAT LEADING(@SEL$1_1 l d e j) USE_NL(@SEL$1_1 d e j)

                      LEADING(@SEL$1_2 d e l j) USE_NL(@SEL$1_2 e l j)

                      LEADING(@SEL$1_3 e d l j) USE_NL(@SEL$1_3 d l j)

                      LEADING(@SEL$1_4 j e d l) USE_NL(@SEL$1_4 e d l) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND (   ( :v_delimit = 1 AND j.job_id = :v_job )               --> :v_delimit = 1 입력, :v_job = 'SA_MAN' 입력

        OR ( :v_delimit = 2 AND e.manager_id = :v_emp

                            AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to )

        OR ( :v_delimit = 3 AND d.department_id = :v_dept )

        OR ( :v_delimit = 4 AND l.location_id = :v_loc   )

       )

   AND d.manager_id > 0;

 

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

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

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

|   0 | SELECT STATEMENT                  |                  |      1 |      5 |00:00:00.01 |      20 |

|   1 |  CONCATENATION                    |                  |      1 |      5 |00:00:00.01 |      20 |

|*  2 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |

|   3 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |

|   4 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |

|   5 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |

|   6 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |

|   7 |        TABLE ACCESS BY INDEX ROWID| LOCATION         |      0 |      0 |00:00:00.01 |       0 |

|*  8 |         INDEX UNIQUE SCAN         | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|*  9 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |

|* 10 |         INDEX RANGE SCAN          | DEPT_LOCATION_IX |      0 |      0 |00:00:00.01 |       0 |

|  11 |       TABLE ACCESS BY INDEX ROWID | EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |

|* 12 |        INDEX RANGE SCAN           | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |

|* 13 |      INDEX UNIQUE SCAN            | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|  14 |     TABLE ACCESS BY INDEX ROWID   | JOB              |      0 |      0 |00:00:00.01 |       0 |

|* 15 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |

|  16 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |

|  17 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |

|  18 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |

|  19 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |

|* 20 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |

|* 21 |         INDEX UNIQUE SCAN         | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |

|  22 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |

|* 23 |         INDEX RANGE SCAN          | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |

|* 24 |       TABLE ACCESS BY INDEX ROWID | LOCATION         |      0 |      0 |00:00:00.01 |       0 |

|* 25 |        INDEX UNIQUE SCAN          | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|* 26 |      INDEX UNIQUE SCAN            | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|  27 |     TABLE ACCESS BY INDEX ROWID   | JOB              |      0 |      0 |00:00:00.01 |       0 |

|* 28 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |

|  29 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |

|  30 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |

|  31 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |

|  32 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |

|  33 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |

|* 34 |         INDEX RANGE SCAN          | EMP_MGR_HR_DT_IX |      0 |      0 |00:00:00.01 |       0 |

|* 35 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |

|* 36 |         INDEX UNIQUE SCAN         | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |

|* 37 |       TABLE ACCESS BY INDEX ROWID | LOCATION         |      0 |      0 |00:00:00.01 |       0 |

|* 38 |        INDEX UNIQUE SCAN          | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|* 39 |      INDEX UNIQUE SCAN            | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|  40 |     TABLE ACCESS BY INDEX ROWID   | JOB              |      0 |      0 |00:00:00.01 |       0 |

|* 41 |   FILTER                          |                  |      1 |      5 |00:00:00.01 |      20 |

|  42 |    NESTED LOOPS                   |                  |      1 |      5 |00:00:00.01 |      20 |

|  43 |     NESTED LOOPS                  |                  |      1 |      5 |00:00:00.01 |      15 |

|  44 |      NESTED LOOPS                 |                  |      1 |      5 |00:00:00.01 |      13 |

|  45 |       NESTED LOOPS                |                  |      1 |      5 |00:00:00.01 |       6 |

|  46 |        TABLE ACCESS BY INDEX ROWID| JOB              |      1 |      1 |00:00:00.01 |       2 |

|* 47 |         INDEX UNIQUE SCAN         | JOB_ID_PK        |      1 |      1 |00:00:00.01 |       1 |

|* 48 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      1 |      5 |00:00:00.01 |       4 |

|* 49 |         INDEX RANGE SCAN          | EMP_JOB_IX       |      1 |      5 |00:00:00.01 |       2 |

|* 50 |       TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |      5 |      5 |00:00:00.01 |       7 |

|* 51 |        INDEX UNIQUE SCAN          | DEPT_ID_PK       |      5 |      5 |00:00:00.01 |       2 |

|* 52 |      INDEX UNIQUE SCAN            | LOC_ID_PK        |      5 |      5 |00:00:00.01 |       2 |

|* 53 |     TABLE ACCESS BY INDEX ROWID   | LOCATION         |      5 |      5 |00:00:00.01 |       5 |

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

 

힌트에 쿼리블럭명을 사용하였다. 각각의 쿼리블럭명은 DBMS_XPLAN.DISPLAY_CURSOR 함수에 +ALIAS 옵션을 추가하면 조회할 수 있다. 아래의 예제가 그것이다.

 

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

 

중간생략

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 

   7 - SEL$1_1 / L@SEL$1

   8 - SEL$1_1 / L@SEL$1

   9 - SEL$1_1 / D@SEL$1

  10 - SEL$1_1 / D@SEL$1

  11 - SEL$1_1 / E@SEL$1

  12 - SEL$1_1 / E@SEL$1

  13 - SEL$1_1 / J@SEL$1

  14 - SEL$1_1 / J@SEL$1

  20 - SEL$1_2 / D@SEL$1_2

  21 - SEL$1_2 / D@SEL$1_2
중간생략

  53 - SEL$1_4 / L@SEL$1_4

중간생략

 

가장 좌측의 번호는 Plan 상의 id에 해당한다. 쿼리블럭명은 ‘/’을 기준으로 좌측이다. SEL$1_1부터 SEL$1_4까지 쿼리블럭명들을 볼 수 있다. 이것들을 힌트에 사용하면 조건절에 OR로 분기된 SQL이 아무리 많아도 원하는 SQL(쿼리블럭)만을 콕 집어서 실행계획을 변경시킬 수 있다.

 

OR-Expansion  VS  Union All

이제 OR를 이용한 경우와 Union all을 사용한 경우를 비교해보자. 아래의 SQLUnion all로 분기한 경우인데 두가지 단점이 있다. 특히 Oracle11g R2를 사용하는 사람은 눈 여겨 보아야 한다. 여기서도 구분자에는 1을 대입한다. 네가지 SQL의 힌트가 서로 다름을 주목하자.

 

SELECT /*+ leading(j e d l) use_nl(e d l) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND j.job_id = :v_job                   --> ‘SA_MAN’ 입력

   AND d.manager_id > 0

   AND :v_delimit = 1                      --> 1 입력

UNION ALL

SELECT /*+ leading(e d l j) use_nl(d l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND e.manager_id = :v_emp

   AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to

   AND d.manager_id > 0

   AND :v_delimit = 2

UNION ALL

SELECT /*+ leading(d e l j) use_nl(e l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND d.department_id = :v_dept

   AND d.manager_id > 0

   AND :v_delimit = 3

UNION ALL

SELECT /*+ leading(l d e j) use_nl(d e j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND l.location_id = :v_loc 

   AND d.manager_id > 0  

   AND :v_delimit = 4 ;

 

단점 1: SQL의 길이가 너무 길다

구분자 별로 OR를 사용할 때보다 SQL이 많이 길어졌다. Union을 사용하는 방법의 단점은 SQL의 길이뿐만이 아니다. Oracle11g R2 에서는 개발자의 의도를 무시하는 결과가 발생할 수 있다. 개발자의 의도란 :v_delimit = 1 인 경우의 SQL만 실행하는 것이다. :v_delimit의 값이 2~4인 경우는 한 블록도 Scan해서는 안 된다. 과연 그렇게 되는지 아래의 Plan을 보자.

 

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

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

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

|   0 | SELECT STATEMENT                     |                    |      1 |      5 |00:00:00.01 |      22 |

|   1 |  UNION-ALL                           |                    |      1 |      5 |00:00:00.01 |      22 |

|*  2 |   FILTER                             |                    |      1 |      5 |00:00:00.01 |      20 |

|   3 |    NESTED LOOPS                      |                    |      1 |      5 |00:00:00.01 |      20 |

|   4 |     NESTED LOOPS                     |                    |      1 |      5 |00:00:00.01 |      15 |

|   5 |      NESTED LOOPS                    |                    |      1 |      5 |00:00:00.01 |      13 |

|   6 |       NESTED LOOPS                   |                    |      1 |      5 |00:00:00.01 |       6 |

|   7 |        TABLE ACCESS BY INDEX ROWID   | JOB                |      1 |      1 |00:00:00.01 |       2 |

|*  8 |         INDEX UNIQUE SCAN            | JOB_ID_PK          |      1 |      1 |00:00:00.01 |       1 |

|   9 |        TABLE ACCESS BY INDEX ROWID   | EMPLOYEE           |      1 |      5 |00:00:00.01 |       4 |

|* 10 |         INDEX RANGE SCAN             | EMP_JOB_IX         |      1 |      5 |00:00:00.01 |       2 |

|* 11 |       TABLE ACCESS BY INDEX ROWID    | DEPARTMENT         |      5 |      5 |00:00:00.01 |       7 |

|* 12 |        INDEX UNIQUE SCAN             | DEPT_ID_PK         |      5 |      5 |00:00:00.01 |       2 |

|* 13 |      INDEX UNIQUE SCAN               | LOC_ID_PK          |      5 |      5 |00:00:00.01 |       2 |

|  14 |     TABLE ACCESS BY INDEX ROWID      | LOCATION           |      5 |      5 |00:00:00.01 |       5 |

|* 15 |   FILTER                             |                    |      1 |      0 |00:00:00.01 |       0 |

|  16 |    NESTED LOOPS                      |                    |      0 |      0 |00:00:00.01 |       0 |

|  17 |     NESTED LOOPS                     |                    |      0 |      0 |00:00:00.01 |       0 |

|  18 |      NESTED LOOPS                    |                    |      0 |      0 |00:00:00.01 |       0 |

|  19 |       NESTED LOOPS                   |                    |      0 |      0 |00:00:00.01 |       0 |

|  20 |        TABLE ACCESS BY INDEX ROWID   | EMPLOYEE           |      0 |      0 |00:00:00.01 |       0 |

|* 21 |         INDEX RANGE SCAN             | EMP_MGR_HR_DT_IX   |      0 |      0 |00:00:00.01 |       0 |

|* 22 |        TABLE ACCESS BY INDEX ROWID   | DEPARTMENT         |      0 |      0 |00:00:00.01 |       0 |

|* 23 |         INDEX UNIQUE SCAN            | DEPT_ID_PK         |      0 |      0 |00:00:00.01 |       0 |

|  24 |       TABLE ACCESS BY INDEX ROWID    | LOCATION           |      0 |      0 |00:00:00.01 |       0 |

|* 25 |        INDEX UNIQUE SCAN             | LOC_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|* 26 |      INDEX UNIQUE SCAN               | JOB_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|  27 |     TABLE ACCESS BY INDEX ROWID      | JOB                |      0 |      0 |00:00:00.01 |       0 |

|  28 |   MERGE JOIN                         |                    |      1 |      0 |00:00:00.01 |       2 |

|  29 |    TABLE ACCESS BY INDEX ROWID       | JOB                |      1 |      1 |00:00:00.01 |       2 |

|  30 |     INDEX FULL SCAN                  | JOB_ID_PK          |      1 |      1 |00:00:00.01 |       1 |

|* 31 |    SORT JOIN                         |                    |      1 |      0 |00:00:00.01 |       0 |

|  32 |     VIEW                             | VW_JF_SET$B71A25AA |      1 |      0 |00:00:00.01 |       0 |

|  33 |      UNION-ALL                       |                    |      1 |      0 |00:00:00.01 |       0 |

|* 34 |       FILTER                         |                    |      1 |      0 |00:00:00.01 |       0 |

|  35 |        NESTED LOOPS                  |                    |      0 |      0 |00:00:00.01 |       0 |

|  36 |         NESTED LOOPS                 |                    |      0 |      0 |00:00:00.01 |       0 |

|* 37 |          TABLE ACCESS BY INDEX ROWID | DEPARTMENT         |      0 |      0 |00:00:00.01 |       0 |

|* 38 |           INDEX UNIQUE SCAN          | DEPT_ID_PK         |      0 |      0 |00:00:00.01 |       0 |

|  39 |          TABLE ACCESS BY INDEX ROWID | LOCATION           |      0 |      0 |00:00:00.01 |       0 |

|* 40 |           INDEX UNIQUE SCAN          | LOC_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|  41 |         TABLE ACCESS BY INDEX ROWID  | EMPLOYEE           |      0 |      0 |00:00:00.01 |       0 |

|* 42 |          INDEX RANGE SCAN            | EMP_DEPT_IX        |      0 |      0 |00:00:00.01 |       0 |

|* 43 |       FILTER                         |                    |      1 |      0 |00:00:00.01 |       0 |

|  44 |        NESTED LOOPS                  |                    |      0 |      0 |00:00:00.01 |       0 |

|  45 |         NESTED LOOPS                 |                    |      0 |      0 |00:00:00.01 |       0 |

|  46 |          NESTED LOOPS                |                    |      0 |      0 |00:00:00.01 |       0 |

|  47 |           TABLE ACCESS BY INDEX ROWID| LOCATION           |      0 |      0 |00:00:00.01 |       0 |

|* 48 |            INDEX UNIQUE SCAN         | LOC_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|* 49 |           TABLE ACCESS BY INDEX ROWID| DEPARTMENT         |      0 |      0 |00:00:00.01 |       0 |

|* 50 |            INDEX RANGE SCAN          | DEPT_LOCATION_IX   |      0 |      0 |00:00:00.01 |       0 |

|* 51 |          INDEX RANGE SCAN            | EMP_DEPT_IX        |      0 |      0 |00:00:00.01 |       0 |

|  52 |         TABLE ACCESS BY INDEX ROWID  | EMPLOYEE           |      0 |      0 |00:00:00.01 |       0 |

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

 

단점 2 : 불필요한 쿼리블럭을 Scan 하며 힌트가 무시된다

Join factorization(1) 이라는 쿼리변환이 발생하여 불필요한 두 블록(Plan의 빨강색 부분) Scan 하였다. : v_delimit = 3 인 경우와 :v_delimit = 4인 경우의 SQL이 실행되어 버린 것이다. 확률은 많지 않겠지만 만약 테이블이 대용량이라면 index full scan과 그에 따른 테이블로의 접근은 성능에 치명적일 것이다. 또한 쿼리변환으로 인해 개발자가 작성한 힌트도 무시되어 sort merge join이 발생되었다.

의도하지 않은 쿼리변환을 경계하라
이렇게 다양한 검색조건에서 Union을 사용하는 경우는 11g R2부터 발생되는 Join factorization의 악영향에 주의해야 한다. 왜냐하면 :v_delimit = 1에 해당하는 SQL만 실행되어야 하지만 Join factorization으로 인해 인라인뷰 외부로 빠진 쿼리블럭은 구분자(:v_delimit )의 값에 영향을 받지 않기 때문이다.

 

그런데 Join factorization을 발생시키지 않을 목적으로 SQL 마다 rownum을 사용하는 사람이 있다. 아래의 SQL이 그것인데 그럴 필요 없다.

 

SELECT /*+ leading(j e d l) use_nl(e d l) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND j.job_id = :v_job                   --> 'SA_MAN' 입력

   AND d.manager_id > 0

   AND ROWNUM > 0

   AND :v_delimit = 1                      --> 1 입력

UNION ALL

SELECT /*+ leading(e d l j) use_nl(d l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND e.manager_id = :v_emp

   AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to

   AND d.manager_id > 0

   AND ROWNUM > 0 

   AND :v_delimit = 2

UNION ALL

중간생략

 

Rownum을 네 번 사용하면 Join factorization이 방지 되기는 하지만 SQL마다 조건절을 추가해야 하므로 막노동에 가깝고 SQL이 길어진다. 가장 쉬운 방법은 쿼리변환을 방지하는 힌트를 사용하는 것이다. 가장 위쪽 SQL의 힌트에 NO_FACTORIZE_JOIN(@SET$1)을 추가하면 된다. SQL마다 힌트를 추가할 필요는 없다. 아래의 예제를 보자.

 

SELECT /*+ leading(j e d l) use_nl(e d l) NO_FACTORIZE_JOIN(@SET$1) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND j.job_id = :v_job                   --> 'SA_MAN' 입력

   AND d.manager_id > 0

   AND :v_delimit = 1                      --> 1 입력

UNION ALL

SELECT /*+ leading(e d l j) use_nl(d l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

중간생략

 

위처럼 힌트를 한번만 추가하여 쿼리변환을 방지하면 하면 불필요한 블록을 Scan하지 않으며, 개발자가 작성한 힌트를 무시하지 않는다. Oracle11g R2를 사용한다면 직접 실행계획을 확인해보기 바란다.

 

결론 

동적인 검색조건이 많지 않아 Union all을 사용할 때에도 쿼리변환을 조심해야 한다. 원하지 않는 블록을 Scan할 수 있기 때문이다. 이때 쿼리변환을 방지할 목적으로 Rownum을 사용하는 것은 좋지 않다. 왜냐하면 Join factorization을 막을 수는 있지만 또 다른 쿼리변환인 FPD(2) JPPD(3)등의 쿼리변환도 같이 막혀버린다. 따라서 NO_FACTORIZE_JOIN 힌트를 사용하는 것이 적절하다.

오라클의 버전이 올라갈수록 쿼리변환의 기능이 많아진다. 하지만 기능이 많아질수록 어두운 측면도 부각된다. 물론 쿼리변환의 문제점은 자주 발생하지는 않으며 예외적인 경우이다. 하지만 그 예외가 발생된다면 위의 SQL처럼 원하지 않을 때도 쿼리변환이 발생하여 문제가 될 것이다. 지금은 CBQT의 태동기이므로 앞으로 문제가 개선될 것으로 기대한다.  

 

검색조건이 동적으로 바뀔 때는OR로 분기하는 방법을 사용하라. 이 방법을 적절히 사용하면 Union all을 사용하는 방법의 단점인 SQL이 길어지는 것을 피할 수 있다. 또한  Dynamic SQL처럼 힌트와 where절을 동적으로 교체할 필요 없이 명시적으로 작성할 수 있다. Where 절에 OR를 사용하는 것이 항상 나쁜 것은 아니며 분명 뭔가 남다른 장점이 있다. 우리는 그 점을 이해해야 한다.  

 

1: JF(Join factorization)을 간단히 설명하면 Union / Union All 사용시 공통으로 사용하는 테이블을 분리시키는 것이다. 즉 아래와 같이 SQL1 SQL2로 변경되는 기능이다.

SQL1

SELECT /*+ USE_HASH(c s)  */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 3

UNION ALL

SELECT /*+ USE_HASH(c s) */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 9 ;

 

SQL2

SELECT s.prod_id prod_id, s.cust_id cust_id, s.quantity_sold,

       s.amount_sold, vw_jf_set$0a277f6d.item_2 channel_desc

  FROM (SELECT c.channel_id AS item_1, c.channel_desc AS item_2

          FROM channels c

         WHERE c.channel_id = 3

        UNION ALL

        SELECT c.channel_id AS item_1, c.channel_desc AS item_2

          FROM channels c

         WHERE c.channel_id = 9) vw_jf_set$0a277f6d, --> JF 가 발생하면 인라인뷰vw_jf ~ 가 생성된다.

       sales s                                       --> sales 테이블을 인라인뷰 외부로 분리시킴

 WHERE vw_jf_set$0a277f6d.item_1 = s.channel_id ;

 

2: FPD(Filter Push Down)는 뷰/인라인뷰 외부의 조건이 뷰 내부로 파고드는 기능이다.

3: JPPD(Join Predicate Push Down)는 뷰/인라인뷰 외부의 조인조건이 뷰 내부로 파고드는 기능이다. FPD JPP의 차이는 FPD는 상수조건이 파고드는 것이며 JPPD는 조인절이 파고든다는 점이다.

참고: JF JPPD CBQT(Cost Based Query Transformation)이며 FPDHQT(Heuristic Query Transformation)이다. HQT Rule Based Query Transformation 이라고 부르기도 한다.


 

신고
Posted by extremedb

댓글을 달아 주세요

  1. Ejql 2011.01.17 16:16 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽었습니다. 감사합니다.


-변경이력에서 full table scan을 동반하는 대용량 배치의 성능관점
이 글을 이해하기 위해 이전 글들을 먼저 읽기 바란다. 

 

이전 글의 요약

첫 번째 글두 번째 글에서 변경이력에 종료일자를 추가하는 것이 성능상 유리하다는 네 가지 주장이 사실과 다름을 증명해 보았다. 즉 시작일자만으로도 종료일자+시작일자 인덱스와 같은 성능을 발휘하며, 때에 따라서는 시작일자 인덱스가 더 빠르기까지 하다. 종료일자를 추가해야 한다는 네 가지 주장을 정리하면 다음과 같다.

 

1. 비교적 최근 데이터를 구할 때는 종료일자 + 시작일자가 빠르다. 그러므로 종료일자를 추가해야 한다.
2.
특정 시점의 데이터를 보기 위해서는 종료일자 + 시작일자 인덱스를 이용하여 BETWEEN을 쓰면 되므로 시작일자만 사용하는 것에 비해 빠르다
.
3. max
값을 구할 때 종료일자에 = '99991231' 만 주면 되므로 시작일자만 사용하는 것에 비해 빠르다.

4. SQL의 결과가 한 건이 아니라 여러 건인 경우 rownum = 1 조건을 사용할 수 없으므로 역정규화를 하여 종료일자를 추가하는 것이 성능상 유리하다.

 

이 네 가지 주장이 사실이 아님을 증명하였는데, 이 과정에서 독자들이 두 가지 오해를 할 수 있으므로 이를 밝히고자 한다.

 

첫 번째, max값을 구하기 위해 인라인뷰 내부에서 order by를 사용하고 인라인뷰 밖에서 rownum = 1을 사용할 때 결코 Sort가 발생하지 않는다. 따라서 Sort area도 소모하지 않는다. Sort를 하는 경우는 인덱스(고객번호 + 시작일자)가 존재하는 않는 경우뿐이다. 즉 아래의 SQL은 인덱스를 사용하므로 추가적인 Sort를 발생시키지 않는다. 다만 인덱스를 Drop하는 실수나 장애상황에서 답이 틀려지지 않게 조치된 것뿐이다.

 

SELECT *

FROM (SELECT /*+ INDEX_DESC(a 인덱스명) */ *

FROM test1 a

WHERE cust_no = 5

ORDER BY a.start_dt DESC)

WHERE ROWNUM = 1 ;

 

두 번째, "SQL의 결과가 여러 건일 때(주장 4번의 반박에 해당함) 테이블을 중복해서 사용해야 하므로 불리하다. 또한 이력테이블을 두 번 Scan하지 않으려면 type을 써야 하는데 이는 불편하다." 라는 두 가지 이유를 들어 사용할 수 없다고 주장하였다. (이메일로 의견을 받았음) 하지만 이 또한 인덱스가 있다면 테이블을 두 번 Scan 하지 않는다. 아래의 SQL을 보라.

 

SELECT /*+ use_nl(a b c d) */ a.svc_no, a.cust_no, a.acct_no, a.svc_date,

a.txt, b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt

  FROM (SELECT a.*,

               (SELECT b.rowid||c.rowid||d.rowid as rid

                  FROM ( SELECT * FROM svc_hist  b ORDER BY start_dt DESC) b,

                       ( SELECT * FROM cust_hist c ORDER BY start_dt DESC) c,

                       ( SELECT * FROM acct_hist d ORDER BY start_dt DESC) d   

                 WHERE b.svc_no    = a.svc_no

                   AND c.cust_no   = a.cust_no

                   AND d.acct_no   = a.acct_no

                   AND b.start_dt <= a.svc_date

                   AND c.start_dt <= a.svc_date

                   AND d.start_dt <= a.svc_date

                   AND ROWNUM = 1) AS rid

          FROM svc a

         WHERE a.svc_date BETWEEN SYSDATE - 201 AND SYSDATE - 1 ) a,

       svc_hist b, cust_hist C, acct_hist D 

 WHERE SUBSTR(A.RID, 1,                     LENGTH(A.RID)/3) = B.ROWID

   AND SUBSTR(A.RID, LENGTH(A.RID)/3 + 1,   LENGTH(A.RID)/3) = C.ROWID

   AND SUBSTR(A.RID, 2*LENGTH(A.RID)/3 + 1, LENGTH(A.RID)/3) = D.ROWID ;

 

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

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

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

|   0 | SELECT STATEMENT                 |              |      1 |    200 |00:00:00.03 |    1998 |

|   1 |  NESTED LOOPS                    |              |      1 |    200 |00:00:00.03 |    1998 |

|*  2 |   COUNT STOPKEY                  |              |    200 |    200 |00:00:00.01 |    1403 |

|   3 |    NESTED LOOPS                  |              |    200 |    200 |00:00:00.01 |    1403 |

|   4 |     NESTED LOOPS                 |              |    200 |    200 |00:00:00.01 |     801 |

|   5 |      VIEW                        |              |    200 |    200 |00:00:00.01 |     401 |

|*  6 |       INDEX RANGE SCAN DESCENDING| PK_CUST_HIST |    200 |    200 |00:00:00.01 |     401 |

|   7 |      VIEW                        |              |    200 |    200 |00:00:00.01 |     400 |

|*  8 |       INDEX RANGE SCAN DESCENDING| PK_ACCT_HIST |    200 |    200 |00:00:00.01 |     400 |

|   9 |     VIEW                         |              |    200 |    200 |00:00:00.01 |     602 |

|* 10 |      INDEX RANGE SCAN DESCENDING | PK_SVC_HIST  |    200 |    200 |00:00:00.01 |     602 |

|  11 |   NESTED LOOPS                   |              |      1 |    200 |00:00:00.03 |    1798 |

|  12 |    NESTED LOOPS                  |              |      1 |    200 |00:00:00.02 |    1605 |

|  13 |     VIEW                         |              |      1 |    200 |00:00:00.02 |    1410 |

|* 14 |      FILTER                      |              |      1 |    200 |00:00:00.01 |       7 |

|  15 |       TABLE ACCESS BY INDEX ROWID| SVC          |      1 |    200 |00:00:00.01 |       7 |

|* 16 |        INDEX RANGE SCAN          | IX_SVC_01    |      1 |    200 |00:00:00.01 |       4 |

|  17 |     TABLE ACCESS BY USER ROWID   | CUST_HIST    |    200 |    200 |00:00:00.01 |     195 |

|  18 |    TABLE ACCESS BY USER ROWID    | ACCT_HIST    |    200 |    200 |00:00:00.01 |     193 |

|  19 |   TABLE ACCESS BY USER ROWID     | SVC_HIST     |    200 |    200 |00:00:00.01 |     200 |

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

 

Predicate Information (identified by operation id):

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

   2 - filter(ROWNUM=1)

   6 - access("C"."CUST_NO"=:B1 AND "C"."START_DT"<=:B2)

   8 - access("D"."ACCT_NO"=:B1 AND "D"."START_DT"<=:B2)

  10 - access("B"."SVC_NO"=:B1 AND "B"."START_DT"<=:B2)

  14 - filter(SYSDATE@!-201<=SYSDATE@!-1)

  16 - access("A"."SVC_DATE">=SYSDATE@!-201 AND "A"."SVC_DATE"<=SYSDATE@!-1)

 

실행계획을 보면 인덱스 PK_SVC_HIST와 테이블 SVC_HIST를 각각 한번씩 만 Scan한다. rowid를 사용했기 때문이다. SVC_HIST 테이블 이외의 나머지 변경이력도 마찬가지이다. 물론 스칼라 서브쿼리에 추가적인 filter 조건이 있다면 테이블을 두 번 Scan 하게 된다. 하지만 이때에도 인덱스를 추가하면 테이블을 두 번 Scan하지 않는다. 예를 들어 고객변경이력에 col1 > ‘1’ 이라는 조건이 추가되었다면 고객번호 + 시작일자 + col1 인덱스를 추가하면 된다. Source 테이블의 값이 매우 자주 변경되어 이력 테이블이 insert에 의한 부하가 심하다면 새로운 인덱스를 추가하는 것은 부담이 될 것이다. 그럴 때는 Type을 사용하면 된다. Type을 사용하기 어렵다면 그냥 테이블을 두 번 Scan 해도 큰 무리가 없다.

이전 글의 예제를 본다면 테이블을 두 번씩 Scan해도 0.02초 혹은 0.03초의 성능을 보장한다. 다시 말하면 테이블을 두 번 Scan 하였음에도 한번만 Scan하는 경우(위의 예제에서 0.03)와 비교해보면 속도차이는 미미하다. 왜냐하면 스칼라 서브쿼리에서 먼저 읽었던 테이블의 블럭은 대부분 buffer cache에 올라가 있으므로 인라인뷰 외부에서 다시 한번 읽을 때는 매우 가볍다. 이것은 "Scan한 블럭수는 차이가 나는데 Elapsed Time은 왜 동일한가요?" 에 대한 대답이다.

 

변경이력 테이블을 FTS(Full Table Scan) 하는 대용량 배치의 경우

인덱스를 사용할 수 없는 경우에 대해 알아보자. 천만 건에 해당하는 데이터와 그 데이터의 변경이력 1억건 중에 특정시점의 데이터를 구하려고 할 때는 인덱스를 사용할 수 없다. 이때에는 변경이력에 FTS를 사용해야 한다. 이 경우에 종료일자를 이용하여 between 조인을 사용하는 것과 시작일자 인덱스만 사용하는 것의 성능을 비교해보자. 실습을 할 사람들은 환경을 만들기 위해 아래 첨부파일을 다운 받기 바란다. 필자는 2010 11 28일을 사용하였으나 실습을 진행할 사람들은 일자가 달라지므로 sysdate – 4 를 사용하기 바란다. 이제 테스트를 진행해보자. 노트북에서 테스트를 진행할 때 건수가 많아 느려짐으로 테스트를 진행 할 수 없었다. 따라서 노트북이 아닌 개발 DB에서 테스트를 진행 하였다.


실습을 위한 object 생성.SQL

종료일자가 관리되는 테이블과 시작일자만 존재라는 테이블을 따로 생성함


 

테이블의 건수

svc : 2천만건 (active_yn = 1 인건은 천만 건)

svc_hist1 : 1억건

acct_hist1: 3천만건

cust_hist1: 5천만건

 

alter session set statistics_level = all;

 

CREATE TABLE TMP_HIST_END_DT NOLOGGING AS

SELECT /*+ leading(a b d c) use_hash(b d c) swap_join_inputs(d) swap_join_inputs(c) */ 

       a.*, b.start_dt as svc_st_dt, b.txt as svc_txt, c.start_dt as acct_st_dt,

       c.txt as acct_txt, d.start_dt as cust_st_dt, d.txt as cust_txt

  FROM svc a, svc_hist1 b, acct_hist1 c, cust_hist1 d

 WHERE a.active_yn = 1

   AND b.svc_no = a.svc_no

   AND d.cust_no = a.cust_no

   AND c.acct_no = a.acct_no

   AND TO_DATE('20101128', 'YYYYMMDD') BETWEEN b.start_dt AND b.end_dt

   AND TO_DATE('20101128', 'YYYYMMDD') BETWEEN c.start_dt AND c.end_dt

   AND TO_DATE('20101128', 'YYYYMMDD') BETWEEN d.start_dt AND d.end_dt ;

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

 

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

| Id  | Operation             | Name       | A-Rows |   A-Time   | Buffers | Reads  | Used-Mem | Used-Tmp|

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

|   1 |  LOAD AS SELECT       |            |      1 |00:04:42.50 |    1651K|   1540K|  519K (0)|         |

|*  2 |   HASH JOIN           |            |   9990K|00:03:51.15 |    1421K|   1540K| 1237K (0)|         |

|*  3 |    TABLE ACCESS FULL  | ACCT_HIST1 |    500 |00:00:22.56 |     207K|    207K|          |         |

|*  4 |    HASH JOIN          |            |   9990K|00:03:18.60 |    1214K|   1333K| 1250K (0)|         |

|*  5 |     TABLE ACCESS FULL | CUST_HIST1 |    833 |00:00:34.23 |     346K|    346K|          |         |

|*  6 |     HASH JOIN         |            |   9990K|00:02:24.38 |     868K|    987K|  453M (1)|     967K|

|*  7 |      TABLE ACCESS FULL| SVC        |   9990K|00:00:09.99 |     149K|    149K|          |         |

|*  8 |      TABLE ACCESS FULL| SVC_HIST1  |     19M|00:01:08.50 |     718K|    718K|          |         |

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

 

Predicate Information (identified by operation id):

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

   2 - access("C"."ACCT_NO"="A"."ACCT_NO")

   3 - filter(("C"."END_DT">=TO_DATE(' 2010-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C"."START_DT"<=TO_DATE(' 2010-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

   4 - access("D"."CUST_NO"="A"."CUST_NO")

   5 - filter(("D"."END_DT">=TO_DATE(' 2010-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."START_DT"<=TO_DATE(' 2010-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

   6 - access("B"."SVC_NO"="A"."SVC_NO")

   7 - filter("A"."ACTIVE_YN"=1)

   8 - filter(("B"."START_DT"<=TO_DATE(' 2010-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."END_DT">=TO_DATE(' 2010-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

 

모든 변경이력에 FTS를 사용하였지만 Between 조인에 의해서 불필요한 Sort가 발생하지 않는다. 따라서 성능도 최적이다.

 

이제 종료일자가 없는 테이블로 테스트를 진행해 보자.

 

CREATE TABLE TMP_HIST_START_DT1 NOLOGGING AS

SELECT /*+ leading(a b d c) use_hash(b d c) swap_join_inputs(d) swap_join_inputs(c) */

       a.*, b.start_dt as svc_st_dt, b.txt as svc_txt, c.start_dt as acct_st_dt,

       c.txt as acct_txt, d.start_dt as cust_st_dt, d.txt as cust_txt

  FROM svc a,

       (SELECT b.*,

               ROW_NUMBER () OVER (PARTITION BY svc_no ORDER BY start_dt DESC) AS rnum

          FROM svc_hist b

         WHERE TO_DATE ('20101128', 'YYYYMMDD') >= start_dt ) b,

       (SELECT c.*,

               ROW_NUMBER () OVER (PARTITION BY acct_no ORDER BY start_dt DESC) AS rnum

          FROM acct_hist c

         WHERE TO_DATE ('20101128', 'YYYYMMDD') >= start_dt) c,

       (SELECT d.*,

               ROW_NUMBER () OVER (PARTITION BY cust_no ORDER BY start_dt DESC)  AS rnum

          FROM cust_hist d

         WHERE TO_DATE ('20101128', 'YYYYMMDD') >= start_dt) d

 WHERE a.active_yn = 1

   AND b.svc_no = a.svc_no

   AND d.cust_no = a.cust_no

   AND c.acct_no = a.acct_no

   AND b.rnum = 1

   AND c.rnum = 1

   AND d.rnum = 1 ;

 

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

| Id  | Operation                    | Name      | A-Rows |   A-Time   | Buffers | Reads  | Used-Mem | Used-Tmp|

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

|   1 |  LOAD AS SELECT              |           |      1 |00:10:59.63 |    1450K|   1575K|  519K (0)|         |

|*  2 |   HASH JOIN                  |           |   9990K|00:10:14.07 |    1221K|   1574K|   46M (0)|         |

|*  3 |    VIEW                      |           |    500 |00:01:24.11 |     173K|    173K|          |         |

|*  4 |     WINDOW SORT PUSHED RANK  |           |   1000 |00:01:24.11 |     173K|    173K|   97M (0)|    1024 |

|*  5 |      TABLE ACCESS FULL       | ACCT_HIST |     29M|00:00:30.00 |     173K|    173K|          |         |

|*  6 |    HASH JOIN                 |           |   9990K|00:08:39.91 |    1048K|   1401K|   47M (0)|         |

|*  7 |     VIEW                     |           |    833 |00:02:19.91 |     289K|    289K|          |         |

|*  8 |      WINDOW SORT PUSHED RANK |           |   1666 |00:02:19.91 |     289K|    289K|   97M (0)|    1024 |

|*  9 |       TABLE ACCESS FULL      | CUST_HIST |     49M|00:00:49.98 |     289K|    289K|          |         |

|* 10 |     HASH JOIN                |           |   9990K|00:05:59.96 |     758K|   1111K|  377M (1)|     947K|

|* 11 |      TABLE ACCESS FULL       | SVC       |   9990K|00:00:19.99 |     149K|    149K|          |         |

|* 12 |      VIEW                    |           |     19M|00:04:16.25 |     608K|    844K|          |         |

|* 13 |       WINDOW SORT PUSHED RANK|           |     39M|00:03:56.27 |     608K|    844K|   97M (1)|    1848K|

|* 14 |        TABLE ACCESS FULL     | SVC_HIST  |     39M|00:01:12.45 |     608K|    608K|          |         |

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

                          

Predicate Information (identified by operation id):

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

   2 - access("C"."ACCT_NO"="A"."ACCT_NO")

   3 - filter("C"."RNUM"=1)

   4 - filter(ROW_NUMBER() OVER ( PARTITION BY "ACCT_NO" ORDER BY INTERNAL_FUNCTION("START_DT") DESC )<=1)

   5 - filter("START_DT"<=TO_DATE(' 2010-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

   6 - access("D"."CUST_NO"="A"."CUST_NO")

   7 - filter("D"."RNUM"=1)

   8 - filter(ROW_NUMBER() OVER ( PARTITION BY "CUST_NO" ORDER BY INTERNAL_FUNCTION("START_DT") DESC )<=1)

   9 - filter("START_DT"<=TO_DATE(' 2010-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  10 - access("B"."SVC_NO"="A"."SVC_NO")

  11 - filter("A"."ACTIVE_YN"=1)

  12 - filter("B"."RNUM"=1)

  13 - filter(ROW_NUMBER() OVER ( PARTITION BY "SVC_NO" ORDER BY INTERNAL_FUNCTION("START_DT") DESC )<=1)

  14 - filter("START_DT"<=TO_DATE(' 2010-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

종료일자를 사용한 between 조인이 없으므로 전체 건을 sort 해야 한다. 따라서 부하가 상당하며 성능이 두 배 이상 저하되었다. FTS를 사용한 것은 같지만 모든 변경이력 테이블(1 8천만건) Sort해야 한다. (WINDOW SORT PUSHED RANK 부분참조) 이런 경우는 종료일자를 사용하여 between으로 처리하는 것이 확실히 빠르다. 물론 Parallel을 사용하여 degree 2혹은 3정도 준다면 해결할 수 있지만 신중해야 한다. 초를 다투는 중요한 배치인 경우만 적용해야 하며, 동 시간대 CPU 사용량과 PGA 사용량을 감안해야 한다. sort_area_size hash_area_size를 수동으로 튜닝 하는 것 또한 마찬가지 이다. Parallel을 사용하거나 수동으로 PGA를 조절하는 것은 자원을 독점하는 것이므로 다른 배치 프로그램에 악영향을 줄 수 있다.

 

이제 지난 글과 이번 글에서 나타난 특징을 표로 정리해보자.
 

비교항목

시작일자만 관리

종료일자도 관리

사용 빈도수

중요도

정합성(데이터 품질)을 보장하는가?

우수

나쁨

N/A

매우 중요

성능관점

최근 시점의 값 조회시 성능

우수

우수

90%

중요

중간 시점의 값 조회시 성능

우수

중간

9%

오래된 시점의 값 조회시 성능

우수

나쁨

1%

FTS를 동반하는 대용량 배치 성능

나쁨

우수

0.01%

보통

SQL의 복잡성

나쁨

우수

N/A

보통

추가적인 비용(노력)이 얼마나 드는가?

우수

나쁨

N/A

보통


종료일자를 사용하면 정합성을 보장하지 못한다. 성능관점에서 인덱스를 사용하는 경우(온라인 업무)는 시작일자만으로 인덱스를 사용하는 것이 유리하다. 왜냐하면 종료일자+시작일자 인덱스는 최종(max)값을 구할 때 가장 빠르지만 최종 값에서 멀어질수록 점점 성능이 떨어진다. 하지만 시작일자 인덱스는 항상 빠르다.
FTS를 동반하는 대용량 배치에서는 종료일자를 사용하여 between 조인을 하면 두 배 ~ 세 배 정도 빠르다. SQL의 복잡성 측면에서는 시작일자만 관리하는 경우는 SQL이 길어지므로 종료일자를 사용하는 것이 유리하다.
 

역정규화하여 종료일자를 사용하려면 아래와 같은 추가적인 비용이 든다.
1. Source
테이블의 데이터가 변경되면 트리거성으로 변경이력에 Update하는 프로그램을 추가로 작성해야 한다.
2.
이때 데이터가 자주 변경되는 경우는 Update 자체의 부하도 무시할 수 없다.
3.
데이터가 틀어진 경우를 대비하여 정합성을 보정하는 프로그램을 추가로 작성해야 한다.

 

정합성을 보정하는 프로그램은 상당히 복잡하다. 왜냐하면 점의 중복제거뿐만 아니라 선분의 중복도 제거해야 하기 때문이다. 예를 들어 고객변경이력이라고 한다면 점(고객번호 + 시작일시)이 중복이 되어선 안되므로 Cleansing이 필요하다. 또한 선분(고객번호 + 시작일시 + 종료일시)의 중복도 해결해야 한다. 아래의 그림을 보자.
사용자 삽입 이미지

위의 경우 구간1과 구간2의 시작점은 다르므로 점의 중복은 없다. 하지만 선분이 겹치므로 구간을 3등분해야 한다. 따라서 Insert가 추가로 발생한다. 위의 그림은 하나의 경우만 나타낸 것이다. 하지만 구간2가 왼쪽 혹은 오른쪽으로 이동되어 겹치는 구간이 달라질 수 있으므로 각각의 경우에 처리하는 SQL이 달라질 수 있다.


역정규화를 했을 때 정합성 보정 프로그램은 동시성 제어(원본소스를 변경시키는 update, 변경이력에 insert, 변경이력의 종료일자에 update)를 하여 one transaction으로 관리하더라도 필요하다. 급한 경우 프로그램을 통하지 않고 직접 DBinsert를 날릴 수 있고, 이때는 작업자가 실수 할 수 있기 때문이다. 어떠한 실수가 있더라도 정합성을 보정하는 프로그램이 있다면 데이터 품질을 유지할 수 있다.

 

결론: 습관적인 종료일자의 추가는 위험하다

결과적으로 표의 결과는 간발의 차이로 나쁨 2개인 시작일자만 관리하자는 측의 승리이다. 이제부터 역정규화를 할 때는 표의 항목을 비교해보고 많은 고민을 해야 한다. 왜냐하면 역정규화의 장점보다 단점이 더 클수 있으며 데이터의 정합성(품질)은 성능이나 개발생산성과 바꿀 수 있는 성격이 아니기 때문이다.

 

변경이력을 실시간으로 조회하는 온라인 프로그램이 많고 조회빈도수도 많으므로 성능이 중요하다. 따라서 종료일자를 사용해야 한다.” 라는 주장은 사실과 다르다. 오히려 이런 경우는 종료일자의 성능상 장점이 없으므로 시작일자만 사용하면 된다. 또한 변경이력을 full table scan하는 대용량 배치프로그램의 성능이 느리다고 무작정 종료일자를 추가해서는 안 된다. 그 배치프로그램이 종료일자를 사용하는 경우보다는 느려지겠지만, 속도가 목표시간 내에 들어온다면 느리다고 할 수 없다. 많은 경우에 배치프로그램은 늦은 저녁에 시작하여 다음날 새벽 6시까지 끝나면 된다.

 

반대로 온라인 프로그램이 아닌 대용량 배치프로그램의 성능이 매우 중요한 경우(example: 대금청구 시스템)이고 속도가 느리다면 표에 나타난 다른 항목을 희생해서라도 역정규화를 고려할 수 있다. SQL의 길이가 길어지므로 종료일자를 추가하자는 주장은 장단점을 비교하여 역정규화 할 수 있다. 예를 들어 정합성이 틀어질 위험이 있고, 역정규화에 의한 추가적인 노력(비용) 들더라도 SQL 실력이 약한 신입개발자가 과반수라면 종료일자를 고려해야 한다. 하지만 이경우에도 '아주 복잡한 정합성 보정 프로그램을 SQL 실력이 약한 신입이 개발할 수 있을까?' 라는 의문은 남는다. 쉬운 SQL을 사용하려다 보니 더욱 어려운 SQL을 만날 수 있다는 말이다.

 

PS

표를 만드는 동안 양측(시작일자만 관리 VS 종료일자도 관리)의 집중 견제를 받았다.

 

종료일자를 사용해야 한다는 측의 주장

원래는 표에 우수나쁨만 있었는데 중간이라는 것이 생겼다. 종료일자를 관리해야 한다는 측의 주장에 따라 중간시점의 조회성능은 나쁨이 아니라 중간으로 바뀌었다. 원래는 상대적으로 불리하면 나쁨이라 표시하고 유리하면 우수로 표시 했었다. 또한 조회빈도수를 추가했다. 조회빈도수를 추가하지 않으면 시작일자만 관리한다는 측이 유리해 보인다는 것 이었다. 또 다른 의견으로는 추가적인 노력(비용)이 증가하는 것은 원래는 아래의 세 개의 항목으로 나타내었다.

1. Source가 변경되면 변경이력에 update 하는 프로그램을 추가로 작성해야 한다.

2. Source가 자주 변경된다면 그 Update가 부하가 될 수 있다.

3. 역정규화에 의한 정합성 보정 프로그램을 추가로 작성해야 한다.

 

이렇게 세 항목으로 구분하여 우수나쁨으로 나타내었지만 노력(비용)이 증가하는 것하나의 항목으로 나타내 달라고 주장했다. 세 항목이 전부 나쁨으로 표시되면 불리하게 보일 수 있으므로 하나의 항목으로 나타내자는 것 이었다. 받아들였다. 마지막 주장은 조회시점 별 성능항목 세가지를 인덱스를 사용할 때의 성능항목 하나로 바꾸고 성능의 안정성(시점 별로 성능이 좌지우지 되는지)을 추가하자는 의견이 있었으나 받아들이지 않았다. 바꾸어 보아도 성능의 안정성은 나쁨이 될 것이기 때문이다.  

시작일자만 사용해도 된다는 측의 주장

시작일자만 관리하자는 측도 가만히 보고 있진 않았다. SQL의 복잡성 항목에 나쁨대신에 중간으로 바꿔달라고 했다. 이 정도면 복잡한 정도는 아니고 길이만 조금 길어진다는 것이었다. 받아들이지 않고 그냥 나쁨으로 두었다. 또한 FTS를 동반하는 대용량 배치에서 나쁨이 아니라 중간으로 하자는 주장도 만만치 않았다. 1억건 단위의 FTS와 조인 그리고 18천만건의 Sort가 고작 11분 걸렸는데 그것의 성능이 나쁜 것은 아니라는 것이었다. 배치가 매우 중요하여 초를 다투는 상황이라 하더라도 튜닝의 여지가 있으므로 중간으로 하자는 의견도 있었다. 이 두가지 의견은 받아들이지 않았다. 왜냐하면 일단 성능이 두 배 이상 느리고, 튜닝을 하자는 의견은 종료일자 + 시작일자의 단점인 오래된 데이터를 조회할 때에도 똑같이 튜닝으로 해결 할 수 있다. 위의 표는 튜닝을 하자는 관점이 아니라 장단점을 나타내는 관점이다. 마지막으로 주장한 것이 중요도 항목이다. 데이터 정합성(품질)은 성능이나 SQL 복잡성 보다 훨씬 중요하다는 것이었다. 이것은 받아들였다.


마지막으로 의견을 제시하신 양측 분들께 감사 드린다. 양측의 주장을 모두 조율하였지만 그래도 양측의 불만은 여전히 존재할 것이다. 어쩔 수 없는 일이다. 양측의 주장이 워낙 강하다 보니 이제는 블로그의 글을 내 논리대로 쓰지 못하는 시기가 온 것 같다.



저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. 선분이력 2010.12.03 18:20 신고  댓글주소  수정/삭제  댓글쓰기

    "결과적으로 표의 결과는 간발의 차이로 ‘나쁨’이 2개인 시작일자만 관리하자는 측의 승리다"???

    < 의문점 1 >
    중간 시점의 값 조회시 성능 --> 중간
    오래된 시점의 값 조회시 성능 --> 나쁨

    시작일자, 종료일자를 둘 다 관리하더라도 시작일자만 관리할 때랑 똑같은 방식으로 이력을 조회할 수 있고, 그렇다면 중간 시점이나 오래된 시점을 조회할 때 성능이 나쁘지 않습니다. 앞서 말씀드린 사항이고, 오동규님도 그 점을 인정하셨는데 다시 나쁘다고 평가하셨군요.

    < 의문점 2 >
    정합성(데이터 품질)을 보장하는가? --> 나쁨
    추가적인 비용(노력)이 얼마나 드는가? --> 나쁨

    "추가적인 비용(노력)"이라고 한 부분이 "정합성을 보장"하기 위한 비용(노력)이므로 이 둘을 따로 떼어서 나쁨을 2개로 만드신 것은 공평하지 않게 느껴집니다.

    < 결론 >
    위 2가지 사항을 보정하고 나면 아래와 같이 역전되는 걸요.

    시작일자만 관리의 "나쁨"은 2개
    종료일자도 관리의 "나쁨"은 1개

    그러면 간발의 차이로 ‘나쁨’이 1개인 "종료일자도 관리하자"는 측의 승리인가요? ㅎㅎ
    암튼 수고하셨고, 잘 읽었습니다. 판단은 독자들의 몫으로...

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.12.05 04:31 신고  댓글주소  수정/삭제

      선분이력님 반갑습니다.

      <의문점1>
      제가 인정하지 않았으면 단점이 되고 인정했다면 단점이 안되는 것은 아무래도 이상한 것 같습니다. 제가 대안(튜닝방법)을 인정했다고 해서 종료일자+시작일자 인덱스를 이용하여 between으로 사용하는 방법의 단점이 없어지는것은 아닙니다.

      제가 이글의 마지막 부분에서도 말씀드렸지만 튜닝을 하면 미래에 성능을 향상시킬 수 있습니다만 그러면 단점을 가리는 격이 됩니다. 현실도 그게 아니라는 것을 증명합니다.
      1. 같은 화면에서 인덱스를 시점에 따라 동적으로 바꿔서 사용하는 튜너는 0%에 가까울 것입니다.
      2.현실에서는 중간시점과 오래된 시점을 조회할 목적으로 화면을 따로 만들지 않습니다. 종료일자가 pk 인덱스에 포함된 경우는 두번째 글에서 제가 제시한 rowid를 활용한 SQL을 사용하지 않고 그냥 종료일자와 시작일자로 between을 사용합니다. 이것이 현실입니다.

      제가 처음으로 제시한 방법을 많은 사람들이 사용하고 있을 가능성은 별로 없을것 입니다. 앞으로 많은 사람들이 적용하길 기대합니다.

      <의문점2>
      "데이터의 품질을 보장하는가?"는 선분이력님이 말씀하신 것 입니다. 즉 벤더사에 개발해 달라고 부탁 하셨던 기능(Constraint)입니다. 데이터의 정합성을 100% 보장할 수 있는 기능입니다. 종료일자를 key로 사용했다면 이런 기능은 없습니다.

      두번째는 추가적인 코딩량 증가(개발생산성 감소)입니다. "추가적인 노력이 얼마나 드는가?"는 일반적인 거래나 업무처리의 목적으로 만든프로그램 이외에 추가적으로 개발해야 하는 노력(코딩량)을 나타낸 것입니다. 즉 종료일자를 사용하면 추가적인 노력이 들므로 개발생산성이 떨어진다는 것입니다.

      Constraint 존재유무와 추가적인 코딩량 증가(개발생산성 감소)는 전혀 다른것 입니다. 공정하지 않다고 하셨지만 만약 추가적인 노력을 뺀다면 개발생산성을 나타내는 'SQL의 복잡성' 항목도 같이 빠져야 합니다. 그래야 공정할 것입니다. 공정하게 같이 뺀다면 2:1로 시작일자만 관리한다는 측의 승리 입니다.

      의문이 풀리셨으면 합니다.

      선분이력님이 공정하지 않다고 하셨지만 양측의 의견을 약 일주일간 조율 했습니다. 시작일자측에게 더 혹독하게 조율했습니다. 그래서 시작일자만 관리하자는 측의 불만이 더 많습니다. 제가 공정한지 아닌지 모든 것은 독자들이 판단하실 것 입니다.


      ----------------------------------------------------------------------------------------------------
      답변은 끝났습니다. 제가 궁금한 것이 있어 선분이력님 뿐만 아니라 모든 분들께 질문을 드려 봅니다.

      이상한 것은 1부와 2부에서 인덱스를 사용하는 경우에 대하여 논의했는데 그 결과를 종료일자를 사용하자는 측에서는 인정하려 들지 않는다는 것입니다. 왜그런지 모르겠습니다.

      "종료일자 + 시작일자 인덱스를 between으로 사용하는 것은 시작일자만 사용하는것 보다 빠르지 않고 오히려 특정 시점에서는 느리다."를 종료일자를 관리하자는 측에서 아무도 인정하지 않더군요. 그래서 내가 다시 물어 보았습니다. 증명이 되지 않았냐고 말입니다. 답변은 증명이 된게 아니라고 하더군요. 인정하지 않는 이유는 아래의 아래의 3개 였습니다.

      1."인덱스 힌트를 동적(Dynamic SQL)으로 바꾸어 종료일자 + 시작일자 대신에 시작일자 + 종료일자 인덱스를 사용하면 되지"
      2."오래된 시점은 화면을 따로 개발하면 되지"
      3."오동규님이 제시하신 SQL을 사용하면 되지"

      위의 세가지 이유로 인정하지 않는것은 논리적인 오류입니다. 즉 종료일자를 between으로 사용하는것이 느리다고 증명(인정)이 되었으므로 위의 3가지 방법을 사용하려는 것입니다.

      이 현상은 종료일자를 지지하는 측에서만 나타납니다. 시작일자만 관리하자는 측에서는 시작일자 인덱스의 단점 두가지를 인정합니다.
      "배치에서는 확실히 느려지는 군"
      "betweeb을 사용할 때보다 복잡해 보이는 군"

      블로그의 독자, 이메일로 의견을 주고 받은분들, 그리고 사내의 컨설턴트들에 이르기 까지 이런 현상은 광범위 하게 나타납니다. 혹시 왜 이런 현상이 나타나는지 이유를 아시는 분이 있으면 저에게 알려주시기 바랍니다. 심리학을 아시거나 전공하신 분이 있으면 좋겠습니다. 그래야 정확히 원인을 밝히고 다음번 글을 쓸때 더 공감을 받을 수 있겠죠.

      제가 이런 질문을 드리는 이유는 이 글을 쓰며 느꼈던 점 때문입니다. "종료일자의 장점이 없다는 것을 증명을 하면 할 수록 종료일자를 선호하던 사람들은 더욱 종료일자를 사랑하게 된다." 는 이상한 현상 때문입니다.

      감사합니다.

  2. 2010.12.10 11:38  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.12.10 14:26 신고  댓글주소  수정/삭제

      그런 것이 있었네요. 이 상황과 거의 흡사합니다.
      좋은 정보 감사합니다.

    • Ejql 2011.01.17 16:28 신고  댓글주소  수정/삭제

      심리는 모르겠지만. 종말론을 믿는 종교 얘기를 하나 하겠습니다. 예전에 1999년 종말론자들은. 1999년 12월 31일에 어느 빌등 옥상에 모두 모였습니다. 종말이 다가오니 신께서 우리를 데려가신다는 아주 확고한 믿음으로 인해서요.
      그러나. 1999년 12월말... 밤12시가.. 지나고. 아침이 되었습니다. 당연히 종말은 없었고 사회이슈가 되었던 종말예언은 가짜로 판명이 난것이 확실했습니다. 그러나. 누구하나 종말론자들의 교주한테 달려드는 사람이 없었습니다. 오히려.. 그때 상황을 본사람들의 얘기를 듣자면. 더 확고히 종말론을 믿고 내려왔다는 것입니다.

      나중에 심리학자가 내 놓은 답은 " 종말이 오지 않은 현실을 알았지만. 심리적으로 그것을 인정하게되면 자기의 자아(?)가 붕괴되고 사회적지위가 떨어지기 때문에 더욱 집합을하고 종말론의 끈을 놓지않기 위해서 이유를 찾고 심리적으로 그렇게 되었다"라고 합니다.

      물론 비교대상은 아니지만, 그러한 비슷한 심리가 아닌가 합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.17 17:55 신고  댓글주소  수정/삭제

      반갑습니다.
      결국 자기 만족을 위한 것이군요.
      결국 관성의 법칙과 비슷하구요.
      생각해보니 자연스러운 현상인듯 합니다.
      저도 그런적이 있습니다.
      좋은 말씀 감사드립니다.

  3. Favicon of http://blog.naver.com/bluepupils BlogIcon 김기창 2011.01.27 22:10 신고  댓글주소  수정/삭제  댓글쓰기

    논쟁이 어느 정도는 끝난 거 같네요. 열정이 부럽네요…

    모든 사람의 관점을 만족시키기는 힘들지만 선분이력에 대해 전문적으로 접근했다고 생각해요.
    위의 표가 판단의 중요한 가이드가 될 거 같아요.
    아마 무턱대고 종료일자를 사용하는 경향은 줄어들 거 같습니다.

    제 책에도 이력 엔터티의 성능을 간단하게 설명했는데요. 성능을 논할 위치는 아니라서 일반적인 내용으로요...
    하지만 또 다른 중요한 포인트는 이력 데이터에 대한 정의라서 제 블로그는 아니지만 간단하게 글을 쓰려고요.

    내역과 이력을 어느 정도 구분해야 되는데 이걸 혼동해서 엔터티의 성격이 애매해지는 경우가 많습니다.
    물론 진짜 애매한 경우가 있어서 이력 관리가 힘든데요. 오래 연구하고 있는 분야인데 어려운 거 같습니다.

    기본적으로 발생해서 그냥 쌓이는 데이터는 내역으로 정의해야 되고요.
    이미 쌓여 있는 데이터가 변경돼야 이력 데이터라고 생각합니다.

    예를 들어 주문내역 같이 그냥 발생한 데이터에 종료일자를 사용하면 안 됩니다.
    종료일자 없이 시작일자 대신 발생일자, 생성일자 등으로 사용하는 게 명확하고요.

    발생과 변경의 판단 기준은 해당 엔터티입니다. 해당 엔터티가 [주문]이면 주문한 게 변경돼야 이력 데이터고요. 고객이 상품을 바꿔서 주문한 것이라 확대하면 안 되고요. 이는 식별자가 기준이라는 얘기와 같습니다. 식별자에 종속된 데이터가 변경되느냐로 판단하면 됩니다.

    제 책에도 각종 종속성을 많이 설명했는데, 결국 모델링은 종속성을 따지는 작업이라 생각됩니다.

    이력은 어쨌든 글로 설명하기 힘든데요(말로도 잘 설명이 안 됨).
    성능 문제는 요건에 따라, 환경이나 전략에 따라 판단해야 하는 중요한 문제고요.
    이력 데이터를 정의하는 것은 그 자체로 중요한 문제라고 생각해서 간략하게 적었습니다.

  4. Favicon of http://www.yousungc.pe.kr BlogIcon 정유성 2012.01.02 13:22 신고  댓글주소  수정/삭제  댓글쓰기

    잘 보았습니다. 많은 도움이 되었어요.
    감사합니다 ^^