'쿼리변환' 태그의 글 목록

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

,

검색조건을 동적으로 바꾸어야 할 때

Oracle/SQL Pattern 2010. 12. 14. 07:01

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

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

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

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

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

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

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

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

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

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

신입사원: 감사합니다.

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

환경

DBMS: Oracle11g R2

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

EMP_MGR_HR_DT_IX: employee( manager_id + hire_date )

EMP_DEPT_IX : employee( department_id )

EMP_JOB_IX : employee( job_id )

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

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

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

FROM employee e,

job j,

department d,

location l

WHERE e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

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

업무 요구사항

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

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

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

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

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

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

성능 요구사항

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

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

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

SELECT /*+ USE_CONCAT */

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

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

FROM employee e,

job j,

department d,

location l

WHERE e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

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

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

AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to )

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

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

)

AND d.manager_id > 0;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

FROM employee e,

job j,

department d,

location l

WHERE e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

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

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

AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to )

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

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

)

AND d.manager_id > 0;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

…중간생략

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

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

1 - SEL$1

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

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

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

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

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

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

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

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

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

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

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

…중간생략

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

OR-Expansion VS Union All

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

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

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

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

FROM employee e,

job j,

department d,

location l

WHERE e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

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

AND d.manager_id > 0

AND :v_delimit = 1 --> 1 입력

UNION ALL

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

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

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

FROM employee e,

job j,

department d,

location l

WHERE e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

AND e.manager_id = :v_emp

AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to

AND d.manager_id > 0

AND :v_delimit = 2

UNION ALL

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

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

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

FROM employee e,

job j,

department d,

location l

WHERE e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

AND d.department_id = :v_dept

AND d.manager_id > 0

AND :v_delimit = 3

UNION ALL

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

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

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

FROM employee e,

job j,

department d,

location l

WHERE e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

AND l.location_id = :v_loc

AND d.manager_id > 0

AND :v_delimit = 4 ;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

FROM employee e,

job j,

department d,

location l

WHERE e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

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

AND d.manager_id > 0

AND ROWNUM > 0

AND :v_delimit = 1 --> 1 입력

UNION ALL

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

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

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

FROM employee e,

job j,

department d,

location l

WHERE e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

AND e.manager_id = :v_emp

AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to

AND d.manager_id > 0

AND ROWNUM > 0

AND :v_delimit = 2

UNION ALL

…중간생략

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

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

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

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

FROM employee e,

job j,

department d,

location l

WHERE e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

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

AND d.manager_id > 0

AND :v_delimit = 1 --> 1 입력

UNION ALL

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

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

…중간생략

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

결론

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

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

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

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

SQL1

SELECT /*+ USE_HASH(c s) */

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

s.amount_sold, c.channel_desc

FROM sales s, channels c

WHERE c.channel_id = s.channel_id

AND c.channel_id = 3

UNION ALL

SELECT /*+ USE_HASH(c s) */

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

s.amount_sold, c.channel_desc

FROM sales s, channels c

WHERE c.channel_id = s.channel_id

AND c.channel_id = 9 ;

SQL2

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

s.amount_sold, vw_jf_set$0a277f6d.item_2 channel_desc

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

FROM channels c

WHERE c.channel_id = 3

UNION ALL

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

FROM channels c

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

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

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

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

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

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

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

Oracle Analytic Function의 모든 것 (20)	2010.03.29
오라클 Regular Expressions 완전정복 (22)	2009.10.07
간단한 집합개념 Test (7)	2009.07.06
스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기. (7)	2009.06.29
Model 절에 대하여 (10)	2009.05.28

Posted by extremedb

,

union과 union all의 숨겨진 차이점

Oracle/Optimizer 2010. 10. 18. 00:01

부제목: view에서 union, minus, intersect를 제거하라

많은 사람들이 union과 union all의 차이점에 대해 알고 있다. 즉 union은 Sort와 중복제거라는 기능으로 인해 UNION ALL에 비하여 성능이 떨어진다는 것이다. 옳은 말이다. 하지만 union대신에 union all을 써야 하는 또 다른 이유가 있다는 것을 아는 사람은 얼마나 될까? 이 사실을 알지 못하면 불필요한 엑세스가 추가될 수 있으므로 성능이 저하된다.

먼저 고객테이블을 이용하여 뷰를 하나 만들고 그것을 이용한 SQL문을 만들어 보자.
환경 : Oracle 11.2.0.1

create or replace view vw_cust5 as
select *
from customers
union --> union을 사용함
select *
from customers;

select a.cust_id,
   b.prod_id,
   b.time_id,
   b.channel_id,
   b.quantity_sold
from vw_cust5 a,
   sales b
where a.cust_id = b.cust_id
   and a.cust_id = 14865 ;

뷰에서 사용하는 컬럼은 a.cust_id 하나 뿐이다. 따라서 고객 테이블에 PK인덱스를 사용한다면 customers 테이블로 엑세스 하지 않아도 된다. 하지만 아래의 실행계획을 본다면 문제를 발견할 수 있다.

-------------------------------------------------------------+----------------
| Id | Operation    | Name    | Rows | Cost |
-------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | |    62 |
| 1 | HASH JOIN | | 260 |    62 |
| 2 | VIEW | VW_CUST5    | 2 | 6 |
| 3 |    SORT UNIQUE | | 2 | 6 |
| 4 | UNION-ALL    | | | |
| 5 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 6 | INDEX UNIQUE SCAN    | CUSTOMERS_PK | 1 | 1 |
| 7 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 8 | INDEX UNIQUE SCAN    | CUSTOMERS_PK | 1 | 1 |
| 9 | PARTITION RANGE ALL    | | 130 |    56 |
| 10 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 |    56 |
| 11 | BITMAP CONVERSION TO ROWIDS    | | | |
| 12 |    BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX| | |
-------------------------------------------------------------+----------------

PK 인덱스를 사용하였지만 customers 테이블로 불필요한 엑세스를 하였다. 이유가 무엇일까? 10053 trace를 보자.

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME"
…중간생략
"CUST_TOTAL_ID","CUSTOMERS"."CUST_SRC_ID" "CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS" WHERE "CUSTOMERS"."CUST_ID"=14865 AND "CUSTOMERS"."CUST_ID"=14865)UNION (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME"
…중간생략
"CUST_TOTAL_ID","CUSTOMERS"."CUST_SRC_ID" "CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS" WHERE "CUSTOMERS"."CUST_ID"=14865 AND "CUSTOMERS"."CUST_ID"=14865)) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "B"."CUST_ID"=14865

위의 trace는 쿼리변환을 끝낸 상태의 SQL이다. 그런데 SQL을 자세히 보면 뷰 내부의 모든 컬럼을 select 하고 있다. 다시 말해 뷰 내부의 컬럼중에 cust_id만 존재하면 되는데, 나머지 컬럼이 삭제되지 않고 남아있다. 이것 때문에 불필요한 테이블 엑세스가 나타난 것이다.

