'SORT ORDER BY'에 해당되는 글 2건

  1. 2011.04.28 Sort Merge Join에 대한 오만과 편견 476
  2. 2011.03.29 Sort 부하를 좌우하는 두 가지 원리 11

Sort Merge Join에 대한 오만과 편견

Oracle/Data Join Method 2011. 4. 28. 07:00

일반적인 의견
흔히 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_id가 equal 조건이냐 아니면 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
요즘 워낙 바빠서 예전에 미리 글을 써놓지 않았더라면 글을 하나도 올리지 못할뻔 하였다. 
저작자표시 비영리 동일조건

'Oracle > Data Join Method' 카테고리의 다른 글

PARTITION WISE JOIN이란 무엇인가?  (406) 2013.05.21
같은 테이블을 두 번 읽었지만 일량이 틀려요  (16) 2011.01.14
Oracle 조인 방법 25가지  (15) 2010.02.26
Nested Loop Join 성능향상과 관련된 2가지 원리  (17) 2009.05.09
Hash Join Right (Semi/Anti/Outer) 의 용도  (17) 2009.03.02
Posted by extremedb
,

Sort 부하를 좌우하는 두 가지 원리

Oracle/SQL Tuning 2011. 3. 29. 15:11

▶적절한 인덱스가 없을 때 Sort의 부하를 줄이는 방법

▶Pagination에서 Sort의 부하 줄이기

▶주의사항

 

Order by 절에 의한 Sort의 부하는 성능에 치명적이다. Block I/O의 최소화는 분명 튜닝의 핵심이다. 하지만, Block I/O를 최소화 해도 Sort의 부하가 심하다면 결코 만족스런 성능을 내지 못한다. 특히 “페이징 처리용 SQL에는 Sort를 대신할 수 있는 인덱스가 있어야 성능을 확보할 수 있다”고 많은 튜너들이 주장한다. 맞는 말이다. 그렇게만 된다면 Sort가 전혀 발생하지 않을 테니까. 하지만, 다음과 같은 어려움도 있다.

 

인덱스 최적화가 힘든 이유
첫 번째, 인덱스를 모든 조회화면의 기준에 맞게 만들려면 테이블마다 많은 수의 인덱스가 필요할 것이다. 두 번째, 운영중인 환경에서 인덱스를 생성 혹은 변경하기는 매우 어렵다. 따라서, 인덱스를 만들기 어렵다면, Sort의 부하를 최소화하는 다른 방법은 없는지를 고려해야 한다. 분명히 방법은 있다. 이 방법을 알지 못한다면 오직 인덱스에만 목숨을 거는 사람이 될 가능성이 높다. 오늘은 인덱스를 전혀 만들지 않은 상태에서 Sort의 부하를 최소화 하는 방법에 대해 알아볼 것이다.

 

단 한 가지 개념만 안다면, Order By에 의한 Sort의 부하를 이해한 것이다. Sort의 부하량은 면적에 비례한다는 것. 이 개념은 아래와 같이 표현할 수 있다. 참고로 ∝는 비례한다는 의미이다.

 

Sort의 부하량(PGA 사용량) ∝ 세로(결과 건수) X 가로(컬럼 Size 합계)

 

공식의 이해가 부족하다
주위의 지인들에게 위의 식을 질문한 결과 거의 모두가 세로에 대해서는 정확히 이해하고 있었다. 즉, Sort할 건수가 많아지면 Sort의 부하가 증가 한다는 것이다. 이에 반해서 가로에 대해서는 정확한 이해를 하는 사람이 드물었다. 대부분, Order By절에 의해 Sort의 부하가 발생하므로 Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각하는 것이다. 다시 말해, Order By절의 컬럼이 세 개라면, 세 컬럼의 Size를 합친 것이 가로라는 것이다. 과연 그럴까?


위의 주장을 검증하기 위해 테이블을 하나 만들고, 추가적으로 컬럼을 3개 만들자.

 

CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;

ALTER TABLE SALES_T ADD (char_100  CHAR(100)  DEFAULT 'a' NOT NULL );

ALTER TABLE SALES_T ADD (char_1000 CHAR(1000) DEFAULT 'a' NOT NULL );

ALTER TABLE SALES_T ADD (char_2000 CHAR(2000) DEFAULT 'a' NOT NULL );

 

추가된 컬럼은 모두 Char Type이며 Default 값이 ‘a’ 이다. Char Type이므로 Default값인 ‘a’가 들어오는 경우 컬럼 size는 각각 100, 1000, 2000 바이트씩 채워진다. 이제 이 컬럼들을 이용하여 SQL을 각각 실행해보자. 100 byte, 1000 byte, 2000 byte 컬럼으로 각각 Sort하여 Sort의 부하가 어떻게 달라지는지 알아보자.

 

