일반적인 의견
흔히 Sort Merge Join에 대해 다음과 같이 이야기 한다. “조인에 참여하는 양측 집합에 Sort가 발생하므로 대용량 집합간의 조인에는 불리하다. 그러나 조인 되는 양쪽 집합에 적절한 인덱스가 있다면 Sort가 발생되지 않으므로 성능이 좋다.일견 일리가 있는 말이다. 하지만 이 정도는 튜닝에 입문하는 단계에서 언급되는 정도일 뿐이다. 2단을 외운다고 해서 구구단을 모두 안다고 할 수는 없다. 튜닝에 입문하는 사람과는 반대로 경력이 있는 사람들은 좀더 구체적인 사실들을 알고 있다. Sort Merge Join과 관련된 튜닝을 많이 해보고, Merge Join에 대해 여기저기 튜닝서적들을 탐독한다. 그 결과 다음과 같은 섣부른 결론을 내리는 사람이 많이 있다.

 

1. 양쪽 집합이 Full Table Scan을 사용하면 조인순서에 상관없이 일량이 동일하므로 처리시간도 동일하다.

2. 조인순서에 상관없이 Sort량은 동일하다.

3. 부분범위처리가 안 된다.

4. Full Scan이 발생하면 인덱스를 사용할 수 없으므로 항상 Sort 작업을 동반한다.

5. Sort Merge Join 대신 Cartesian Merge Join이 나오면 조인조건이 빠진 악성 SQL이다.

6. 조인컬럼 기준으로 Sort되므로 Order by절과 조인 컬럼이 일치해야만 Sort가 발생하지 않는다.

 

완벽하지 않거나 잘못된 결론

혹시 당신도 Sort Merge Join에 대해서 1~6번이 옳다고 생각하는가? 위의 List는 깊이 고민해 보지 않고 내린 결론이다. 물론 1~6 번이 옳은 경우도 있다. 하지만 그것은 잘못된 것 혹은 완벽하지 않은 결론이다. 왜냐하면 간단한 테스트로 1~6번이 잘못된 개념임을 증명하거나, 1~6번에 해당하지 않는 경우를 증명할 수 있기 때문이다. 지금부터 시작해 보자.

 

먼저 실습용 테이블을 생성한다.

<환경: Oracle 11.2.0.1>

 

CREATE TABLE SALES_T AS SELECT * FROM SALES;

 

 

1. 조인순서에 상관없이 처리시간이 동일할까?

 

ALTER SYSTEM FLUSH BUFFER_CACHE; 

  

SELECT /*+ leading(s) full(p) full(s) use_merge(p) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_name = 'CD-R with Jewel Cases, pACK OF 12'

   AND p.prod_id = s.prod_id ;

 

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

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

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

|   0 | SELECT STATEMENT    |          |      1 |  22189 |00:00:07.74 |    4447 |   4439 |          |

|   1 |  MERGE JOIN         |          |      1 |  22189 |00:00:07.74 |    4447 |   4439 |          |

|   2 |   SORT JOIN         |          |      1 |    590K|00:00:04.75 |    4440 |   4433 |   43M (0)|

|   3 |    TABLE ACCESS FULL| SALES_T  |      1 |    918K|00:00:01.23 |    4440 |   4433 |          |

|*  4 |   SORT JOIN         |          |    590K|  22189 |00:00:01.29 |       7 |      6 | 2048  (0)|

|*  5 |    TABLE ACCESS FULL| PRODUCTS |      1 |      1 |00:00:00.01 |       7 |      6 |          |

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

 

Predicate Information (identified by operation id):

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

   4 - access("P"."PROD_ID"="S"."PROD_ID")

       filter("P"."PROD_ID"="S"."PROD_ID")

   5 - filter("P"."PROD_NAME"='CD-R with Jewel Cases, pACK OF 12')

 

Sales_t 집합을 선행으로 실행하니 Scan한 블록수는 4447 이며 Sort량은 43M + 2048 이다. 그리고 처리시간은 7 74이다. 그리고 조인시도(Merge)횟수는 59만 번이다. 그러면 이제 조인 순서만 바꿔보자. 과연 처리시간이 동일 할까?

 

ALTER SYSTEM FLUSH BUFFER_CACHE; 

 

SELECT /*+ leading(p) full(p) full(s) use_merge(s) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_name = 'CD-R with Jewel Cases, pACK OF 12'

   AND p.prod_id = s.prod_id ;

 

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

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

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

|   0 | SELECT STATEMENT    |          |      1 |  22189 |00:00:02.52 |    4447 |   4439 |          |

|   1 |  MERGE JOIN         |          |      1 |  22189 |00:00:02.52 |    4447 |   4439 |          |

|   2 |   SORT JOIN         |          |      1 |      1 |00:00:00.03 |       7 |      6 | 2048  (0)|

|*  3 |    TABLE ACCESS FULL| PRODUCTS |      1 |      1 |00:00:00.03 |       7 |      6 |          |

|*  4 |   SORT JOIN         |          |      1 |  22189 |00:00:02.44 |    4440 |   4433 |   43M (0)|

|   5 |    TABLE ACCESS FULL| SALES_T  |      1 |    918K|00:00:01.25 |    4440 |   4433 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - filter("P"."PROD_NAME"='CD-R with Jewel Cases, pACK OF 12')

   4 - access("P"."PROD_ID"="S"."PROD_ID")

       filter("P"."PROD_ID"="S"."PROD_ID")

 

일량이 같은데 수행시간은 세배이상 빠르다. 그 이유는?

조인 순서를 바꾸어 Products를 선행집합으로 Sort Merge Join을 해도 Scan한 블록 수와 Sort량은 완전히 같다. 일량이 같으므로 처리시간도 같을 것으로 생각해서는 안 된다. 처리시간이 2 52로 무려 세배이상 빨라졌다. 그 이유는 조인시도(Merge)횟수가 단 한번이기 때문이다. 이와는 대조적으로 Sales_t가 선행집합인 경우는 Merge 횟수가 무려 59만 번에 이르므로 성능이 느릴 수 밖에 없는 것이다. 그러므로 다음과 같은 결론을 낼 수 있다.

 

양쪽 집합이 Full Table Scan을 사용하면 조인순서에 상관없이 일량이 동일하므로 처리시간도 동일하다 ( X )

-->일량은 동일하더라도 Merge 횟수가 달라지면 처리시간이 달라진다 ( O )

 

 

2. 조인순서에 상관없이 Sort량이 동일할까?

 

실습을 위해 테이블을 두 개 만든다.

 

CREATE TABLE TAB1 NOLOGGING AS

SELECT ROWNUM AS SALES_NO, A.* FROM SALES A;

 

CREATE INDEX IDX_TAB1_01 ON TAB1 (PROD_ID, SALES_NO); 

 

CREATE TABLE TAB2 NOLOGGING AS

SELECT A.*, B.SEQ

  FROM TAB1 A,

       (SELECT LEVEL AS SEQ

          FROM DUAL

       CONNECT BY LEVEL <= 5) B  ;

    

CREATE INDEX IDX_TAB2_01 ON TAB2 (PROD_ID, SALES_NO, SEQ);

 

SELECT /*+ LEADING(A) INDEX(A)  INDEX(B) USE_MERGE(B) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22  ;

 

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

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

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

|   0 | SELECT STATEMENT              |             |      1 |  17205 |00:00:00.22 |   17355 |          |

|   1 |  MERGE JOIN                   |             |      1 |  17205 |00:00:00.22 |   17355 |          |

|   2 |   TABLE ACCESS BY INDEX ROWID | TAB1        |      1 |   3441 |00:00:00.01 |      94 |          |

|*  3 |    INDEX RANGE SCAN           | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      14 |          |

|*  4 |   SORT JOIN                   |             |   3441 |  17205 |00:00:00.16 |   17261 | 1054K (0)|

|   5 |    TABLE ACCESS BY INDEX ROWID| TAB2        |      1 |  17205 |00:00:00.11 |   17261 |          |

|*  6 |     INDEX RANGE SCAN          | IDX_TAB2_01 |      1 |  17205 |00:00:00.02 |      56 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("A"."PROD_ID"=22)

   4 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   6 - access("B"."PROD_ID"=22)

 

건수가 적은 tab1을 선행집합으로 하여 실행하였다. 선행집합은 적절한 인덱스 덕분으로 Sort가 발생하지 않았다. 하지만 후행집합 tab2 17205건을 Sort하여 1054K PGA를 사용하였다. 이제 선행집합을 바꿔서 실행하여 Sort량이 같은지 검증해보자.

 

SELECT /*+ LEADING(B) INDEX(A)  INDEX(B) USE_MERGE(A) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22  ;

 

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

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

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

|   0 | SELECT STATEMENT              |             |      1 |  17205 |00:00:00.34 |   17354 |          |

|   1 |  MERGE JOIN                   |             |      1 |  17205 |00:00:00.34 |   17354 |          |

|   2 |   TABLE ACCESS BY INDEX ROWID | TAB2        |      1 |  17205 |00:00:00.15 |   17263 |          |

|*  3 |    INDEX RANGE SCAN           | IDX_TAB2_01 |      1 |  17205 |00:00:00.04 |      58 |          |

|*  4 |   SORT JOIN                   |             |  17205 |  17205 |00:00:00.08 |      91 |83968  (0)|

|   5 |    TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |   3441 |00:00:00.01 |      91 |          |

|*  6 |     INDEX RANGE SCAN          | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      12 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("B"."PROD_ID"=22)

   4 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   6 - access("A"."PROD_ID"=22)

 

Sort Merge Join도 조인순서가 중요하다

선행집합을 tab2 로 바꾸어 실행하였다. 이번에는 tab1 3441건만 Sort하였으므로 PGA 83K 만 사용하였다. Tab2 Sort하는 경우는 PGA 1054K 나 사용하였으므로 12배 이상 차이가 난다. 다시 말해, 적절한 인덱스가 존재하는 경우는 Filtering 된 건수가 적은 집합을 후행집합으로 하는 것이 Sort의 부하를 줄일 수 있다.

 

하나는 안다. 하지만 둘은?

Sort의 부하를 12배 이상 줄였으므로 만족해서는 안 된다. Sort량이 극적으로 줄어도 속도는 오히려 떨어질 수 있다. 위의 실행계획 두 가지의 처리속도를 비교해보면 Sort량이 많은 것이 오히려 더 빠르다. 그 이유는 건수가 적은 집합을 후행으로 놓으면 선행집합이 건수가 많아지므로 Merge 시도횟수가 증가하기 때문이다. Sort의 부하와 Merge 횟수를 모두 고려해야 최적의 튜닝을 할 수 있다.

 

튜닝의 목적이 무엇인가?

예를 들면, 배치 SQL을 튜닝할 때 응답시간을 단축시키려면 작은 집합을 선행집합으로 하여 Merge 횟수를 줄여야 한다. 이와는 반대로 Sort의 부하가 커서 multi-pass가 나오는 경우라면 작은 집합을 후행집합으로 하여 Sort의 부하를 줄여야 한다. , 응답시간 단축이냐 아니면 Sort량을 감소가 목적이냐에 따라서 튜닝방법이 달라져야 한다.

 

이 테스트를 통하여 다음을 증명해 보았다.

 

조인순서에 상관없이 Sort량이 동일하다 ( X )

-->적절한 인덱스를 사용하는 경우, Sort량은 Join 순서에 의해 달라진다 ( O )

 

참고사항: 첫 번째 조건이 참이 되려면 두 가지 전제가 필요하다. 전체범위를 처리해야 하며, 양측집합이 Full Scan인 경우에 해당한다.

 

 

3. Merge Join은 부분범위처리가 안 될까?

 

SELECT *

  FROM (SELECT /*+ LEADING(B) INDEX(A)  INDEX(B) USE_MERGE(A) */

               B.*, A.CHANNEL_ID AS  CHAN

          FROM TAB1 a, TAB2 b

         WHERE A.SALES_NO = B.SALES_NO

           AND A.PROD_ID = 22

           AND B.PROD_ID = 22

         ORDER BY B.PROD_ID, B.SALES_NO, B.SEQ )

WHERE ROWNUM <= 1 ;

 

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

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

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

|   0 | SELECT STATEMENT                |             |      1 |      1 |00:00:00.02 |      95 |          |

|*  1 |  COUNT STOPKEY                  |             |      1 |      1 |00:00:00.02 |      95 |          |

|   2 |   VIEW                          |             |      1 |      1 |00:00:00.02 |      95 |          |

|   3 |    MERGE JOIN                   |             |      1 |      1 |00:00:00.02 |      95 |          |

|   4 |     TABLE ACCESS BY INDEX ROWID | TAB2        |      1 |      1 |00:00:00.01 |       4 |          |

|*  5 |      INDEX RANGE SCAN           | IDX_TAB2_01 |      1 |      1 |00:00:00.01 |       3 |          |

|*  6 |     SORT JOIN                   |             |      1 |      1 |00:00:00.02 |      91 |83968  (0)|

|   7 |      TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |   3441 |00:00:00.02 |      91 |          |

|*  8 |       INDEX RANGE SCAN          | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      12 |          |

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

 

Predicate Information (identified by operation id):

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

   1 - filter(ROWNUM<=1)

   5 - access("B"."PROD_ID"=22)

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

       filter("A"."SALES_NO"="B"."SALES_NO")

   8 - access("A"."PROD_ID"=22)

 

 

단 한 건만 읽는다

인라인뷰 외부에서 ROWNUM <= 1 조건을 사용하자, 후행집합은 전체 건을 읽었지만 선행집합은 정확히 한 건만 읽었다. 선행집합이 전체범위로 처리되었다면 17205건을 읽었을 것이다. 즉 선행집합에 대해서는 완벽히 부분범위로 처리된다. 만약 후행집합이 몇 건 안 된다면 부분범위처리의 효율은 더욱 높아진다.

이제 Rownum 조건을 10, 100으로 변경해 가면서 실행계획을 관찰 해보자.

 

ROWNUM <= 10 조건일 때의 실행계획  

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

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

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

|   0 | SELECT STATEMENT                |             |      1 |     10 |00:00:00.02 |     105 |          |

|*  1 |  COUNT STOPKEY                  |             |      1 |     10 |00:00:00.02 |     105 |          |

|   2 |   VIEW                          |             |      1 |     10 |00:00:00.02 |     105 |          |

|   3 |    MERGE JOIN                   |             |      1 |     10 |00:00:00.02 |     105 |          |

|   4 |     TABLE ACCESS BY INDEX ROWID | TAB2        |      1 |     10 |00:00:00.01 |      14 |          |

|*  5 |      INDEX RANGE SCAN           | IDX_TAB2_01 |      1 |     10 |00:00:00.01 |       4 |          |

|*  6 |     SORT JOIN                   |             |     10 |     10 |00:00:00.02 |      91 |83968  (0)|

|   7 |      TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |   3441 |00:00:00.02 |      91 |          |

|*  8 |       INDEX RANGE SCAN          | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      12 |          |

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

 

ROWNUM <= 100 조건일 때의 실행계획 

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

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

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

|   0 | SELECT STATEMENT                |             |      1 |    100 |00:00:00.03 |     195 |          |

|*  1 |  COUNT STOPKEY                  |             |      1 |    100 |00:00:00.03 |     195 |          |

|   2 |   VIEW                          |             |      1 |    100 |00:00:00.03 |     195 |          |

|   3 |    MERGE JOIN                   |             |      1 |    100 |00:00:00.03 |     195 |          |

|   4 |     TABLE ACCESS BY INDEX ROWID | TAB2        |      1 |    100 |00:00:00.01 |     104 |          |