union 대신에 union all을 사용해보자

테스트를 위하여 100건 짜리 고객테이블을 만들고 unique 인덱스를 만들어 보자.

create table customers_100 as
select *
from customers
where rownum < 101;

create unique index PK_CUSTOMERS_100 on customers_100 (cust_id);

이제 union 대신에 union all로 뷰를 생성하여 테스트한다.

create or replace view vw_cust2 as
select *
from customers
UNION ALL
select *
from customers_100 ;

SELECT a.cust_id,
   b.prod_id,
   b.time_id,
   b.channel_id,
   b.quantity_sold
FROM vw_cust2 a,
   sales b
WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865 ;

---------------------------------------------------------------+----------------
| Id | Operation    | Name    | Rows | Cost |
---------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | |    57 |
| 1 | HASH JOIN | | 260 |    57 |
| 2 | VIEW | VW_CUST2    | 2 | 1 |
| 3 |    UNION-ALL | | | |
| 4 | INDEX UNIQUE SCAN    | CUSTOMERS_PK    | 1 | 1 |
| 5 | INDEX UNIQUE SCAN    | PK_CUSTOMERS_100| 1 | 0 |
| 6 | PARTITION RANGE ALL    | | 130 |    56 |
| 7 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 |    56 |
| 8 | BITMAP CONVERSION TO ROWIDS    | | | |
| 9 |    BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
---------------------------------------------------------------+----------------

SLP란 무엇인가?

실행계획에서 보듯이 union all 을 사용하니 테이블 엑세스가 사라졌다. 이것은 SLP(Select List Pruning)라는 쿼리변환의 기능 때문에 가능한 것이다. SLP는 union all이 들어있는 뷰를 사용할 때 발생하며 뷰의 컬럼중에 사용하지 않는 것을 제거한다. 이제 10053 trace 내용 중에서 SLP에 대해 분석해보자. 특히 SLP 변환 전 SQL과 SLP 변환 후 SQL을 비교해보라. 아래의 10053 trace 내용이 복잡해 보이지만 구조는 단순하며 다음과 같다.

1) 쿼리변환 전 SQL
2) 쿼리변환(SLP)
3) 쿼리변환 후 SQL