CREATE TABLE SORT_100 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_100

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_100 ;

 

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

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

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:01.41 |    7323 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:01.41 |    7323 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:01.23 |    5915 | 4708K (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.15 |    5915 | 3471K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.21 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:00.66 |    4447 |          |

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

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:00.59 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("S"."CUST_ID"="C"."CUST_ID")

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

100 byte컬럼으로 Sort하니 PGA를 4.7MB 사용하였다. 이제 100 byte보다 10배나 큰 1000 byte 컬럼으로 Sort 하여 PGA 사용량을 비교해보자.

 

CREATE TABLE SORT_1000 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_1000

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_1000 ;

 

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

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

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:02.17 |   13162 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:02.17 |   13162 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:01.75 |    5915 |   30M (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.56 |    5915 | 3486K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.22 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:01.05 |    4447 |          |

|*  6 |      TABLE ACCESS FULL | PRODUCTS  |      1 |      1 |00:00:00.02 |       7 |          |

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:00.98 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("S"."CUST_ID"="C"."CUST_ID")

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

Sort 컬럼의 size를 100에서 1000 byte로 늘리자 PGA 사용량도 4.7 MB에서 30 MB로 크게 늘었다. 내친김에 Order By절을 2000 byte 컬럼으로 바꿔서 PGA 사용량이 얼마나 늘어나는지 테스트 해보자.

 

CREATE TABLE SORT_2000 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_2000

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_2000 ;

 

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

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

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:03.16 |   19298 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:03.16 |   19298 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:02.06 |    5915 |   58M (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.74 |    5915 | 3515K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.24 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:01.19 |    4447 |          |

|*  6 |      TABLE ACCESS FULL | PRODUCTS  |      1 |      1 |00:00:00.02 |       7 |          |

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:01.12 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("S"."CUST_ID"="C"."CUST_ID")

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

예상대로 Sort 대상 컬럼을 1000에서 2000 byte로 바꾸자 PGA 사용량이 30 MB에서 58MB 로 약 두 배 늘었다. 위의 결과를 언뜻 보면, Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각할 수 있다. 왜냐하면, Sort 대상컬럼의 Size에 비례하여 PGA 사용량이 증가되었다고 판단하기 때문이다. 하지만 이런 생각은 절반만 옳고 나머지 절반은 틀렸다. 제대로 된 식은 다음과 같다.

 

Sort의 부하를 좌우하는 원리

Sort의 부하량(PGA 사용량) ∝ 세로 X 가로

세로: SQL의 결과 건수

가로: Order by 절의 컬럼 size + Order by 절을 제외한 나머지 컬럼의 size

 

근거 있는 주장인가?
이 공식이 글 전체의 핵심이다. 하지만, 많은 사람들이 위와 같은 가로 세로 개념을 주장할 수 있는 근거가 무엇인지 궁금해한다. 이제 가로가 Order by 절의 컬럼 size + 나머지 컬럼의 size라는 주장에 대한 근거를 보자.

 

SELECT s.channel_id, s.char_2000

  FROM sales_t s

 WHERE s.prod_id = 30

 ORDER BY s.channel_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |  29282 |00:00:00.84 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |  29282 |00:00:00.84 |    4440 |   56M (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |      1 |  29282 |00:00:00.68 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

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

 

Order By절에는 Size가 작은 channel_id 컬럼 뿐이다. 그런데 PGA 사용량은 56 MB나 된다. 세로가 3만 건도 안 되는 집합을 Sort하는데 그 부하는 56 MB나 된다. 이상하지 않은가? 과부하의 이유는 Select절의 char_2000 컬럼 때문이다. 이 컬럼을 Select 절에서 제거하고 다시 실행해 보자.

 

SELECT s.channel_id

  FROM sales_t s

 WHERE s.prod_id = 30

 ORDER BY s.channel_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |  29282 |00:00:00.64 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |  29282 |00:00:00.64 |    4440 |  424K (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |      1 |  29282 |00:00:00.58 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

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

 

Order By절 이외의 컬럼에 주목하라 
Select절의 char_2000 컬럼을 제거하자 Sort의 부하는 424K로 급격히 줄어들었다. 왜냐하면, Sort Area에는 Order By절의 컬럼을 Sort할뿐만 아니라 나머지 컬럼 List도 Loading 되기 때문이다. Order By절 뿐만 아니라, Select 절에도 size가 큰 컬럼이 있다면 성능이 급격히 저하됨을 알 수 있다. 지금까지 Sort부하량 공식에 의해 가로는 Order By절 컬럼 Size + Sort 대상 이외의 컬럼 Size가 됨을 증명해 보았다.

 

이제 이 개념을 실제 SQL 프로그래밍에 적용해보자. 아래는 전형적인 Pagination SQL이다. 아래의 예제에서 처음의 약속을 지키기 위해 인덱스를 만들지도, 사용하지도 않을 것이다.
 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT s.prod_id, p.prod_name, p.prod_category_desc, s.cust_id,

c.cust_first_name, c.cust_last_name, s.time_id, s.channel_id,

                       s.char_100, s.char_1000, s.char_2000

                  FROM sales_t s, customers c, products p

                 WHERE s.cust_id = c.cust_id

                   AND s.prod_id = p.prod_id

                   AND s.channel_id = 3

                 ORDER BY c.cust_first_name, c.cust_last_name, p.prod_category_desc, s.time_id ) a

         WHERE ROWNUM <= :v_max_row ) --> 200 대입

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

 

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

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

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

|   0 | SELECT STATEMENT         |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  1 |  VIEW                    |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  2 |   COUNT STOPKEY          |           |      1 |    200 |00:00:08.71 |    5915 |          |

|   3 |    VIEW                  |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  4 |     SORT ORDER BY STOPKEY|           |      1 |    200 |00:00:08.71 |    5915 | 3321K (0)|

|*  5 |      HASH JOIN           |           |      1 |    540K|00:00:06.30 |    5915 | 1176K (0)|

|   6 |       TABLE ACCESS FULL  | PRODUCTS  |      1 |     72 |00:00:00.01 |       7 |          |

|*  7 |       HASH JOIN          |           |      1 |    540K|00:00:03.91 |    5908 | 3568K (0)|

|   8 |        TABLE ACCESS FULL | CUSTOMERS |      1 |  55500 |00:00:00.21 |    1468 |          |

|*  9 |        TABLE ACCESS FULL | SALES_T   |      1 |    540K|00:00:01.14 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

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

   2 - filter(ROWNUM<=:V_MAX_ROW)

   4 - filter(ROWNUM<=:V_MAX_ROW)

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

   7 - access("S"."CUST_ID"="C"."CUST_ID")

   9 - filter("S"."CHANNEL_ID"=3)

 

페이징 처리된 SQL의 Sort 부하량은 3321K 이다. 이제 Sort의 부하를 줄이기 위해 select 절의 모든 컬럼을 제거하자.

 

SELECT s.prod_id, p.prod_name, p.prod_category_desc, s.cust_id,

c.cust_first_name, c.cust_last_name, s.time_id, s.channel_id,

        s.char_100, s.char_1000, s.char_2000

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT s.rowid as s_rid, p.rowid as p_rid, c.rowid as c_rid

                  FROM sales_t s, customers c, products p

                 WHERE s.cust_id = c.cust_id

                   AND s.prod_id = p.prod_id

                   AND s.channel_id = 3

                 ORDER BY c.cust_first_name, c.cust_last_name, p.prod_category_desc, s.time_id ) a

         WHERE ROWNUM <= :v_max_row ) a,   --> 200 대입   

       sales_t s, customers c, products p

   WHERE s.rowid  = a.s_rid

   and c.rowid = a.c_rid

   and p.rowid = a.p_rid

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

 

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

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

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

|   0 | SELECT STATEMENT              |           |      1 |    200 |00:00:06.51 |    6168 |          |

|   1 |  NESTED LOOPS                 |           |      1 |    200 |00:00:06.51 |    6168 |          |

|   2 |   NESTED LOOPS                |           |      1 |    200 |00:00:06.51 |    5969 |          |

|   3 |    NESTED LOOPS               |           |      1 |    200 |00:00:06.51 |    5918 |          |

|*  4 |     VIEW                      |           |      1 |    200 |00:00:06.51 |    5915 |          |

|*  5 |      COUNT STOPKEY            |           |      1 |    200 |00:00:06.51 |    5915 |          |

|   6 |       VIEW                    |           |      1 |    200 |00:00:06.51 |    5915 |          |

|*  7 |        SORT ORDER BY STOPKEY  |           |      1 |    200 |00:00:06.51 |    5915 |96256  (0)|

|*  8 |         HASH JOIN             |           |      1 |    540K|00:00:05.81 |    5915 | 1193K (0)|

|   9 |          TABLE ACCESS FULL    | PRODUCTS  |      1 |     72 |00:00:00.01 |       7 |          |

|* 10 |          HASH JOIN            |           |      1 |    540K|00:00:03.65 |    5908 | 4514K (0)|

|  11 |           TABLE ACCESS FULL   | CUSTOMERS |      1 |  55500 |00:00:00.22 |    1468 |          |

|* 12 |           TABLE ACCESS FULL   | SALES_T   |      1 |    540K|00:00:01.06 |    4440 |          |

|  13 |     TABLE ACCESS BY USER ROWID| CUSTOMERS |    200 |    200 |00:00:00.01 |       3 |          |

|  14 |    TABLE ACCESS BY USER ROWID | PRODUCTS  |    200 |    200 |00:00:00.01 |      51 |          |

|  15 |   TABLE ACCESS BY USER ROWID  | SALES_T   |    200 |    200 |00:00:00.01 |     199 |          |

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

 

Predicate Information (identified by operation id):

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

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

   5 - filter(ROWNUM<=:V_MAX_ROW)

   7 - filter(ROWNUM<=:V_MAX_ROW)

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

  10 - access("S"."CUST_ID"="C"."CUST_ID")

  12 - filter("S"."CHANNEL_ID"=3)

 

 

Trade Off가 유리한 경우

Sort 부하량이 3321K에서 96K로 약 34.5배 줄어들었다. 이렇게 ROWID만 남기고 select 절의 모든 컬럼을 제거해도 결과는 같다. 왜냐하면, Sort된 상태로 rowid가 보관되어있기 때문이다. 페이징 처리가 모두 끝나고 200건에 대해서만 rowid로 테이블에 접근하기 때문에 테이블의 중복사용에 의한 비효율은 매우 적다. Buffers 항목을 비교해보면 5915 블록에서 6168 블록으로 비효율은 253 블록(4%) 밖에 차이가 나지 않는다. 하지만 Sort의 부하는 34.5배나 줄어들었다. 약간의 Block I/O를 손해 보더라도 Sort의 부하가 아주 큰 경우는 같은 블록을 중복해서 읽어야 함을 알 수 있다.

 

장점 + 장점

이렇게 Rowid를 제외한 Select List를 인라인뷰 외부로 빼면, Sort 부하의 최소화 이외에 또 다른 효과를 누릴 수도 있다. 인덱스만 읽고 테이블로의 접근을 하지 않을 수 있다. 즉 Where 조건에 최적화된 인덱스가 존재하고, 그 인덱스가 Order By절 컬럼을 포함 한다면 인라인뷰 내부에서는 테이블 접근을 하지 않는다. 물론 Select List의 모든 컬럼들을 가져오려면 테이블을 접근해야 한다. 하지만 위의 예제처럼 Rowid를 사용했다면 페이징 처리가 끝난 후에, 특정 페이지에 해당하는 건들만 테이블로 접근할 수 있으므로 Random Access 도 최소화 된다. Sort를 최소화 하려고 했더니 Block I/O를 최소화 하는것까지 덤으로 얻을 수 있는 것이다.    

 

주의사항

SELECT 절에 상수나 변수 심지어 NULL이 오더라도 PGA 사용량은 증가하므로 주의해야 한다.
 

SELECT s.cust_id

  FROM sales_t s

 ORDER BY s.cust_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |    918K|00:00:03.38 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |    918K|00:00:03.38 |    4440 |   13M (0)|

|   2 |   TABLE ACCESS FULL| SALES_T |      1 |    918K|00:00:01.38 |    4440 |          |

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

 

Select 절에 다른 컬럼이 없기 때문에 PGA를 13MB 사용 하였다. 이번에는 Select절에 Null을 추가해보자.

 

SELECT s.cust_id, null

  FROM sales_t s

 ORDER BY s.cust_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |    918K|00:00:03.48 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |    918K|00:00:03.48 |    4440 |   17M (0)|

|   2 |   TABLE ACCESS FULL| SALES_T |      1 |    918K|00:00:01.37 |    4440 |          |

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

 

Select List에 Null을 추가하자 PGA사용량이 4MB 증가했다. 오라클은 Null이나 상수도 컬럼처럼 취급함을 알 수 있다. 따라서 Order by절이 있는 인라인뷰의 Select List에 상수나 변수 혹은 Null을 집어넣는 것은 Sort의 부하를 증가시킨다. 상수나 변수는 Order By가 있는 인라인뷰에 넣지 말고 외부로 빼서 사용하면 된다.

결론
Sort의 부하를 최소화 하려면 Order By절의 컬럼에만 집중해서는 안되며, 전체 컬럼 List를 바라보아야 한다. 또한 프로그래밍을 할 때 상수 하나, 변수 하나의 위치도 고려해야 최적의 성능을 가진 프로그램이 됨을 알 수 있다. 즉 Sort의 최적화는 튜닝의 문제일 뿐만 아니라 프로그래밍의 문제인 것이다. 이점은 Sort 부하량이 무엇으로 결정되는지 개발자도 알아야 하는 이유가 된다.

면적은 가로와 세로로 구성된다.

Sort의 부하량은 면적의 크기에 비례한다.

Sort의 부하량 ∝ 결과 건수 X 전체 컬럼 Size

저작자표시 비영리 동일조건

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

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

티스토리툴바