|*  5 |      INDEX RANGE SCAN           | IDX_TAB2_01 |      1 |    100 |00:00:00.01 |       4 |          |

|*  6 |     SORT JOIN                   |             |    100 |    100 |00:00:00.03 |      91 |83968  (0)|

|   7 |      TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |   3441 |00:00:00.02 |      91 |          |

|*  8 |       INDEX RANGE SCAN          | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      12 |          |

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

 

Rownum 조건을 10, 100으로 변경하자 tab2를 정확히 10, 100건만 읽는다. 도대체 누가 “Sort Merge Join은 부분범위처리가 안 된다라는 말을 한 것일까?

 

Sort Merge Join은 부분범위처리가 안 된다 ( X )

-->적절한 인덱스가 있다면 선행집합은 부분범위처리가 가능하다 ( O )

 

 

4. Full Scan을 하면 인덱스를 사용할 수 없으므로 항상 Sort 작업이 발생할까?

 

SELECT /*+ leading(s) full(p) full(s) use_merge(p) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_id = 119

   AND p.prod_id = s.prod_id;

 

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

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

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

|   0 | SELECT STATEMENT     |          |      1 |  22189 |00:00:00.29 |    4450 |          |

|   1 |  MERGE JOIN CARTESIAN|          |      1 |  22189 |00:00:00.29 |    4450 |          |

|*  2 |   TABLE ACCESS FULL  | SALES_T  |      1 |  22189 |00:00:00.06 |    4443 |          |

|   3 |   BUFFER SORT        |          |  22189 |  22189 |00:00:00.07 |       7 | 2048  (0)|

|*  4 |    TABLE ACCESS FULL | PRODUCTS |      1 |      1 |00:00:00.01 |       7 |          |

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

 

Predicate Information (identified by operation id):

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

   2 - filter("S"."PROD_ID"=119)

   4 - filter("P"."PROD_ID"=119)

 

Sort Join이 사라진 이유

힌트를 주어 sales_t products 모두 full table scan을 발생시켰다. 그러자 product 테이블 쪽은 buffer sort가 존재하지만 sales_t 테이블 쪽은 Sort가 사라졌다. Sort가 사라질 수 있는 이유는 product 쪽에 unique 조건(prod_id = 119)에 의해서 집합이 항상 한 건임을 보장하기 때문이다. , 집합이 한 건뿐이므로 조인이 필요 없어지는 것이다. 바로 이것이 Sort Merge Join Cartesian Merge Join 으로 바뀔 수 있는 이유이다. 반대로 이야기하면, 위의 SQL에서 unique 조건이 없다면 Cartesian Merge Join buffer sort는 결코 발생하지 않는다.

 

Full Scan이 발생하면 인덱스를 사용할 수 없으므로 항상 Sort 작업을 동반한다 ( X )

-->Full Scan이 발생해도 Unique 조건이 들어오면 Sort Join Operation이 사라진다 ( O )

 

 

5. Sort Merge Join 대신에 Cartesian Merge Join이 나오면 조인조건이 빠진 악성 SQL일까?

 

위에서 Unique 조건 때문에 Sort Merge Join Cartesian Merge Join으로 바뀐다고 했다. 이 현상은 아주 바람 직한 것이다. 왜냐하면 불필요한 Sort를 없애버리기 때문이다. 따라서 Cartesian Merge Join이라고 해서 항상 실수로 조인을 하지 않은 악성 SQL은 아니다.

 

이번에는 Unique 인덱스를 사용하는 경우를 보자.

 

SELECT /*+ leading(p) full(s) use_merge(s) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_id = 119

   AND p.prod_id = s.prod_id;

 

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

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

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

|   0 | SELECT STATEMENT     |             |      1 |  22189 |00:00:00.14 |    4444 |

|   1 |  MERGE JOIN CARTESIAN|             |      1 |  22189 |00:00:00.14 |    4444 |

|*  2 |   INDEX UNIQUE SCAN  | PRODUCTS_PK |      1 |      1 |00:00:00.01 |       1 |

|*  3 |   TABLE ACCESS FULL  | SALES_T     |      1 |  22189 |00:00:00.05 |    4443 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("P"."PROD_ID"=119)

   3 - filter("S"."PROD_ID"=119)

 

Unique 인덱스를 사용하자 Sort가 사라졌고, 심지어 Buffer Sort도 사라졌다. 따라서 성능도 최적이 되었다. 그러므로 MERGE JOIN CARTESIAN 이라는 operation 만 보고 조인절이 빠졌다거나 악성 SQL 이라고 판단해서는 안 된다.

 

만약 조인 순서가 바뀌면 buffer sort가 나타나므로 주의해야 한다. 아래의 SQL을 보자.

 

SELECT /*+ leading(s) full(s) use_merge(p) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_id = 119

   AND p.prod_id = s.prod_id;

 

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

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

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

|   0 | SELECT STATEMENT     |             |      1 |  22189 |00:00:00.28 |    4444 |          |

|   1 |  MERGE JOIN CARTESIAN|             |      1 |  22189 |00:00:00.28 |    4444 |          |

|*  2 |   TABLE ACCESS FULL  | SALES_T     |      1 |  22189 |00:00:00.06 |    4443 |          |

|   3 |   BUFFER SORT        |             |  22189 |  22189 |00:00:00.07 |       1 | 2048  (0)|

|*  4 |    INDEX UNIQUE SCAN | PRODUCTS_PK |      1 |      1 |00:00:00.01 |       1 |          |

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

 

Predicate Information (identified by operation id):

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

   2 - filter("S"."PROD_ID"=119)

   4 - access("P"."PROD_ID"=119)

 

카테시안 조인도 순서대로 실행해야 한다

Buffer sort 뿐만 아니라 merge 횟수도 22189번이나 시도되어 성능이 저하되었다. 위의 실행계획에서 볼 수 있듯이 CARTESIAN MERGE JOIN 도 조인의 순서가 중요하므로 실행계획을 유심히 살펴야 한다.

 

카테시안 조인의 발생조건

Unique 컬럼에 조건이 Equal로 들어오면 옵티마이져가 성능향상을 위해서 조인절을 삭제한다. 만약 Unique 컬럼이라도 Equal 조건이 아니라 Range 조건이라면 위의 CARTESIAN MERGE JOIN 실행계획이 나타나지 않는다. 아래의 SQL이 그것을 증명한다.

 

SELECT /*+ leading(s) full(s) use_merge(p) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_id >= 119

   AND p.prod_id < 120

   AND p.prod_id = s.prod_id;

  

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

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

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

|   0 | SELECT STATEMENT    |             |      1 |  22189 |00:00:00.35 |    4441 |          |

|   1 |  MERGE JOIN         |             |      1 |  22189 |00:00:00.35 |    4441 |          |

|   2 |   SORT JOIN         |             |      1 |  22189 |00:00:00.16 |    4440 | 1117K (0)|

|*  3 |    TABLE ACCESS FULL| SALES_T     |      1 |  22189 |00:00:00.06 |    4440 |          |

|*  4 |   SORT JOIN         |             |  22189 |  22189 |00:00:00.08 |       1 | 2048  (0)|

|*  5 |    INDEX RANGE SCAN | PRODUCTS_PK |      1 |      1 |00:00:00.01 |       1 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - filter(("S"."PROD_ID">=119 AND "S"."PROD_ID"<120))

   4 - access("P"."PROD_ID"="S"."PROD_ID")

       filter("P"."PROD_ID"="S"."PROD_ID")

   5 - access("P"."PROD_ID">=119 AND "P"."PROD_ID"<120)

 

카테시안 조인이 더 빠르다

비록 SQL의 결과는 같지만 sort join operation에 의해서 PGA를 소모한다. where절의 prod_idequal 조건이냐 아니면 Range조건이냐에 따라서 성능이 좌우된다. , 성능이 나쁜 Sort Merge Join으로 풀리느냐 아니면, 추가적인 Sort가 없어서 성능이 우수한 CARTESIAN MERGE JOIN으로 풀리느냐는 where 조건에 따라 좌우된다. Unique 컬럼에 = 조건인지 아닌지에 따라 Sort의 부하가 좌우되는 것이다.

 

만약 Unique 컬럼에 = 조건이 들어오면 옵티마이져가 hash join을 선택하는 경우가 있을까?

 

SELECT /*+ leading(p) use_hash(s) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_id = 119

   AND p.prod_id = s.prod_id;

 

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

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

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

|   0 | SELECT STATEMENT   |             |      1 |   22189 |00:00:00.10 |    4444 |

|   1 |  NESTED LOOPS      |             |      1 |   22189 |00:00:00.10 |    4444 |

|*  2 |   INDEX UNIQUE SCAN| PRODUCTS_PK |      1 |       1 |00:00:00.01 |       1 |

|*  3 |   TABLE ACCESS FULL| SALES_T     |      1 |   22189 |00:00:00.06 |    4443 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("P"."PROD_ID"=119)

   3 - filter("S"."PROD_ID"=119)

 

Hash join은 실행할 수 없다

Unique 컬럼에 = 조건이 들어오면 결코 hash join을 선택하지 않는다. 강제로 힌트를 사용해도 merge join이나 nested loop join을 선택한다. 왜냐하면 Hash Join은 반드시 Equal Join이 필요한데, 조인절이 삭제되어 hash join이 발생될 수 없기 때문이다.

 

Sort Merge Join 대신 Cartesian Merge Join이 나오면 조인조건이 빠진 악성 SQL이다 ( X )

-->Unique 조건이 Equal로 들어오고 같은 컬럼으로 조인하면 옵티마이저는 성능향상을 위해 조인절을 삭제한다 ( O )

 

 

6. 조인컬럼 기준으로 Sort되므로 Order by절과 조인 컬럼이 일치할 때만 Sort가 발생되지 않는다. 정말 그럴까?

 

Sort의 기준이 조인컬럼이라는 말이 항상 참일까? 아래의 SQL을 보자.

 

SELECT /*+ LEADING(B) FULL(A)  FULL(B) USE_MERGE(A) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22

 ORDER BY B.PROD_ID, B.SALES_NO, B.SEQ ;

 

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

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

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

|   0 | SELECT STATEMENT     |      |      1 |  17205 |00:00:03.34 |   32327 |          |

|   1 |  SORT ORDER BY       |      |      1 |  17205 |00:00:03.34 |   32327 | 1180K (0)|

|   2 |   MERGE JOIN         |      |      1 |  17205 |00:00:03.29 |   32327 |          |

|   3 |    SORT JOIN         |      |      1 |  17205 |00:00:02.62 |   27257 | 1054K (0)|

|*  4 |     TABLE ACCESS FULL| TAB2 |      1 |  17205 |00:00:02.53 |   27257 |          |

|*  5 |    SORT JOIN         |      |  17205 |  17205 |00:00:00.59 |    5070 |83968  (0)|

|*  6 |     TABLE ACCESS FULL| TAB1 |      1 |   3441 |00:00:00.52 |    5070 |          |

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

 

Predicate Information (identified by operation id):

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

   4 - filter("B"."PROD_ID"=22)

   5 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   6 - filter("A"."PROD_ID"=22)

 

Order by 절에 조인컬럼(SALES_NO) 이외의 것들이 있으므로 SORT ORDER BY operation이 추가로 발생하여 성능이 저하되었다. 이제 조인컬럼으로만 order by를 해보자.

 

 

SELECT /*+ LEADING(B) FULL(A)  FULL(B) USE_MERGE(A) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22

 ORDER BY B.SALES_NO;

 

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

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

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

|   0 | SELECT STATEMENT    |      |      1 |  17205 |00:00:02.69 |   32331 |          |

|   1 |  MERGE JOIN         |      |      1 |  17205 |00:00:02.69 |   32331 |          |

|   2 |   SORT JOIN         |      |      1 |  17205 |00:00:02.49 |   27257 | 1054K (0)|

|*  3 |    TABLE ACCESS FULL| TAB2 |      1 |  17205 |00:00:02.41 |   27257 |          |

|*  4 |   SORT JOIN         |      |  17205 |  17205 |00:00:00.11 |    5074 |83968  (0)|

|*  5 |    TABLE ACCESS FULL| TAB1 |      1 |   3441 |00:00:00.04 |    5074 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - filter("B"."PROD_ID"=22)

   4 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   5 - filter("A"."PROD_ID"=22)

 

참고사항으로 알아두자. Order by절에 prod_id가 추가되어도 위의 실행계획은 같다. 왜냐하면 prod_id는 상수 22로 고정되어 있으므로 Sort가 필요 없기 때문이다.

 

조인컬럼으로 sort를 하니 SORT ORDER BY operation이 사라져 버렸다. 얼핏 보면 Sort의 기준은 조인컬럼인 것처럼 보인다. 하지만 이 조건을 항상 만족하려면 Full Scan을 해야 한다는 전제조건이 붙어야 한다. 그러면 이제 Full Scan 대신에 인덱스를 사용해보자.

 

 

SELECT /*+ LEADING(A) INDEX(A)  INDEX(B) USE_MERGE(B) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22

 ORDER BY B.PROD_ID, B.SALES_NO, B.SEQ ;

 

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

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

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

|   0 | SELECT STATEMENT               |             |      1 |  17205 |00:00:02.21 |   17352 |          |

|   1 |  SORT ORDER BY                 |             |      1 |  17205 |00:00:02.21 |   17352 | 1117K (0)|

|   2 |   MERGE JOIN                   |             |      1 |  17205 |00:00:02.16 |   17352 |          |

|   3 |    TABLE ACCESS BY INDEX ROWID | TAB1        |      1 |   3441 |00:00:00.31 |      91 |          |

|*  4 |     INDEX RANGE SCAN           | IDX_TAB1_01 |      1 |   3441 |00:00:00.05 |      12 |          |

|*  5 |    SORT JOIN                   |             |   3441 |  17205 |00:00:01.80 |   17261 | 1054K (0)|

|   6 |     TABLE ACCESS BY INDEX ROWID| TAB2        |      1 |  17205 |00:00:01.75 |   17261 |          |

|*  7 |      INDEX RANGE SCAN          | IDX_TAB2_01 |      1 |  17205 |00:00:00.06 |      56 |          |

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

 

Predicate Information (identified by operation id):

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

   4 - access("A"."PROD_ID"=22)

   5 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   7 - access("B"."PROD_ID"=22)

 

인덱스를 사용했음에도 추가적인 Sort가 발생하는 이유

이런! 인덱스를 사용했지만, SORT ORDER BY가 발생하였다. 왜 그럴까? 인덱스를 사용할 때 Sort의 기준은 선행집합의 인덱스 컬럼이다. , 선행집합의 인덱스컬럼이 order by절에 나온다면 Sort가 발생하지 않는다. 위의 SQL에서 선행집합의 인덱스컬럼은 PROD_ID + SALES_NO 이다. 따라서 B.SEQ 컬럼 때문에 Sort가 발생한 것이다. 그러면 이제 Sort를 없애기 위하여 선행집합을 바꿔보자.

 

SELECT /*+ LEADING(B) INDEX(A)  INDEX(B) USE_MERGE(A) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22

 ORDER BY B.PROD_ID, B.SALES_NO, B.SEQ ;

 

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

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

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

|   0 | SELECT STATEMENT              |             |      1 |  17205 |00:00:03.09 |   17387 |          |

|   1 |  MERGE JOIN                   |             |      1 |  17205 |00:00:03.09 |   17387 |          |

|   2 |   TABLE ACCESS BY INDEX ROWID | TAB2        |      1 |  17205 |00:00:02.58 |   17296 |          |

|*  3 |    INDEX RANGE SCAN           | IDX_TAB2_01 |      1 |  17205 |00:00:00.14 |      91 |          |

|*  4 |   SORT JOIN                   |             |  17205 |  17205 |00:00:00.39 |      91 |83968  (0)|

|   5 |    TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |   3441 |00:00:00.32 |      91 |          |

|*  6 |     INDEX RANGE SCAN          | IDX_TAB1_01 |      1 |   3441 |00:00:00.06 |      12 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("B"."PROD_ID"=22)

   4 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   6 - access("A"."PROD_ID"=22)

 

선행집합의 인덱스 컬럼과 Order By절의 컬럼이 동일하다. 그리고 인덱스와 Order by절의 컬럼순서도 동일하다. 이 두 가지 조건을 만족하므로 추가적인 SORT ORDER BY operation이 발생하지 않았다. Order By 뿐만 아니라 Group By도 마찬가지이다. 이제 Order by Group By를 동시에 사용해보자.

 

SELECT /*+ LEADING(B) INDEX(A)  INDEX(B) NO_PLACE_GROUP_BY USE_MERGE(A) */

       B.PROD_ID, B.SALES_NO, COUNT(*)

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22

 GROUP BY B.PROD_ID, B.SALES_NO, B.SEQ