SQL:******* UNPARSED QUERY IS *******
SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME" "CUST_LAST_NAME","CUSTOMERS"."CUST_GENDER"
…중간생략
"CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS") UNION ALL (SELECT "CUSTOMERS_100"."CUST_ID" "CUST_ID","CUSTOMERS_100"."CUST_FIRST_NAME""CUST_FIRST_NAME",
"CUSTOMERS_100"."CUST_LAST_NAME" "CUST_LAST_NAME","CUSTOMERS_100"."CUST_GENDER"
…중간생략
"CUST_SRC_ID","CUSTOMERS_100"."CUST_EFF_FROM""CUST_EFF_FROM","CUSTOMERS_100"."CUST_EFF_TO"
"CUST_EFF_TO" FROM "TLO"."CUSTOMERS_100" "CUSTOMERS_100")) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "A"."CUST_ID"=14865
Query block SEL$1 (#0) unchanged
SLP: Removed select list item CUST_FIRST_NAME from query block SEL$3
SLP: Removed select list item CUST_FIRST_NAME from query block SEL$2
SLP: Removed select list item CUST_FIRST_NAME from query block SET$1
...중간생략
SLP: Removed select list item CUST_EFF_TO from query block SEL$3
SLP: Removed select list item CUST_EFF_TO from query block SEL$2
SLP: Removed select list item CUST_EFF_TO from query block SET$1
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID" FROM "TLO"."CUSTOMERS" "CUSTOMERS") UNION ALL (SELECT "CUSTOMERS_100"."CUST_ID" "CUST_ID" FROM "TLO"."CUSTOMERS_100" "CUSTOMERS_100")) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "A"."CUST_ID"=14865

필요한 컬럼만 살아남다
SLP(Select List Pruning)가 발생하여 사용하지 않는 모든 컬럼을 삭제하였다. 쿼리변환 후의 SQL을 보면 뷰 내부의 컬럼은 모두 제거되고 cust_id만 남아있다. SLP 기능에 의해 테이블 엑세스가 없어진 것이다. 뷰 내부에는 union뿐만 아니라 minus,intersect 등의 집합 연산의 사용을 자제해야 한다. SLP가 발생하지 않기 때문이다.

그렇다면 뷰내부에 union all과 minus를 동시에 사용하면 어떻게 될까? 이제 union all과 minus를 동시에 사용한 뷰를 생성하고 SLP가 발생하는지 테스트 해보자.

CREATE OR REPLACE VIEW vw_cust33 AS
SELECT *
FROM customers
UNION ALL
SELECT *
FROM customers
MINUS
SELECT *
FROM customers_100;

이제 위의 뷰를 사용하여 select문을 실행해보자.

SELECT a.cust_id,
   b.prod_id,
   b.time_id,
   b.channel_id,
   b.quantity_sold
FROM vw_cust33 a, sales b
WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865 ;

---------------------------------------------------------------+----------------
| Id | Operation    | Name    | Rows | Cost |
---------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | |    63 |
| 1 | HASH JOIN | | 260 |    63 |
| 2 | VIEW | VW_CUST33 | 2 | 7 |
| 3 |    MINUS | | | |
| 4 | SORT UNIQUE    | | 2 | |
| 5 |    VIEW    | | 2 | 4 |
| 6 | UNION-ALL    | | | |
| 7 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 8 | INDEX UNIQUE SCAN    | CUSTOMERS_PK    | 1 | 1 |
| 9 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 10 | INDEX UNIQUE SCAN    | CUSTOMERS_PK    | 1 | 1 |
| 11 | TABLE ACCESS BY INDEX ROWID    | CUSTOMERS_100 | 1 | 1 |
| 12 |    INDEX UNIQUE SCAN | PK_CUSTOMERS_100| 1 | 0 |
| 13 | PARTITION RANGE ALL    | | 130 |    56 |
| 14 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 |    56 |
| 15 | BITMAP CONVERSION TO ROWIDS    | | | |
| 16 |    BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
---------------------------------------------------------------+----------------

minus가 존재하여 SLP가 발생되지 않았다. Minus 때문에 불필요한 테이블 엑세스가 세 번이나 발생되었다. 어떻게 하면 이 문제를 해결할 수 있을까? 물론 부정형 서브쿼리(not exists)를 사용하면 minus 를 대신할 수 있으므로 불필요한 테이블 엑세스는 없어질 것이다.

minus를 사용하면서 SLP가 가능한가?

문제는 minus를 사용하면서 불필요한 엑세스를 방지할 수 있는 방법이 있냐는 것이다. 가장 쉬운 방법은 뷰에서 minus 부분을 제거하는 것이다. 즉 아래의 SQL처럼 minus 대신에 테이블을 직접 사용하면 된다.

create or replace view vw_cust as
select * from customers
UNION ALL
select * from customers ;

SELECT a.cust_id,
   b.prod_id,
   b.time_id,
   b.channel_id,
   b.quantity_sold
FROM VW_CUST a, --minus가 빠진 뷰를 사용함
   sales b
WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865
MINUS
SELECT a.cust_id,
   b.prod_id,
   b.time_id,
   b.channel_id,
   b.quantity_sold
FROM CUSTOMERS_100 a, --테이블을 직접 사용함
   sales b
WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865 ;

-----------------------------------------------------------------+----------------
| Id | Operation    | Name    | Rows | Cost |
-----------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | | 115 |
| 1 | MINUS | | | |
| 2 | SORT UNIQUE    | | 260 |    59 |
| 3 |    HASH JOIN | | 260 |    58 |
| 4 | VIEW | VW_CUST | 2 | 2 |
| 5 |    UNION-ALL | | | |
| 6 | INDEX UNIQUE SCAN    | CUSTOMERS_PK    | 1 | 1 |
| 7 | INDEX UNIQUE SCAN    | CUSTOMERS_PK    | 1 | 1 |
| 8 | PARTITION RANGE ALL    | | 130 |    56 |
| 9 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 |    56 |
| 10 | BITMAP CONVERSION TO ROWIDS    | | | |
| 11 |    BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
| 12 | SORT UNIQUE    | | 130 |    56 |
| 13 |    NESTED LOOPS    | | 130 |    55 |
| 14 | INDEX UNIQUE SCAN    | PK_CUSTOMERS_100| 1 | 0 |
| 15 | PARTITION RANGE ALL    | | 130 |    55 |
| 16 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 |    55 |
| 17 | BITMAP CONVERSION TO ROWIDS    | | | |
| 18 |    BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
-----------------------------------------------------------------+----------------
예상대로 뷰에서 minus를 삭제하니 성공적으로 SLP가 발생되었고 테이블 엑세스가 모두 사라졌다.
결론
이번 시간에는 union과 union all의 또 다른 차이점에 대해 알아보았다. union all을 사용하면 SLP가 발생되어 뷰에서 사용되지 않는 컬럼을 제거한다. 이때 인덱스만으로 scan을 끝낼수 있는 경우 불필요한 테이블스캔이 방지된다. 따라서 뷰 내부에서는 union, minus, intersect를 빼는 것이 유리하다. 뷰 내부의 minus는 not exists로 바꾸면 된다. 대부분의 경우 뷰 내부에 Intersect도 필요치 않다. Intersect란 교집합이며 이것은 조인으로 해결할 수 있다. 왜냐하면 조인이란 두 집합에서 조인된 컬럼을 기준으로 값이 같은 것만 추출하는 기능이기 때문이다. 그렇지 않은가?

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

Cardinality Feedback이 위험할 때 (10)	2010.10.25
공지 - Cardinality Feed Back이 위험할 때 (1)	2010.10.22
메트릭스나 터미네이터는 먼 미래의 이야기 인가? (2)	2010.04.28
해결사 되기 (15)	2010.02.04
Distinct Elimination : 불필요한 Distinct를 제거하라 (6)	2010.01.25

Posted by extremedb

,

The Logical Optimizer-Part 4 PPT

The Logical Optimizer 2010. 10. 4. 09:59

책 (The Logical Optimizer)의 Part 4에 대한 PPT가 완성되었다. 이제 본문의 모든 내용이 PDF로 요약 되었다. 책을 쓴 저자의 의무를 어느 정도 한것 같다.

Part 4는 CBQT (Cost Based Query Transformation)의 내부원리에 대한 내용이다. 즉 쿼리변환(Query Transformation)에 대한 내용이 아니라 옵티마이져의 원리에 대한 내용이다. 본문 내용중에서 가장 난위도가 있는 부분이기도 하다.

Tstory의 용량제한 때문에 할 수 없이 파일을 2개로 나눠(분할압축) 올린다.

invalid-file

압축 프로그램 7zip

THE LOGICAL OPTIMIZER (양장): 국내도서>컴퓨터/인터넷; 저자 : 오동규; 출판 : 오픈메이드 2010.04.05

상세보기

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

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

DP(Distinct Placement): 뷰의 건수를 Distinct로 줄여서 조인하라 (6)	2013.05.13
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

,

The Logical Optimizer-Part 3 PPT

The Logical Optimizer 2010. 9. 15. 11:23

PDF 파일의 95 페이지에 타이틀이 잘못되어 수정해서 다시 올림(2010-09-15 오후 6시)

책 (The Logical Optimizer)의 Part 3에 대한 PPT가 완성되었다. Oracle 10g 부터 시작된 CBQT (Cost Based Query Transformation)에 대한 내용이다. 파워포인트 작업을 할때는 몰랐는데 완성하고 보니 130 페이지가 넘어가고 파일크기도 30MB가 넘는다. Tstory의 용량제한 때문에 할 수 없이 파일을 3개로 나눠(분할압축) 올린다. Part 3의 내용을 이해하는데 도움이 되었으면 한다.

압축 프로그램 7zip

invalid-file

PS
Part 4 도 작업이 완료되는 대로 올릴 예정이다.

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

DP(Distinct Placement): 뷰의 건수를 Distinct로 줄여서 조인하라 (6)	2013.05.13
The Logical Optimizer-Part 4 PPT (10)	2010.10.04
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

,

Heuristic Query Transformation-PPT

The Logical Optimizer 2010. 8. 12. 17:30

책(The Logical Optimizer) 내용중 Part 2 부분의 PPT 파일이 완성되어 올립니다.
Tstory는 10MB보다 큰 파일은 올릴 수 없게 되어있군요. 파일의 사이즈가 커서 분할 압축하여 올립니다.
압축을 푸시면 아래그림처럼 3개의 파일이 됩니다. 각각 10MB 정도 되는군요.

첫 번째 파일(The Logical Optimizer_Part II_1)은 Basic 부분(2.A ~2.16장)까지 입니다.
두 번째 파일(The Logical Optimizer_Part II_2)은 Subquery부분(2.17~2.29장)까지 입니다.
세 번째 파일(The Logical Optimizer_Part II_2)은 Data Warehouse부분(2.30~Part2 마무리)까지 입니다.

PPT 파일로 다시 한번 정리하시기 바랍니다.
압축 프로그램 7zip
감사합니다.

invalid-file

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

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

Posted by extremedb

,

The Logical Optimizer

The Logical Optimizer 2010. 4. 5. 00:01

원래 3월에 출간 예정이 었으나 마음대로 되지 않았다. 회사 내/외부에서 책이 왜 늦어지냐고 원성을 많이 들었다.
여러분들에게 사과드린다.

필름 마감
드디어 인쇄용 필름이 마감되었다. 은행에도 일 마감이 있듯이 출판에도 필름 마감이라는게 있다. 이 과정이 끝나면 인쇄가 시작된다. 오늘 인쇄작입이 시작될 것이다. 1월에 원고를 완성했지만 여러가지 문제(오탈자 수정 작업, 표지 디자인, 띠지 디자인, 메켄토시용 워드로 변환 과정에서 오류및 페이지수가 달라지는 현상, 페이지가 달라졌으므로 목차 및 색인 재작업, 인쇄용지 부족현상, ISBN 번호 취득, 표지와 띠지 그리고 본문의 용지 선택, 최종 필름의 검증) 과정에서 시간을 많이 소모 하였다. 이 모든 과정이서 작가의 의견이 직 간접적으로 들어가야 한다. 이제 남은건 서점과의 계약인데 4월 20일 정도에 YES24나 교보문고 등에서 주문이 가능할 것이다.

그럼 이제 책의 겉모습을 보자.

삼장법사와 손오공의 관계는?
표지는 빈티지 스타일로 처리하여 케케묵은 고서(오래된 책)의 느낌을 받도록 하였다. 앞 표지의 그림은 삼장법사와 손오공이다. 이 그림은 Logical Optimizer와 Physical Optimizer의 관계를 나타낸 것이다. 제일 아래의 미리보기 파일을 보면 상세한 내용을 알 수 있다. 총 430 페이지 이므로 책등을 보더라도 그다지 두껍지는 않다.

이제 표지에 띠지를 입혀 보자.

그림을 클릭하면 크게 볼 수 있다. 띠지가 너무 강렬하다는 의견도 있었으나 바꿀 경우 작업시간 때문에 출간일자가 늦어지므로 그냥 가기로 하였다. 나중에 알고보니 띠지가 강렬한 것이 아니라 띠지의 표준색이 빨강이라 한다. 평소에 띠지를 주의 깊게 보지 않아서 오해한 것이다.

책을 집필 하게된 원인
2006년 늦은 가을의 한 사건 때문에 이 책이 나올 수 있었다. 그 사건이 아니었다면 Logical Optimizer로 인한 문제가 실무에서 얼마나 중요한지 알 수 없었을 것이다. 아래에 그 사건과 관련된 에피소드를 소개한다.

Episode

영화 <아바타>에는 영혼의 나무를 통하여 생명체와 교감하며 평화로운 생활을 영위하는 판도라 행성의 나비족이 등장한다. 하지만 이 행성의 광물에 눈이 먼 지구인들은 무력을 통해 이들을 짓밟게 되고, 인간의 탐욕에 치를 떤 지구인 제이크 셜리는 인간을 등지고 나비족의 편에 선다. 하지만 그 과정에서 나비족의 신뢰를 받지 못한 제이크는 무모하게도 나비족 역사 이래 5번밖에 소유하지 못했던 영적 동물 토르쿠 막토를 획득하려는 불가능한 시도를 하게 된다. 천신만고 끝에 얻어낸 토르쿠 막토는 모든 상황을 급 반전시킨다. 결국 그는 토르쿠 막토의 힘을 빌려 나비족의 새로운 지도자가 되고 인간과의 전쟁을 승리로 이끈다.

토르쿠 막토, 우리가 가질 수 있나
영화가 아닌 현실에서도 모든 상황을 한번에 해결할 만한 토르쿠 막토 같은 위력적인 무기를 가질 수 있을까? 지금부터 그것을 손에 넣었던 필자의 경험담을 소개한다.

2006년 늦은 가을이었던가? 필자는 새로운 사이트에 투입되어 DBA들과 튜닝 중에 있었다. 개발자들이 튜닝을 의뢰하면 먼저 DBA들이 튜닝을 실시하고, DBA가 해결하지 못하는 SQL은 필자에게 튜닝 요청이 들어온다. 하지만 그 당시 한 달이 넘게 DBA들과 필자가 튜닝 작업에 고심하였음에도 요청되는 튜닝 건수에 비해 해결되는 건수가 턱없이 부족했다. 베테랑 DBA가 3명이나 있었음에도 불구하고 해결되지 않는 SQL의 건수는 계속해서 쌓여가고 있었다.

도대체 왜?
한 달째인 그날도 밤 12시가 넘었지만 퇴근하지 못했으며 이것이 어쩔 수 없는 컨설턴트의 숙명이거니 하는 자포자기의 심정이 들었다. 새벽 한 시가 되어 주위를 둘러보니 사무실엔 아무도 없었다. 얼마 후 건물 전체가 소등되었고 모니터의 불빛만이 남아있었다. 암흑과 같은 공간에서 한동안 적막이 흘렀다. 바로 그 순간 요청된 SQL에는 일정한 패턴이 있지 않을까 하는 생각이 번쩍 들었다. 갑자기 든 그 생각으로 필자는 퇴근할 생각도 잊은 채 SQL에 대한 패턴을 분석하기 시작했다. 그리고 몇 시간 후 동 틀 무렵, 놀라운 결과를 발견할 수 있었다.

필자에게 튜닝을 요청한 SQL의 많은 부분이 Query Transformation(이하 QT) 문제였다. 즉 Logical Optimizer의 원리만 알았다면 필자를 비롯한 DBA들은 저녁 7시 이전에 일을 마칠 수 있었을 것이다. QT란 Logical Optimizer가 성능 향상의 목적으로 SQL을 재 작성(변경)하는 것을 말한다. 하지만 옵티마이져가 완벽하지 못하므로 많은 경우에 문제를 일으키게 된다.

베테랑 DBA들의 아킬레스건은 고전적인 튜닝 방법에 의존하는 것
DBA들은 지금껏 전통적인 튜닝 방법 3가지(Access Path, 조인방법, 조인순서)에 대한 최적화만 시도하고, 그 방법으로 해결되지 않으면 필자에게 튜닝을 요청한 것이다. 그들에게 QT를 아느냐 물었을 때 대답은 거의 동일했다. 그들이 아는 것은 Where 조건이 뷰에 침투되는 기능, 뷰가 Merging(해체)되는 기능, OR 조건이 Union All로 변경되는 기능, 세 가지 뿐이었다. 실무에서 발견되는 대부분의 문제를 해결하려면 최소한 30가지 이상은 알아야 한다. 그런데 세 가지만 알고 있다니...... 충격적인 결과였다. 10개 중에 9개를 모르는 것과 같았다.

하지만 QT와 관련된 적절한 교재나 교육기관이 전무한 상태였기 때문에 이러한 문제에 대해 DBA들을 탓할 수는 없을 것이다(이 사실은 2006년이 아닌 2010년 현재도 마찬가지이다). 필자는 다음날부터 삼 일 동안 튜닝을 전혀 하지 않기로 마음 먹었다. 대신에 DBA들에게 Query Transformation에 대한 교육을 하기로 작정했다. 필자의 입장에서는 교육을 진행하지 않아도 그때까지 쌓여있는 튜닝 이슈만 해결하면 프로젝트를 마무리 할 수 있었다. 하지만 열정 때문인지 아니면 윤리적 의무감이 원인인지 모르겠으나 교육을 진행하지 않은 상태에서 프로젝트를 끝낼 수 없다고 생각하고 있었다.

난관
다음날 필자는 DBA들과 담당 책임자를 불러서 교육에 관한 회의를 하였다. 책임자는 삼 일간 18시간의 교육 때문에 튜닝 실적이 거의 없게 되므로 교육은 불가능하다는 것이었다. 업무시간 중 교육을 하게 됨으로 필자 뿐만 아니라 모든 DBA들의 튜닝실적이 없게 되는 것이다. 책임자와 DBA들은 해결되지 않는 튜닝문제의 대부분이 Logical Optimizer 때문이라는 사실을 필자의 분석자료를 통해 알고 있었다. 하지만 책임자는 상부에 튜닝 실적을 보고해야 되는 처지였으므로 교육은 불가하다고 하였다.

필자는 교육 후에 가속도가 붙을 것이므로 실적을 충분히 따라잡을 것 이라고 책임자를 설득하였다. 그는 실적 대신에 교육 후에 향상된 DBA들의 문제 해결능력을 상부에 보고하겠다고 하였다. 다행스러운 일 이었다. 그런데 이번에는 DBA들이 교육을 완강히 거부했다. 그들은 튜닝 이외에 Database 관리업무도 진행해야 하는데 삼 일의 교육기간 중 업무를 처리하지 못하게 된다는 것이었다. 따라서 교육 후에 밤을 세워서라도 밀린 업무를 수행해야 되는 처지였으므로 교육을 부담스러워 했다. 또한 Logical Optimizer의 원리보다는 고전적인 튜닝 방법을 신뢰하고 있었기 때문에 며칠간의 교육으로 문제가 해결될지 의심하고 있었다.

설득의 방법
필자는 강한 반대 의견 때문에 ‘억지로 교육을 해야 하나?’ 라는 생각이 들었다. 마지막 이라는 심정으로 설득의 방법을 바꾸어 보았다. DBA들이 교육을 통해서 무엇을 얻을 것인가(WIFM) 관점보다는 교육을 받지 못하면 손해를 보게될 상황을 설명 하였다. 즉 튜닝 프로젝트가 끝나고 필자가 나간 뒤에도 같은 패턴의 튜닝 문제가 발생할 것인데 지금 교육을 받지 않는다면 그때가 되어도 튜닝을 할 수 없을 것이라고 강조하였다. 또한 업무시간 후에 교육을 받으면 시간을 거의 뺏기지 않을 것 이라고 설명하였다.

마침내 설득은 효과를 발휘했다. 업무시간을 제외한 저녁 7시부터 10시까지 총 6일간 교육을 진행하기로 모두가 합의하였다. 3일 간의 교육이 6일간의 교육으로 늘어지긴 하였지만 교육을 진행할 수 있게 되었다는 사실만으로도 아주 다행스런 결과였다. 교육시간에 실무에서 가장 발생하기 쉬운 QT 기능들의 원리와 튜닝방법부터 설명하였다. 일주일의 교육을 마치자 곧바로 효과가 나타났다. 교육 후 필자에게 들어오는 튜닝 의뢰 건수가 절반으로 줄어든 것이다. 비로소 필자는 정상적인 시간에 퇴근할 수 있게 되었다.

기적은 필자에게만 일어난 것이 아니었다. 교육 이전에 DBA들은 밤 11시가 넘어서야 퇴근 하였다. 왜냐하면 필자에게 튜닝 요청을 하기 전에 성능이 개선되지 않는 SQL을 짧게는 몇 시간, 길게는 며칠 동안 붙잡고 고민하다가 요청하기가 일쑤였기 때문이었다. 교육 이후로는 DBA들이 SQL을 보는 관점부터 달라졌으며 필자가 없어도 QT 문제를 스스로 해결할 수 있는 능력을 갖게 되었다. 기대 반 우려 반의 심정으로 교육을 허락한 책임자의 얼굴에도 화색이 돌았다. 지난 수 년간 진행되었던 Logical Optimizer의 원리에 대한 연구가 한 순간에 빛을 발하고 있었다.

그 사이트의 문제가 해결되고 얼마 후 지난 2년간 다른 프로젝트에서 요청 받았던 튜닝 문제를 같은 방법으로 분석 하였는데 원인 중 절반이 QT 문제였다. 이 같은 경험은 우리에게 시사하는 바가 크다. 어떤 문제로 베테랑 DBA들이 밤을 세우는지, 어떤 기술로 문제를 해결 할 수 있는지 혹은 어떤 기술이 고급 튜너로 가기 위한 것인지 알 수 있다. 혹시 당신이 속한 프로젝트에 DBA, 튜너 혹은 고급 개발자들이 퇴근을 못하고 밤새 일하고 있다면
고심해 보라. Logical Optimizer의 원리가 상황을 반전 시킬 수 있는지를.
의심해 보라. 그 원리가 토르쿠 막토가 아닌지를......
<본문 내용 중에서>

이 책의 가장 큰 특징은 목차만 보고 어떤 기능을 하는 것인지 떠올릴 수 있다는 것이다. 물론 책을 한번 읽은 상태에서 가능하다. 복습할 때 가장 유용한 것이 목차만 보고 요약이 되는 것인데 Part 2와 Part 3가 이런 접근법을 따르고 있다.

아래에 책의 미리보기(Preview)파일을 올린다. 에피소드, 서문, 감사의 글, 책의 구성과 책을 읽는 방법, 목차, 종문, 참조문서, 색인 등을 볼 수 있다.

invalid-file

The Logical Optimizer 미리보기

PS
글을 준비하고 작성하는데 5년이나 걸렸고 글을 실물의 책으로 만드는 과정에서 3개월이 소모되었다. 맡은 프로젝트 + 전공이외의 Study + 블로그 관리+ 옵티마이져의 연구 및 집필을 동시에 진행하는 것은 고통의 연속이었다. 이제 좀 쉬어야 겠다. 몇년뒤에 다음 책이 나올 수 있을지.....
지금의 심정으로는 자신이 없다.

위에서 언급한 필자의 에피소드가 한국 오라클의 2010년 매거진 여름호에 실려있다. 아래의 PDF 파일을 참고하기 바란다.
(2010년 7월 추가)

오라클 매거진 2010년 여름호

THE LOGICAL OPTIMIZER (양장): 국내도서>컴퓨터/인터넷; 저자 : 오동규; 출판 : 오픈메이드 2010.04.05

상세보기

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

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

The Logical Optimizer Part 1 - PPT (17)	2010.07.26
The Logical Optimizer-서점 (0)	2010.04.27
The Logical Optimizer-Script Download (37)	2010.04.20
The Logical Optimizer-오타와 오류등록 (27)	2010.04.20
저자와의 대화 (36)	2010.04.20

Posted by extremedb

,

Query Transformation Internal ( about JPPD using Lateral View )

Oracle/Optimizer 2009. 4. 20. 01:30

2007년 가을이었던가? 사내 컨설턴트로 부터 조금 어려운 질문이 들어왔다.
"다른종류의 쿼리변환은 모두 내가 수동으로 쿼리를 만들수 있습니다. 하지만 JPPD 는 쿼리를 만들수 없습니다.
혹시 오라클이 JPPD 를 적용한 SQL 을 보여줄수 있는지요?"

JPPD 는 수도없이 봐왔지만 막상 개념을 적용한 SQL 을 내손으로 작성하려 하니 전혀 작성할수가 없었다.
여러분이 알만한 미국및 영국의 유명한 컨설턴트들과 접촉을 해보았지만 역시 그들도 마찬가지였다. 필자는 이문제로 1주간 고생을한 끝에 직접 원리를 알아내었다. 따라서 어떤 메뉴얼에도 어떤 튜닝책에도 이런 이야기는 없음을 먼저 밝혀둔다.

JPPD ? 그게뭐야?
쿼리변환의 중요성을 알았으므로 이제 쿼리변환중에 가장 자주나오는 Unnesting 과 JPPD 의 개념을 알아보자.
Unnesting 이란 서브쿼리를 인라인뷰로 만들어 from 절로 끌어올리는 쿼리변환을 의미한다. JPPD 란 (Join Predicate Push Down)의 약자로서 인라인뷰 외부의 조인 조건이 인라인뷰 안쪽으로 파고드는 것을 의미한다.
물론 인라인뷰는 대신에 뷰로 사용해도 마찬가지 이다.

그럼 쿼리변환을 한번 해보자.
지난번 오라클에 트랜스포머가 있다? 라는 글에서 다단계 쿼리변환(Unnesting + View Merging) 사례를 설명한바 있다. 이번에는 다단계 쿼리변환 이면서 서브쿼리 Unnsting 후에 View Merging 이 실패하는 경우에 JPPD가 수행되는 사례를 알아보자.

환경 : Oracle 10.2.0.4

select /*+ gather_plan_statistics PUSH_PRED(OUTER) */
outer.*
from (SELECT * FROM emp outer
UNION ALL
SELECT * FROM emp outer) OUTER
where outer.sal > ( select /*+ QB_NAME(SUB) UNNEST */ avg(inner.sal)
from emp inner
where inner.deptno = outer.deptno
) ;

---------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS    | |    1 | 10 |00:00:00.03 |    27 |
| 2 | VIEW | VW_SQ_1 |    1 |    5 |00:00:00.02 | 7 |
| 3 |    HASH GROUP BY | |    1 |    5 |00:00:00.02 | 7 |
| 4 | TABLE ACCESS FULL    | EMP |    1 | 14 |00:00:00.02 | 7 |
| 5 | VIEW | |    5 | 10 |00:00:00.01 |    20 |
| 6 |    UNION ALL PUSHED PREDICATE | |    5 | 10 |00:00:00.01 |    20 |
|* 7 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 |    11 |
|* 8 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 5 |
|* 9 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 | 9 |
|* 10 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("OUTER"."SAL">"VW_COL_1")
   8 - access("OUTER"."DEPTNO"="DEPTNO")
   9 - filter("OUTER"."SAL">"VW_COL_1")
10 - access("OUTER"."DEPTNO"="DEPTNO")

위실행계획은 쿼리변환이 2단계로 쿼리변환이 수행되었다.
지금부터 과정을 하나하나 살펴보자.

1.단계 : Unnesting 수행
먼저 서브쿼리가 인라인뷰로 바뀌어 from 절로 올라간다.
그리고 쿼리의 바깥쪽에 WHERE 조건이 생성되며 조인절도 생성된다.
이것은 아래의 쿼리와 같다.

select /*+ gather_plan_statistics LEADING(SUB OUTER) USE_NL(OUTER) NO_PUSH_PRED(OUTER) */
   outer.*
from (SELECT * FROM SI31041.emp outer    --> JPPD not yet
   UNION ALL
   SELECT * FROM SI31041.emp outer) OUTER ,
   ( select deptno, avg(sal) AS VW_COL_1
from emp
   group by deptno
   ) SUB
where outer.sal > SUB.VW_COL_1
and outer.deptno = SUB.deptno ;

------------------------------------------------------------------------------
| Id | Operation    | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------
| 1 | NESTED LOOPS    |    |    1 | 12 |00:00:00.01 |    50 |
| 2 | VIEW |    |    1 |    3 |00:00:00.01 | 7 |
| 3 |    HASH GROUP BY |    |    1 |    3 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS FULL| EMP |    1 | 14 |00:00:00.01 | 7 |
|* 5 | VIEW |    |    3 | 12 |00:00:00.01 |    43 |
| 6 |    UNION-ALL |    |    3 | 84 |00:00:00.01 |    43 |
| 7 | TABLE ACCESS FULL| EMP |    3 | 42 |00:00:00.01 |    22 |
| 8 | TABLE ACCESS FULL| EMP |    3 | 42 |00:00:00.01 |    21 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("OUTER"."SAL">"SUB"."VW_COL_1" AND "OUTER"."DEPTNO"="SUB"."DEPTNO"))

