'DISTINCT' 태그의 글 목록

'DISTINCT'에 해당되는 글 2건

2013.05.13 DP(Distinct Placement): 뷰의 건수를 Distinct로 줄여서 조인하라 6
2013.05.06 COUNT(Distinct 컬럼)의 성능 9

DP(Distinct Placement): 뷰의 건수를 Distinct로 줄여서 조인하라

The Logical Optimizer 2013. 5. 13. 06:00

SQL에서 DISTINCT의 위치는 중요하다. DISTINCT가 메인쿼리에 위치하면 조인이 모두 처리된 후 DISTINCT가 실행된다.
그 반대로 각각의 집합을 DISTINCT 한 후에 조인한다면 양측 집합의 건수가 줄어들므로 조인의 부하가 줄어든다. 그런 관점에서 보면 아래의 SQL은 최악이다.

환경: ORACLE 11.2

SELECT /*+ qb_name(MAIN) LEADING(S@INLINE) USE_NL(C@MAIN) */
       DISTINCT c.channel_id, c.channel_desc, s.prod_id, s.promo_id
   FROM channels c,
        (SELECT /*+ qb_name(INLINE) NO_MERGE */
                s.channel_id, s.prod_id, promo_id
           FROM sales_t s
          WHERE prod_id BETWEEN 13 AND 15) s
WHERE c.channel_id = s.channel_id ;

---------------------------------------------------------------------------------------------------------
| Id | Operation                     | Name        | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |     22 |00:00:00.22 |   22222 |          |
|   1 | HASH UNIQUE                  |             |      1 |     22 |00:00:00.22 |   22222 | 1271K (0)|
|   2 |   NESTED LOOPS                |             |      1 | 17778 |00:00:00.21 |   22222 |          |
|   3 |    NESTED LOOPS               |             |      1 | 17778 |00:00:00.16 |    4444 |          |
|* 4 |     TABLE ACCESS FULL         | SALES_T     |      1 | 17778 |00:00:00.11 |    4440 |          |
|* 5 |     INDEX UNIQUE SCAN         | CHANNELS_PK | 17778 | 17778 |00:00:00.03 |       4 |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| CHANNELS    | 17778 | 17778 |00:00:00.03 |   17778 |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(("PROD_ID">=13 AND "PROD_ID"<=15))
   5 - access("C"."CHANNEL_ID"="S"."CHANNEL_ID")

위의 SQL을 보면 인라인뷰 S에 미리 건수를 줄이지 않아서 조인이 17778번 발생하였다. 다시 말해 조인하기 전에 인라인뷰 S에 DISTINCT 작업이 있었다면 조인을 22번만 하면 된다. 따라서 전체 DISTINCT 작업은 필요 없다. 아래는 튜닝된 SQL이다.

SELECT /*+ qb_name(main) */
       c.channel_id, c.channel_desc, s.prod_id, s.promo_id
   FROM channels c,
        (SELECT /*+ qb_name(inline) */
                DISTINCT s.channel_id, s.prod_id, promo_id
           FROM sales_t s
          WHERE prod_id BETWEEN 13 AND 15) s
WHERE c.channel_id = s.channel_id ;

--------------------------------------------------------------------------------------------------------
| Id | Operation                    | Name        | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |     22 |00:00:00.12 |    4466 |          |
|   1 | NESTED LOOPS                |             |      1 |     22 |00:00:00.12 |    4466 |          |
|   2 |   NESTED LOOPS               |             |      1 |     22 |00:00:00.12 |    4444 |          |
|   3 |    VIEW                      |             |      1 |     22 |00:00:00.12 |    4440 |          |
|   4 |     HASH UNIQUE              |             |      1 |     22 |00:00:00.12 |    4440 | 1264K (0)|
|* 5 |      TABLE ACCESS FULL       | SALES_T     |      1 | 17778 |00:00:00.11 |    4440 |          |
|* 6 |    INDEX UNIQUE SCAN         | CHANNELS_PK |     22 |     22 |00:00:00.01 |       4 |          |
|   7 |   TABLE ACCESS BY INDEX ROWID| CHANNELS    |     22 |     22 |00:00:00.01 |      22 |          |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("PROD_ID">=13 AND "PROD_ID"<=15))
6 - access("C"."CHANNEL_ID"="S"."CHANNEL_ID")