ORDER BY B.PROD_ID, B.SALES_NO ;

 

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

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

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

|   0 | SELECT STATEMENT     |             |      1 |  17205 |00:00:00.30 |      70 |          |

|   1 |  SORT GROUP BY NOSORT|             |      1 |  17205 |00:00:00.30 |      70 |          |

|   2 |   MERGE JOIN         |             |      1 |  17205 |00:00:00.25 |      70 |          |

|*  3 |    INDEX RANGE SCAN  | IDX_TAB2_01 |      1 |  17205 |00:00:00.02 |      58 |          |

|*  4 |    SORT JOIN         |             |  17205 |  17205 |00:00:00.08 |      12 |57344  (0)|

|*  5 |     INDEX RANGE SCAN | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      12 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("B"."PROD_ID"=22)

   4 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   5 - access("A"."PROD_ID"=22)

 

Order By/Group By절의 컬럼이 모두 선행집합의 인덱스 컬럼과 순서가 같으므로 추가적인 Sort가 전혀 발생하지 않았다. 따라서 다음과 같은 결론을 낼 수 있다.

 

조인컬럼 기준으로 Sort되므로 Order by절과 조인 컬럼이 일치해야만 Sort가 발생하지 않는다. ( X )

-->Full table scan일 때는 조인컬럼 기준으로 sort 되는 것이 옳다. 하지만, index를 사용한다면 조인컬럼 뿐만 아니라, 선행집합의 인덱스 컬럼과 order by/group by절을 일치시켜도 Sort가 발생하지 않는다 ( O )

 

 

결론

6가지의 오만과 편견 중에 하나라도 얻은 것이 있다면 성공이다. 다시 한번 여섯 가지를 정리하기 바란다.

 

양쪽 집합이 Full Table Scan을 사용하면 조인순서에 상관없이 일량이 동일하므로 처리시간도 동일하다 ( X )

-->일량은 동일하더라도 Merge 횟수가 달라지면 처리시간이 달라진다  ( O )

 

조인순서에 상관없이 Sort량이 동일하다 ( X )

-->적절한 인덱스를 사용하는 경우, Sort량은 Join 순서에 의해 달라진다 ( O )

 

Sort Merge Join은 부분범위처리가 안 된다 ( X )

-->적절한 인덱스가 있다면 선행집합은 부분범위처리가 가능하다 ( O )

 

Full Scan이 발생하면 인덱스를 사용할 수 없으므로 항상 Sort 작업을 동반한다 ( X )

-->Full Scan이 발생해도 Unique 조건이 들어오면 Sort Join Operation이 사라진다 ( O )

 

Sort Merge Join 대신 Cartesian Merge Join이 나오면 조인조건이 빠진 악성 SQL이다 ( X )

-->Unique 조건이 Equal로 들어오고 같은 컬럼으로 조인하면 옵티마이저는 성능향상을 위해 조인절을 삭제한다 ( O )

 

조인컬럼 기준으로 Sort되므로 Order by절과 조인 컬럼이 일치해야만 Sort가 발생하지 않는다. ( X )

-->Full table scan일 때는 조인컬럼 기준으로 sort 되는 것이 옳다. 하지만, index를 사용한다면 조인컬럼 뿐만 아니라, 선행집합의 인덱스 컬럼과 order by/group by절을 일치시켜도 Sort가 발생하지 않는다 ( O )