위의 Predicate Information을 보면 서브쿼리가 인라인뷰로 바뀌었지만 아직 인라인뷰 내로 WHERE 조건및
조인조건이 파고들지 못했다. 따라서 뷰를 만들고 FILTER 처리가 된것이다.

2단계: JPPD 수행
조인조건및 WHERE 조건이 UNION ALL 로 분리된 각각의 SQL 에 파고든다.
최종적으로 완성된 쿼리는 아래와 같다.

select /*+ push_pred (outer) */
outer.*
from (select /*+ unnest qb_name (sub) */
avg(inner.sal) vw_col_1,inner.deptno deptno
   from emp inner
group by inner.deptno
) vw_sq_1,
( select *    --> JPPD OK using lateral view
   from emp outer
where outer.deptno=vw_sq_1.deptno --> 조건이 인라인뷰 내로 들어옴 (Lateral view)
and outer.sal>vw_sq_1.vw_col_1
union all
   select *
   from emp outer
where outer.deptno=vw_sq_1.deptno --> 조건이 인라인뷰 내로 들어옴(Lateral view)
and outer.sal>vw_sq_1.vw_col_1
) outer

JPPD 의 비밀이 풀리다!
위의 SQL 에서 이상한점을 발견할수 있는가?
인라인뷰 OUTER 에서 다른 인라인뷰 VW_SQ_1 의 컬럼을 참조하고 있다. 이것은 놀라운 일이다. 인라인뷰 내에서 마치 스칼라 서브쿼리처럼 from 절의 다른 테이블 혹은 다른 인라인뷰의 정보를 사용한것이다. 바로 이것이 Lateral View 의 개념이다. 다시말하면 Lateral View란 스칼라 서브쿼리처럼 사용할수 있는 "스칼라 인라인뷰" 라고 생각하면 된다. 위의 SQL 을 보면 아래의 실행계획과 같을수 밖에 없다는것을 알수 있다.

