일반적인 의견
흔히 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
,

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

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

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

 

포장마차에서 지인에게 재미있는 이야기를 들었다. 물론 공장 이야기 이다. 나는 이야기를 재미있게 들었지만, 지인의 입장에서는 머리가 쭈뼛쭈뼛 서는 심각한 일이었다. 사건은 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' 카테고리의 다른 글

Sort 부하를 좌우하는 두 가지 원리  (11) 2011.03.29
SQL튜닝 방법론  (20) 2011.01.27
Pagination과 분석함수의 위험한 조합  (26) 2010.12.23
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (7) 2010.02.11
Posted by extremedb
,

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
,

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

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

-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 부하를 좌우하는 두 가지 원리  (11) 2011.03.29
SQL튜닝 방법론  (20) 2011.01.27
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (7) 2010.02.11
USE_CONCAT 힌트 제대로 알기  (5) 2009.07.17
Posted by extremedb
,

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

 

이 글을 보기 전 고려사항

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

 

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의 내용을 같이 보야야 이해가 빠를것이다.


invalid-file

튜닝시_이슈극복_사례



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

Posted by extremedb
,

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
,