미리 건수를 줄였으므로 22번만 조인하여 BLOCK I/O가 22222에서 4466으로 약 4~5배 줄어들었다. 이런 SQL 튜닝은 오라클 11.2에서는 더 이상 필요 없다. 아래의 SQL을 보자.

SELECT /*+ qb_name(main) */
       DISTINCT c.channel_id, c.channel_desc, s.prod_id, s.promo_id
   FROM channels c,
        (SELECT /*+ qb_name(inline) */
                s.channel_id, s.prod_id, promo_id
           FROM sales_t s
          WHERE prod_id BETWEEN 13 AND 15) s
WHERE c.channel_id = s.channel_id ;

-------------------------------------------------------------------------------------------------------------
| Id | Operation                     | Name            | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |     22 |00:00:00.09 |    4466 |          |
|   1 | HASH UNIQUE                  |                 |      1 |     22 |00:00:00.09 |    4466 | 1218K (0)|
|   2 |   NESTED LOOPS                |                 |      1 |     22 |00:00:00.09 |    4466 |          |
|   3 |    NESTED LOOPS               |                 |      1 |     22 |00:00:00.09 |    4444 |          |
|   4 |     VIEW                      | VW_DTP_2F839831 |      1 |     22 |00:00:00.09 |    4440 |          |
|   5 |      HASH UNIQUE              |                 |      1 |     22 |00:00:00.09 |    4440 | 1283K (0)|
|* 6 |       TABLE ACCESS FULL       | SALES_T         |      1 | 17778 |00:00:00.08 |    4440 |          |
|* 7 |     INDEX UNIQUE SCAN         | CHANNELS_PK     |     22 |     22 |00:00:00.01 |       4 |          |
|   8 |    TABLE ACCESS BY INDEX ROWID| CHANNELS        |     22 |     22 |00:00:00.01 |      22 |          |
-------------------------------------------------------------------------------------------------------------