---------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS    | |    1 | 10 |00:00:00.03 |    27 |
| 2 | VIEW | VW_SQ_1 |    1 |    5 |00:00:00.02 | 7 |
| 3 |    HASH GROUP BY | |    1 |    5 |00:00:00.02 | 7 |
| 4 | TABLE ACCESS FULL    | EMP |    1 | 14 |00:00:00.02 | 7 |
| 5 | VIEW | |    5 | 10 |00:00:00.01 |    20 |
| 6 |    UNION ALL PUSHED PREDICATE | |    5 | 10 |00:00:00.01 |    20 |
|* 7 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 |    11 |
|* 8 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 5 |
|* 9 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 | 9 |
|* 10 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("OUTER"."SAL">"VW_COL_1")
   8 - access("OUTER"."DEPTNO"="DEPTNO")
   9 - filter("OUTER"."SAL">"VW_COL_1")
10 - access("OUTER"."DEPTNO"="DEPTNO")

오라클만이 Lateral View를 사용할수 있다.
아쉽게도 Lateral View 는 오라클만이 내부적으로 사용할수 있다. 필자나 여러분이 사용할 경우 에러가 발생한다. 그렇다면 위의 SQL은 어디서 나온것인가? 그것은 쿼리 트랜스포머의 쿼리변환작업을 10053 이벤트를 이용하여 Trace 파일에서 추출한 결과 이다.