PS
요즘 워낙 바빠서 예전에 미리 글을 써놓지 않았더라면 글을 하나도 올리지 못할뻔 하였다. 
저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. 라튜니 2011.04.28 10:17 신고  댓글주소  수정/삭제  댓글쓰기

    병풍에 관한 포스팅 이후 다음 포스팅을 오래 기다렸는데~ 정말 좋은 내용을 포스팅 해주셨네요. 항상 좋은 내용 감사합니다. 특히 Cartesian Merge Join에 관한 내용은 새롭게 안 내용이네요. 이번 포스팅을 계기로 Sort Merge Join에 대해 정리할 수 있는 계기가 되었습니다. 감사합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.04.29 09:19 신고  댓글주소  수정/삭제

      글을 미리 써놓은 것이 다행이라고 생각합니다.^^
      한가지 걱정은 어떤 주제에 대해 정리하려다 보니 글이 너무 길어진다는 단점입니다. 위의 글도 14페이지나 됩니다. 블로그에 올리기에는 너무.....
      블로그 글이라기보다는 논문이 되버린 느낌 이네요

  2. Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.04.29 15:08 신고  댓글주소  수정/삭제  댓글쓰기

    카르테시안이란 실행계획만 봐도 경기를 일으켜야 한다고 생각했는데, 역시 오라클이 더 빠른 걸 찾아가는 거였군요, 좋은 글 감사드림니다 ^^

    참, 3번의 경우 rownum 조건에 의해 1건만 가져온 데이터가 후행 테이블에 없을 땐 당연히 다음 데이터를 가져와서 후행 테이블과 비교를 하겠죠?
    후행은 소트조인의 형태를, 그리고 선행에서 후행을 찾는 방식은 네스티드 루프랑 비슷한 형식이 되지 않을까 싶어서요.

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

      SITD님이 말씀하신대로 입니다.
      선행집합의 동작은 nested loop join과 같습니다.
      조인 두번중에 한번을 실패하게 만들어서 테스트 해보시면 정확히 알 수 있습니다.
      아래 조건을 인라인뷰 안에 넣으시고 rownum <= 100건으로 테스트 하시면 merge 횟수가 정확히 두배인것을 관찰 하실수 있을 겁니다. 한번 해보세요.

      ...생략
      AND MOD(A.SALES_NO,2) = 1 --> 추가조건
      ORDER BY B.PROD_ID, B.SALES_NO, B.SEQ )
      WHERE ROWNUM <= 100 ;

    • Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.05.03 14:24 신고  댓글주소  수정/삭제

      DB 생성 권한이 없어서, 집에가서 한번 해봐야겠네요 ^^
      답변 감사드립니다.

  3. feelie 2011.05.03 22:26 신고  댓글주소  수정/삭제  댓글쓰기

    sort merge join 의 새로운 느낌입니다. 그래서일까요 이해하기가 어렵습니다.
    sort merge join은 각 테이블에 대하여 입력조건으로 데이터를 조회하여 조인컬럼으로 각각 sort을 하고
    하나씩 비교하는 방식인것으로 알고 있습니다.
    이런 개념에 사로잡혀있어서 그런가요? 아님 제가 sort merge join을 잘못 이해하고 있는건가요?
    1. 일량은 동일하더라도 Merge 횟수가 달라지면 처리시간이 달라진다
    => Merge의 횟수는 동일할것 같고,
    2. 적절한 인덱스를 사용하는 경우, Sort량은 Join 순서에 의해 달라진다
    => 개별 처리가되니 sort 량은 동일할것 같습니다.
    참 어렵습니다..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.05.04 11:39 신고  댓글주소  수정/삭제

      승필님 반갑습니다.
      Sort Merge Join의 두 집합이 독립적으로 처리되므로 조인순서는 아무런 상관이 없다는 미신을 깨기 위해서 이글이 작성되었습니다.

      1.Merge 횟수는 선행집합이 filter 된 후의 건수에 비례합니다. 따라서 작은 집합을 선행으로 두면 merge 횟수가 감소하므로 성능이 향상됩니다. 이 성격은 nested loop join과 유사합니다.

      2.Sort를 방지하는 적절한 인덱스가 있으면 후행집합을 작은것으로 선택하면 sort가 최소화 됩니다. 왜냐하면 인덱스가 있으므로 선행집합은 sort를 전혀 하지 않기 때문입니다. sort량을 튜닝하려면 1번과는 반대로 후행집합을 작은 것으로 해야합니다.

      감사합니다.

    • feelie 2011.05.19 09:15 신고  댓글주소  수정/삭제

      볼때마다 생소하니 아직도 너무많이 부족한것 같습니다.
      2. 조인순서에 상관없이 Sort량이 동일할까 ?
      에서 후행테이블은 인덱스을 사용했지만
      소트를 수행한다.
      소트머지의 특징인가요??
      후행테이블도 인덱스를 사용하였으므로 소트가 필요없을것 같은데요..

  4. salvationism 2011.05.09 14:27 신고  댓글주소  수정/삭제  댓글쓰기

    테스트할 수 있도록 스크립도 같이 올려 주셔서 도움이 많이 되고 있습니다.
    보통 정성으로는 힘든 것인데.. ㅎㅎ
    열정 본 받고 싶습니다. ^^

  5. salvationism 2011.05.09 17:19 신고  댓글주소  수정/삭제  댓글쓰기

    5번에서 Unique 컬럼에 = 조건이 들어오면 결코 hash join을 선택하지 않는다고 하셨는데
    다음과 같이 Hash 조인으로 풀립니다.
    왜 그런지요???
    SH 스키마 테이블 입니다.

    SELECT /*+ leading(p) use_hash(s) */
    s.*, p.prod_id
    FROM sales_t s, products p
    WHERE p.prod_id = 119
    AND p.prod_id = s.prod_id;

    -----------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 106 |00:00:00.11 | 130 | 148 | | | |
    |* 1 | HASH JOIN | | 1 | 106 |00:00:00.11 | 130 | 148 | 1036K| 1036K| 348K (0)|
    |* 2 | INDEX UNIQUE SCAN| PRODUCTS_PK | 1 | 1 |00:00:00.01 | 1 | 0 | | | |
    |* 3 | TABLE ACCESS FULL| SALES_T | 1 | 106 |00:00:00.10 | 129 | 148 | | | |
    -----------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    1 - access("P"."PROD_ID"="S"."PROD_ID";)
    2 - access("P"."PROD_ID"=119)
    3 - filter("S"."PROD_ID"=119



    *************************
    Join Elimination (JE)
    *************************
    SQL:******* UNPARSED QUERY IS *******
    SELECT /*+ LEADING ("P";) USE_HASH ("S";) */ "S"."PROD_ID" "PROD_ID","S"."CUST_ID" "CUST_ID","S"."TIME_ID" "TIME_ID","S"."CHANNEL_ID" "CHANNEL_ID","S"."PROMO_ID" "PROMO_ID","S"."QUANTITY_SOLD" "QUANTITY_SOLD","S"."AMOUNT_SOLD" "AMOUNT_SOLD","P"."PROD_ID" "PROD_ID" FROM "SH"."SALES_T" "S","SH"."PRODUCTS" "P" WHERE "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    SQL:******* UNPARSED QUERY IS *******
    SELECT /*+ LEADING ("P";) USE_HASH ("S";) */ "S"."PROD_ID" "PROD_ID","S"."CUST_ID" "CUST_ID","S"."TIME_ID" "TIME_ID","S"."CHANNEL_ID" "CHANNEL_ID","S"."PROMO_ID" "PROMO_ID","S"."QUANTITY_SOLD" "QUANTITY_SOLD","S"."AMOUNT_SOLD" "AMOUNT_SOLD","P"."PROD_ID" "PROD_ID" FROM "SH"."SALES_T" "S","SH"."PRODUCTS" "P" WHERE "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    Query block SEL$1 (#0) unchanged
    PM: Considering predicate move-around in query block SEL$1 (#0)
    **************************
    Predicate Move-Around (PM)
    **************************
    PM: PM bypassed: Outer query contains no views.
    PM: PM bypassed: Outer query contains no views.
    query block SEL$1 (#0) unchanged
    FPD: Considering simple filter push in query block SEL$1 (#0)
    "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    try to generate transitive predicate from check constraints for query block SEL$1 (#0)
    finally: "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID" AND "S"."PROD_ID"=119

    FPD: transitive predicates are generated in query block SEL$1 (#0)
    "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID" AND "S"."PROD_ID"=119
    apadrv-start sqlid=14344755796783354000
    :
    call(in-use=26684, alloc=32736), compile(in-use=61628, alloc=64696), execution(in-use=2024, alloc=4060)

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

      equal 조인조건이 삭제되지 않아서 hash join이 가능한 것입니다.

      products 테이블이 문제인것 같습니다. pk constraint가 valid 하지 않습니다. 아래처럼 products_t 테이블을 만들고 새로 테스트 해보시기 바랍니다. 그럼 카테시안 조인이 발생할 것입니다.

      create table sh.products_t as select * from sh.products;

      ALTER TABLE SH.PRODUCTS_T ADD CONSTRAINT PRODUCTS_T_PK PRIMARY KEY (PROD_ID) USING INDEX;



      SELECT /*+ leading(s) full(p) full(s) use_merge(p) */
      s.*, p.prod_id
      FROM sales_t s, products_t p
      WHERE p.prod_id = 119
      AND p.prod_id = s.prod_id;

      SELECT /*+ leading(p) use_hash(s) */
      s.*, p.prod_id
      FROM sales_t s, products_t p
      WHERE p.prod_id = 119
      AND p.prod_id = s.prod_id;

      감사합니다.

  6. salvationism 2011.05.09 17:29 신고  댓글주소  수정/삭제  댓글쓰기

    4번 테스트에서 같은 버전인데 결과가 좀 다르게 나오네요.
    글 올리신 것처럼 발생하려면 JE만 발생해야 할거 같은데 FPD로 인해 join predicate가 그대로 있는거 같습니다.
    동일한 Unique 조건인데 왜 그런지 이해가 잘 안가네요.


    SQL> select * from v$version;

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE 11.2.0.1.0 Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production


    SELECT /*+ leading(s) full(p) full(s) use_merge(p) */
    s.*, p.prod_id
    FROM sales_t s, products p
    WHERE p.prod_id = 119
    AND p.prod_id = s.prod_id;

    ---------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 101 |00:00:00.05 | 127 | 151 | | | |
    | 1 | MERGE JOIN | | 1 | 101 |00:00:00.05 | 127 | 151 | | | |
    |* 2 | TABLE ACCESS FULL | SALES_T | 1 | 101 |00:00:00.03 | 123 | 148 | | | |
    |* 3 | SORT JOIN | | 101 | 101 |00:00:00.02 | 4 | 3 | 73728 | 73728 | |
    |* 4 | TABLE ACCESS FULL| PRODUCTS | 1 | 1 |00:00:00.01 | 4 | 3 | | | |
    ---------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - filter("S"."PROD_ID"=119)
    3 - access("P"."PROD_ID"="S"."PROD_ID";)
    filter("P"."PROD_ID"="S"."PROD_ID";)
    4 - filter("P"."PROD_ID"=119)

    (10053 TRACE)
    *************************
    Join Elimination (JE)
    *************************
    SQL:******* UNPARSED QUERY IS *******
    SELECT /*+ LEADING ("S";) USE_MERGE ("P";) FULL ("P";) FULL ("S";) */ "S"."PROD_ID" "PROD_ID","S"."CUST_ID" "CUST_ID","S"."TIME_ID" "TIME_ID","S"."CHANNEL_ID" "CHANNEL_ID","S"."PROMO_ID" "PROMO_ID","S"."QUANTITY_SOLD" "QUANTITY_SOLD","S"."AMOUNT_SOLD" "AMOUNT_SOLD","P"."PROD_ID" "PROD_ID" FROM "SH"."SALES_T" "S","SH"."PRODUCTS" "P" WHERE "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    SQL:******* UNPARSED QUERY IS *******
    SELECT /*+ LEADING ("S";) USE_MERGE ("P";) FULL ("P";) FULL ("S";) */ "S"."PROD_ID" "PROD_ID","S"."CUST_ID" "CUST_ID","S"."TIME_ID" "TIME_ID","S"."CHANNEL_ID" "CHANNEL_ID","S"."PROMO_ID" "PROMO_ID","S"."QUANTITY_SOLD" "QUANTITY_SOLD","S"."AMOUNT_SOLD" "AMOUNT_SOLD","P"."PROD_ID" "PROD_ID" FROM "SH"."SALES_T" "S","SH"."PRODUCTS" "P" WHERE "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    Query block SEL$1 (#0) unchanged
    PM: Considering predicate move-around in query block SEL$1 (#0)

    **************************
    Predicate Move-Around (PM)
    *************************
    PM: PM bypassed: Outer query contains no views.
    PM: PM bypassed: Outer query contains no views.
    query block SEL$1 (#0) unchanged
    FPD: Considering simple filter push in query block SEL$1 (#0)
    "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    try to generate transitive predicate from check constraints for query block SEL$1 (#0)
    finally: "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID" AND "S"."PROD_ID"=119

    FPD: transitive predicates are generated in query block SEL$1 (#0)
    "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID" AND "S"."PROD_ID"=119

더미 테이블을 사용해서 장애를 만나는 경우

더미 테이블을 사용하는 이유

더미 테이블을 사용하지 않는 방법

 

포장마차에서 지인에게 재미있는 이야기를 들었다. 물론 공장 이야기 이다. 나는 이야기를 재미있게 들었지만, 지인의 입장에서는 머리가 쭈뼛쭈뼛 서는 심각한 일이었다. 사건은 2011년 겨울에 시작된다.

 

2011 1 1일 이른 아침, 갑자기 잘 돌아가던 시스템에 몇몇 프로그램들이 작동하지 않는 장애를 만났다. Y2K 버그도 아니고 2011 1 1일에 장애라니? 서버와 네트워크 그리고 Database는 정상이므로 관심의 화살은 개발팀으로 집중되었다. 개발팀에서 장애 프로그램을 조사해보니 지난 한 달간 프로그램 수정이 없다고 하였다. 결국 모든 것이 정상인데 프로그램만 돌아가지 않는 상황이다. 귀신이 곡할 노릇이 아닌가? 빨리 정상적인 서비스를 해야 하므로 1, 1초가 아쉬운 시점이었다. 모두들 땀을 흘리며 원인을 찾고 있었다. 프로그램 담당자는 장애를 일으킨 사람을 찾으면 죽여버리겠다고 소리쳤다.

 

여러분은 이런 장애에서 안전한가?

다행히 오래 걸리지 않고 원인을 찾았다. 돌아가지 않는 프로그램들의 공통점은 더미테이블을 사용한다는 것이었다. 즉 Copy_ymd를 사용한 것이다. 그 테이블을 조사해보니 일자가 2010년 까지만 들어가 있었다. 그래서 2011년이 되자마자 장애가 발생한 것이었다. 다시 말해, Copy_ymd 테이블에 2011년 데이터가 없으므로, 이 테이블과 조인하면 한 건도 나오지 않는 것이다. 생각해보니, 모든 시스템에 이런 일이 발생할 수 있다. 이야기를 듣는 필자의 간담이 갑자기 서늘해진다.

 

시스템을 구축한 업체에게 항의하려고 문서를 찾아보니 2001년에 Open한 시스템으로 2001년 기준으로 미래의 일자를 10년치 넣어 놓았다. 소프트웨어의 라이프 사이클을 고려한다면, 10년이면 충분하다고 생각했을 것이다. 하지만 운이 없게도 차세대 프로젝트를 하지 않고 10년간 유지보수를 하면서 사용한 것이다. 그리고 인수인계서에 2011년이 되기 전에 몇 년치의 데이터를 더 넣어놓으라고 명시되어 있었다. 시스템을 구축한 업체에게 항의할 수 도 없는 일이었다. 인수인계서를 보는 사람이 한 명이라도 있었을까?

 

왜 더미 테이블을 사용할까?

데이터베이스에 관심이 있는 개발자라면 Copy_ymd, Copy_ym, Copy_y, Copy_t 등 네 개의 더미테이블을 알 것이다. 많은 시스템에 이런 더미 테이블들이 있다. 과거에는 이런 테이블들을 사용해야만 했다. 하지만 2011년의 시점에서 새로운 프로젝트를 할 때 이런 테이블들이 필요할까? 필요한지 아닌지를 알려면 먼저 더미테이블의 용도를 알아야 한다. 이 테이블들의 용도 중에서 대표적인 것은 아래와 같이 세 가지로 볼 수 있다.

 

1. Copy: 같은 집합을 여러 번 복제하여 원하는 결과집합을 구한다.

2. 데이터 체크: 일자의 경우 입력된 값이 올바른지 확인한다. 예를 들면, 2 30일은 잘못된 일자이다.

3. 인덱스의 효율적 사용: 인덱스의 첫 번째 컬럼 혹은 중간 컬럼이 Where 조건에 사용되지 않을 때 더미 테이블을 이용하여 IN으로 공급해주면 인덱스를 효율적으로 사용할 수 있다.

 

물론, 다른 용도로 더미테이블을 사용할 수 도 있지만, 대부분은 위의 세가지 경우 때문에 더미테이블이 필요하다. 가끔 기준일자를 관리하는 테이블을 볼 수 있는데, 이것은 더미테이블이 아니라 business에 필요한 것이다. 더미테이블은 업무적인 것이 아니라, 성능적인 관점, 혹은 관리적인 목적으로 사용되는 것이다. 업무적인 데이터가 없으므로 차세대 시스템을 구축할 때 더미 테이블은 분석 대상에서 빠져도 된다. 이런 이유 때문에 모델러들도 더미테이블을 중요하게 생각하지 않는다.

 

더미 테이블의 단점

위의 세 가지를 더미 테이블을 사용하지 않고 처리할 수 있다면 굳이 사용할 필요는 없다. 왜냐하면 아래와 같은 단점이 있기 때문이다.

 

첫 번째, 더미 테이블이라고 해도 시스템 속성을 추가해야만 한다. 시스템 속성이란 입력자, 입력일시, 수정자, 수정일시 등을 의미한다. 모든 테이블에 이런 컬럼들이 4 ~ 6개 정도 존재한다. 많은 기업들이 메타시스템을 사용하고 있다. 메타시스템에 테이블에 시스템 속성이 없으면 등록할 수가 없는 경우가 많다. 심지어 자동으로 시스템속성을 추가하는 메타시스템도 있다.

 

그런데 더미테이블은 튜닝의 목적이 있으므로 매우 가벼워야 한다. 생각해보라. Copy_t에 존재하는 숫자컬럼의 length3 byte에 불과한데 시스템 속성 네 개가 48 byte를 차지한다. 3 byte를 위해서 건건이 48 byte를 낭비해야 한다. 테이블이 무거워 질 수 밖에 없다. 더미 테이블은 메타시스템으로 관리하지 말고 엑셀로 관리하면 된다고? 왜 추가적인 관리를 해야만 하는가?

 

두 번째, 누가 더미 테이블을 중요하게 생각하는가? 더미 테이블을 인수인계 시 중요항목으로 관리되고 있는가? 2011년이 가까이 다가와도, Copy_ymd에 데이터를 넣어줄 생각을 하는 사람은 아무도 없었다. 왜냐하면 10년간 담당자가 세 번이나 바뀌었고, 더미테이블은 인수인계 시 중요관심사가 아니었기 때문이다. 결국 더미테이블을 신경 쓰는 사람은 아무도 없을 수 있다. 시스템은 이렇게 중요 테이블이 아니더라도 조그만 블랙홀이 생기면 장애를 맞는다. 이런 일이 발생할 수 밖에 없는 걸까?

 

세 번째, 관리해야 할 DB 서버가 많다면 위험이 증가한다. DB 팀이 관리하는 DB30개라고 가정하자. 지금 30개의 DB에 대해서 더미테이블을 관리하고 있는가? Copy_ymd에 추가적인 데이터를 insert 해야 하는 시기를 알고 있는가? 관리하고 있지 않다면 장애를 맞을 가능성이 높다. 그렇다면, 신경 쓰지 않아도 되도록, 시간이 되면 자동으로 insert되는 프로그램을 고려해 보아야 하는가? 아니면 시스템마다 더미테이블 들을 뒤져서 안전하게 100년치를 넣을 것인가? 왜 그래야 하는가? 아예 더미테이블을 사용하지 않으면 될 것을

 

지금은 운영 중이기 때문에 SQL을 바꾸는 것이 어렵다고 하더라도, 차세대 시스템을 구축할 때는 테이블을 관리할 필요도 없고, 장애도 일으키지 않는 방법이 무엇인지 고려하기 바란다. 방법은 얼마든지 있다. 이미 똑똑한 개발자들은 아래의 방법을 사용하고 하고 있다.

 

1. Copy

Copy_t 대신에 Rollup, Cube, Grouping Sets를 활용하면 원하는 집합을 만들 수 있다. 사용방법은 해당 을 참고하라. 물론 내부적으로 쿼리변환이 발생되어 UNION ALL로 풀릴 수도 있으므로 성능이 저하되는지 실행계획의 확인은 필요하다. 이런 경우에도 COPY_T는 필요 없으며 DUAL + CONNECT BY LEVEL을 사용하면 된다. 또한 LEAD/LAG를 사용한다면 복제하지 않고도 전/후의 데이터를 비교할 수 있다.

 

2. 데이터 체크

데이터를 Insert 하기 전에 일자 컬럼을 체크하려고, DBMS Call을 해야만 하나? 다시 말해, 무슨 이유 때문에 DB에 불필요한 부하를 주어야 하는가? 비슷한 노력을 들이고도 DBMS Call을 하지 않을 수 있다. 화면 단에서 Java Script로 처리하던지, 아니면 Constraint를 걸면 Insert할 때에 자동으로 체크 되므로 별도의 DBMS Call은 필요 없다. Constraint에 대해서는 관련 을 참조하라.

 

3. 인덱스의 효율적 사용

INDEX SKIP SCAN 기능이 추가되었기 때문에 IN 서브쿼리를 사용해야 되는 경우는 많이 한정 되었다. 또한 IN 서브쿼리를 사용한다고 하더라도 Copy_t, Copy_ymd 대신에 Dual + Connect By를 사용하면, Pseudo 컬럼인 Level을 사용할 수 있다. 물론 주의사항은 있다. 해당 을 참조하라.

 

3번에 대해서 어느 개발자가 다음과 같이 질문한다.

 

질문1

개발자: 인덱스가 거래일자 + 고객번호 입니다. 거래일자에 Between 조건이 들어오고 고객번호에 = 조건이 들어온다고 칩시다. 인덱스의 선두 컬럼이 Range 조건이므로 똑똑한 고객번호를 인덱스로 액세스 할 수 없습니다. 이럴 때, Copy_ymd가 있어서 거래일자를 IN 서브쿼리로 공급할 수 있었습니다. 그런데 Copy_ymd 테이블 없이 Dual + Connect By + Level로 처리가 가능 한가요? Copy_tLevel로 처리가 가능하지만 일자는 Range 조건으로 만들기 힘들 것 같은데요.

필자: 됩니다.

개발자: 어떻게요?

 

질문2

개발자: INDEX SKIP SCAN은 인덱스가 A+B+C 로 되어있고, A 혹은 B Where 조건에서 생략될 때만 사용할 수 있는 것 아닙니까? , A 컬럼에 Range 조건이 오고 B = 조건이 오면 INDEX SKIP SCAN을 사용할 수 없는 걸로 알고 있습니다만.

필자: 꼭 그런 것은 아닙니다. A 컬럼에 조건이 Between이나 LIKE 조건이 오고 B 컬럼에 = 조건이 오더라도 INDEX SKIP SCAN이 발생합니다. , 선두나 중간 컬럼의 조건이 생략될 때만 INDEX SKIP SCAN이 발생하는 것은 아니며, 선두나 중간 컬럼에 조건이 Range로 들어올 때도 발생합니다.  

개발자: 그럴 리가요?

 

이제부터 두 가지 질문에 대해 대답해보자. 먼저 Sales 테이블에 인덱스를 하나 만들고 Copy_ymd를 만들자.

 

CREATE INDEX IDX_SALES_01 ON SALES (time_id, cust_id, prod_id) ;

 

CREATE TABLE COPY_YMD AS

SELECT TO_CHAR(ROWNUM + TO_DATE('19800101', 'YYYYMMDD'), 'YYYYMMDD') AS YMD_CHAR,

       ROWNUM + TO_DATE('19800101', 'YYYYMMDD') AS YMD_DT

  FROM SALES

WHERE ROWNUM <= 14600;

 

ALTER TABLE COPY_YMD ADD CONSTRAINT PK_COPY_YMD

PRIMARY KEY (YMD_CHAR) USING INDEX; 

 

CREATE UNIQUE INDEX IDX_COPY_YMD_01 ON COPY_YMD(YMD_DT);

 

Sales 테이블의 인덱스는 Time_id _+ cust_id + Prod_id 이다. 해당 매출테이블의 transaction이 많아서 인덱스를 변경할 수도, 생성할 수도 없는 상황이라고 가정한다. 이제 테스트를 시작해보자.

 

참고로 아래의 힌트는 INDEX SKIP SCAN을 방지할 목적으로 사용한 것이다. INDEX SKIP SCAN이 나오기 전에는 이렇게 INDEX RANGE SCAN으로 수행되었다.

 

SELECT /*+ NO_INDEX_SS(S IDX_SALES_01) INDEX_RS_ASC(S IDX_SALES_01) */ s.*

  FROM sales s

 WHERE time_id BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                   AND TO_DATE('20011130', 'YYYYMMDD')

   AND cust_id = 53;

 

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

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

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

|   0 | SELECT STATEMENT                   |              |      1 |      6 |00:00:00.01 |     209 |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      1 |      6 |00:00:00.01 |     209 |

|*  2 |   INDEX RANGE SCAN                 | IDX_SALES_01 |      1 |      6 |00:00:00.01 |     203 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CUST_ID"=53

              AND "TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("CUST_ID"=53)

 

과거에는 선두컬럼이 Between이나 Like등의 Range 조건이 들어오면 위의 실행통계에서 볼 수 있듯이 비효율이 심했다. 고작 6건을 출력하기 위해 209 블록이나 Scan했다. 왜냐하면, 똑똑한 조건인 고객번호가 선두컬럼의 Range 조건 때문에 Access 조건이 못되고 Filter로 빠졌기 때문이다. 이런 비효율을 없애기 위해 예전에는 아래와 같이 더미테이블을 이용한 서브쿼리를 사용하였다.

 

SELECT /*+ LEADING(C@SUB) USE_NL(S) */ s.*

  FROM sales s

 WHERE time_id IN ( SELECT /*+ QB_NAME(SUB) */ ymd_dt

                      FROM copy_ymd c

                     WHERE ymd_dt BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                                      AND TO_DATE('20011130', 'YYYYMMDD') )

   AND cust_id = 53;

 

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

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

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

|   0 | SELECT STATEMENT                    |                 |      1 |      6 |00:00:00.01 |     136 |

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

|   2 |   NESTED LOOPS                      |                 |      1 |      6 |00:00:00.01 |     130 |

|*  3 |    INDEX RANGE SCAN                 | IDX_COPY_YMD_01 |      1 |     61 |00:00:00.01 |       4 |

|*  4 |    INDEX RANGE SCAN                 | IDX_SALES_01    |     61 |      6 |00:00:00.01 |     126 |

|   5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES           |      6 |      6 |00:00:00.01 |       6 |

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

 

Predicate Information (identified by operation id):

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

   3 - access("YMD_DT">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "YMD_DT"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

   4 - access("TIME_ID"="YMD_DT" AND "CUST_ID"=53)

       filter(("TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

 

서브쿼리를 사용하자 고객번호를 Access 조건으로 사용할 수 있게 되었다. 이에 따라 서브쿼리를 사용하지 않은 경우(209 블럭)보다는 Scan량이 줄어 136 블록이 되었지만 약간의 비효율이 있다. Copy_ymd 때문에 4블럭을 Scan 하였다. 이것을 해결하려면 아래처럼 Dual + Connect By Level을 사용하면 된다. 위의 SQL과 아래의 SQL의 답은 같으며 아래의 SQL은 질문1의 답변에 해당한다.  

 

SELECT s.*

  FROM sales s,

      ( SELECT TO_DATE('20011001', 'YYYYMMDD') + LEVEL - 1 AS time_id

          FROM dual

       CONNECT BY LEVEL <= TO_DATE('20011130', 'YYYYMMDD') - TO_DATE('20011001', 'YYYYMMDD') + 1) d

 WHERE s.time_id = d.time_id

   AND s.cust_id = 53; 

 

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

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

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

|   0 | SELECT STATEMENT                    |              |      1 |      6 |00:00:00.01 |     132 |

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

|   2 |   NESTED LOOPS                      |              |      1 |      6 |00:00:00.01 |     126 |

|   3 |    VIEW                             |              |      1 |     61 |00:00:00.01 |       0 |

|   4 |     CONNECT BY WITHOUT FILTERING    |              |      1 |     61 |00:00:00.01 |       0 |

|   5 |      FAST DUAL                      |              |      1 |      1 |00:00:00.01 |       0 |

|*  6 |    INDEX RANGE SCAN                 | IDX_SALES_01 |     61 |      6 |00:00:00.01 |     126 |

|   7 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      6 |      6 |00:00:00.01 |       6 |

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

 

Predicate Information (identified by operation id):

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

   6 - access("S"."TIME_ID"=INTERNAL_FUNCTION("D"."TIME_ID") AND "S"."CUST_ID"=53)

 

Dual을 사용했기 때문에 Block I/O가 없어졌다. 하지만 여기서 만족하면 안 된다. 왜냐하면 쓸모 없는 조인이 61번이나 시도되었고 이에 따라 126블록을 Scan하였기 때문이다. 따라서 SQL을 아래처럼 바꾸어야 한다.

 

SELECT /*+ INDEX_SS(S IDX_SALES_01) */ s.*

  FROM sales s

 WHERE time_id BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                   AND TO_DATE('20011130', 'YYYYMMDD')

   AND cust_id = 53;

 

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

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

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

|   0 | SELECT STATEMENT                   |              |      1 |      6 |00:00:00.01 |      70 |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      1 |      6 |00:00:00.01 |      70 |

|*  2 |   INDEX SKIP SCAN                  | IDX_SALES_01 |      1 |      6 |00:00:00.01 |      64 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CUST_ID"=53

              AND "TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("CUST_ID"=53)

 

불필요한 조인도 없어졌으며 Block I/O도 서브쿼리를 사용할 때에 비해서 약 절반으로 줄어들었다. 이것이 질문 2에 대한 대답이다.

 

참고사항

위의 SQL들을 보면 인덱스가 cust_id + time_id로 되어 있는 것이 최적이지만 막상 튜너가 현장에 투입되면 인덱스를 변경/생성/삭제 하기는 대단히 어려우므로 위의 방법을 잘 알아놓아야 한다.

 

결론

Copy_ymd, Copy_ym, Copy_y, Copy_t는 구시대의 유물이다. 성능에도 좋지 않으며, 코드가 길어지고, 장애가 발생할 수 있음에도 여러 가지 이유를 대어 차세대 시스템에 더미 테이블들이 또 포함될 수 있다. 안타깝게도 관행이나 표준으로 생각하는 사람이 많기 때문이다. 이제는 바뀔 때가 되었다. 지금 운영되는 모든 시스템에서 더미테이블을 사용하는 SQL을 모조리 조사해서 고치라는 이야기가 아니다. 그렇게 하기는 힘들 것이다. 다만 모든 더미테이블을 찾아서 미래의 데이터를 미리 그리고 넉넉히 넣자는 이야기 이다. 그리고 앞으로 시작될 프로젝트에서 더미테이블을 사용하지 않았으면 하는 것이 나의 바램이다. 당신이 발 뻗고 잘 수 있도록
저작자 표시 비영리 동일 조건 변경 허락
신고

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

COPY_T 테이블 필요한가?  (6) 2011.04.04
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
Posted by extremedb

댓글을 달아 주세요

  1. finecomp 2011.04.06 00:43 신고  댓글주소  수정/삭제  댓글쓰기

    8i, 9i 등 ~i 이전 시대의 방법론들을 현재의 ~g시대에도 고수하려는 고집들은 대부분 말도 안되는 논리인 듯 보입니다.
    (물론, 예~~전엔 그 방법들이 최적일 때가 분명히 있었더랬죠...)

    DB모델링이나 SQL만의 현상은 아니더군요...항상 잘 보고, 느끼고 갑니다...^^;;;

  2. 에너자이져 2011.04.06 09:32 신고  댓글주소  수정/삭제  댓글쓰기

    예전에 유용하게 사용한 적이 있었는데 구시대의 유물이 되어버렸네요..
    좋은글 감사합니다.

  3. 최윤호 2011.04.06 11:32 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 항상 감사합니다.


select /*+ full(a) full(b)  leading(a) use_hash(b) */

a.col1, b.col2

  from tab1 a,

       tab1 b

  where a.col1 = b.col2 ;

 

오해와 현실

위의 SQL을 보면 from 절의 두 테이블은 동일하다. 그리고 건수가 많아서 힌트를 주었으므로, 둘 다 full table scan을 할 것이다. 따라서 위의 SQL을 실행하고 결과를 본다면, a b의 일량(block I/O)은 동일하다.”라고 알고 있는 사람이 많이 있다. a를 읽었더니 block I/O 량이 1000 블럭이라면 b를 읽을 때도 1000 블럭이 나올 것이라는 이야기다. 이런 주장이 사실일까? 결론부터 말하자면 사실이 아니다. b쪽이 더 많은 블럭을 scan 해야 한다. 그래서 b쪽을 scan할 때 더 느리다. b쪽에 더 많은 일량이 나온다면 버그라고 생각하는 사람도 있지만, 버그가 아니라 정상적인 결과이다.

 

이 글의 목적

위의 결론에 따르면 후행테이블을 scan 할 때 심각한 성능저하가 발생 할 수 있다. 이런 현상을 주위의 지인들에게 질문한 결과 적절한 이유나 원인을 말하는 사람은 거의 없었다. 성능문제의 원인을 모르면 튜닝을 할 수 없다. 그러므로 이 글에서는 성능이 저하되는 이유를 독자에게 제시하고, 비효율을 해결 할 수 있는 방법을 설명한다. 또한 이런 문제가 발생하지 않는 예외적인 경우도 살펴본다.

 

이제 테스트를 진행하기 위해 테이블을 하나 만들자.

 

create table test1 as

select lpad(level, 5, '0') as num,

       lpad(level, 60, '0') as num_txt

  from dual

connect by level <= 50000 ;

 

인덱스가 없음으로 앞으로 모든 실행계획은 full table scan이 될 것이다. 정확한 분석을 위해 test1 테이블의 full table scan 일량(logical reads)을 알아보자.

 

select count(*)

  from test1;

 

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

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

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

|   0 | SELECT STATEMENT   |       |      1 |      1 |00:00:00.01 |     504 |

|   1 |  SORT AGGREGATE    |       |      1 |      1 |00:00:00.01 |     504 |

|   2 |   TABLE ACCESS FULL| TEST1 |      1 |  50000 |00:00:00.06 |     504 |

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

 

full table scan의 결과 일량은 504 블럭이다. 따라서 test1 테이블의 데이터가 변경되지 않는다면 항상 504 블럭이 나와야 한다. 정말 그렇게 될까?

 

아래 SQL의 조인 순서는 a--> b 이다.

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100'; --> substr의 인자 -5는 마지막 다섯 자리라는 뜻이다.

 

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

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

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

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.45 |    5998 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.45 |    5998 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.15 |    5494 |

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

 

Predicate Information (identified by operation id):

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

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

 

무려 11배나 차이가 난다

선행테이블은 정상적으로 504블록이 나왔다. 하지만 이상하게도 선행테이블과 동일한 테이블인 후행테이블( b )의 일량이 약 11배나 많다. 수행시간도 후행테이블이 더 느리다. 같은 테이블을 동일한 방법으로 scan 했는데 왜 Block I/O 수가 11배나 차이가 날까?

 

힌트를 주어 조인 순서를 바꿔보자.

 

select /*+ leading(b a) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

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

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

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

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.34 |    5998 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.34 |    5998 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.11 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |    5494 |

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

 

Predicate Information (identified by operation id):

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

   1 - access("A"."NUM"="B"."NUM")

   2 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

   3 - filter("A"."NUM">'00100')

  

array size가 원인이다

이번에는 반대로 a의 일량이 b보다 11배 많게 나왔다. 즉 일관성 있게 후행테이블의 일량이 11배가 많다. 그 이유는 툴(오렌지) array size 10 으로 되어있었기 때문이다. 다른 말로 바꾸면 array size 10 이기 때문에 49900건을 모두 출력하려면 4990 fetch 해야 한다. 즉 위의 일량 5494는 원래의 블록 수인 504 fetch 회수(4990 블럭)을 더한 것이다. 여기까지는 이해가 될 것인데 문제는 fetch 할 때마다 한 블록을 더 읽어야 하는가?이다.

 

Fetch 할 때마다 이전에 읽었던 1블럭을 더 읽어야 한다

한 블록에 20건이 들어있다고 가정하고, Array size 10 이라고 치자. 그러면 한 블럭의 데이터(20)를 모두 출력 하려면 동일한 블럭을 반복적으로 두 번 fetch 해야 한다. 바로 이것이 fetch 할 때마다 이미 읽었던 블럭(직전에 fetch 했던 block중 마지막 block)을 다시 Scan 할 수 밖에 없는 이유이다.

 

비효율을 없애려면 array size를 적정 수준으로 늘려라

 

set arraysize 100 --array size 100으로 변경

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

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

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

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

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.38 |    1507 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.38 |    1507 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.11 |    1003 |

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

 

Predicate Information (identified by operation id):

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

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

  

array size를 올리자 logical read 5494 에서 1003 으로 변경되었다. 5배 이상 일량(logical reads )이 줄어들었다. 하지만 아직도 원래의 블록 수인 504 보다배정도 많다. 

 

set arraysize 1000 --array size 1000으로 변경

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

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

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

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

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.34 |    1058 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.34 |    1058 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.09 |     554 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

 

array size1000으로 올리자 logical read 1003 에서 554로 변경되었다. 이 정도면 원래의 블럭수인 504와 비슷하다. 554와 504의 차이는 50 블럭이므로 fetch를 50번 했다는 것을 알 수 있다.

 

해결방법
테스트의 결과는 fetch
가 발생할 때마다 직전 블럭을 읽어야 함을 알 수 있다. 따라서 array size를 적절히 늘리면 fetch 회수가 줄어들므로 이전 블럭을 읽는 횟수도 같이 줄어든다. 이에 따라 성능도 향상된다. 하지만 array size를 늘려도 선행테이블은 logical read의 변화가 없다. 왜냐하면 선행테이블은 fetch에 영향을 끼치지 못하며, 후행 테이블이 scan 되어 조인에 성공될 때만 데이터가 client로 전송(fetch) 되기 때문이다.

조인이 없을 때도 비효율은 발생한다
이런 현상은 full table scan과 해시조인의 조합에서만 발생하는 것은 아니다. 조인 없이 from 절에 테이블이 하나뿐일 때도 동일하게 발생한다. 아래의 SQL이 전형적인 예제이다.

 

array  size 10일 때       

 

select num

  from test1;

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 10

                  Long  Size = 80

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

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

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

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch     5001    0.328        0.219          0       5504          0      50000

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

Total     5003    0.328        0.219          0       5504          0      50000

 

Misses in library cache during parse: 0

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

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

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=5504 pr=0 pw=0 time=67049 us cost=143 size=300000 card=50000)

 

fetch를 5001 번 했기 때문에 원래의 블럭수( 504 )에 비해 logical read량도 약 5000 블럭이 늘었다. 
 


array
 size
100일 때

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 100

                  Long  Size = 80

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

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

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

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch      501    0.063        0.041          0       1004          0      50000

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

Total      503    0.063        0.041          0       1004          0      50000

 

Misses in library cache during parse: 1

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

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

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=1004 pr=0 pw=0 time=75254 us cost=143 size=300000 card=50000)

 

Array size 10인 경우(5504)에 비해 일량이 약 5배 정도 감소했다. 그 이유는 fetch 회수가 10배로 줄어들었기 때문이다.

 


array  size
1000 일 때

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 1000

                  Long  Size = 80

 

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

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

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

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch       51    0.031        0.016          0        554          0      50000

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

Total       53    0.031        0.017          0        554          0      50000

 

Misses in library cache during parse: 1

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

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

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=554 pr=0 pw=0 time=50383 us cost=143 size=300000 card=50000)        

 

무작정 크게 한다고 좋아지지 않는다

array size 1000으로 변경하니 array size가 10인 경우(5504 블럭)에 비해 일량이 약 10배 정도 감소했다. 하지만 array size 100 인 경우와 비교해 보면 일량이 고작 2배 정도만 줄어들었다. 다시 말해 여기서 array size를 더 크게 하더라도 얻는 이익은 별로 없다는 것이다. 따라서 무작정 array size를 늘려서는 안 된다. 메모리에 부하를 줄 뿐만 아니라 한번에 많은 데이터가 client로 전송되므로 네트웍 I/O가 과도 하게 늘어날 수 있다. 따라서 clientfetch 할 건수가 많고, 네트웍 망의 성능이 좋다면 1000~ 2000 정도를 유지하는 것이 적당하다. 물론 조회 프로그램에서는 페이징 처리를 하는 것이 가장 좋지만, 업무적으로 전체 건을 볼 수 밖에 없는 경우는 array size를 적절히 조절하는 것이 대안이 될 수 있다.


성능문제의 발생조건 
fetch의 비효율은 select문에서만 발생한다. 즉 insert–select CTAS(create table as select) 그리고 merge 문 등에서는 이런 종류의 성능저하가 발생하지 않는다. 왜냐하면 DML문은 select문과 달리 조회(데이터를 clientfetch) 할 필요가 없고, commit이 되면 바로 종료되기 때문이다.

모든 규칙에 예외는 있다

full table scan + sort merge join 의 조합에서는 fetch의 비효율이 발생하지 않는다. 왜냐하면 full table scan + sort merge join 조합은 hash join의 조합과 달라서 모든 데이터를 sort 해야하기 때문이다. 모든 데이터를 sort하려면 어차피 모든 블럭을 scan해야 하므로 fetch를 여러번 해야만 하는 array size를 사용할 필요가 없는 것이다.  그리고 fetch를 여러번 하지 않기 때문에 항상 일량이 일정하다.

또 다른 예외의 경우는
 1 블럭에 1 row만 저장되는 경우이다. 이런 경우는 블럭을 한번만 엑세스 해도 그 블럭의 모든 데이터를 한번에 fetch 할 수 있으므로, 같은 블록을 반복해서 읽을 필요가 없다. 따라서 array size를 변경해도 일량이 달라지지 않는다.

 

호기심이 있는 독자는 아래의 테이블을 만들고 위의 테스트를 똑같이 진행 해보기 바란다. 위의 test 결과와는 다를 것이다.

 

drop table test1 ;

 

create table test1 as

select lpad(level, 5, '0') as num,

       lpad(level, 7000, '0') as num_txt

  from dual

connect by level <= 50000 ;

 
array size 항상 나쁜가?
우리는 array size가 있음으로 해서 부분범위처리를 할 수있다. full table scan을 동반하는 해시조인의 경우에도 중간에 효율적으로 멈출 수 있다. 예를 들어 결과건수가 1억건이며, 만건을 먼저 조회한 후에 다음 만건을 보고 싶다고 할때, 운반단위(array size)가 1000 이라면 10번 fetch 하면 멈출 수 있다. 반면에 array size가 없다면 중간에 멈출 수 없으므로 1억건을 모두 fetch 한후에나 결과를 화면에서 볼 수 있다.

결론

같은 테이블을 두 번 full table scan 하고, 그 둘을 해시조인하면 대부분의 경우 후행 테이블의 I/O량이 더 많다. 그래서 후행테이블을 scan 할 때가 더 느리다. 왜냐하면 직전 fetch 때에 이미 읽었던 block의 데이터가 모두 fetch 되지 않을 수 있으므로 그 블럭을 한번 더 읽어보아야 확인 할 수 있기 때문이다. 이런 비효율이 많이 발생하는 경우는 array size가 작기 때문이다. 따라서 적절한 array size로 늘려주면 성능문제를 해결 할 수 있다. 

fetch의 비효율은 full table scan이나 full table scan + hash join 조합을 사용할 때만 발생하는 것은 아니다. index scan을 할때도 똑같이 비효율이 발생한다.(주1)  즉 fetch의 비효율 문제는 인덱스를 사용할때나 테이블을 scan할때를 가리지 않고 모두 발생한다. 이런 사실들로 미루어 볼때, 위에서 언급한 몇가지의 예외를 제외한다면, 우리는 다음과 같은 결말을 낼 수 있다.

"select문의 결과건수가 많음에도 불구하고, 페이징 처리가 되지 않고, array size가 작은 조회용 프로그램이라면 fetch의 비효율은 존재한다."



주1 : 인덱스 사용시 fetch의 비효율 문제는 이미 책으로 정리가 되어 있으므로 필자가 언급하지 않는다. 이 문제에  관심이 있는 사람은 조동욱 님의 책 Optimizing Oracle Optimizer를 참조하기 바란다.

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

댓글을 달아 주세요

  1. feelie 2011.01.14 12:45 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 내용 감사합니다.

    늦었지만 새해 복많이 받으시고, 올해 목표하시는 일 다 이루시길 바랍니다.

  2. Ejql 2011.01.17 15:58 신고  댓글주소  수정/삭제  댓글쓰기

    이런 문의가 종종있었나 보네요? 확실히 알고 갑니다. 감사합니다. 추가 원인이 그 이유였군요.

  3. 오라클완전초보 2011.01.18 17:18 신고  댓글주소  수정/삭제  댓글쓰기

    매일 매일 SQL 을 보면서 사는데
    왜 저는 이런걸 발견하지 못할까요.. 아무생각없이 튜닝을 해서 그런가 봅니다.
    반성하게 되네요

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

      너무 걱정하지 마시기 바랍니다.
      튜닝에 집중하다 보면 다른것은 보이지 않기 때문입니다.
      하나의 방법은 Q&A에서 답변을 자주하면 실력이 늡니다.
      답변을 하기위해 공부를 많이 해야하고, 원인을 찾아야 하고.... 하다보면 한단계 업그레드 되어있는 자신을 발견하실 것입니다.
      감사합니다.

  4. sid 2011.01.18 21:00 신고  댓글주소  수정/삭제  댓글쓰기

    “왜 fetch 할 때마다 한 블록을 더 읽어야 하는가?”
    이건 어디서 판단해야 하나요?
    이 부분이 잘 이해가 안가서 계속 보고 있는데, 어디서 블럭을 또 본다는 걸 파악해야 하는지 잘 이해가 안가서요.
    전체적으로 워낙에 잘 풀어쓰셔서 술술 이해가 되는데 그 부분만 막혀버려서, 답답해서 이렇게 질문 드립니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.19 09:36 신고  댓글주소  수정/삭제

      안녕하세요.
      select num from test1; 부분의 10046 trace 를 보시면 됩니다. 여기를 보시면 패치회수만큼 블럭을 더 읽는다는 것을 알 수 있습니다. 즉 array size가 10일때 5만건(결과건수)을 패치하려면 5천번을 실행해야 합니다. 이 정보가 10046 trace의 fetch에 나타납니다. 그리고 current에 블럭 i/o량이 나타납니다. trace 상의 굵은 글씨를 중점적으로 보시면 됩니다.

      즉 원래의 블럭량인 504와 패치횟수 5000을 더하면 logical read 량인 5504 가 나옵니다. 이해가 되셨나요?

  5. sid 2011.01.19 10:47 신고  댓글주소  수정/삭제  댓글쓰기

    화면상으론 확인할 수 없나 보군요 ㅎ
    네, 알겠습니다. 지금은 권한상 무리니까 집에가서 한번 테스트 해봐야겠네요.
    좋은 글 감사합니다 ^^

  6. salvationism 2011.01.23 20:41 신고  댓글주소  수정/삭제  댓글쓰기

    "select문의 결과건수가 많음에도 불구하고, 페이징 처리가 되지 않고, array size가 작은 조회용 프로그램이라면 fetch의 비효율은 존재한다."
    자연스럽게 고개를 끄덕이게 되는 단순 명료한 정의 같습니다. 좋은 글 감사합니다. ^^

  7. 나그네 2011.01.24 15:35 신고  댓글주소  수정/삭제  댓글쓰기

    궁금한 점이 있습니다. 어레이 사이즈로 인해 후행 테이블의 로지컬 리드가 높아졌다면 왜 선행 테이블의 로지컬 리드는 안 늘어 나는 건가요? 선행 테이블도 어레이 사이즈에 맞춰서 읽지 않는지요. 이 부분이 궁금합니다.

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

      반갑습니다.
      선행집합만 화면에 뿌리는 것은 의미가 없습니다. 다시말해,select 결과를 화면에 fetch 하려면 조인에 성공한 건만 해야 합니다. 어차피 후행집합이 조인에 성공한 후에 fetch가 시작되므로 성행집합에 성능이 저하되는 array size를 사용할 필요가 없는것 입니다. 이해가 되셨나요?

  8. 나그네 2011.02.16 20:23 신고  댓글주소  수정/삭제  댓글쓰기

    일량이 틀려요 => 일량이 달라요가 맞습니다.

    다르다 = different, 틀리다 = wrong

    우리나라 사람이 가장 잘못 사용하는 단어 중 하나라고 생각합니다.

    요즘 얼마 전 출간된 AWR 관련 서적을 읽고 있는데, 이 책의 저자는 '다르다'는 표현을 전부 '틀리다'로 써 놓으셨더군요.

    일상 대화 중에서야 그러려니 하겠지만, 전문서적에서 전부 잘못 써 놓으니 책 읽기가 싫어질 정도였습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.16 21:23 신고  댓글주소  수정/삭제

      좋은 의견입니다. 제목이 틀렸군요. 개발자에게 들은 것을 그대로 사용하면 안되겠네요.
      한글의 사용이 잘못되어 지적을 하는것은 중요합니다.

      마찬가지로 DB 실력향상도 중요합니다. 국어와 한글을 사랑하시어 댓글을 남기신 만큼,
      이번주에 올라간 분석함수 문제에도 어문규정 만큼 관심을 가져 주시고, 문제를 푸셔서 댓글로 남겨주세요.

      감사합니다.

  9. rose 2011.11.29 18:04 신고  댓글주소  수정/삭제  댓글쓰기

    이런 이유가 있었네요~ 재밌게 잘 읽었습니다 ^^

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

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

-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 을 적용하는 방법입니다. 아래 링크의 제일 마지막 부분을 보시면 됩니다.

처음에 이 글을 블로그에 올릴지 말지 고민을 많이 하였다. 왜냐하면 튜닝이나 모델링의 기술적인 문제가 아니고 튜닝 프로젝트 시 이슈의 해결 과정이기 때문이다. 하지만 의외로 이런 사례를 알고 싶어하는 사람이 많이 있을 것으로 생각한다. 오래된 자료이긴 하지만.....

 

이 글을 보기 전 고려사항

이 글은 튜닝 프로젝트 시 발생한 문제이다. 개인이 급한 불을 끄기 위해 소방수로 들어가서 튜닝을 하는 것과 튜닝프로젝트를 하는 것은 전혀 다르다. 이점을 알고 글을 보기 바란다. 또한 튜닝 프로젝트마다 이슈는 다를 수도 있고 아래의 이슈가 전혀 문제가 되지 않고 쉽게 해결되는 경우도 있다. 아래의 이슈들은 철저히 필자의 관점임을 밝혀둔다. 이슈는 다음과 같다.   

 

1. 뱅킹 시스템 RDBMS 경험자 전무(AS-IS Network DB)

2. DBMS 설치후 성능관련 파라미터 튜닝이슈

3. 업무변경 시 SQL튜닝 반영의 문제

4. CPU 사용율 60% 미만에서 초당 4700 TPS 를 확보하라

 

이제 각각의 이슈 사항을 자세히 알아보기 위해 타임머신을 타고 그때 그 시각으로 돌아가 보자.

그림을 클릭하면 크게 볼수 있다.
 

사용자 삽입 이미지

1. 뱅킹 시스템 RDBMS 경험자 전무(AS-IS Network DB) 상황:

AS-IS DB Network DB 이므로 RDBMS 경험이 없음.

RDBMS 를 사용하는 다른시스템 및 Legacy 시스템의 DBA 및 개발자들은 기존 시스템의 유지보수 관계로 차세대 뱅킹 프로젝트에 참여하지 못함.

외부업체의 개발자의 경우 상대적으로 RDBMS 에서 개발경험이 많으나 시스템 OPEN 직후부터의 유지보수는 은행직원들이 수행함으로 개발 시 직원의 적극적인 참여가 필수적으로 요구되는 상황임.

과연 시스템을 제대로 개발할 수 있을 것인가에 관한 의구심이 증폭됨.

 



사용자 삽입 이미지
2. DBMS 설치 후 성능관련 파라미터 튜닝이슈 상황:

개발 DB 가 설치된 지 한달 이 지났지만 성능관련 파라미터의 세팅을 완료하지 못함.

지연된 가장 큰 이유는 일반적인 가이드는 존재하지만 그 사이트에 맞는 정확한 파라미터값은 어디에도 존재하지 않음.

DBMS 밴더사 조차 초기값을 제시하지 못함. 개발이 진행되고 있는 상황에서 계속 지연될 경우 개발후기에는 실행계획이 대폭 바뀔 수 있으므로 빠른 시일 내에 최적의 성능관련 파라미터의 세팅이 필요함.

잘못되었을 경우 전체시스템에 악영향을 끼치므로 책임이 막중하여 DBMS 밴더사, DBA, 인프라팀 누구도 나서지 못함.

 



사용자 삽입 이미지
3. 업무변경 시 SQL튜닝 반영시 문제의 상황:

개발자가 작성한 SQL DA 팀에서 품질점검 및 튜닝을 수행할 경우 SQL을 수정해야만 하는 경우가 발생한다. 그런 다음 튜닝 된 SQL 은 개발자에게 전달되어 SQL 이 수정되게 된다. 이때 개발자가 수정을 잘못하여 SQL 의 결과가 틀릴 수 있다.

 

또 하나의 경우는 DA 팀에서 원본 SQL 을 튜닝하고 개발자에게 전달하는데 이틀이 걸렸다. 하지만 그 이틀 사이에 업무가 수정되어 원본 SQL 이 수정되는 경우가 있다.  하지만 DA 팀에서는 업무가 바뀐 것을 알지 못하므로 무조건 개발자에게 수정을 요청하는데 이때 해당 개발자가 업무가 바뀐 것을 확인하지 않고 DA 팀에서 수정한 SQL 로 바꿔버리는 경우가 있다. 이 경우에도 SQL의 결과가 틀리게 된다.

 

이런 일이 자주 발생될 경우 SQL 의 결과가 틀린 건이 많아지며 개발자는 DA팀에게 SQL 튜닝신청을 꺼리게 된다. 실제로 4000 여개의 SQL 중에서 약 100 개정도가 답이 틀리며 업무 팀에서는 앞으로 상황이 더욱 악화될 것으로 예상함. 

 

튜닝을 하면 값이 잘못 나온다는 소문이 돌기 시작하면서 DA 팀에서 SQL 을 튜닝 해도 개발자가 반영을 하지 않는 심각한 상황이 발생됨.

SQL 의 결과 값이 틀릴 경우 개발자는 SQL의 수정을 DA 팀에서 하였으므로 개발자 자신은 잘못이 없음을 강조함.

 



사용자 삽입 이미지
4. CPU 사용율 60% 미만에서 초당 4700 TPS 를 확보하라

상황:

성능목표 달성기준 : CPU소모량이 60% 미만에서 초당 4700 TPS 를 확보한다.

A은행의 초당 최대 TPS 2100 정도임을 감안할 때 이것은 매우 높은 목표임을 인식함.

이를 확보하려면 평균처리시간은 건당 0.12 초를 확보하여야 한다. 참고로 AS-IS 에서는 초당 최대 TPS 1800 이었음.

 

-OPEN 6개월 전 단위성능 테스트 :

   1) 건당 0.12 초가 목표였으나 평균 2.5초가 나왔음.

   2) DA 팀이 4월부터 품질점검 및 SQL 튜닝을 하였으므로 이것은 매우 실망스러운 결과임

   3) 단말로부터 수신한 전문의 해석과 로깅을 하고 본 서비스를 Forward 시키는 서비스는 모든 거래 시작 시 사용하는 아주 중요한 서비스이므로 집중 튜닝을 실시함(이 서비스에서 사용하는 테이블에 파티션 정책반영, 불필요한 인덱스 제거, 업무팀의 주요거래테이블도 파티션 정책 반영)

 

-OPEN 4개월 전 1차 통합 성능 테스트:

  1)성능이 초당 2000 TPS 정도밖에 나오지 않고 CPU 사용율도 매우높음.

  2)모든거래에서 사용하는 고객,상품,공통쪽 SQL 에 대하여 집중 튜닝 실시

 

-OPEN 3개월 전 2차 통합 성능 테스트:

  1)목표 달성율이 64% 에 그침으로써 OPEN3달밖에 남지 않은 상황이었으므로 매우 급박한 상황이었음.

  2)성능튜닝은 DB 튜닝만으로는 해결하기 어려워 OS 의 패치적용, OS 의 커널 파라미터 튜닝, 개발 프레임워크의 성정파일 수정등을 통하여 1차 실거래 테스트에 대비함.

  3)지속적인 튜닝에도 불구하고 전체적인 Apllication 의 성능이 나오지 않는 원인에 대한 규명이 필요해짐.

여기까지가 4가지 문제의 이슈들을 요약한것이다.
글이 길어지므로 문의의 해결과정은 아래의 파일을 참조하기 바란다.
단순히 슬라이드만 보지말고 Note의 내용을 같이 보야야 이해가 빠를것이다.


튜닝시_이슈극복_사례.ppt

튜닝시_이슈극복_사례



PS
이것들 외에 여러분이 격었던 이슈들 있었다면 필자에게도 공유해 주기 바란다.
공유할 수록 세상은 살기 편해지지 않을까?

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

댓글을 달아 주세요

  1. hyun 2010.05.20 14:16 신고  댓글주소  수정/삭제  댓글쓰기

    생각을 하게 만드는 글이군요..
    좋은 글 감사합니다.

    값이 잘못나오는 경우는 명확하지만,
    튜닝을 했는데도, 개발자가 적용을 잘못하는 바람에 별로 개선되지 못하는 경우도 많이 봤습니다.

    개인적으로는,
    튜닝을 해서 개발팀에게 전달이 되면 개발자는 무조건 적용을 하는 것 보다는,
    왜 이렇게 튜닝이 되었는지, 본인이 구현한 sql과 무엇이 어떻게 다른지 이해를 하고 적용을 한다면 결과가 잘못되거나,
    튜너의 생각을 잘못반영하여 성능개선이 되지 않는 일은 없을꺼라 생각됩니다.

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

      이현석 책임님 오랜만 입니다.
      말씀하신것처럼 SQL을 받아서 튜닝의도를 파악 한다면
      성능개선 뿐 아니라 실력도 향상되겠네요. 우리가 원하는 것이죠.
      저번에 댓글이 너무 간단하다고 불평하셔서 좀 길게 적어봤습니다.^^

  2. 2010.05.21 04:48  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.05.24 09:07 신고  댓글주소  수정/삭제

      그렇네요.
      초당 TPS 라는 말은 중복된 거네요.
      TPS 자체가 초당 트랜잭션 수이니 초당 이라는 말은 필요없습니다.
      감사합니다.

  3. 타락천사 2010.05.25 10:03 신고  댓글주소  수정/삭제  댓글쓰기

    2번 사항까지 신경쓰는 DA / 튜너는 거의 못봤습니다.
    그냥 디폴트를 원하더군요...
    보통 소프트웨어 설치 후에 동종 업계를 참조해서
    버그나 추천 받는 파라미터는 검토해서 최소(NOT Default)한만 반영하게 되더군요.
    영향도가 큰 만큼 아무도 나서지 않더라구요...

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

      말씀하신대로 영향도가 크기 때문에 보통은 나서질 않습니다. 그래서 Default인 상태에서 Open하는 경우가 많이 있습니다. 위의 글도 그런 상황 이었습니다.

      감사합니다.

  4. 로또 2010.06.06 18:57 신고  댓글주소  수정/삭제  댓글쓰기

    2번, 3번은 의레히 있어왔던 일이고 별 문제가 없었는데
    왜 튜닝 프로젝에서만 갑자기 이슈가 되었을까요. ppt 에서도 특이한 해결책은 발견을 못했습니다.
    왜냐하면 대부분 상황에선 거의 문제가 안되는 이슈이고 당연한(??) 사항이기 때문이죠.

    2번 파라미터 관련 내용 :

    우선 설치후에 바로 최적화값을 찾겠다는 시도가 좀 이상합니다.
    실 운영에 가깝게 데이타도 그럴듯하게 쌓이고 애플리케이션도 돌아가서
    트래픽이 있는 상태에서 찾는다면 몰라도,
    달랑 DB 만 있는 상태에서는 뭘 수정할지 막막하기도 하고 한계가 있을건데요.

    또한 이론상은 성능관련 파라미터를 조정하면 물론 성능이 나아질수는 있겠지만
    수정한다는 시도 (수정값) 이 버그가 있는경우가 있어서
    플랫폼과 버전사이 교묘한 함수들의 지뢰밭을 전부 다 피해다녀야하는 리스크가 새롭게 발생하고,
    (이미 검증이된 디폴트가 세상에서 가장 안전한 값일수도)

    무사히 수정해도 드라마틱한 효과를 보기는 어렵고, 그 개선효과라는것이
    시시각각 바뀌는 시스템 트래픽의 오차범위내에 있다고 보여지는데,
    그래서 겨우겨우 찾는다고 한들 유저가 그 효과를 체감을 할수 있을지도 의문이고요.

    즉 찾는 과정도 매우 어렵지만, 찾는다고 한들 효과 보기도 어렵다는거죠.
    파라메터 몇개 바꿨더니 시스템이 확 빨라졌더라 이런거는 없다는겁니다.
    (개선효과를 구체적으로 수치화해서 제시하기도 어려울것임)
    그래서 대부분은 그냥 디폴트로 해도 큰 문제가 없으리라 보여지는데요.
    대부분의 문제는 SQL 이지 파라미터가 아니라는것이죠.

    파라미터가 이슈가 되는 경우는 좀 더 나은 성능찾기 상황이 아니라,
    특정버전에서 히든 파라미터와 버그가 결합되어 DB 가 사망하는 경우는 봤습니다.

    이때는 최적화 개념이 아니라 당장 죽고 사는 문제이기때문에
    누가 나서기를 기다리고 있는것이 아니고, 누군가에의해 곧바로 해결이됩니다.
    따라서 파라미터 자체가 이슈가 되어 장시간 늘어지는 경우는 못봤다는것입니다.


    3번 반영의 문제는 좀 어이없는 경우인데요.

    튜닝하는 사람이 튜닝후에 개발자가 받아봤을때 자신이 그 사이에 바뀌었는지를
    전혀 모른다는것은 버전관리가 안되었다는 뜻입니다.

    변경된것을 알고도 무시하고 반영했다는건 말이 안되고,
    변경되었는지 확인안했다는것도 말이 안되고. 어느경우든 어불성설입니다.

    튜닝 요청 제출하고 나서 소스가 변경되었다면, DBA 에게 알려줘야겠죠.
    하지만 이런 친절한 금자씨는 실제론 드물고 튜닝 다 끝난거 받아보고 나서 그때는 변경되었는데
    말 못해줘서 미안하다, DBA는 그말듣고 뒤집어진다 대충 이런 스토리로 흘러가게되죠.

    따라서 반영쪽은 개발쪽에서 개발자 본인 또는 PL급이 관리를 해줘야 하는데
    개발파트에서 당연히 관리가 되어야 하는 부분이 관리가 안되었다는뜻입니다.

    반대로, DBA (튜닝한사람) 가 꼭 해줘야 하는부분이 있는데,
    만약 튜닝과정에서 SQL 을 변경했을때는 원본과 결과값이 똑같다는것을
    튜닝한 사람이 직접 검증하고 나서 개발쪽에 넘겨주는것이 맞다고 생각합니다.(도의적으로도)

    원본과 비교 검증 책임을 DBA 가 안하고 개발자에게 알아서 하라고
    떠넘기는 경우를 봤는데 이건 심하게 말하면 무책임한 처사라고 보여집니다.

    수정을 했다면 검증까지 완벽히 해서 개발쪽에 넘겨주는것이 맞죠.
    대량 배치라면 검증 과정 자체가 또다른 튜닝기술이나
    DBA 권한이 필요할수도 있는것이기때문에.

    때로는 나는 너무나도 당연하다고 생각하지만 그것이 안되는경우가 있을순 있겠습니다.
    그 "당연하다"는 기준이 사람마다 약간씩 다를수도 있겠고 바빠서 깜빡할수도 있으니까요.
    하지만, 정규군이 아닌 특수병 (튜닝)에 추가비용을 쏟는 상황에서 이런 커뮤니케이션쪽의 문제를
    방치하는 PM 은 없으리라 보여집니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.06.07 10:06 신고  댓글주소  수정/삭제

      글의 처음에 나오는 고려사항을 언급하였음에도 많은 분들이 2,3번이 왜 문제가 되는지 이해할 수 없다고 하시는 군요. 이해가 갑니다. 그래서 글을 올릴 지 말지 고민 하였습니다.

      로또님의 말씀을 요약하면 2번은 '조정해도 별 이득이 없으므로 괜한 고생을 하지 말라'
      3번은 '너무나 기본적인것이 이슈가 되다니 어이가 없다" 혹은 "그런사항을 방치하는 PM이나 PL은 있을 수 없기 때문에 이슈가 될 수 없다."
      등이 되겠네요. 그런데 설치후에 바로 최적화 하겠다는 내용은 어디서 발견하셨죠? 그런 내용은 없는것 같습니다.

      2번에 대해서 말씀드리겠습니다. DB 만 달랑있는 상태는 아니었습니다. 기본적인 테스트 데이터가 들어오고 APPLICATION이 테스트로 돌아가는 상태였습니다. 그리고 파라미터는 9i 시스템의 것을 기초로 하여 에서 10g에 적용을 해놓은 상태였습니다. 예를 들면 ALL_ROWS가 아닌 first_rows 로 되어 있었지요. 그리고 SESSION_CACHED_CURSOR 는 30 으로 매우 낮은 상태였습니다. 다른 파라미터도 많지만 여기서는 두가지만 언급하겠습니다. 문제는 9i에 익숙한 DBA들도 10g 로 갈때는 많은 것을 고려해야 한다는 것입니다. 아마 다 아시는 내용일 것입니다. 앞으로 10g-> 11g 도 마찬가지라고 생각합니다.

      3번에 대해서 말씀드리겠습니다.
      살다보면... 그런사람도 있고 저런 사람도 있습니다.
      만약 그런상황을 닥쳤을때 "좀 어이가 없다" 라는 반응을 보인다면 서로 싸움이 일어날 가능성이 큽니다. 즉 어이가 없다고 생각할 수 있지만 개발자에게 이야기 할때는 감정을 가라 않히고 당연한 것도 차분히 이야기 해주어야 합니다. 서로 신뢰를 주는것이 중요합니다.

      대규모 튜닝프로젝트에서는 3번 이슈가 자주 일어납니다. 예를 들면 SQL의 답이 틀렸는데 PM 이 '그 SQL을 작성한 사람을 불러오라' 고 했는데 해당 개발자는 DBA가 최종적으로 SQL을 튜닝(재작성) 하였으므로 DBA에게 출석을 요구하는 경우도 있었습니다. 그래서 DBA는 튜닝하기전의 SQL을 꼭 저장둔다고 하네요. DBA는 죄가 없다는 것을 증명하기 위해 증거를 남기는 경우인데 심한 경우죠.

      이 경우많은 수의 튜너들은 냉정한 대처 대신에 개발자에게 화를 내는것을 많이 보았습니다. 왜냐하면 너무나 당연한 것이므로 개발자를 어이없이(개념없이) 보기 때문입니다. 이때 튜닝 프로젝트를 처음하는 튜너가 이런 일이 발생할 수 있다고 알고 있다면 침착하게 대처 할 수 있겠지요.

      '알고 했으면 유죄 모르고 했으면 무죄' 라는 말이 있듯이 대규모 프로젝트를 처음하는 초급 개발자의 경우 대부분 무죄였습니다. 다른말로 하면 한번 이야기 했더니 바뀐 SQL은 반영하지 않는 것은 물론이고 미래에 바뀔 SQL도 튜닝 하지 말라고 DBA 에게 통보해 주더군요. ^^

      내공이 많은 분들은 "너무나 당연한거 아니냐?"라고 말씀 하실 수 있지만 이 글을 읽고 도움을 받는 사람이 단 한명이라도 있을 것이라고 생각하여 쓴 것 입니다. 다시말 하면 의외로 프로젝트 막바지의 소방수는 많이 있었지만 대규모 튜닝 프로젝트는 안해본 사람이들이 많았습니다.

  5. 로또 2010.06.08 06:46 신고  댓글주소  수정/삭제  댓글쓰기

    친절하신 답변에 먼저 감사드립니다.

    구체적으로 물어보시니, 오해를 풀기 위하여 추가 올립니다.(말꼬리 잡는것은 아닙니다)

    설치후에 바로 최적화 하겠다는 내용은 어디서 발견하셨죠? 라고 문의하신 부분.
    => "개발 DB 가 설치된 지 한달 이 지났지만 성능관련 파라미터의 세팅을 완료하지 못함."

    여기서 "설치된지 한달이 지났지만" 이라는 부분을 그렇게 이해했습니다.
    => 한달이 지나도록 못했다. => 처음부터 시도는 하고 있었다.
    이렇게 유추가 된것입니다.
    테스트 데이타나 appl 은 본문엔 없고 저의 답글로 최초 언급하신것이고요.

    난독증인지 설명이 부족한건지 모르겠네요. 전자일지도 ^^

    또한, 저의 글을 나름 요약을 하셨는데 둘다 잘못 짚으셨습니다.

    괸한 고생만하지 별 이득이 없다는것이 아니고 저의 진의는,
    즉 +10% 향상을 바라다가 버그에 빠지는 리스크가 있다는걸 말씀드리고 싶었습니다.

    이득이 아니라 리스크에요.
    님과 저는 보는 관점이 많이 다릅니다. (성능 위주 vs 안정성 위주)
    블로그 일부글은 히든 파라미터 조정하는것이 일부 있더군요. 글쎄요.

    DB는 안정성(정합성) > 성능 > 운영편리성 이렇게 생각하기때문에
    그 어떤 경우에라도 안정을 먼저 고려하고, 오라클을 쓰면서도 100% 믿지는 않습니다.
    오라클 자체도 소프트웨어에 지나지 않고 언젠가는 버그로 배신해서 내 뒤통수를 때릴 놈이기에.

    예를 들자면 index 관련 파라미터도 온라인용이면 디폴트를 수정해서 몇개 수정하는것이
    좋다는걸 어디선가 본적이 있는데 현장에 적용해보려고 하니 10g 는 버그가 있더군요.
    그런뜻입니다.

    이런것까지 버그 문서를 미리 전부 뒤져가면서 확인후 적용한다면 이부분도 역시 할말 없습니다.
    버그는 보통 문제 터진다음에 찾으니까요.

    하지만 저에게 하라고 한다면 그렇게는 당연히 안하고 10% 향상을 다른쪽으로 찾을겁니다.
    모든것은 결국 고객의 만족도라고 생각하고 그것엔 온오프적 많은점을 시사합니다.

    조금 확장하면 튜닝 불가상황을 디자인만 바꾸면 OK 가 됩니다.
    좀더 확장하면 전혀 다른것이 나옵니다.

    이것은 과연 튜닝이란것이 고객이냐 튜닝하는 사람 자신이냐
    누가 더 궁극적으로 만족해야 할까요.

    만족도라는것의 개념은 기술적으로 포장하거나 속이자는것은 물론 아닙니다.
    고객사측에서도 DBA 가 나와서 검증시키고 이해시키면 됩니다. 스스로의 양심도 있을것이고.


    3번 관련한것은 아주 소소하지만 중대한 커뮤니케이션 문제입니다.

    3번은 있을수 없다가 아니고,
    의사소통은 사람이 사는곳엔 어디서나 생기는 기본적인 문제이기때문에
    같이 풀어가면 된다는것이죠.

    죽마고우나 친한 사람끼리는 표정만 보면 압니까 ?
    처음 보는 사람은 그 속마음 아는데 얼마나 걸립니까.
    프로젝 험한 상황에서 마주친 사람끼리 상대방 마음을 얼마나 알겠습니까.

    사람간의 문제는 결국 디지털이 아니라 아날로그인것입니다.

    이런 문제는 혼자나 팀단위로 끌어안고 고민 안하고 PM 또는 이슈제기해서 같이 해결합니다.
    제 3자의 냉정한 관점과 리더십이 필요하기도하고,
    개발자는 눈앞의 개발과 디버깅이 중요하지 튜닝 이런거 신경 안쓰기때문이죠.

    나의 입장이 절대적으로 옳고, 중요하고, 반드시 그렇게 가야할것 같지만
    상대방 입장도 똑같습니다. 그래서 중재자가 필요합니다.

    튜닝전의 원본은 증거개념이 아니라 백업개념으로 당연히 남겨두는건줄 알았는데
    그게 아닌가보죠.
    원본을 덮어 쓰나요 ? 하긴 사람마다 습관이 다르니까요.

    대규모 프로젝 경험을 유난히도 강조하시는데,
    사실 커뮤니케이션 문제는 사람이 사는곳이면 대규모든 아니든 어디서나
    항상 발생될수 있는 문제입니다.
    쥐꼬리 만한 일을해도 옆자리 동료랑 말이 안통하는 문제가 생길수도 있는것이고요.

    결국 이런 소소한 사람간의 문제가 대규모이기때문에 더 큰것처럼,
    다른 문제처럼 보이는 착시현상일 뿐입니다.
    파라미터도 꼭 대규모 튜닝프로젝트여야만 문제가 된다는 논리도 좀 이상하게 들리네요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.06.08 09:28 신고  댓글주소  수정/삭제

      말씀하신대로 리스크는 항상 있습니다. 또한 소규모 프로젝트도 문제가 될 수 있지요. 생각하신대로 하시면 됩니다. 장문의 댓글 감사합니다.

    • 로또님 2010.06.08 19:07 신고  댓글주소  수정/삭제

      로또님의 글은 반대를 위한 반대가 아닌가요?
      최소한 저에게는 그렇게 보여지는 군요.

      3번에 대하여
      "의사소통은 사람이 사는곳엔 어디서나 생기는 기본적인 문제이기때문에
      같이 풀어가면 된다는것이죠." 라고 하셨는데 위에서 로또님은 그런 이야기를 하지도 않았습니다. 오히려 개발자와 소통해야 한다는것은 extremedba 님이 하셨구요.

      로또님이 이야기 하신것은 "너무나 당연한것이다. 그래서 이슈가 됬다는 것이 어이가 없다"는 것이 로또님이 하신말씀 맞는것 같습니다.

  6. 로또 2010.06.11 07:47 신고  댓글주소  수정/삭제  댓글쓰기

    새로운 글이 있네요.

    반대를 위한 반대인지 아닌지는 하늘이 내려보고있고 저도 양심이란것이 있습니다.

    전체적으로 무엇을 말하는지 진의를 보지 않고,

    세세한 문장 하나 하나 잡고 다 따진다면 참으로 피곤한 일입니다. .

    • 로또님의 글도 이슈 2010.06.20 23:35 신고  댓글주소  수정/삭제

      "전체적으로 무엇을 말하는지 진의를 보지 않고,
      세세한 문장 하나 하나 잡고 다 따진다면 참으로 피곤한 일입니다."

      라고 하셨는데 로또님의 글을 읽고 제가 요약을 해도 동규님의 요약과 다르지 않은데요? 다만 동규님의 요약에 리스크를 추가하면 더욱 완벽한 요약일 것입니다. 여러사람이 동규님과 비슷하게 요약한다면 그것은 말꼬리 잡는것이 아니라 윗분이 언급하신 반대를 위한 반대가 맞습니다. 의심이 가시면 옆의 분들에게 로또님의 글을 요약해보라고 해보세요. 어떻게 요약하는지. 전체적으로 로또님의 글이 그렇게 되어 있습니다. 본인의 글을 한번 읽어 보세요.

      2번 '조정해도 별 이득이 없으므로 괜한 고생을 하지 말라. 리스크만 커진다'
      3번 '너무나 기본적인것이 이슈가 되다니 어이가 없다" 혹은 "이슈가 될수 없다"

      이런 댓글은 블로그를 작성한 주인장이 기분 나쁘게 생각할 수 있다는것 입니다. 물론 아닐 수도 있습니다.

      이 블로그의 다른글에서 파라미터를 수정하는 이유는 그 파라미터가 어떤일을 하는지 보여주려고 하기 때문일것 같습니다. 토마스 카이트의 이펙티브 오라클 이란 책을 보면 성능관련 파라미터를 이리저리 수정해서 테스트 하지요. 또한 조나단 루이스의 책과 동규님의 책도 마찬가지 입니다.

      이런 사람들은 안정성을 고려하지 않는 것이 아니라 글을 성능관점에서 쓰기 때문입니다. 하지만 로또님의 말처럼 "성능 파라미터를 default가 아닌값으로 수정하라" 라고 느낄 수 는 있습니다. 하지만 그러한 유추에 의한 잘못된 결과(버그등)를 저자들에게 책임을 돌릴 수는 없을것입니다.

      동규님 글의 매력은 여러가지 유추가 가능하다는 것입니다. 왜냐하면 글을 읽고 사람마다 여러가지 다른 생각이 나올 수 있기 때문이지요. 제가 생각하는 동규님의 진의는

      "성능 파라미터는 벤더사와 협력하고 여러가지 조사를 통하여 파라미터의 초기값을 정하고 미세조정 단계를 거쳐 확정하는 단계가 있어야 한다"

      인것 같습니다. 다시말하면 파라미터의 값을 따지는 것이 아니라 성능파라미터의 확정 절차(프로세스)를 이야기 하는 것이 동규님의 진의 인것 같습니다. 이런글을 쓰는 분은 한국에 별로 없습니다. 제겐 도움이 되느데요? 미세조정 단계라...

  7. ExtraOdinary 2010.10.07 13:46 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 ^^ 이제 막 고객사에서 컨설팅 업무를 하고 있는 튜너 입니다.
    길지 않은 경력이지만 위에 언급하신 내용들이 와닿네요..ㅋ 특히 4번의 경우는 참 해결하기도 어렵고 어떻게 접근해야 할 지도 말 꺼내기가 쉽지 않은 경우가 많았습니다.
    아마 저같은 사람들을 위해 글을 올리신 것 같습니다. 앞으로 많은 도움이 되는 좋은 정리였던 것 같습니다.
    종종 여기와서 많은 걸 배우고 가다가 이글에는 왠지 댓글이 달고 싶더라구요...ㅋ
    앞으로도 부탁드리겠습니다!

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

      ExtraOdinar님 반갑습니다. 4번의 경우는 저도 고생을 하였습니다. 목표 수준이 워낙 높게 잡혀 있어서 DB 튜닝 만으로는 불가능 한 경우 였습니다. 이 글에 대해 좋게 생각해 주시니 고맙습니다.

SQL 튜닝시의 업무적 접근
SQL
튜닝시 Buisiness 관점으로 접근하는 것은 매우 중요한 일이다. 예를 들면 SQL 아무리 튜닝을 해도 해결되지 않는 경우가 있다. 당신이 아무리 화려한 튜닝 테크닉을 가지고 있다고 해도 말이다. 이런 경우 발상을 전환하는 것이 필요하다. SQL 튜닝을 하지않고 이슈를 해결해야 한다는 이다. 튜닝시 이슈가 가장 많이 발생하는 3가지 경우를 소개한다.

조회조건은
제약이 필요없나?
웹화면에서 조회조건으로 일자 From ~ To 조건으로 주는 경우가 다반사이다. 사용자는 많은 기간을 조회하고 싶어한다. 실제로 10년간의 거래내역집계를 조회하는 경우도 보았다. 이런 경우는 반드시 화면에서 기간의 제한을 두어야 한다. 한달로 제한 한다든지 아니면 일주일로 한다든지 상황에 따라서 얼마든지 제약을 줄수 있다. 조회조건 선택시 Combo Box Drop Down List Box 에서 전체보기를 없앤다면 마찬가지로 성능향상을 기대할수 있다. 개발자와의 대화 혹은 화면분석이 필요한 순간이다. 필자는 이러한 작업들을 튜닝 프로젝트시에 반드시 실행해야하는 필수 과정으로 생각한다. 이러한 과정을 사용자 화면 튜닝으로 정의 해야한다.

집계 테이블은 필요한가?
다른 예제는 SQL 집계(Group By + Count) 하는 경우 아무리 튜닝을 해보아야 느릴수 밖에 없다. 가장 좋은 것은 회의를 통하여 이러한 화면들을 DW시스템으로 넘기는 것이다. 이것은 어느정도의 정치력이 필요하다. 주장하는 사람의 권위와 Power 필요하다는 이야기 이다. 하지만 이것이 안될경우 업무팀과 협의 하여 집계 테이블을 만들어야 한다. 집계테이블은 최소화 시켜야 한다. 예를 들어 년별, 지점별 집계 SQL 있고 월별 지점별 집계 SQL 있다면 집계 테이블은 월별로 하나만 만들어야 한다. 집계 테이블이 많아지면 정합성을 저해할수 있다.

 

저작의도를 알면 길이 보인다
계약
테이블과 고객 테이블을 불필요하게 조인 하는 SQL 있었다. 하지만 조인만 하고 Select 절에서는 고객 테이블의 컬럼이 하나도 없었다. 상식적으로 보면 계약테이블의 고객번호는 100% 고객 테이블에 있어야 한다. 그렇다면 고객 테이블을 From 절에서 삭제 하면 될까? 아주 위험한 발상이다. 특수한 SQL 경우에는 검증용 SQL 프로그램으로 만들기도 한다. 이러한 경우 방법은 한가지이다. SQL 작성한 개발자에게 질문하여 이것이 계약 테이블의 고객번호를 검증하기 위한 SQL 인지 물어보는 것이다. 경험상 대부분의 경우 불필요한 조인을 삭제할수 있었다. 이러한 상황은 특히 개발자들 끼리 SQL Copy 약간 수정하여 사용하는 경우 많이 발생 하게된다.

회의나 대화도 중요한 튜닝 Skill 이라는 점
여러분은
위의 3가지 경우를 어떻게 생각하는가? 튜닝의 기술적인 Skill 아니므로 무시하고 넘어가는 경우를 많이 보아왔다. 절대 문제를 해결할수 없으며 문제를 키울 뿐이다. 이렇게 되지 않으려면 튜닝시에 사용자 혹은 개발자와 자주 회의를 가져야 한다. 경험상 대화를 자주 할수록 문제의 해결 혹은 대안을 빨리 찾는 경우가 많았다. 이때 고객과의 대화의 기술 혹은 설득의 기술이 많은 도움이 된다. 이러한 기술들은 튜닝 자체의 Skill 보다 상위의 개념이며 문제를 해결할 경우 팀원이나 직원들에게 존경을 받거나 부러움의 대상이 가능성이 크다.


결론:
실제로 SQL 튜닝 등으로 성능향상이 불가능한 경우는 당신이 생각하는 것 보다 훨씬 많다.
이럴 경우 화려한 튜닝 테크닉으로 문제를 해결을 시도하는 사람, 대화와 설득으로 해결하는 사람 누가 더 뛰어난 튜너인가?



신고
Posted by extremedb

댓글을 달아 주세요

  1. 혈기린 2009.09.16 10:45 신고  댓글주소  수정/삭제  댓글쓰기

    가장 어려운 튜닝은 사실 고객을튜닝(?) 하는것일지도 모르겠네요
    꽉막히고 고집센 현업을 만나면 ~~~ 설득의 기술 대화와 협상의 기술이 필요할지도 ㅎㅎ
    좋은글 매번 감사 드립니다 ^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.09.16 12:44 신고  댓글주소  수정/삭제

      공감 합니다.^^
      말씀 하신대로 대화의 기술은 튜닝 뿐만 아니라 인생전체에 대해서도 영향을 끼치니 반드시 익히는것이 좋겠죠.^^

  2. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.09.16 11:24 신고  댓글주소  수정/삭제  댓글쓰기

    저도 동규님 포스팅에 공감을 표합니다.
    역시나 튜닝스킬로는 한계가 있고 업무 내용까지 아는것이 가장 좋다는 것을 요즘 많이 느끼고 있습니다.

    아마도 향후 튜닝이라 함은 'Application - Tech - Database - Module' 4개를 관통하는 서비스가 되지 않을까 싶습니다. 저 또한 'module-tech consultant'가 되는게 목표긴 하지만 너무 방대하기에 호흡을 길게 가져가고 있습니다.

    아마 동규님은 저보다 저 멀리서 먼저 걸어가시고 있으시니 힘드시더라도 뒤에서 여러사람들이 응원하고 있다는 것을 알아주시기 바랍니다. ^^

    그럼 오늘 하루도 행복하세요~

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.09.16 12:46 신고  댓글주소  수정/삭제

      성원에 감사드립니다.
      4가지를 관통하는 튜닝은 앞으로 대세가 될거 같습니다.
      그렇게 하려면 APM TOOL 이 우선적으로 도입되어야 겠죠.
      제니퍼 같은 APM 툴은 상당히 유용하다고 들었습니다.
      좋은하루 되세요.

  3. 만두돌이 2009.09.16 21:52 신고  댓글주소  수정/삭제  댓글쓰기

    좋은글 감사합니다.
    자주 들릴께요 ^^;

  4. feelie 2009.09.16 22:06 신고  댓글주소  수정/삭제  댓글쓰기

    가끔 기술적인 부분에서 벗어나 데이터 이외의 고려사항까지 기술해주시니 감사할 딸름입니다.

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

      말씀 하신대로 기술로 해야할것과 대화로 풀어야 할 것을 잘 분별 하는것이 핵심이라고 할수 있겠네요.
      늦은밤에도 열심히 하시네요.
      좋은밤 되세요.

  5. 금땡이 2009.09.18 11:08 신고  댓글주소  수정/삭제  댓글쓰기

    중요한 통찰 입니다.
    많이 바쁘신 줄 알지만 이러한 내용들이 이 블로그를 방문하는 다양한 분들에게 도움이 될 것 같습니다.
    잠을 줄여서라(^^)도 글을 많이 올려 주시면 합니다.

    이미 님은 혼자의 몸이 아닌 것 같습니다. 건강에 유념하시기를....

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.09.18 11:33 신고  댓글주소  수정/삭제

      오랜만 입니다.
      '잠을 줄여서라도 글을 올려라' 는 말에 동감합니다.
      노력해 보겠습니다.
      현재는 매일 5시간 정도 수면을 취하고 있습니다.
      좋은 하루 되세요.

  6. 호야 2009.09.21 18:10 신고  댓글주소  수정/삭제  댓글쓰기

    포스팅에 공감이 가네요..

    아직도 튜너를 신으로 알고 일단 해보라고 하는 고객이 이미 다 제한할것은 했다고 하고 업무를 변경안하려는 하는데

    커뮤니케이션 스킬이 절대적으로 필요하더군요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.09.21 18:19 신고  댓글주소  수정/삭제

      네 맞습니다.
      경험상 거의 1/3 의 경우에 튜너 혼자 해결하지 못하는 경우가 발생 하더군요. 말씀 하신것 처럼 이럴경우는 대화로 문제를 해결하는 것이 가장 좋은 방법이 되겟네요.
      좋은 하루 되세요.^^