Outline Data
-------------
/*+
      BEGIN_OUTLINE_DATA
      ...생략
      PLACE_DISTINCT(@"SEL$8FA4BC11" "S"@"INLINE")--> 2 DISTINCT를 추가한 뷰 VW_DTP_2F839831를 만듦
      ...생략
      MERGE(@"INLINE")                            --> 1 먼저 MERGE를 진행함
      ...생략
      END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(("PROD_ID"<=15 AND "PROD_ID">=13))
   7 - access("C"."CHANNEL_ID"="ITEM_1")

SQL이 비효율 적으로 작성되었지만 Logical Optimizer가 Distinct를 추가하여 쿼리를 재 작성하였다. 이 쿼리변환을 Distinct Placement(DP) 라고 한다. DP는 주의해야 될 점이 있다. 인라인뷰 S를 해체(MERGE)하고 Distinct를 추가한 인라인뷰를 새로 만든다. 따라서 인라인뷰 S에 NO_MERGE 힌트를 사용한다면 결코 DP가 발생하지 않는다. 이 글에서 소개된 첫 번째 SQL에 NO_MERGE 힌트가 사용됨으로써 DP가 발생되지 않은 것이다.

DP는 약간의 비효율이 있다. 즉 필요 없는 전체 Distinct 작업이 수행된다. 실행계획을 보면 HASH UNIQUE가 두 번 존재하는데, 마지막 전체 Distinct(id 1번)는 필요 없다. SQL을 아래처럼 재 작성 하였기 때문에 불필요한 HASH UNIQUE가 추가된 것이다.

SELECT DISTINCT              --> 필요 없는 DISTINCT
        C.CHANNEL_ID CHANNEL_ID,
        C.CHANNEL_DESC CHANNEL_DESC,
        VW_DTP_2F839831.ITEM_2 PROD_ID,
        VW_DTP_2F839831.ITEM_3 PROMO_ID
   FROM (SELECT DISTINCT
                 S.CHANNEL_ID ITEM_1,
                 S.PROD_ID ITEM_2,
                 S.PROMO_ID ITEM_3
            FROM TLO.SALES_T S
           WHERE S.PROD_ID <= 50
             AND S.PROD_ID >= 13
             AND 50 >= 13) VW_DTP_2F839831,
        TLO.CHANNELS C
WHERE C.CHANNEL_ID = VW_DTP_2F839831.ITEM_1 ;

따라서 아직까지는 사람이 튜닝하는 것을 따라올 수 없다.

힌트는 PLACE_DISTINCT/NO_PLACE_DISTINCT를 사용할 수 있으며 _optimizer_distinct_placement 파라미터로 기능을 컨트롤 할 수 있다. 이 파리미터의 Default값은 True이다. DP는 Cost Based Query Transformation에 속한다. Search Type과 Iteration이 존재하기 때문이다. 10053 Trace의 내용을 보면 더 확실히 알 수 있다.

****************************************
Cost-Based Group-By/Distinct Placement
****************************************
GBP/DP: Checking validity of GBP/DP for query block SEL$8FA4BC11 (#1)
GBP: Checking validity of group-by placement for query block SEL$8FA4BC11 (#1)
GBP: Bypassed: Query has invalid constructs.
DP: Checking validity of distinct placement for query block SEL$8FA4BC11 (#1)

DP: Using search type: linear
DP: Considering distinct placement on query block SEL$8FA4BC11 (#1)
DP: Starting iteration 1, state space = (1) : (0)
DP: Original query
******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("INLINE") QB_NAME ("MAIN") */ DISTINCT "C"."CHANNEL_ID" "CHANNEL_ID","C"."CHANNEL_DESC" "CHANNEL_DESC","S"."PROD_ID" "PROD_ID","S"."PROMO_ID" "PROMO_ID" FROM "TLO"."CHANNELS" "C","TLO"."SALES_T" "S" WHERE "C"."CHANNEL_ID"="S"."CHANNEL_ID" AND "S"."PROD_ID">=13 AND "S"."PROD_ID"<=15
FPD: Considering simple filter push in query block SEL$8FA4BC11 (#1)
"C"."CHANNEL_ID"="S"."CHANNEL_ID" AND "S"."PROD_ID">=13 AND "S"."PROD_ID"<=15
try to generate transitive predicate from check constraints for query block SEL$8FA4BC11 (#1)
finally: "C"."CHANNEL_ID"="S"."CHANNEL_ID" AND "S"."PROD_ID">=13 AND "S"."PROD_ID"<=15 AND 13<=15

FPD: transitive predicates are generated in query block SEL$8FA4BC11 (#1)
"C"."CHANNEL_ID"="S"."CHANNEL_ID" AND "S"."PROD_ID">=13 AND "S"."PROD_ID"<=15 AND 13<=15
DP: Costing query block.
CBQT: Looking for cost annotations for query block SEL$8FA4BC11, key = SEL$8FA4BC11_00000000_0
CBQT: Could not find stored cost annotations.
kkoqbc: optimizing query block SEL$8FA4BC11 (#1)
...생략
kkoqbc: finish optimizing query block SEL$8FA4BC11 (#1)
CBQT: Saved costed qb# 1 (SEL$8FA4BC11), key = SEL$8FA4BC11_00000000_0
DP: Updated best state, Cost = 1237.16

먼저 DP가 실행될 수 있는지 Validity Checking을 한다. DP를 실행하는데 문제가 없다면 Iteration 1 에서 변환되지 않은
SQL(Original query)을 보여주고 Cost를 구한다. 그결과 변환되지 않은 쿼리의 Cost는 1237.16이다. 이제 변환된 SQL의 COST를
구할 차례이다.

DP: Starting iteration 2, state space = (1) : (1)
DP: Using DP transformation in this iteration.
Registered qb: SEL$2F839831 0x11c3c2dc (QUERY BLOCK TABLES CHANGED SEL$8FA4BC11)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$2F839831 nbfros=2 flg=0
fro(0): flg=0 objn=75859 hint_alias="C"@"MAIN"
fro(1): flg=5 objn=0 hint_alias="VW_DTP_2F839831"@"SEL$2F839831"

Registered qb: SEL$DC663686 0x11c3b800 (SPLIT/MERGE QUERY BLOCKS SEL$2F839831)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$DC663686 nbfros=1 flg=0
fro(0): flg=0 objn=76170 hint_alias="S"@"INLINE"

Registered qb: SEL$7323A7B6 0x11c3c2dc (VIEW ADDED SEL$2F839831)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$7323A7B6 nbfros=2 flg=0
fro(0): flg=0 objn=75859 hint_alias="C"@"MAIN"
fro(1): flg=1 objn=0 hint_alias="VW_DTP_2F839831"@"SEL$2F839831"

Registered qb: SEL$10E34D75 0x11c3c2dc (DISTINCT PLACEMENT SEL$8FA4BC11; SEL$8FA4BC11; "S"@"INLINE")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$10E34D75 nbfros=2 flg=0
fro(0): flg=0 objn=75859 hint_alias="C"@"MAIN"
fro(1): flg=1 objn=0 hint_alias="VW_DTP_2F839831"@"SEL$2F839831"

Iteration 2에는 DP가 적용된 SQL의 Cost를 구한다. 여기서 DP가 수행되는 절차를 QUERY BLOCK SIGNATURE에서 볼 수 있다. 먼저 VIEW MERGE가 발생된다.(MERGE QUERY BLOCKS 부분 참조) 그 후 SALES 테이블이 포함된 뷰를 메인쿼리에 추가한다.(VIEW ADDED 부분 참조). 마지막으로 추가된 인라인뷰에 Distinct를 추가한다. (DISTINCT PLACEMENT 부분 참조)

DP: Transformed query
******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("INLINE") QB_NAME ("MAIN") */ DISTINCT "C"."CHANNEL_ID" "CHANNEL_ID","C"."CHANNEL_DESC" "CHANNEL_DESC","VW_DTP_2F839831"."ITEM_2" "PROD_ID","VW_DTP_2F839831"."ITEM_3" "PROMO_ID" FROM (SELECT DISTINCT "S"."CHANNEL_ID" "ITEM_1","S"."PROD_ID" "ITEM_2","S"."PROMO_ID" "ITEM_3" FROM "TLO"."SALES_T" "S" WHERE "S"."PROD_ID"<=15 AND "S"."PROD_ID">=13) "VW_DTP_2F839831","TLO"."CHANNELS" "C" WHERE "C"."CHANNEL_ID"="VW_DTP_2F839831"."ITEM_1"
FPD: Considering simple filter push in query block SEL$10E34D75 (#1)
"C"."CHANNEL_ID"="VW_DTP_2F839831"."ITEM_1"
try to generate transitive predicate from check constraints for query block SEL$10E34D75 (#1)
finally: "C"."CHANNEL_ID"="VW_DTP_2F839831"."ITEM_1"
...생략
kkoqbc: finish optimizing query block SEL$10E34D75 (#1)
CBQT: Saved costed qb# 2 (SEL$DC663686), key = SEL$DC663686_00001000_2
CBQT: Saved costed qb# 1 (SEL$10E34D75), key = SEL$10E34D75_00000008_0
DP: Updated best state, Cost = 1236.23
DP: Doing DP on the preserved QB.

이제 쿼리변환이 끝났으므로 변경된 SQL을 보여주고 Costing을 시작한다. DP가 적용된 SQL의 Cost는 1236.23임으로 원본 쿼리의 Cost에 비해 저렴하다. 따라서 DP가 선택된다.(Doing DP 부분 참조)

이로써 졸저 The Logical Optimizer의 416페이지 미해결 과제에서 약속한 것을 지켰다. DP의 예제가 발견되면 블로그와 책에 반영하기로 약속 했었다. 출력을 해서 책의 416페이지에 끼워넣기 바란다. 2011년에 DP를 발견했지만 여러가지 문제로 반영하지 못하다가 이제서야 올리게 되었다. 사과드린다.

저작자표시 비영리 변경금지

'The Logical Optimizer' 카테고리의 다른 글

The Logical Optimizer-Part 4 PPT (10)	2010.10.04
The Logical Optimizer-Part 3 PPT (5)	2010.09.15
Heuristic Query Transformation-PPT (11)	2010.08.12
Null Aware Hash Anti Join에 관한 오해 (0)	2010.08.06
NULL AWARE ANTI JOIN은 SQL을 어떻게 변경시키나? (2)	2010.08.02

Posted by extremedb

COUNT(Distinct 컬럼)의 성능

Oracle/Optimizer 2013. 5. 6. 06:00

집계함수 내부에 Distinct를 사용할 수 있다는 것은 많은 사람들이 알고 있다. 하지만 실제로 그렇게 사용했을 때 내부적으로 무슨 일이 일어나는지 아는 사람은 드물다. 한걸음 더 나아가서 COUUNT(COL) 대신에 COUNT(Distinct COL)를 사용했다면 분명히 추가적인 부하가 존재할 것인데, 그 부하를 어떻게 해결할 것인가를 아는 사람은 거의 없을 것이다. 만약 그렇다면 SQL을 실행할 때 마다 성능이 느려질 것이고 문제를 해결할 수 없을 것이다. 여러분들에게는 그런 일이 발생하지 않는다. 이미 이 글을 읽고 있기 때문이다.

이 글은 위에서 언급된 두 가지 문제를 다룬다. 즉 내부적으로 어떤 변화가 발생하는지 알아보고, 추가적인 부하를 어떻게 없앨 수 있는지도 연구해보자.

SQL 변경에 따른 내부적인 변화를 알아보는 가장 좋은 방법은 비교하는 것이다. 다시 말해, COUUNT(COL)로 실행했을 때의 일량과 COUNT(Distinct COL)로 사용했을 때의 일량을 비교해 보는 것이다. 따라서 우리는 SQL 두 개를 실행한 다음 각각의 작업량(실행통계)을 비교할 것이다.

환경: 오라클 11.2.0.1

CREATE TABLE SALE_T AS SELECT * FROM SALES;

SELECT /*+ NO_USE_HASH_AGGREGATION */
        S.PROD_ID
       ,COUNT(S.CHANNEL_ID)
       ,SUM(S.AMOUNT_SOLD)
       ,SUM(S.QUANTITY_SOLD)
FROM SALE_T S
GROUP BY S.PROD_ID;

-----------------------------------------------------------------------------------------
| Id | Operation          | Name   | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |     72 |00:00:01.12 |    4440 |          |
|   1 | SORT GROUP BY     |        |      1 |     72 |00:00:01.12 |    4440 | 6144 (0)|
|   2 |   TABLE ACCESS FULL| SALE_T |      1 |    918K|00:00:00.32 |    4440 |          |
-----------------------------------------------------------------------------------------

위의 SQL이 실행되는데 시간이 1.12초 걸렸고 PGA는 6144 Byte를 소모하였다. 그런데 아래처럼 COUNT에 DISTINCT를 추가를 추가한다면 어떻게 될까?

SELECT /*+ NO_QUERY_TRANSFORMATION */
        S.PROD_ID
       ,COUNT(DISTINCT S.CHANNEL_ID)
       ,SUM(S.AMOUNT_SOLD)
       ,SUM(S.QUANTITY_SOLD)
FROM SALE_T S
GROUP BY S.PROD_ID;

-----------------------------------------------------------------------------------------
| Id | Operation          | Name   | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |     72 |00:00:02.20 |    4440 |          |
|   1 | SORT GROUP BY     |        |      1 |     72 |00:00:02.20 |    4440 |14336 (0)|
|   2 |   TABLE ACCESS FULL| SALE_T |      1 |    918K|00:00:00.33 |    4440 |          |
-----------------------------------------------------------------------------------------

작업량이 증가된 이유
Distinct 만 추가했을 뿐인데 시간이 약 두 배나 걸리고 PGA도 약 두 배로 사용하였다. 그 이유는 Operation에는 나오지 않지만 내부적으로 SORT UNIQUE가 실행되기 때문이다. 즉 PROD_ID별로 SORT GROUP BY를 했음에도 CHANNEL_ID 별로 SORT UNIQUE를 다시 실행해야 한다. 약 92만 건의 데이터를 CHANNEL_ID 별로 SORT한 후에 중복을 제거하는 작업이 Distinct에 의해서 추가된 것이다. 그렇기 때문에 FULL TABLE SCAN의 수행시간은 거의 같지만 SORT GROUP BY의 수행시간이 0.8초에서 1.87초로 늘어나고 PGA사용량도 두 배가 된 것이다.

비효율을 제거하는 방법
첫 번째 의문점인 집계함수에 Distinct가 추가되면 어떤 일이 발생하는지 알아냈다. 그렇다면 두 번째 문제인 비효율(추가적인 Sort와 중복제거)을 없애는 방법은 무엇일까? SQL을 아래처럼 튜닝 할 수 있을 것이다.

SELECT /*+ NO_USE_HASH_AGGREGATION */
        PROD_ID,
        COUNT(S.CHANNEL_ID),
        SUM(S.AMOUNT_SOLD),
        SUM(S.QUANTITY_SOLD)
FROM (SELECT /*+ NO_USE_HASH_AGGREGATION */
                 S.CHANNEL_ID ,
                 S.PROD_ID ,
                 SUM(S.AMOUNT_SOLD) AMOUNT_SOLD,
                 SUM(S.QUANTITY_SOLD) QUANTITY_SOLD
           FROM SALE_T S
          GROUP BY PROD_ID, CHANNEL_ID) S
GROUP BY S.PROD_ID ;

-------------------------------------------------------------------------------------------
| Id | Operation            | Name   | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |      1 |     72 |00:00:01.39 |    4440 |          |
|   1 | SORT GROUP BY NOSORT|        |      1 |     72 |00:00:01.39 |    4440 |          |
|   2 |   VIEW               |        |      1 |    228 |00:00:01.39 |    4440 |          |
|   3 |    SORT GROUP BY     |        |      1 |    228 |00:00:01.39 |    4440 |18432 (0)|
|   4 |     TABLE ACCESS FULL| SALE_T |      1 |    918K|00:00:00.33 |    4440 |          |
-------------------------------------------------------------------------------------------

비록 PGA 사용량은 약간 늘어났지만 수행시간은 DISTINCT가 없는 SQL과 비슷해졌다. 먼저 PROD_ID, CHANNEL_ID로 GROUP BY 되었기 때문에 인라인뷰 외부에서는 Distinct를 할 필요가 없다. 다른 말로 표현하면 먼저 GROUP BY했기 때문에 PROD_ID 별로는 CHANNEL_ID가 UNIQUE 하다. 따라서 인라인뷰 외부에서는 Distinct가 필요 없게 된 것이다.

더 좋은 것은 실행계획의 Id 1을 보면 SORT GROUP BY NOSORT가 나온다. NOSORT가 나온 이유는 인라인뷰가 이미 PROD_ID로 SORT 되어있기 때문에 더 이상의 SORT는 필요 없기 때문이다. 따라서 추가적인 Group By의 부하는 거의 없다. 이렇게 튜닝하면 Distinct에 의한 SORT UNIQUE의 부하가 대부분 사라진다.

옵티마이저가 사람을 대신한다
집계함수에 Distinct를 사용한다면 무조건 위의 SQL처럼 튜닝 해야 하는가? 그건 아니다. 오라클 11.2를 사용한다면 Logical Optimizer가 SQL을 자동으로 변경시켜 준다. 아래의 튜닝 되지 않은 SQL을 실행시켜보자.

SELECT /*+ NO_USE_HASH_AGGREGATION(@"SEL$5771D262") */
        S.PROD_ID
       ,COUNT(DISTINCT S.CHANNEL_ID)
       ,SUM(S.AMOUNT_SOLD)
       ,SUM(S.QUANTITY_SOLD)
FROM SALE_T S
GROUP BY S.PROD_ID

---------------------------------------------------------------------------------------------
| Id | Operation            | Name     | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |     72 |00:00:01.39 |    4440 |          |
|   1 | SORT GROUP BY NOSORT|          |      1 |     72 |00:00:01.39 |    4440 |          |
|   2 |   VIEW               | VW_DAG_0 |      1 |    228 |00:00:01.39 |    4440 |          |
|   3 |    SORT GROUP BY     |          |      1 |    228 |00:00:01.39 |    4440 |18432 (0)|
|   4 |     TABLE ACCESS FULL| SALE_T   |      1 |    918K|00:00:00.32 |    4440 |          |
---------------------------------------------------------------------------------------------

Outline Data
-------------
/*+
      BEGIN_OUTLINE_DATA
      …생략
      TRANSFORM_DISTINCT_AGG(@"SEL$1")
      …생략
      END_OUTLINE_DATA
*/

오라클이 내부적으로 TRANSFORM_DISTINCT_AGG 힌트를 사용하였고 SQL을 자동으로 변경하였다. 실행계획도 튜닝된 SQL과 같다. 즉 11.2 버전부터는 집계함수내부에 Distinct가 존재하면 Logical Optimizer가 SQL을 변경시킴으로써 성능이 향상되는 것이다. 이 기능을 Distinct To Aggregation이라고 부른다.

아래는 10053 Trace 파일의 내용이다. 내용이 많지만 개념은 간단하다. 쿼리변환 전의 SQL을 보여주고 쿼리변환 후의 SQL을 보여준다. 그리고 두 개의 SQL 사이에는 쿼리블럭 SEL$1이 Distinct To Aggregation 기능에 의해서 두 개로 찢어지는 과정(SPLIT QUERY BLOCK)을 보여준다.

DAGG_TRANSFORM: transforming query block SEL$1 (#0)
qbcp (before transform):******* UNPARSED QUERY IS *******
SELECT "S"."PROD_ID" "PROD_ID",COUNT(DISTINCT "S"."CHANNEL_ID") "COUNT(DISTINCTS.CHANNEL_ID)",SUM("S"."AMOUNT_SOLD") "SUM(S.AMOUNT_SOLD)",SUM("S"."QUANTITY_SOLD") "SUM(S.QUANTITY_SOLD)" FROM "TLO"."SALE_T" "S" GROUP BY "S"."PROD_ID"
pgactx->ctxqbc (before transform):******* UNPARSED QUERY IS *******
SELECT "S"."PROD_ID" "PROD_ID",COUNT(DISTINCT "S"."CHANNEL_ID") "COUNT(DISTINCTS.CHANNEL_ID)",SUM("S"."AMOUNT_SOLD") "SUM(S.AMOUNT_SOLD)",SUM("S"."QUANTITY_SOLD") "SUM(S.QUANTITY_SOLD)" FROM "TLO"."SALE_T" "S" GROUP BY "S"."PROD_ID"
Registered qb: SEL$5771D262 0xea51918 (SPLIT QUERY BLOCK FOR DISTINCT AGG OPTIM SEL$1; SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$5771D262 nbfros=1 flg=0
fro(0): flg=0 objn=76169 hint_alias="S"@"SEL$1"

Registered qb: SEL$C33C846D 0xde78e84 (MAP QUERY BLOCK SEL$5771D262)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$C33C846D nbfros=1 flg=0
fro(0): flg=5 objn=0 hint_alias="VW_DAG_0"@"SEL$C33C846D"

qbcp (after transform):******* UNPARSED QUERY IS *******
SELECT "VW_DAG_0"."ITEM_2" "PROD_ID",COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTS.CHANNEL_ID)",SUM("VW_DAG_0"."ITEM_4") "SUM(S.AMOUNT_SOLD)",SUM("VW_DAG_0"."ITEM_3") "SUM(S.QUANTITY_SOLD)" FROM (SELECT /*+ NO_USE_HASH_AGGREGATION */ "S"."CHANNEL_ID" "ITEM_1","S"."PROD_ID" "ITEM_2",SUM("S"."QUANTITY_SOLD") "ITEM_3",SUM("S"."AMOUNT_SOLD") "ITEM_4" FROM "TLO"."SALE_T" "S" GROUP BY "S"."CHANNEL_ID","S"."PROD_ID") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2"

Distinct To Aggregation 쿼리변환은Heuristic Query Transformation에 속한다. _optimizer_distinct_agg_transform 파라미터로 이 기능을 제어할 수 있고 Default로 true이다. 힌트로는 TRANSFORM_DISTINCT_AGG / NO_TRANSFORM_DISTINCT_AGG 를 사용할 수 있다.

이제 우리는 집계함수에 Distinct가 추가되면 SORT UNIQUE의 부하로 성능이 느려짐을 안다. 또 Distinct 대신에 Group By를 사용하여 그 부하를 대부분 없애는 방법도 알게 되었다. 하지만 이제는 이런 일들을 옵티마이저가 대신하게 되었다. 이런 기능들이 계속 추가된다면 언젠가는 튜너라는 직업이 사라지 않을까? 만약 튜너가 없어진다면, 그 후에 옵티마이저를 연구하는 사람까지 사라질 것이다. 왜냐하면 옵티마이저를 연구하는 사람은 튜너를 위해 존재하기 때문이다.

PS
다들 잘 지내시죠? 개인 사정으로 지난 2년간 뵙지 못했습니다. 5월달에 글을 한 두개 더 올릴 생각 입니다. 기대해 주세요. 5월 중순 부터는 바빠서 글쓰기가 힘들 것 같습니다.

그럼 건강하세요.

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

'Oracle > Optimizer' 카테고리의 다른 글

Cardinality Feedback이 위험할 때 (10)	2010.10.25
공지 - Cardinality Feed Back이 위험할 때 (1)	2010.10.22
union과 union all의 숨겨진 차이점 (6)	2010.10.18
메트릭스나 터미네이터는 먼 미래의 이야기 인가? (2)	2010.04.28
해결사 되기 (15)	2010.02.04

Posted by extremedb

이전 1 다음

Science of Database

'DISTINCT'에 해당되는 글 2건

DP(Distinct Placement): 뷰의 건수를 Distinct로 줄여서 조인하라

'The Logical Optimizer' 카테고리의 다른 글

COUNT(Distinct 컬럼)의 성능

'Oracle > Optimizer' 카테고리의 다른 글

카테고리

최근에 올라온 글

최근에 달린 댓글

글 보관함

링크

태그목록

최근에 받은 트랙백

티스토리툴바


	오동규 블로그 odong91@naver.com by extremedb