결론:
이상으로 우리는 2가지 사실을 알아내었다.
첫번째는 서브쿼리 Unnsting 후에 View Merging 이 실패하는 경우에 JPPD를 시도한다는것.
두번째는 쿼리 트랜스포머는 JPPD 수행시 Lateral View를 사용한다는것이다.
마지막으로 가까운 미래에 Lateral View를 User가 직접 사용할수 있는날을 기대하면서 이글을 마치고자 한다.

Further Reading :
Lateral View 개념 : http://scidb.tistory.com/search/lateral%20view
SubQuery Unnesting : http://scidb.tistory.com/entry/SubQuery-Using-Method-1

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

11g DBMS_STATS 패키지 성능개선의 3가지 원리 (3)	2009.06.15
PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라. (2)	2009.06.09
오라클에 트랜스포머가 있다? (4)	2009.04.15
Access Sub Query 의 함정 (4)	2008.11.19
오라클은 얼마나 똑똑한가? (3)	2008.10.10

Posted by extremedb

,

오라클에 트랜스포머가 있다?

Oracle/Optimizer 2009. 4. 15. 09:59

부제목: 다단계 쿼리변환 (Muti-Phase Query Transformation)

SF 영화 트랜스포머를 보면 자동차가 로봇으로 변환하는 과정이 있다. 자동차와 로봇간의 변환과정은 아주 현란하다 못해 활홍하여 시청자자로 하여금 넋을 놓고 빠져들게 한다. 컴퓨터그래픽(CG) 기술의 발전 덕분이다.

변환과정이 있어야 지구를 지킬수 있어
만약 이 영화에서 자동차가 로봇으로 변환을 못한다고 상상해보자. 악한 로봇이 쳐들어와도 싸울수가 없고 격렬한 전투장면도 사라진다. 이래서는 영화가 재미없을 뿐더러 지구를 지킬수도 없다. 그럼 오라클에서 Query Transformer 가 없어진다면 어떻게 될까? 마찬가지로 Query 의 상당부분을 튜닝할수 없게 되어 전체 시스템이 느려지게된다. Query Transformer 의 목적은 성능향상에 있다.

오라클에도 트랜스포머가 있다.
오라클 Optimizer 에서 Query Transformer 는 3대 Components 로서 아주 중요한 위치에 있다.
먼저 Query Transformer 를 이해하기 위해서 Optimizer 구조를 살펴볼 필요가 있다.

먼저 Query Parser 가 SQL 을 검사하여 넘겨주면 Transformer 가 SQL 을 변신시켜서 Estimator 에 넘겨준다.
이때 Estimator는 통계정보등을 참조하여 가장 낮은 cost 를 갖는 SQL 을 찾아내어 Plan Generator 에 넘겨주고 실행계획을 완성하게 된다. 사실 위의 그림은 오라클 Performance Tuning Guide 에 있는 그림 이지만 잘못된 것이 있다. Query Transformer 가 Estimator 에게 주는 SQL 은 하나이상이 될수 있으므로 Estimator 와 Plan Generator 의 관계처럼 반복적인 Loop 가 있어야 한다.

변환과정도 로봇에 따라 다양하다.
트랜스포머에서 주인공 로봇의 변환과정은 아주 복잡하다. 하지만 소형 악당 로봇이 카세트 레코더로 변환하는 과정을 유심히 보았는가? 이 과정은 매우 간단하다. 오라클의 쿼리변환(Query Transformation) 과정도 간단한 것에서 부터 아주 복잡한 과정을 거치는 것 까지 다양하다.

구슬이 서말이라도 꿰어야 보배
오늘은 조금 어려운 다단계 쿼리변환-(Muti-Phase-Query Transformation)에 대하여 알아보려 한다.
참고로 아래의 글이 이해하기 힘든 독자는 필자의 이전글 Using Sub query Method (Sub query Flattening ) 과 Using Sub query Method( Filter / Access sub Query ) 를 먼저 읽어보기 바란다.
그럼 각 단계별로 변환과정을 보자.

1 단계 : 원본 쿼리
자신이 속한 부서의 평균급여 보다 돈을 많이 받는 사원을 추출하는 예제이다.

select /*+ gather_plan_statistics */ outer.*
from emp outer
where outer.sal > ( select /*+ NO_UNNEST */ avg(inner.sal)
from emp inner
where inner.deptno = outer.deptno
);

--------------------------------------------------------------------------------------------
| Id | Operation | Name    | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
|* 1 | FILTER | |    1 |    5 |00:00:00.01 |    16 |
| 2 | TABLE ACCESS FULL | EMP |    1 | 14 |00:00:00.01 | 8 |
| 3 | SORT AGGREGATE    | |    5 |    5 |00:00:00.01 | 8 |
| 4 |    TABLE ACCESS BY INDEX ROWID| EMP |    5 | 13 |00:00:00.01 | 8 |
|* 5 | INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">)
   5 - access("INNER"."DEPTNO"=:B1)

전통적인 Filter Subquery(확인자 SubQuery) 이다.

2.단계 : 서브쿼리를 인라인뷰로 바꿔라.
이 단계에서 unnest 힌트를 사용함으로서 Subquery 가 인라인뷰로 바뀌며 서브쿼리가 없어진다. 이때 메인쿼리의 건수를 유지하기 위해 인라인뷰에 group by 가 추가된다.

select /*+ gather_plan_statistics */ outer.*
from emp outer
where outer.sal > ( select /*+ QB_NAME(SUB) UNNEST */ avg(inner.sal)
from emp inner
where inner.deptno = outer.deptno
);

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name    | Starts | A-Rows | A-Time | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP |    1 |    5 |00:00:00.01 |    16 |    |
| 2 | NESTED LOOPS    | |    1 | 19 |00:00:00.09 |    10 |    |
| 3 |    VIEW | VW_SQ_1 |    1 |    5 |00:00:00.01 | 7 |    |
| 4 | HASH GROUP BY | |    1 |    5 |00:00:00.01 | 7 | 1622K (0)|
| 5 |    TABLE ACCESS FULL    | EMP |    1 | 14 |00:00:00.01 | 7 |    |
|* 6 |    INDEX RANGE SCAN | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 3 |    |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">"VW_COL_1")
   6 - access("DEPTNO"="OUTER"."DEPTNO")
   filter("OUTER"."DEPTNO" IS NOT NULL)

이것은 Optimizer가 쿼리를 아래처럼 변형시킨것이다.

select /*+ gather_plan_statistics */
   outer.*
from emp outer,
   ( select deptno, avg(sal) AS VW_COL_1
      from emp
group by deptno
) A
where outer.sal > A.VW_COL_1
   and outer.deptno = A.deptno ;

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name    | Starts | A-Rows | A-Time | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP |    1 |    5 |00:00:00.01 |    16 |    |
| 2 | NESTED LOOPS    | |    1 | 19 |00:00:00.13 |    10 |    |
| 3 |    VIEW | |    1 |    5 |00:00:00.01 | 7 |    |
| 4 | HASH GROUP BY | |    1 |    5 |00:00:00.01 | 7 | 1622K (0)|
| 5 |    TABLE ACCESS FULL    | EMP |    1 | 14 |00:00:00.01 | 7 |    |
|* 6 |    INDEX RANGE SCAN | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 3 |    |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OUTER"."SAL">"A"."VW_COL_1")
   6 - access("OUTER"."DEPTNO"="A"."DEPTNO")
   filter("OUTER"."DEPTNO" IS NOT NULL)

2단계의 원본 쿼리와 Plan 이 일치함을 알수 있다.

3단계 : 인라인뷰를 해체하라.
MERGE 힌트를 사용함으로서 2단계에서 Unnesting 된 인라인뷰를 해체하여 조인으로 바뀌었다. 이것을 View Merging 이라고 부른다.

select /*+ gather_plan_statistics MERGE(@SUB) */
outer.*
from emp outer
where outer.sal > ( select /*+ QB_NAME(SUB) UNNEST */ avg(inner.sal)
from emp inner
where inner.deptno = outer.deptno
);

다시말하면 위의 쿼리를 Optimizer가 아래처럼 재작성 한것이다.

select /*+ gather_plan_statistics */
   outer.deptno deptno,outer.sal sal,
   outer.empno empno
   from emp inner,
emp outer
where inner.deptno=outer.deptno
group by inner.deptno, outer.rowid, outer.empno, outer.sal, outer.deptno
having outer.sal > avg(inner.sal) ;

메인쿼리의 결과집합을 보존하기위하여 rowid 로 Group by 를 한것에 유의하자.
두개의 Query Plan 은 동일하며 아래와 같다.

-----------------------------------------------------------------------------------------------
| Id | Operation    | Name    | A-Rows | A-Time | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------
|* 1 | FILTER    | |    5 |00:00:00.01 |    12 |    |
| 2 | HASH GROUP BY    | | 13 |00:00:00.01 |    12 | 1103K (0)|
| 3 |    MERGE JOIN    | | 51 |00:00:00.01 |    12 |    |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 13 |00:00:00.01 | 5 |    |
|* 5 |    INDEX FULL SCAN | IX_EMP_N3 | 13 |00:00:00.01 | 1 |    |
|* 6 | SORT JOIN    | | 51 |00:00:00.01 | 7 | 2048 (0)|
|* 7 |    TABLE ACCESS FULL | EMP | 13 |00:00:00.01 | 7 |    |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">AVG("INNER"."SAL"))
   5 - filter("INNER"."DEPTNO" IS NOT NULL)
   6 - access("INNER"."DEPTNO"="OUTER"."DEPTNO")
   filter("INNER"."DEPTNO"="OUTER"."DEPTNO")
   7 - filter("OUTER"."DEPTNO" IS NOT NULL)

드디어 1~3 단계에 걸친 Query Transformation 단계가 완성 되었다. 그결과는 성능면에서 대성공이다. Buffers(읽은 Block수) 가 16(원본쿼리) 에서 12 로 약 25% 감소했다.

오라클 트랜스포머는 악성쿼리와 싸워...
오라클 Query Transformer 는 SQL 을 멋지게 변화시켰다. 이모든 과정을 개발자가 해야한다고 상상해보자.
개발자들에게 전체과정을 이해시키는 교육과정이 추가되어야 하고 개발속도는 몇배나 느려질것이다. 이는 프로젝트의 Risk 가 될것이다. 하지만 오라클 Query Transformer 가 있으므로 악당 로봇이 아닌 악성쿼리와 멋지게 싸워서 이길수 있는 것이다.

편집후기 :
Query Transformation 을 하려면 반드시 unnesting 이나 merge 힌트를 써야 하는지 질문이 들어왔다. 대부분의 경우 Query Transformer 가 자동으로 변환과정을 수행해준다. 하지만 이것이 가끔 제대로 수행이 안될수 있으므로 이럴경우에만 명시적으로 힌트를 사용하는것이 바람직하다.

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

PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라. (2)	2009.06.09
Query Transformation Internal ( about JPPD using Lateral View ) (0)	2009.04.20
Access Sub Query 의 함정 (4)	2008.11.19
오라클은 얼마나 똑똑한가? (3)	2008.10.10
Using Sub query Method( Filter / Access sub Query ) (9)	2008.09.29

Posted by extremedb

,

Science of Database

'쿼리변환'에 해당되는 글 10건

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

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

COUNT(Distinct 컬럼)의 성능

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

검색조건을 동적으로 바꾸어야 할 때

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

union과 union all의 숨겨진 차이점

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

The Logical Optimizer-Part 4 PPT

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

The Logical Optimizer-Part 3 PPT

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

Heuristic Query Transformation-PPT

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

The Logical Optimizer

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

Query Transformation Internal ( about JPPD using Lateral View )

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

오라클에 트랜스포머가 있다?

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

카테고리

최근에 올라온 글

최근에 달린 댓글

글 보관함

링크

태그목록

최근에 받은 트랙백

티스토리툴바


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