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를 구한다그결과 변환되지 않은 쿼리의 Cost1237.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를 발견했지만 여러가지 문제로 반영하지 못하다가 이제서야 올리게 되었다. 사과드린다.

Posted by extremedb
,

집계함수 내부에 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_IDGROUP 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$1Distinct 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 파라미터로 이 기능을 제어할 수 있고 Defaulttrue이다. 힌트로는 TRANSFORM_DISTINCT_AGG / NO_TRANSFORM_DISTINCT_AGG 를 사용할 수 있다.

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


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

그럼 건강하세요.

Posted by extremedb
,

부제: Cardinality Feed Back의 개념과 사용예제

이번 글은 난이도가 높으므로 익숙하지 않은 사람은 Cardinality Feedback의 개념 정도만 이해하기 바란다. 물론 이 블로그를 꾸준히 구독한 독자라면 어려움 없이 볼 수 있다.

 

현재 많은 시스템이 Oracle11g로 옮겨가고 있다. 11g는 새로운 기능이 많이 추가되었다. 하지만 새롭고 좋은 기능이라도 완벽하지 못하면 문제가 될 수 있다. 오늘은 11g의 새 기능 때문에 성능문제가 발생하는 경우를 소개한다.

시스템이 운영 중에 있을 때 가장 곤욕스러운 경우 중 하나는 SQL의 실행계획이 갑자기 바뀌어 성능이 나빠지는 것이다. SQL과 인덱스 그리고 통계정보가 모두 바뀌지 않아도 실행계획은 바뀔 수 있다. 예를 들면 Oracle11g의 기능인 Cardinality Feedback을 사용함으로 해서 얼마든지 실행계획이 바뀔 수 있는 것이다. 이번 시간에는 실행계획이 변경되는 원인 중 하나인 Cardinality Feedback 의 개념과 작동방식에 대해 알아보고 이것이 언제 문제가 되는지 분석해 보자. 이번에 소개할 예제는 종합적이다. Cardinality Feedback + Cost Based Query Transformation + Bloom Filter가 결합된 것이다. 이를 놓친다면 이들이 어떻게 결합되는지 알 수 없을 뿐만 아니라 성능이 악화된 원인을 파악할 수 없다.

 

예측, 실행, 비교, 그리고 전달

소 잃고 외양간 고친다는 말이 있다. 이미 늦었다는 이야기 이지만 좋은 말로 바꾸면 실수를 다시 하지 않겠다는 의지이다. cardinality feedback(이후 CF)도 이와 비슷한 개념이다. 예를 들어 col1 = ‘1’ 이라는 조건으로 filter되면 백만 건이 return된다고 옵티마이져가 예측해서 full table scan을 했다. 하지만 예측과 달리 실행결과가 100건이 나왔다면? 해당 SQL을 다시 실행할 때는 full table scan보다는 index scan이 유리할 것이다. 그런데 같은 SQL을 두 번째 실행할 때 "실제로는 백만 건이 아니라 100건 뿐이야"라는 정보를 옵티마이져에게 알려주는 전달자가 필요하다. 그 전달자가 바로 CF이다. CF가 없으면 결과가 100건 임에도 SQL을 실행 할 때마다 full table scan을 반복할 것이다. 결국 CF는 악성 실행계획을 올바로 수정하는 것이 목적이며 매우 유용한 기능임을 알 수 있다. CF의 단점은 최초에 한번은 full table scan이 필요하다는 것이다. 왜냐하면 실행해서 결과가 나와야만 실제 분포도(건수)를 알 수 있기 때문이다.

 

CF는 어떻게 실행되나?

CF는 같은 SQL을 두 번 이상 실행했을 때 적용된다. 그 이유는 아래의 CF 적용순서를 보면 알 수 있다.

1. 최초의 실행계획을 작성할 때(Hard Parsing 시에) 예측 분포도가 계산된다.

2. SQL이 실행된다. 한번은 실행 해봐야 예측 분포도와 실제 분포도를 비교할 수 있다.

3. 예측 분포도와 실제 분포도의 값이 차이가 크다면 실제 분포도를 저장한다.

4. 두 번째 실행될 때 CF에 의해 힌트의 형태로 옵티마이져에게 전달되어 실제 분포도가 적용된다. 이때 분포도뿐만 아니라 실행계획이 바뀔 수 있다. 두 번째 이후로 실행될 때는 CF가 계속 적용된다.

 

CF를 발생시켜보자

실행환경 :Oracle 11.2.0.1

 

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SESSION SET "_OPTIMIZER_USE_FEEDBACK" = TRUE; -- CF를 활성화 한다. default true이다.

 

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(c)  */

       c.cust_id, c.cust_first_name, c.cust_last_name,

       s.prod_cnt, s.channel_cnt, s.tot_amt

  FROM customers c,

       (SELECT   s.cust_id,

                 COUNT (DISTINCT s.prod_id) AS prod_cnt,

                 COUNT (DISTINCT s.channel_id) AS channel_cnt,

                 SUM (s.amount_sold) AS tot_amt

            FROM sales s

        GROUP BY s.cust_id) s

 WHERE c.cust_year_of_birth = 1987

   AND s.cust_id = c.cust_id ;

 

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

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

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

|   0 | SELECT STATEMENT               |                   |        |     23 |00:00:00.15 |    5075 |          |

|*  1 |  HASH JOIN                     |                   |    162 |     23 |00:00:00.15 |    5075 | 1215K (0)|

|   2 |   JOIN FILTER CREATE           | :BF0000           |    162 |    151 |00:00:00.01 |     148 |          |

|   3 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS         |    162 |    151 |00:00:00.01 |     148 |          |

|   4 |     BITMAP CONVERSION TO ROWIDS|                   |        |    151 |00:00:00.01 |       2 |          |

|*  5 |      BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX |        |      1 |00:00:00.01 |       2 |          |

|   6 |   VIEW                         |                   |   7059 |     55 |00:00:00.15 |    4927 |          |

|   7 |    SORT GROUP BY               |                   |   7059 |     55 |00:00:00.15 |    4927 |88064  (0)|

|   8 |     JOIN FILTER USE            | :BF0000           |    918K|   7979 |00:00:00.12 |    4927 |          |

|   9 |      PARTITION RANGE ALL       |                   |    918K|   7979 |00:00:00.11 |    4927 |          |

|* 10 |       TABLE ACCESS FULL        | SALES             |    918K|   7979 |00:00:00.09 |    4927 |          |

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

Predicate Information (identified by operation id):        

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

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

   5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)               

  10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID")) --> Bloom Filter 적용     

                

SQL 실행결과 sales 테이블의 예측 분포도는 918K건이며 실제 분포도는 Bloom Filter가 적용되어 7979건이다. 그리고 group by operation(ID 7)의 예측 분포도는 7059건이며 실제 분포도는 55건이다. 예측과 실제의 분포도 차이는 두 경우 모두 100배 이상이다. 따라서 CF가 적용될 것이다. 이와는 반대로 customers 테이블의 예측 분포도와 실제 분포도는 162 152로 크게 다르지 않으므로 CF가 적용되지 않을 것이다. 이제 위의 SQL을 재 실행한다면 CF가 적용되어 실제 분포도가 적용될 것이다.

 

--> CF를 발생시키기 위해 위의 SQL 다시 실행               

                

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

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

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

|   0 | SELECT STATEMENT               |                   |        |     23 |00:00:05.61 |    5075 |          |

|   1 |  SORT GROUP BY                 |                   |     55 |     23 |00:00:05.61 |    5075 |75776  (0)|

|*  2 |   HASH JOIN                    |                   |    270 |   3230 |00:00:05.60 |    5075 | 1201K (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS         |    162 |    151 |00:00:00.01 |     148 |          |

|   4 |     BITMAP CONVERSION TO ROWIDS|                   |        |    151 |00:00:00.01 |       2 |          |

|*  5 |      BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX |        |      1 |00:00:00.01 |       2 |          |

|   6 |    PARTITION RANGE ALL         |                   |   7979 |    918K|00:00:02.82 |    4927 |          |

|   7 |     TABLE ACCESS FULL          | SALES             |   7979 |    918K|00:00:00.98 |    4927 |          |

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

Predicate Information (identified by operation id):               

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

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

   5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

 

Note

-----

   - cardinality feedback used for this statement --> CF가 발생되었음을 나타냄.

 

두 번째 실행 할 때 CF가 적용되어 예측 분포도가 7979로 바뀌었고 group by 분포도는 55건으로 바뀌었다. 이에 따라 실행계획도 바뀌었다. CF에 의해서 쿼리변환(Complex View Merging)이 발생된 것이다. 그리고 note CF가 적용되었다고 친절히 설명된다.

 

이제 더 자세한 분석을 위하여 10053 Trace의 내용을 보자. 두 번째 실행된 SQL 10053 Trace에 따르면 쿼리변환전의 SQL은 다음과 같다.

 

SELECT /*+ LEADING (C) */

       c.cust_id, c.cust_first_name, c.cust_last_name,

       s.prod_cnt, s.channel_cnt, s.tot_amt tot_amt

  FROM tlo.customers c,

       (SELECT   /*+ OPT_ESTIMATE (GROUP_BY ROWS=55.000000 ) OPT_ESTIMATE (TABLE S ROWS=7979.000000 ) */

                 s.cust_id cust_id, COUNT (DISTINCT s.prod_id) prod_cnt,

                 COUNT (DISTINCT s.channel_id) channel_cnt, SUM (s.amount_sold) tot_amt

            FROM tlo.sales s

        GROUP BY s.cust_id) s

 WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

 

CF에 의해서 OPT_ESTIMATE 힌트가 적용되었다. 실제 건수로 적용하는 것이므로 일견 문제가 없어 보인다. 하지만 쿼리변환과정(Complex View Merging)을 거치면 문제가 생긴다. 10053 Trace에서 나타난 쿼리변환 후의 SQL은 다음과 같다.

 

SELECT   /*+ OPT_ESTIMATE (GROUP_BY ROWS=55.000000 ) LEADING (C) OPT_ESTIMATE (TABLE S ROWS=7979.000000 ) */

         c.cust_id cust_id, c.cust_first_name cust_first_name,

         c.cust_last_name cust_last_name, COUNT (DISTINCT s.prod_id) prod_cnt,

         COUNT (DISTINCT s.channel_id) channel_cnt, SUM (s.amount_sold) tot_amt

    FROM tlo.customers c, tlo.sales s

   WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id

GROUP BY s.cust_id, c.ROWID, c.cust_last_name, c.cust_first_name, c.cust_id ;

 

CF의 문제점은?

위의 SQL은 두 가지 문제점이 있다. 두 문제 모두 쿼리변환에 의해 발생된다. 첫 번째 문제는 Bloom Filter와 관련된 것이다. CF의 영향으로 원본 SQL에 존재했던 Group By (Complex View)가 사라졌다. 뷰가 없어짐으로써 Bloom Filter가 적용되지 않는다. Filter가 사라졌음에도 불구하고 Filter가 존재했던 Cardinality 7979를 적용해 버렸다. 이에 따라 CF를 적용했음에도 7979건과 실제건수인 91 8천 건과는 엄청난 차이가 나고 말았다. Bloom Filter가 사라질 때는 CF를 적용하면 안 된다는 이야기이다. 비유하자면 Filter가 없는데도 불구하고 Filter가 존재할 때의 건수를 적용시킨 것이다.

 

두 번째 문제는 쿼리변환 후 힌트의 상속과 관련된다. 쿼리변환전의 CF의 의한 힌트를 보면 Group By된 뷰의 건수는 55건이다. 그런데 이 힌트는 오직 sales 테이블에 대한 것이다. 그런데 쿼리변환후의 힌트를 보면 그대로 55건이 적용되어 되어버렸다. Group by가 외부로 빠져 나옴으로 해서 GROUP_BY ROWS는 전체건수와 마찬가지가 되어버렸다. sales 테이블의 Group By건수는 55건이 맞다. 하지만 쿼리변환 때문에 조인 후에 Group By 하게 된다면 cardinality를 다시 계산해야 한다. 조인이 없는 테이블의 Group By건수와 조인후의 Group By건수가 어떻게 같을 수 있나?

 

두 가지의 문제점은 Cost를 계산할 때 그대로 적용되어 버린다. 10053 trace를 보자.

 

Access path analysis for SALES

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

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for SALES[S]

  Table: SALES  Alias: S

    Card: Original: 918843.000000    >> Single Tab Card adjusted from:918843.000000 to:7979.000000

  Rounded: 7979  Computed: 7979.00  Non Adjusted: 918843.00

  Access Path: TableScan

    Cost:  1328.68  Resp: 1328.68  Degree: 0

      Cost_io: 1321.00  Cost_cpu: 155262306

      Resp_io: 1321.00  Resp_cpu: 155262306

 

Bloom Filter가 없음에도 불구하고 Sales 테이블의 건수(Cardinality) 7979로 적용되어 버렸다. 이제 Group By가 적용된 건수를 보자.

 

GROUP BY cardinality:  270.000000, TABLE cardinality:  270.000000

>> Query Blk Card adjusted from 270.000000  to: 55.000000

    SORT ressource         Sort statistics

      Sort width:         583 Area size:      510976 Max Area size:   102340608

      Degree:               1

      Blocks to Sort: 3 Row size:     69 Total Rows:            270

      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0

      Total IO sort cost: 0      Total CPU sort cost: 20302068

      Total Temp space used: 0


Group By Cardinality와 관련된 Trace 내용이다. 여기서도 잘못된 Group By건수인 55를 적용시키고 있다. 조인 후에 Group By할 때는 Cardinality를 다시 계산해야 옳다. 이래서는 제대로 된 Cost가 나올 수 없다. 여기에 밝혀진 문제점은 SQL 하나에서 나온 것이므로 실전에서는 두 가지 문제뿐만 아니라 더 많을 것이다. 물론 옵티마이져가 모든 경우에 완벽할 수는 없다.


해결책
CF
문제의 해결방법을 생각해보자. 갑자기 실행계획이 바뀌어 성능문제가 발생했을 때 dbms_xplan.display_cursor의 note나 10053 Trace의 실행계획 부분을 보면 CF가 적용되었는지 아닌지 알 수 있다. 만약 CF가 적용되었다면 일단 의심해보아야 한다. 아래는 10053 trace의 실행계획 부분이다.

-----------------------------------------------------------+------------------------

| Id  | Operation                       | Name             | Rows  | Bytes | Cost  |

-----------------------------------------------------------+------------------------

| 0   | SELECT STATEMENT                |                  |       |       |  1368 |

| 1   |  SORT GROUP BY                  |                  |    55 |  2915 |  1368 |

| 2   |   HASH JOIN                     |                  |   270 |   14K |  1367 |

| 3   |    TABLE ACCESS BY INDEX ROWID  | CUSTOMERS        |   162 |  5832 |    38 |

| 4   |     BITMAP CONVERSION TO ROWIDS |                  |       |       |       |

| 5   |      BITMAP INDEX SINGLE VALUE  | CUSTOMERS_YOB_BIX|       |       |       |

| 6   |    PARTITION RANGE ALL          |                  |  7979 |  132K |  1329 |

| 7   |     TABLE ACCESS FULL           | SALES            |  7979 |  132K |  1329 |

-----------------------------------------------------------+------------------------

Predicate Information:

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

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

5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

 

Content of other_xml column

===========================

nodeid/pflags: 7 17nodeid/pflags: 6 17  cardinality_feedback: yes --> CF가 적용됨

...이후 생략


만약 CF가 문제가 된다면 해당 SQL을 시작하기 전에 세션단위로 _optimizer_use_feedback = false를 적용하거나 opt_param 힌트를 사용하면 된다. 이렇게 하면 CF가 방지되어 쿼리변환의 원인이 제거된다. 따라서 Bloom Filter도 보존할 수 있다. 또 다른 방법은 인라인뷰에 no_merge 힌트를 적용하여 쿼리변환을 방지하면 문제는 해결된다. 이 두 가지 방법은 결국 쿼리변환을 방지하는 것이다.

 

결론

CF란 건수를 예측하고, 실행해서 실제건수와 예측건수를 비교하여 차이가 많다면 다음 번에 실행할 때 옵티마이져에게 실제건수를 전달해주는 역할을 한다. CF의 개념을 정리 했으므로 이제 큰 그림을 그려보자. 위의 예제에서 성능이 악화된 직접적인 이유는 Bloom Filter가 사라졌기 때문이다. 하지만 그렇게 된 이유는 쿼리변환 때문이며 쿼리변환의 이유는 CF 때문이다. 직접적인 원인을 찾았다고 해도 포기해선 안 된다. 꼬리에 꼬리를 무는 원인이 있을 수 있기 때문이다. 이를 도식화 하면 다음과 같다.

사용자 삽입 이미지


옵티마이져의 설계관점에서 개선해야 될 사항을 논의 해보자. 옵티마이져가 CBQT를 고려할 때는 두 가지의 경우로 판단한다. 쿼리변환을 적용하기 전(Iteration 1) Cost와 적용 후(Iteration 2) Cost를 비교해야 되기 때문이다. 쿼리변환전의 Cost를 구할 때는 CF를 적용시키고 반대로 쿼리변환 후에는 CF를 적용하지 않는 것이 더 좋은 Cost를 구할 수 있다. 왜냐하면 비록 답이 같다고 하더라도 형태가 전혀 다른 SQL에 대해 CF를 적용시킬 이유는 없기 때문이다. 물론 이렇게 해도 여전히 문제가 될 수는 있다. 하지만 문제의 발생확률은 많이 줄어들지 않겠는가?

 


Posted by extremedb
,

부제목: 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란 교집합이며 이것은 조인으로 해결할 수 있다. 왜냐하면 조인이란 두 집합에서 조인된 컬럼을 기준으로 값이 같은 것만 추출하는 기능이기 때문이다. 그렇지 않은가?

Posted by extremedb
,

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

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

사용자 삽입 이미지
사용자 삽입 이미지


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

압축  프로그램 7zip

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



Posted by extremedb
,

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





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

(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
감사합니다.

사용자 삽입 이미지
사용자 삽입 이미지
사용자 삽입 이미지
Posted by extremedb
,

이전 글(NULL AWARE ANTI JOIN SQL을 어떻게 변경시키나?) 에서 NULL AWARE ANTI JOIN 중에서 조인방법이 NESTED LOOPS 조인을 선택한다면 NULL을 체크하는 서브쿼리가 추가된다고 설명하였다. 이번에는 NESTED LOOPS ANTI NULL AWARE가 아닌 HASH JOIN ANTI NULL AWARE에 대하여 알아보자. 들어가기 전에 이번 글을 이해하려면 이전 글의 이해가 필수적이니 먼저 빠르게 읽고 오기 바란다.

 

오해를 하다

(The Logical Optimizer) 158 페이지의 내용에 따르면 WHERE 조건이 추가되면 NULL을 체크하는 Filter가 적용되지 않는다고 하였다. 하지만 이것은 필자의 오해였다. 얼굴이 화끈거리는 오류이다. 아래의 예제를 보자.

 

SELECT /*+ QB_NAME(MAIN) */

       d.department_id, d.department_name, d.location_id

  FROM department d

 WHERE d.department_id NOT IN (SELECT /*+ QB_NAME(SUB) */

                                      e.department_id

                                 FROM employee e

                                WHERE e.job_id = 'PU_CLERK')

   AND d.location_id = 1700;

 

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

| Id  | Operation                    | Name             | Rows  | Bytes | Cost  | Time     |

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

|   0 | SELECT STATEMENT             |                  |    16 |   512 |     5 | 00:00:01 |

|*  1 |  HASH JOIN ANTI NA           |                  |    16 |   512 |     5 | 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |    21 |   420 |     2 | 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX |    21 |       |     1 | 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |     5 |    60 |     2 | 00:00:01 |

|*  5 |    INDEX RANGE SCAN          | EMP_JOB_IX       |     5 |       |     1 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

   3 - access("D"."LOCATION_ID"=1700)

   5 - access("E"."JOB_ID"='PU_CLERK')

 

위의 예제에서 필자는 “서브쿼리의 조건절에 e.JOB_ID = 'PU_CLERK' 조건을 추가하자 IS NULL FILTER가 사라졌다.” 라고 했는데 이 부분이 잘못되었다. WHERE 조건의 추가유무와는 상관없이 조인종류(JOIN METHOD)에 따라서 NULL을 체크하는 FILTER의 유무가 결정된다. 아래의 SQL로써 이 사실을 증명해보자. 아래의 SQL은 조건절을 추가하지 않고도 조인방법만 HASH로 변경하였다. USE_HASH 힌트를 빼면 NESTED LOOPS ANTI SNA 로 풀리고 NULL을 체크하는 서브쿼리가 추가된다. 


SELECT /*+ gather_plan_statistics use_hash(e@sub) */

       d.department_id, d.department_name, location_id

  FROM department d

 WHERE d.department_id NOT IN (SELECT /*+ qb_name(sub) */ 

e.department_id

                                 FROM employee e)

   AND d.location_id = 1700;

 

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

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

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

|   0 | SELECT STATEMENT             |                  |      0 |00:00:00.01 |       9 |

|*  1 |  HASH JOIN ANTI NA           |                  |      0 |00:00:00.01 |       9 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |     21 |00:00:00.01 |       2 |

|*  3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX |     21 |00:00:00.01 |       1 |

|   4 |   TABLE ACCESS FULL          | EMPLOYEE         |     97 |00:00:00.01 |       7 |

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

 

Predicate Information (identified by operation id):

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

   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

   3 - access("D"."LOCATION_ID"=1700)

 

HASH JOIN ANTI NA NULL을 체크하는 NOT EXISTS 서브쿼리를 만들지 않음을 알 수 있다. Predicate Information의 어디에도 NULL을 체크하는 FILTER는 없다. 다시 말하면 HASH JOIN ANTI NA IS NULL Filter 서브쿼리를 만들지 않고 Hash 조인을 할 때 NULL 데이터를 체크하므로 NULL 체크용 서브쿼리가 필요 없는 것이다.  

 

결론

Null을 체크하는 서브쿼리는 NESTED LOOP ANTI NA인 경우만 추가되고 HASH JOIN ANTI NA에서는 생성되지 않는다. 필자는 책을 집필할 자료를 준비할 때 데카르트의 방법을 의도적으로 사용하였지만 이렇게 간단한 원리도 놓치고 말았다. 데카르트의 방법론이 어렵고 특별할 것 같지만 사실은 아주 간단하다. 어떤 것을 연구하거나 진리를 탐구할 때 내가 아는 것이 없다고 가정하는 것이다. 즉 내가 아는 것까지 모른다고 가정하고 모든 것을 검증하라는 것이다. 궁금한 사람은 데카르트의 방법서설을 자세히 읽어보라.

 

몇 년간 데카르트의 방법을 100% 사용하기는 어려웠다. 그 약속을 지킨다는 것은 엄청난 스트레스를 수반한다. 그럼에도 안다고 생각하는 것을 모두 검증하려고 덤볐지만 결국 오류는 막을 수 없었다. 이유는 지식의 저주 때문이다. 어떠한 결과나 현상을 보았을 때 그것의 생김새나 특징이 매우 친숙하다면 내가 알고 있다고 착각 하는 것. 이것은 매우 위험한 일이었다. 이 문제는 필자를 비롯한 모든 과학자 및 연구원들의 고민일 것이다. 이 문제를 해결할 방법은 없는 걸까?


Posted by extremedb
,

Oracle 10g 까지는 NOT IN 서브쿼리를 사용할 때 NULL을 허용하는 컬럼으로 메인쿼리와 조인하면 Anti Join을 사용할 수 없었고 Filter 서브쿼리로 실행되었기 때문에 성능이 저하되었다. 마찬가지로 메인쿼리쪽의 조인컬럼이 NULL 허용이라도 Filter로 처리된다. 하지만 11g부터는 Anti Join Null Aware를 사용하여 Null인 데이터가 한 건이라도 발견되면 Scan을 중단하므로 성능이 향상된다. (The Logical Optimizer)에서도 이런 사실을 언급하고 있다. 하지만 Anti Join Null Aware로 인해 변환된 SQL의 모습은 책에서 언급되지 않았으므로 이 글을 통하여 알아보자.

 

먼저 가장 기본적인 예제를 실행해보자.

실행환경: Oracle 11.2.0.1

 

--Anti Join Null Aware를 활성화 시킨다. Default True 이므로 실행하지 않아도 됨.

ALTER SESSION SET "_optimizer_null_aware_antijoin" = TRUE;

 

SELECT d.department_id, d.department_name, location_id

  FROM department d

 WHERE d.department_id NOT IN (SELECT e.department_id

                                 FROM employee e)

   AND d.location_id = 1700;

 

NOT IN 서브쿼리는 두 가지 뜻이 있다

위의 SQL을 해석할 때 단순히 location_id = 1700인 부서 중에서 사원이 한 명도 없는 건을 출력한다고 생각하면 한가지를 놓친 것이다. 만약 이런 요건이라면 NOT IN 대신에 NOT EXISTS 서브쿼리를 사용해야 한다. 다시 말해 NOT IN 서브쿼리를 사용하면 employee 테이블의 department_id 값 중에 한 건이라도 Null이 있으면 결과집합이 출력되지 않는다. 실제로도 결과건수가 없다. 이제 위의 SQL에 해당하는 Plan을 보자.

 

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

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

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

|   0 | SELECT STATEMENT              |                   |      0 |00:00:00.01 |       7 |

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

|   2 |   NESTED LOOPS ANTI SNA       |                   |      0 |00:00:00.01 |       0 |

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      0 |00:00:00.01 |       0 |

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

|*  5 |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      0 |00:00:00.01 |       0 |

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

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

Predicate Information (identified by operation id):

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

   1 - filter( IS NULL)

   4 - access("D"."LOCATION_ID"=1700)

   5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

   6 - filter("E"."DEPARTMENT_ID" IS NULL)

 

NULL을 발견하면 멈춘다

NESTED LOOPS ANTI NA라는 기능은 Null 데이터를 찾자마자 Scan을 멈추는 것이다. ID 기준으로 6번의 Predicate Information을 보면 NULL인 데이터를 단 한 건(A-Rows 참조)만 찾아내고 Scan을 멈추었다. 이제 NESTED LOOPS ANTI SNA가 어떻게 수행되는지 10053 Trace를 통하여 살펴보자.

 

FPD: Considering simple filter push in query block SEL$526A7031 (#1)

"D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=1700 AND  NOT EXISTS (SELECT /*+ QB_NAME ("SUB") */ 0 FROM "EMPLOYEE" "E")

FPD: Considering simple filter push in query block SUB (#2)

"E"."DEPARTMENT_ID" IS NULL

try to generate transitive predicate from check constraints for query block SUB (#2)

finally: "E"."DEPARTMENT_ID" IS NULL

 

FPD(Filter Push Down) 기능으로 인하여 쿼리블럭명이 SUB Not Exists 서브쿼리가 추가 되었고 그 서브쿼리에 DEPARTMENT_ID IS NULL 조건이 추가되었다.

 

SQL 어떻게 바뀌었나?

위의 10053 Trace 결과에 따르면 Logical Optimizer SQL을 아래처럼 바꾼 것이다.

 

SELECT d.department_id, d.department_name, d.location_id

  FROM department d

 WHERE NOT EXISTS (SELECT 0           

                     FROM employee e

                    WHERE e.department_id IS NULL) –-NULL 을 체크하는 서브쿼리

   AND NOT EXISTS (SELECT 0           

                     FROM employee e

                    WHERE e.department_id  = d.department_id)                     

   AND d.location_id = 1700 ;

 

SQL을 보면 NOT IN 서브쿼리가 NOT EXIST 서브쿼리로 바뀌었고 NULL을 체크하는 서브쿼리가 추가되었다. 또한 NULL을 체크하는 서브쿼리의 결과가 한 건이라도 존재하면 SQL은 더 이상 실행되지 않는다는 것을 알 수 있다. NESTED LOOPS ANTI SNA의 비밀이 풀리는 순간이다. ORACLE 9i 10g 에서도 위와 같이 SQL을 작성하면 NESTED LOOPS ANTI SNA의 효과를 볼 수 있다. 하지만 위의 SQL처럼 수동으로 작성하는경우 NULL 한건을 체크 하는데 오래 걸리며 부하가 있다면 이렇게 사용하면 안 된다. 이제 Plan을 보자.

 

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

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

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

|   0 | SELECT STATEMENT              |                   |      0 |00:00:00.01 |       7 |

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

|   2 |   NESTED LOOPS ANTI           |                   |      0 |00:00:00.01 |       0 |

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      0 |00:00:00.01 |       0 |

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

|*  5 |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      0 |00:00:00.01 |       0 |

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

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

Predicate Information (identified by operation id):

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

   1 - filter( IS NULL)

   4 - access("D"."LOCATION_ID"=1700)

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

   6 - filter("E"."DEPARTMENT_ID" IS NULL)

 

Operation의 순서에 유의하라

위의 Plan을 과 원본 Plan을 비교해보면 원본이 ANTI SNA라는 것만 제외하면 실행계획과 일량까지 같음을 알 수 있다. 헷갈리지 말아야 할 것은 ID 기준으로 6(NULL 체크 서브쿼리)이 가장 먼저 실행된다는 것이다. 왜냐하면 서브쿼리 내부에 메인쿼리와 조인조건이 없기 때문에 서브쿼리가 먼저 실행될 수 있기 때문이다. 반대로 Filter 서브쿼리내부에 메인쿼리와 조인 조건이 있다면 메인쿼리의 컬럼이 먼저 상수화 되기 때문에 항상 서브쿼리쪽 집합이 후행이 된다. 이런 사실을 모르고 보면 PLAN상으로만 보면 NULL 체크 서브쿼리가 가장 마지막에 실행되는 것으로 착각 할 수 있다.

 

결론

Anti Join Null Aware를 사용하여 Null인 데이터가 한 건이라도 발견되면 Scan을 중단하므로 성능이 향상된다. NULL을 체크하는 Filter 서브쿼리가 추가되기 때문이다. 하지만 그런 서브쿼리가 항상 추가되는 것은 아니다. 추가되는 기준이 따로 있는데 다음 글에서 이 부분을 다루려고 한다.

 

PS

책에 위의 SQL이 빠져있다. SQL PLAN을 출력하여 끼워 넣기 바란다.

Posted by extremedb
,

책(The Logical Optimizer)의 PPT 파일을 올리기로 결정하였다.
Part 1 부분에 해당하는 파일이다. 나머지 부분도 완성되는 즉시 배포할 예정이다.
많이 이용하길 바란다.

사용자 삽입 이미지
사용자 삽입 이미지


invalid-file

The Logical Optimizer_Part 1



파워포인트 작업을 해보니 의외로 시간이 많이 걸린다.^^


Posted by extremedb
,

영화 <마이너리포트>의 주인공인 톰 크루즈가 사용한 Dragging Board는 이미 몇 년전에 구현되었고 아이폰과 아이패드의 탄생으로 누구나 사용하게 되었다. 영화 <메트릭스> <터미네이터>를 보면 인간보다 우월한 기계들에 의해 지배를 당하거나 고통을 받는다. 이런 일을 먼 미래의 것으로 치부해 버리기에는 기술의 발전속도가 너무 빠르다. 이미 우리는 그런 세상에 살고 있다. 근거가 뭐냐고? 현재 적지 않은 수의 개발자들이 기계(옵티마이져) 보다 SQL의 작성능력이 떨어지기 때문이다.

 

예를 들면 옵티마이져가 재작성하는 SQL은 튜닝을 모르는 개발자가 작성한 것 보다 우월하다. 즉 개발자(인간)SQL을 작성했지만 옵티마이져는 품질이 떨어진다고 판단되는 SQL을 주인의 허락 없이 변경시켜 버린다.
인간이 Software 보다 못한 것인가?

 

같은 블록을 반복해서 Scan 하면 성능이 느려진다라는 문구는 비단 개발자, DBA, 튜너만 생각하는 것이 아니다. 옵티마이져는 분석함수를 이용하여 위의 문구를 직접 실천한다. 다시 말하면 같은 테이블을 중복해서 사용하는 경우 옵티마이져는 비효율을 없애기 위해 분석함수를 이용하여 SQL을 변경시킨다. 아래의 SQL을 보자.   

 

WITH v AS  (SELECT /*+ INLINE */

                   department_id, SUM (salary) AS sal

              FROM employee

             WHERE job_id = 'ST_CLERK'

             GROUP BY department_id )

SELECT d.department_id, d.department_name, v.sal

  FROM department d, v

 WHERE d.department_id = v.department_id

   AND v.sal = (SELECT MAX (v.sal)

                  FROM v ) ;

 

 

위의 SQL 보면 인라인뷰 V 먼저 정의해놓고 아래의 Select 절에서 사용한 것을 있다. 다시 말하면 같은 테이블을 (Temp 테이블에 Loading, 메인쿼리에 한번, 서브쿼리에 한번) 사용한 것이다. 아래의 실행계획을 보고 우리의 예상이 맞는지 확인해보자.

 

------------------------------------------------------+-----------------------------------+

| Id  | Operation                         | Name      | Rows  | Bytes | Cost  | Time      |

------------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT                  |           |       |       |     6 |           |

| 1   |  MERGE JOIN                       |           |     5 |   275 |     6 |  00:00:01 |

| 2   |   TABLE ACCESS BY INDEX ROWID     | DEPARTMENT|    27 |   432 |     2 |  00:00:01 |

| 3   |    INDEX FULL SCAN                | DEPT_ID_PK|    27 |       |     1 |  00:00:01 |

| 4   |   SORT JOIN                       |           |     5 |   195 |     4 |  00:00:01 |

| 5   |    VIEW                           |           |     5 |   195 |     3 |  00:00:01 |

| 6   |     WINDOW BUFFER                 |           |     5 |    80 |     3 |  00:00:01 |

| 7   |      HASH GROUP BY                |           |     5 |    80 |     3 |  00:00:01 |

| 8   |       TABLE ACCESS BY INDEX ROWID | EMPLOYEE  |     6 |    96 |     2 |  00:00:01 |

| 9   |        INDEX RANGE SCAN           | EMP_JOB_IX|     6 |       |     1 |  00:00:01 |

------------------------------------------------------+-----------------------------------+

Predicate Information:

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

4 - access("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")

4 - filter("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")

5 - filter("V"."SAL"="ITEM_0")

9 - access("JOB_ID"='ST_CLERK')

 

 

우리의 예상과는 달리 Employee 테이블에 대한 액세스가 한번 나왔다. 놀랍지 않은가? URSW라는 기능으로 인하여 중복 액세스를 제거해 버린 것이다. Logical Optimizer SQL 아래와 같이 재작성 것이다.

 

SELECT d.department_id, d.department_name, v.sal sal

  FROM department d,

       (  SELECT e.department_id, SUM (e.salary) sal,

                 MAX (SUM (e.salary)) OVER () item_0

            FROM employee e

           WHERE e.job_id = 'ST_CLERK'

        GROUP BY e.department_id ) v

 WHERE d.department_id = v.department_id

   AND v.sal = v.item_0 ;

 

옵티마이져가 재작성한 SQL을 보면 employee 테이블을 단 한번 사용하고 있으므로 Plan 상에도 엑세스가 한번 나온 것이다. 이 기능은 Oracle 11gR2에서 추가되었다.  

 

위의 예제는 Uncorrelated Subquery(비상관 서브쿼리)를 사용하는 예제이다. 비상관 서브쿼리라 함은 서브쿼리 내에 메인 쿼리와의 조인절이 없다는 뜻이다. 그런데 옵티마이져는 상관 서브쿼리에서도 같은 방식을 사용한다. 아래의 SQL을 보자.

 

SELECT a.employee_id, a.first_name, a.last_name, b.department_name

  FROM employee a, department b

 WHERE a.department_id = b.department_id

   AND a.employee_id = (SELECT MAX (s.employee_id)

                          FROM employee s

                         WHERE s.department_id = b.department_id);

 

부서별로 MAX 사원번호에 해당하는 정보를 구하는 SQL. SQL Plan 아래와 같다.

----------------------------------------------------+-----------------------------------+

| Id  | Operation                       | Name      | Rows  | Bytes | Cost  | Time      |

----------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT                |           |       |       |     6 |           |

| 1   |  VIEW                           | VW_WIF_1  |   106 |  7208 |     6 |  00:00:01 |

| 2   |   WINDOW BUFFER                 |           |   106 |  6466 |     6 |  00:00:01 |

| 3   |    MERGE JOIN                   |           |   106 |  6466 |     6 |  00:00:01 |

| 4   |     TABLE ACCESS BY INDEX ROWID | DEPARTMENT|    27 |   540 |     2 |  00:00:01 |

| 5   |      INDEX FULL SCAN            | DEPT_ID_PK|    27 |       |     1 |  00:00:01 |

| 6   |     SORT JOIN                   |           |   107 |  4387 |     4 |  00:00:01 |

| 7   |      TABLE ACCESS FULL          | EMPLOYEE  |   107 |  4387 |     3 |  00:00:01 |

----------------------------------------------------+-----------------------------------+

Predicate Information:

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

1 - filter("VW_COL_5" IS NOT NULL)

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

6 - filter("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")

 

Plan 보면 employee 테이블을 단 한번만 엑세스 한다. 이것 역시 사람이 작성한 SQL을 옵티마이져가 성능에 문제가 된다고 판단하여 아래처럼 변경시킨 것이다.
 

SELECT VW_WIF_1.ITEM_1 EMPLOYEE_ID, VW_WIF_1.ITEM_2 FIRST_NAME,
       VW_WIF_1.ITEM_3 LAST_NAME, VW_WIF_1.ITEM_4 DEPARTMENT_NAME
  FROM (SELECT A.EMPLOYEE_ID ITEM_1, A.FIRST_NAME ITEM_2,
               A.LAST_NAME ITEM_3, B.DEPARTMENT_NAME ITEM_4,
               CASE A.EMPLOYEE_ID
                    WHEN MAX (A.EMPLOYEE_ID) OVER (PARTITION BY A.DEPARTMENT_ID)
                    THEN A.ROWID
               END VW_COL_5
          FROM TRANSFORMER.DEPARTMENT B, TRANSFORMER.EMPLOYEE A
         WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) VW_WIF_1
 WHERE VW_WIF_1.VW_COL_5 IS NOT NULL

 


부서별로 MAX(EMPLOYEE_ID)의 값과 EMPLOYEE_ID를 비교하여 같으면 ROWID를 출력하고 있다. 따라서 ROWID 값이 NULL이 아니라면 EMPLOYEE_ID는 부서별로 MAX(EMPLOYEE_ID)와 같음을 보장한다. 그러므로 중복 엑세스가 제거될 수 있는 것이다. 이 사실은 VW_COL_5 IS NOT NULL 조건이 추가된 이유이기도 하다. 이 기능은 Oracle10g R2 에서 추가되었다.

 

SQL을 재작성하는 튜너는 옵티마이져에 포함되어 있다. 내가 작성한 SQL PLAN이 어떻게 변경되었는지 관심을 가져야 한다. 더 나아가서 훈수를 두려면 옵티마이져에 포함되어 있는 튜너보다 더 나아야 할 것이다. “지식의 대융합”(이인식 저)이라는 책을 보면 2030년을 기점으로 하여 인간이 기계보다 더 나은 점을 발견하기 힘들 것이라 한다. 이 책의 내용은 전문가들이 작성한 논문과 책을 종합한 것이므로 함부로 무시 할 수 없다.

 

사람이 기계보다 우월하려면 기계(옵티마이져)의 기능과 한계를 분석하고 이해해야 한다. 영화 <메트릭스>에서 인간과 기계 사이에 평화가 찾아온 이유는 기계의 한계(약점)를 이해하고 그것을 고쳐주었기 때문이 아닌가?

 

참조서적: The Logical Optimizer 2.18 , 2.19


 

Posted by extremedb
,
Posted by extremedb
,

오라클 11.2 버전은 아래의 링크에서 다운받을 수 있다.
http://www.oracle.com/technology/software/products/database/index.html


실습 스크립트 다운로드
실습을 진행하기 위한 스크립트는 아래와 같다.  

1. Schema Generation Script : Oracle 11gR1 과 11gR2중 버젼을 선택해서 다운 받으면 된다.
    다운받은후 User를 생성하고 권한부여 후 Import를 하면 실습 준비가 완료된다. 실습을 진행하려면
    TLO 계정으로 접속해야 한다. TLO 계정의 비밀번호는 transformer이다.
2. Part 1 Script : SQL 파일
3. Part 2 Script : SQL 파일과 10053 Trace 파일 포함
4. Part 3 Script : SQL 파일과 10053 Trace 파일 포함
5. Part 4 Script : SQL 파일과 10053 Trace 파일 포함
6. Appendix Script : 부록의 예제 스크립트임. SQL 파일

모두 다운 받으면 아래와 같이 총 15 개의 압축 파일이 된다.

사용자 삽입 이미지

용량이 크므로 7z 를 이용하여 압축 하였지만 일반적인 압축 프로그램으로 압축을 풀수 있다. 압축을 해제하면 위와 같은 폴더의 모습이 된다.
각 폴더의 용량을 합쳐 586 MB가 나오면 정상이다.
아래의 압축 파일을 모두 Download 하기 바란다.
데이터 import 시 에러가 나는 부분은 무시해도 된다. 정상적으로 처리된 것이다.
 


invalid-file

Schema 생성 Script for Oracle 11.2.0.1

invalid-file

Schema 생성 Script for Oracle 11.1.0.6

invalid-file

Scripts for Part1

invalid-file

Scripts for Part2

invalid-file

Scripts for Part3

invalid-file

Scripts for Part4

invalid-file

Scripts for Appendix

invalid-file

서브쿼리의 From 절에 테이블이 2개 이상일때 CBQT가 발생하는 예제




PS
한가지 걱정은 블로그 구독자 정도의 수준이라면 이책을 읽을 수 있으나 초보가 띠지의 내용등에 혹 해서 사면 어쩌나 하는 것이다.  주위에 그런사람들이 있다면 말려주기 바란다. 이 책은 초보용이 아니다.



구독자분이 스키마를 exp 형태 대신에 script 형태로 제공해 달라는 요청을 받았다.
아래의 스크립트를 이용하면 된다. 단 Oracle Sample 스키마인 SH와 HR 이 존재해야 한다.
 



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

The Logical Optimizer Part 1 - PPT  (17) 2010.07.26
The Logical Optimizer-서점  (0) 2010.04.27
The Logical Optimizer-오타와 오류등록  (27) 2010.04.20
저자와의 대화  (36) 2010.04.20
The Logical Optimizer  (62) 2010.04.05
Posted by extremedb
,

저자와 이야기 나누실 독자는 이 페이지의 댓글을 이용하세요.




유수익님이 질문하신글(http://scidb.tistory.com/112#comment4410920)의 답변입니다.
아래의 첨부파일을 참조하세요.









'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
The Logical Optimizer  (62) 2010.04.05
Posted by extremedb
,

원래 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
,

정답의 결과가 틀리다. WHERE 절에 s.sales_cust.prod_cnt > 0 조건이 추가되어야 한다.
자세한 내용은 oraking 님의 댓글을 참조하기 바란다.(2010.09.03)


좋지 않은 상황

한 고객 사에서 전화가 걸려왔는데 성능 문제였다.

특정 SQL이 성능이 좋지 않은데 더욱 상황을 악화시키는 것은 Peak Time에 수십 만 번 실행된다는 것이다. 그런데 전체 건을 조회하는 것이 아니라 고객번호 순으로 10명의 고객에 대한 통계와 실적을 보는 SQL이라고 한다. SQL은 아래와 같으며 환경은 Oracle10g R2 이다. 오라클 Install시 생성되는 SH 스키마를 이용하면 된다.

 

--인덱스 생성

CREATE INDEX ix_cust_birth ON customers (cust_year_of_birth, cust_id);

 

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(c IX_CUST_BIRTH_CUST) LEADING(c) */

       c.cust_id, c.cust_first_name, c.cust_last_name,

       s.prod_cnt, s.channel_cnt, s.tot_amt

  FROM customers c,

       (SELECT s.cust_id,

               COUNT (DISTINCT s.prod_id) AS prod_cnt,

               COUNT (DISTINCT s.channel_id) AS channel_cnt,

               SUM (s.amount_sold) AS tot_amt

          FROM sales s

         GROUP BY s.cust_id) s

 WHERE c.cust_year_of_birth = 1987

   AND s.cust_id = c.cust_id

   AND ROWNUM <= 10;

 

-------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                |               |     10 |    5035 |          |
|*  2 |   HASH JOIN                   |               |     10 |    5035 | 1150K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS     |    151 |     153 |          |
|*  4 |     INDEX RANGE SCAN          | IX_CUST_BIRTH |    151 |       2 |          |
|   5 |    VIEW                       |               |    523 |    4882 |          |
|   6 |     SORT GROUP BY             |               |    523 |    4882 | 8288K (0)|
|   7 |      PARTITION RANGE ALL      |               |    918K|    4882 |          |
|   8 |       TABLE ACCESS FULL       | SALES         |    918K|    4882 |          |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   2 - access("S"."CUST_ID"="C"."CUST_ID")
   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)


CUSTOMERS 테이블은 10건을 정확히 Scan 하였지만 Salse 테이블을 Full Scan하여 비효율이 발생 하였다. 또한 위의 SQL은 Hash Join을 사용 하였으므로 정렬을 보장 하지 않는다.

좋지 않은 상황 + 악조건

먼저 위의 SQL을 보면 Rownum을 사용하므로 CVM(Complex View Merging)이 발생되지 않는다. 만약 발생된다고 해도 Group By가 전체범위로 처리되므로 TOP SQL에서는 대부분 JPPD(Join Predicate Push Down)의 성능이 우월하다. 또한 Nested Loop Join이 아닌 Hash Join이 수행 되었으므로 고객번호가 작은 것부터 나오지 않는다. Sort 문제도 해결해야 하지만 Oracle10g이므로 JPPD Extension 기능이 수행되지 않는다. JPPD Extension이란 Group By + 집계함수나 Distinct가 존재해도 JPPD가 수행되며 Semi/Anti Join시에도 JPPD가 수행되는 획기적인 기능이다. 이 기능은 Oracle11g부터 사용할 수 있다.

 

정답부터 보지말자 실력이 늘지 않는다
지금부터 여러분이 이 문제를 해결해야 한다. 여러분이 해결사 이다. 퀴즈라고 생각하고 문제를 풀어보라. 하지만 절대 답을 먼저 보아서는 안 된다. 충분히 고민한 후에 답을 풀어보고 정답을 보도록 하자.

힌트가 있다

JPPD
기능을 사용할 수 없다면 JPPD의 흉내를 내면 된다. JPPD의 효과를 만들면 된다.
아래쪽의 실행계획과 결론 부분을 보는것도 힌트가 될 수 있다.

제약사항
Sort 가 되어야 하며 Rownum 조건으로 Customers 테이블에 10건만 Scan되어야 한다. 그 10건에 해당하는 고객만 Sales 테이블에 Access 하는 것이 정답이다.
스칼라 서브쿼리를 세 번 사용하는 것은 정답이 아니다. 그것은 막노동에 가깝다. 필자의 블로그를 꾸준히 구독한 독자라면 어렵지 않게 문제를 풀 수 있다.
 

정답: 아래를 드래그 하면 된다.

 

CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT

(prod_cnt NUMBER(5),

 channel_cnt NUMBER(2),     

 tot_amt NUMBER(15,2));

/

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       s.cust_id, s.cust_first_name, s.cust_last_name,

       s.sales_cust.prod_cnt,  -- Alias 가 여기에 사용 되었다.

       s.sales_cust.channel_cnt,

       s.sales_cust.tot_amt

  FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) */

               c.cust_id, c.cust_first_name, c.cust_last_name,

               (SELECT sales_cust_type -- 타입의 이름을 그대로 사용해야 한다

                          (COUNT (DISTINCT s.prod_id),

                           COUNT (DISTINCT s.channel_id),

                           SUM (s.amount_sold)

                          )

                  FROM sales s

                 WHERE s.cust_id = c.cust_id

) AS sales_cust -- 추후 메인쿼리에서 Alias 가 사용된다.

          FROM customers c

         WHERE c.cust_year_of_birth= 1987

           AND ROWNUM <= 10) s  ;

    

위의 SQL은 JPPD를 수행시킨 효과와 비슷하다. 다른말로 바꾸면 수동으로 Lateral View를 생성한 것이다.
          

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

 

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

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

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

|   1 |  VIEW                         |                    |     10 |    1104 |

|*  2 |   COUNT STOPKEY               |                    |     10 |      13 |

|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS          |     10 |      13 |

|*  4 |     INDEX RANGE SCAN          | IX_CUST_BIRTH_CUST |     10 |       3 |

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

 

Predicate Information (identified by operation id):

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

   2 - filter(ROWNUM<=10)

   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)


읽은 블럭수가 무려 5배 정도 차이가 나며 PGA는 사용 하지도 았았다. 자주 수행될수록 성능의 차이는 급격히 벌어질 것이다.
 

결론:

JPPD 기능을 사용할 수 없으므로 JPPD의 흉내를 낸 것이다. JPPD등이 수행되지 않을 때 Logical Optimizer의 한계를 극복할 수 있는 방법은 여러분의 응용력에 달려있다. 옵티마이져가 한계를 드러낼 때 더 좋은 방법을 적용하는 것이 사람의 할 일이 되었다. 물론 그러기 위해서는 Logical Optimizer의 허와 실을 알아야 할 것이다.

 

PS:

만약 Oracle9i Oracle 8.1.7 버전을 사용하고 있다면 위의 정답이 실행되지 않으므로 http://scidb.tistory.com/69 글을 참조하여 사용하기 바란다.


Posted by extremedb
,

지난시간의 DEUI라는 기능에 이어서 이번시간에는 그 사촌격인 DE에 대해서 논의해보자.

DE (Distinct Elimination)란 무엇인가
DE는 Unique한 집합일 경우 불필요한 Distinct를 제거하는 기능이다. 이렇게 함으로써 Sort와 중복제거 등의 부하가 많은 작업을 수행하지 않을 수 있다. 이 기능은 Oracle 11g에서 추가되었다. 이제 DE가 어떻게 수행되는지 알아보자.

SELECT distinct d.department_id, l.location_id

  FROM department d, location l

 WHERE d.location_id = l.location_id ;

 

----------------------------------------+-----------------------------------+

| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |

----------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT    |           |       |       |     3 |           |

| 1   |  NESTED LOOPS       |           |    27 |   270 |     3 |  00:00:01 |

| 2   |   TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     3 |  00:00:01 |

| 3   |   INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |

----------------------------------------+-----------------------------------+

Predicate Information:

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

3 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

Unique를 보장하는 컬럼이 있으면 Distinct가 필요없다
실행계획에서 Distinct에 해당하는 Operation인 Sort Unique 혹은 Hash Unique가 사라졌다. 이유는 Transformer가 위의 SQL을 분석해서 Distinct가 없어도 Unique 함을 알았기 때문이다. Select 절에 있는 d.department_id와 l.location_id는 From 절에 있는 두 테이블의 PK 이다.  따라서 Distinct는 당연히 필요 없음으로 Logical Optimizer가 삭제한 것이다.

아래는 DE와 관련된 10053 Event의 Trace 내용이다.
 

OBYE:   Considering Order-by Elimination from view SEL$1 (#0)
***************************

Order-by elimination (OBYE)

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

OBYE:     OBYE bypassed: no order by to eliminate.

Eliminated SELECT DISTINCT from query block SEL$1 (#0)

이후생략


10053 Trace 상에서는 DE가 OBYE 자리에서 발생하였다. 이것은 OBYE 수행여부를 체크할 때 DE의 수행여부를 같이 체크하므로 DE가 OBYE 자리에서 발생되는 것 같다.

함정에 주의할 것
Unique 하다고 해서 항상 DE가 발생될까? 그렇지 않다. 아래의 SQL을 보자.

SELECT distinct d.department_id, d.location_id

  FROM department d, location l

 WHERE d.location_id = l.location_id ;


위의 SQL은 location_id를 department 테이블의 컬럼으로 대체하였다는 점을 제외하면 최초의 SQL과 완전히 같다.
-----------------------------------------+-----------------------------------+

| Id  | Operation            | Name      | Rows  | Bytes | Cost  | Time      |

-----------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT     |           |       |       |     4 |           |

| 1   |  HASH UNIQUE         |           |    27 |   270 |     4 |  00:00:01 |

| 2   |   NESTED LOOPS       |           |    27 |   270 |     3 |  00:00:01 |

| 3   |    TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     3 |  00:00:01 |

| 4   |    INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |

-----------------------------------------+-----------------------------------+

Predicate Information:

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

4 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

 

Unique를 보장하는 모든 테이블의 컬럼이 Select 절에 사용되어야
컬럼 하나만이 바뀌었을 뿐인데 실행계획에 Hash Unique가 생긴 것이다. 여기서 알 수 있는 것은 From 절에 나열된 모든 테이블의 PK 컬럼 혹은 Unique 컬럼이 Select 절에 나와야 Distinct가 제거된다는 사실이다. 이것은 아직 DE 기능이 완성되지 않은 것을 의미한다. 논리적으로는 Select 절에 d.location_id를 사용하거나 l.location_id를 사용해도 같기 때문에 DE가 발생 해야 하지만 아직 이런 기능이 없다는 것이 아쉽다
.

DE 기능을 Control 하는 파라미터는 _optimizer_distinct_elimination이며 Default로 True 이다. 하지만 이 파라미터로는 DEUI 기능을 Control 할수 없다. 한가지 주의사항은 DE가 버전 10gR2(10.2.0.4)에서도 수행된다는 점이다. 다만 _optimizer_distinct_elimination 파라미터가 없다는 것이 11g와 다른 점이다.

결론
만약 이런 일이 대용량 테이블에서 발생한다면 결과는 심각한 성능저하로 나타날 수 있으므로 조인된 컬럼을 사용할 것인지 아니면 참조되는 컬럼을 사용할 것인지 아주 신중히 결정해야 한다.

성능저하가 예상되는 부분
예를들면 서브쿼리가 Unnesting되어 Distinct가 자동으로 추가된 인라인 뷰에 CVM이 발생하면 인라인뷰가 해체되므로 전체집합에 대해서 Sort Unique 혹은 Hash Unique가 발생된다. 전체집합이 대용량이라면 성능은 심각하게 저하될 것이다. 이 사실은 어떤 컬럼을 Select 절에서 사용할 것인지 아주 신중히 결정해야 하며 Merge 힌트를 얼마나 조심스럽게 사용해야 하는지를 잘 나타내 주고 있다.

PS
이렇게 나가다간 아마 책을 출판할 필요가 없을듯 하다.^^ 하지만 책보다는 블로그가 우선이다.

Posted by extremedb
,

Query Transformation 모르면 튜닝을 없다

위의
말을 보고 많은 독자들이 말도 된다고 생각할 것이다. Logical Optimizer 결과물인 Query Transformation 알지 못했지만 지금껏 튜닝을 성공적으로 했다고 생각하는 사람이 많이 있기 때문이다. 하지만 과연 그럴까? 아래의 SQL 보고 Query Transformation Logical Optimizer 얼마나 중요한지 알아보자
.

여기서 한단계 더 나아가서 뷰(인라인뷰가 아니다) 내부의 테이블에 대하여 조인순서 및 Access Path 를 바꿀 수 있는 방법에 대해 논의 해보자.

준비

테스트를 위하여 인덱스 3개와 하나를 만들자.

CREATE INDEX loc_postal_idx ON location (postal_code);

CREATE INDEX dept_name_idx ON department (department_name);

CREATE INDEX coun_region_idx ON country (region_id);

 

CREATE OR REPLACE VIEW v_dept AS

SELECT d.department_id, d.department_name, d.manager_id, l.location_id,

       l.postal_code, l.city, c.country_id, c.country_name, c.region_id

  FROM department d, location l, country c

 WHERE d.location_id = l.location_id

   AND l.country_id = c.country_id;


실행시켜보자

이제 모든 준비가 끝났다. 아래는 매우 짧고 쉬운 SQL 이다. SQL 실행시키고 연이어DBMS_XPLAN.display_cursor 실행한 후의 결과 중에서 필요한 부분만 발췌 하였다.

 SELECT /*+ gather_plan_statistics  */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

  FROM employee e, v_dept v

 WHERE e.department_id = v.department_id

   AND v.department_name = 'Shipping'

   AND v.postal_code = '99236'

   AND v.region_id = 2

   AND e.job_id = 'ST_CLERK';

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

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

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

|   1 |  NESTED LOOPS                   |                   |     20 |00:00:00.01 |      11 |

|   2 |   NESTED LOOPS                  |                   |     45 |00:00:00.01 |       8 |

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

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

|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      1 |00:00:00.01 |       3 |

|*  6 |       INDEX RANGE SCAN          | DEPT_NAME_IDX     |      1 |00:00:00.01 |       2 |

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

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

|*  9 |     INDEX UNIQUE SCAN           | COUNTRY_C_ID_PK   |      1 |00:00:00.01 |       1 |

|* 10 |    INDEX RANGE SCAN             | EMP_DEPARTMENT_IX |     45 |00:00:00.01 |       2 |

|* 11 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE          |     20 |00:00:00.01 |       3 |

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

 

Predicate Information (identified by operation id):

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

   6 - access("D"."DEPARTMENT_NAME"='Shipping')

   7 - filter("L"."POSTAL_CODE"='99236')

   8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

   9 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")

       filter("C"."REGION_ID"=2)

  10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

  11 - filter("E"."JOB_ID"='ST_CLERK')

 

조인순서를 바꿀 있겠는가

위의 실행계획에서 조인순서는 V_dept --> Employee 이다. 만약 상태에서 여러분이 조인의 순서를 Employee --> V_dept 바꿀 있겠는가? 아마 아래처럼 힌트를 사용할 것이다.

 

SELECT /*+ gather_plan_statistics LEADING(E V) */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

..이후생략

 

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

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

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

|   1 |  NESTED LOOPS                   |                   |     45 |00:00:00.01 |      11 |

|   2 |   NESTED LOOPS                  |                   |     45 |00:00:00.01 |       8 |

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

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

|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      1 |00:00:00.01 |       3 |

|*  6 |       INDEX RANGE SCAN          | DEPT_NAME_IDX     |      1 |00:00:00.01 |       2 |

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

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

|*  9 |     INDEX UNIQUE SCAN           | COUNTRY_C_ID_PK   |      1 |00:00:00.01 |       1 |

|* 10 |    INDEX RANGE SCAN             | EMP_DEPARTMENT_IX |     45 |00:00:00.01 |       2 |

|  11 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE          |     45 |00:00:00.01 |       3 |

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

 

힌트가 무시 되었다 원인은?

조인의 순서가 전혀 변하지 않았다. 이상하지 않은가? 간단하게 생각되는 SQL 조인순서도 변경할 없다. 이유는 Query Transformation 때문이다. Outline 정보를 보면 실마리를 찾을 있다.

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      중간생략

      MERGE(@"SEL$2")

      중간생략

      END_OUTLINE_DATA

  */

 

원인은 Logical Optimizer 의한 Query Transformation 이다

V_dept View Merging 발생한 것이다. View Merging Query Transformation 종류이며 뷰를 해체하여 정상적인 조인으로 바꾸는 작업이다. Query Transformation 발생하면 많은 경우에 쿼리블럭명이 바뀌어 버린다. 따라서 바뀐 쿼리블럭명을 지정하여 힌트를 사용하거나 Query Transformation 발생하지 않게 하면 힌트가 제대로 적용된다. 

SELECT /*+ gather_plan_statistics NO_MERGE(V) LEADING(E V) */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

  FROM employee e, v_dept v

 WHERE e.department_id = v.department_id

   AND v.department_name = 'Shipping'

   AND v.postal_code = '99236'

   AND v.region_id = 2

   AND e.job_id = 'ST_CLERK';

 

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

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

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

|*  1 |  HASH JOIN                      |                 |     20 |00:00:00.02 |       8 |

|   2 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE        |     20 |00:00:00.02 |       2 |

|*  3 |    INDEX RANGE SCAN             | EMP_JOB_IX      |     20 |00:00:00.02 |       1 |

|   4 |   VIEW                          | V_DEPT          |      1 |00:00:00.01 |       6 |

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

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

|   7 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT      |      1 |00:00:00.01 |       3 |

|*  8 |       INDEX RANGE SCAN          | DEPT_NAME_IDX   |      1 |00:00:00.01 |       2 |

|*  9 |      TABLE ACCESS BY INDEX ROWID| LOCATION        |      1 |00:00:00.01 |       2 |

|* 10 |       INDEX UNIQUE SCAN         | LOC_ID_PK       |      1 |00:00:00.01 |       1 |

|* 11 |     INDEX UNIQUE SCAN           | COUNTRY_C_ID_PK |      1 |00:00:00.01 |       1 |

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

 

Query Transformation 발생하지 않으면 조인순서 변경이 가능해

No_merge 힌트를 사용하여 Query Transformation 발생하지 않게 하였더니 조인 순서가 Employee --> V_dept 바뀌었다. 이제 알겠는가? Query Transformation Logical Optimizer 모른다면 힌트도 먹통이 된다. 이래서는 제대로 튜닝을 없다.

내부의 테이블에 대한 조인순서의 변경은 가능한가
단계 나아가 보자. No_merge 힌트를 사용한 상태에서 내부의 테이블들에 대해서 조인순서를 바꾸고 싶다. 조인순서를 Employee --> (Country --> Location --> Department) 바꾸어야 한다. 이때 여러분은 어떻게 것인가? 아래처럼 Global Hint 사용하면 된다.

SELECT /*+ gather_plan_statistics NO_MERGE(V) LEADING(E V) LEADING(V.C V.L V.D) */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

  FROM employee e, v_dept v

 WHERE e.department_id = v.department_id

   AND v.department_name = 'Shipping'

   AND v.postal_code = '99236'

   AND v.region_id = 2

   AND e.job_id = 'ST_CLERK';

 

Leading 힌트를 사용하였다. 이유는 위의 SQL 쿼리블럭 2개로 구성되어 있기 때문이다. 전체 SQL 대한 Leading 힌트가 필요하며 V_dept 대한 Leading 힌트가 각각 필요하다. V_dept 대한 Leading 힌트를 사용할 Dot 표기법을 사용해야 한다. 내부에 존재하는 테이블들의 Alias 사용해야 한다. 방법은 Global Hint 사용 방법 중에 Dot 표기법을 사용한 것이다.

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

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

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

|*  1 |  HASH JOIN                       |                 |     20 |00:00:00.01 |       9 |

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

|*  3 |    INDEX RANGE SCAN              | EMP_JOB_IX      |     20 |00:00:00.01 |       1 |

|   4 |   VIEW                           | V_DEPT          |      1 |00:00:00.01 |       7 |

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

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

|*  7 |      HASH JOIN                   |                 |      1 |00:00:00.01 |       4 |

|*  8 |       INDEX RANGE SCAN           | COUN_REGION_IDX |      5 |00:00:00.01 |       1 |

|   9 |       TABLE ACCESS BY INDEX ROWID| LOCATION        |      1 |00:00:00.01 |       3 |

|* 10 |        INDEX RANGE SCAN          | LOC_POSTAL_IDX  |      1 |00:00:00.01 |       2 |

|* 11 |      INDEX RANGE SCAN            | DEPT_NAME_IDX   |      1 |00:00:00.01 |       2 |

|* 12 |     TABLE ACCESS BY INDEX ROWID  | DEPARTMENT      |      1 |00:00:00.01 |       1 |

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

 
Global Hint 매우 유용해

성공적으로 조인순서가 Employee --> (Country --> Location --> Department) 바뀌었다. Global Hint 사용하자 힌트가 제대로 적용된다. 방법은 특히 뷰나 인라인뷰를 Control 유용하므로 반드시 익혀두기 바란다.

Query Transformation 발생했을 경우는 힌트를 어떻게 적용할 있나

이제 원래 목적인 Query Transformation 발생했을 경우에 조인순서를 바꾸는 방법에 대해 논의 해보자. 위에서 배운 Global Hint 여기에 적용할 것이다.

 SELECT /*+ gather_plan_statistics LEADING(E V.C V.L V.D) */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

  FROM employee e, v_dept v

 WHERE e.department_id = v.department_id

   AND v.department_name = 'Shipping'

   AND v.postal_code = '99236'

   AND v.region_id = 2

   AND e.job_id = 'ST_CLERK';

 

위의 SQL No_merge 힌트가 사라졌으므로 Query Transformation 발생된다. 그래서 위에서 배운 대로 Dot 표기법을 활용하여 Leading 힌트를 사용 하였다. 힌트가 적용될까?

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

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

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

|   1 |  NESTED LOOPS                   |                   |     20 |00:00:00.01 |      11 |

|   2 |   NESTED LOOPS                  |                   |     45 |00:00:00.01 |       8 |

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

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

|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      1 |00:00:00.01 |       3 |

|*  6 |       INDEX RANGE SCAN          | DEPT_NAME_IDX     |      1 |00:00:00.01 |       2 |

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

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

|*  9 |     INDEX UNIQUE SCAN           | COUNTRY_C_ID_PK   |      1 |00:00:00.01 |       1 |

|* 10 |    INDEX RANGE SCAN             | EMP_DEPARTMENT_IX |     45 |00:00:00.01 |       2 |

|* 11 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE          |     20 |00:00:00.01 |       3 |

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

 
실패사례: 힌트가 무시 되었다

힌트가 전혀 먹혀 들지 않는다. 이유는 View Merging 발생하여 새로운 쿼리블럭이 생성되었기 때문이다. 아래의 Query Block Name 정보를 보면 쿼리블럭명과 테이블의 Alias 조회할 있다. / 기준으로 왼쪽이 쿼리블럭명이고 오른쪽이 테이블의 Alias 이다. 제일 왼쪽의 숫자는 실행계획상의 Id 일치한다.

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

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

   1 - SEL$F5BB74E1

   5 - SEL$F5BB74E1 / D@SEL$2

   6 - SEL$F5BB74E1 / D@SEL$2

   7 - SEL$F5BB74E1 / L@SEL$2

   8 - SEL$F5BB74E1 / L@SEL$2

   9 - SEL$F5BB74E1 / C@SEL$2

  10 - SEL$F5BB74E1 / E@SEL$1

  11 - SEL$F5BB74E1 / E@SEL$1

 

힌트에 쿼리블럭명과 Object Alias 사용해야 가능해

쿼리블럭명은 SEL$F5BB74E1 이며 Object Alias 들은 D@SEL$2, L@SEL$2, C@SEL$2, E@SEL$1 임을 있다. 따라서 정보들을 이용하여 아래처럼 힌트를 바꾸어 보자.


SELECT /*+ gather_plan_statistics LEADING(@SEL$F5BB74E1 E@SEL$1 C@SEL$2 L@SEL$2 D@SEL$2  ) */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

  FROM employee e, v_dept v

 WHERE e.department_id = v.department_id

   AND v.department_name = 'Shipping'

   AND v.postal_code = '99236'

   AND v.region_id = 2

   AND e.job_id = 'ST_CLERK';

 

위의 힌트처럼 쿼리블럭명을 처음에 지정하고 뒤에는 조인될 순서대로 Object Alias 배치하기만 하면 된다. 

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

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

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

|*  1 |  HASH JOIN                     |                 |     20 |00:00:00.01 |       8 |

|*  2 |   HASH JOIN                    |                 |     20 |00:00:00.01 |       5 |

|   3 |    MERGE JOIN CARTESIAN        |                 |    100 |00:00:00.01 |       3 |

|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE        |     20 |00:00:00.01 |       2 |

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

|   6 |     BUFFER SORT                |                 |    100 |00:00:00.01 |       1 |

|*  7 |      INDEX RANGE SCAN          | COUN_REGION_IDX |      5 |00:00:00.01 |       1 |

|   8 |    TABLE ACCESS BY INDEX ROWID | LOCATION        |      1 |00:00:00.01 |       2 |

|*  9 |     INDEX RANGE SCAN           | LOC_POSTAL_IDX  |      1 |00:00:00.01 |       1 |

|  10 |   TABLE ACCESS BY INDEX ROWID  | DEPARTMENT      |      1 |00:00:00.01 |       3 |

|* 11 |    INDEX RANGE SCAN            | DEPT_NAME_IDX   |      1 |00:00:00.01 |       2 |

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

쿼리블럭 표기법은 Leading Hint 뿐만 아니라 모든 힌트에 적용 가능해

조인 순서가 Employee --> Country --> Location --> Department 바뀌었다. 방법은 Global Hint 사용 방법 중에 쿼리블럭 표기법을 사용한 것이다. 방법은 특히 View Merging 같은 Query Transformation 발생하여 쿼리블럭이 새로 생성된 경우 매우 유용하다. 이 방법으로 모든 힌트를 사용할 수 있다. 아래의 Outline Data 는 이런 점을 잘 설명 해준다.

Outline Data

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

  /*+

      중간생략

      MERGE(@"SEL$2")

      중간생략

INDEX_RS_ASC(@"SEL$F5BB74E1" "E"@"SEL$1" ("EMPLOYEE"."JOB_ID"))

      INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("COUNTRY"."REGION_ID"))

      중간생략

      USE_HASH(@"SEL$F5BB74E1" "D"@"SEL$2")

      END_OUTLINE_DATA

  */

PS
위의 내용 또한 이번에 출간될 책의 일부분이다. 블로그에 책의 내용이 많이 올라가서 걱정이다.^^


Posted by extremedb
,

이번 내용은 난위도가 있으므로  'Interleaving은 선수조건이 필요하다' 단락을 읽고 이해가 가는 사람만 보기 바란다. 필자의 글을 꾸준히 구독한 독자라면 어렵지 않게 볼수 있을 것이다.

전략적 결혼

사극을 보면 권력가들이 자신의 세력를 확장하기 위해 자신의 딸을 왕에게 추천하여 결혼시키는 것을 심심치 않게 볼수 있다. 자신의 목표를 이루기 위한 전단계의 포석이라고 할수 있다. 딸이 왕자를 순산하고 그 왕자가 차세대 왕이 된다면 자신은 최고의 권력을 가지는것과 마찬가지 이다. CBQT가 수행될 때에도 이러한 전략적 결혼과 같은것이 존재한다는 사실을 알고 있는가? CBQT를 최적화 하기 위해서 Transformation 간의 관계는 필수적이다. 오라클에서는 Interleaving이라는 기법을 사용하고 있다
.

Interleaving
은 선수조건이 필요하다

하나의 Transformation(T2) 을 수행하기 위해서 다른 Transformation(T1)이 반드시 먼저 실행되어야 하는 경우가 있다. 이럴 때 Interleaving 기법을 이용하여 T1이 수행되면 마치 기다렸다는 듯이 T2가 실행된다. 대표적인 경우가 CSU(Complex Subquery Unnesting)가 실행되면 연이어 CVM(Complex View Merging)혹은 JPPD(Join Predicate Push Down)가 실행되는 경우이다.

서브쿼리가 Unnesting 되면 초기상태는 인라인뷰로 생성된다. 인라인뷰를 제거하고 정상적인 조인으로 바꾸는 기능이 CVM 이라는 것은 이미 알것이다. CVM을 시작하기 위해서는 CSU라는 전단계의 포석이 반드시 이루어 져야만 한다. 이 규칙은 JPPD에도 똑같이 적용된다
.

숫자표기법의 이해

Interleaving
에 대한 10053 Trace 내용을 분석하려면 숫자 표기법을 알아야 한다. 예를 들어 Complex Subquery가 하나 있는 SQL은 아래와 같이 0 1 Unnesting 상태를 나타낼수 있다.

CSU(1) : 서브쿼리가 Unnesting
CSU(0) :
서브쿼리가 Unnesting 되지 않음.

만약 서브쿼리가 2(SUBQ2, SUBQ1)라면 아래처럼 표현된다
.
CSU(1,1) :
모든 서브쿼리가 Unnesting

CSU(1,0) : SUBQ2
Unnesting
CSU(0,1) : SUBQ1
Unnesting
CSU(0,0) :
모든 서브쿼리가 Unnesting 되지 않음.

이러한 표기법은 CSU 뿐만 아니라 CVM, JPPD 등에도 똑같이 적용되며 10053 Trace에서 자주 나타나므로 반드시 알아두어야 한다
.

Interleaving
의 용도

Interleaving
은 주로 CSU 수행시의 비용계산 오류를 줄이는 용도로 사용한다. 예를 들어 CSU(0)이 최저 Cost로 선택되었다면 JPPD의 입장에서는 전혀 다른 결과를 가져올 수 있다. CSU(0)이 아닌 CSU(1) 상태에서 JPPD을 적용하는 것이 최저 Cost가 될 수 있다. 따라서 Interleaving CBQT간에 최저 Cost를 구하기 위해 대화를 하는 기능이라고 할 수 있다. Interleaving 기능이 존재함으로써 CSU + CVM(혹은 JPPD)을 모두 고려한 최적의 Cost를 구 수 있다. 이제 아래의 SQL을 보자.

SELECT /*+ QB_NAME(MAIN_VIEW) */ 

       e1.employee_id, e1.manager_id, e1.salary

  FROM employee e1

 WHERE e1.department_id = 10

     and (e1.manager_id, e1.salary) in (select /*+ QB_NAME(SUBQ) */

                                             e2.manager_id,  max(e2.salary) 

                                        from employee e2

                                       group by e2.manager_id )

     and rownum = 1

 

위의 SQL에 해당하는 10053 Trace의 내용을 미리 예상해보자. 먼저 서브쿼리에 Group By를 사용하였으므로 CSU가 발생할 것이다. 연이어 CSU의 과정 중에 Interleaving 이 발생하여 CVM JPPD Cost가 같이 고려될 것이다. 하지만 위의 SQL은 조건절에 Rownum을 사용하였으므로 CVM이 발생할 수 없다. 따라서 JPPD만 고려될 것이다. 이제 우리가 예상한 내용이 맞는지 확인 해보자. 아래의 Trace 내용은 필요한 부분만 발췌하여 요약한 것이다.

 

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

Cost-Based Subquery Unnesting
*****************************
SU: Using search type: exhaustive

SU: Starting iteration 1, state space = (2) : (1)

  Cost: 6.0035  Degree: 1  Card: 1.0000  Bytes: 41

 

SU: Considering interleaved complex view merging

CVM:     CVM bypassed: Outer QBc referencing ROWNUM.

  Cost: 5.0035  Degree: 1  Card: 1.0000  Bytes: 41

SU: Interleaved cost better than best so far.
SU: Finished interleaved complex view merging

SU: Considering interleaved join pred push down

JPPD: Using search type: linear

JPPD: Considering join predicate push-down

JPPD: Starting iteration 1, state space = (2) : (0)

  Cost: 6.0035  Degree: 1  Card: 1.0000  Bytes: 41

 

JPPD: Starting iteration 2, state space = (2) : (1)

  Cost: 4.0010  Degree: 1  Card: 1.0000  Bytes: 30

 

JPPD: Selected interleaved query.

SU: Finished interleaved join pred push down

SU: Updated best state, Cost = 4.00

 

SU: Starting iteration 2, state space = (2) : (0)

  Cost: 6.0037  Degree: 1  Card: 1.0000  Bytes: 15

 

SU: Not update best state, Cost = 6.00

SU: Will unnest subquery SUBQ (#2)

SU: Reconstructing original query from best state.


우리의 예상대로 먼저 CSU가 발생되었다. 연이어 Interleaving 기능에 의해 CVM이 고려되고 있지만 Rownum 제약사항 때문에 CVM이 발생되지 못한다.(CVM bypassed 부분 참조) 하지만 불완전 Costing(CVM을 제외한 최적화)은 계속 진행된다
.

CVM과 JPPD 는 동시에 고려된다
CVM
과정이 끝나면 JPPDCosting 과정이 진행된다. Interleaving CVM뿐만 아니라 JPPD도 동시에 고려하고 있음을 알 수 있다. JPPD를 적용한 Iteration(JPPD: Starting iteration 2, state space = (2) : (1) 부분 참조) Cost 4.0010으로 최적임을 알 수 있다. 이로서 모든 Interleaving이 완료되었다.( SU: Finished interleaved join pred push down 부분 참조) 마지막으로 CSU를 수행하지 않는 Iteration Cost를 구하고 모든 CSU과정을 마치게 된다.

CSU의 최저 Cost는 CSU + CVM + JPPD를 모두 고려한것
한가지 주의해야 할 사항은 4.0010 JPPD Cost가 되는 것이 아니라 CSU Cost 이다. SU: Updated best state, Cost = 4.00 부분을 보면 이러한 사실을 알 수 있다. CSU Cost는 단순히 CSU 자체의 Cost만 고려하는 것이 아니라 CSU + CVM + JPPD를 모두 고려한 후 최적의 Cost를 갖는 경우를 선택하는 것을 알 수 있다. Interleaving 기능으로 인하여 더욱 정확한 Cost를 구하는 것이 가능한 것이다
.

PS
1)위의 내용은 내년초 출간될 책의 일부분을 발췌한 것이다.
2)아이러니 하게도 맨처음에 설명한 전략적 결혼과 같은 비근대적인 방법을 21세기의 우리사회에서도 가끔 볼수 있다.


Posted by extremedb
,

질문을 받다
독자로 부터 다음과 같은 질문을 받았다. "MERGE 문에 IN 조건을 사용한다면 아래의 Plan처럼 심각한 성능저하가 발생 하였다. 왜그런가?"


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

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

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

|   1 |  MERGE                 | T2   |      1 |        |      2 |00:25:50.73 |      50M|    821 |

|   2 |   VIEW                 |      |      1 |        |    100K|00:25:52.38 |      50M|    795 |

|   3 |    NESTED LOOPS OUTER  |      |      1 |    100K|    100K|00:25:52.28 |      50M|    795 |

|*  4 |     TABLE ACCESS FULL  | T1   |      1 |    100K|    100K|00:00:00.60 |     316 |    629 |

|   5 |     VIEW               |      |    100K|      1 |    100K|00:25:34.26 |      50M|    166 |

|*  6 |      FILTER            |      |    100K|        |    100K|00:25:33.92 |      50M|    166 |

|*  7 |       TABLE ACCESS FULL| T2   |    100K|      1 |    100K|00:25:33.68 |      50M|    166 |

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


T1
의 조건을 만족하는 건수만큼 T2 FULL SCAN을 반복하고 있다. 이것은 재앙이나 다름없다. 어떤 원리로 성능저하가 발생하는지 MERGE문을 실행할 때 내부적으로 발생하는 일들의 특징과 순서를 알아보자.

무조건 아우터 조인이 발생해
MERGE
문을 실행하면 Target 쪽 테이블에는 무조건 아우터 조인으로 바뀐다. 왜냐하면 Match 되지 않는 경우(조인에 실패한 경우)에도 INSERT 해야 하기 때문이다. 그리고 아우터 조인은 다시 LATERAL VIEW로 바뀐다. 왜냐하면 View Merging이 실패할 경우 FPD(Filter Push Down)이나 JPPD(JOIN PREDICATE PUSH DOWN)이 적용 되어야 하기 때문이다. LATERAL VIEW의 개념은 아래의 POST를 참조하라
.

http://scidb.tistory.com/entry/Outer-Join-의-재조명


쿼리변환 순서가 중요하다
아래는 MERGE문 실행시 쿼리변환이 발생하는 순서 이다
.

1.
먼저 Transformer(Logical Optimizer) IN 조건을 OR 로 바꾼다
.

2.TRANSFORMER(Logical Optimizer)
는 아우터 조인되는 쪽을 LATERAL VIEW로 바꾼다
.

3.LATERAL VIEW
가 해체(View Merging이라 불림) 되어 평범한 아우터 조인으로 바뀐다
. 이때 View Merging에 실패하면 심각한 성능저하가 발생할 수 있다. 위의 Plan을 보면 T2 쪽의 View 가 해체되지 못했다. 이것이 실마리가 될 것이다.

아래의 스크립트를 실행하여 실제로 이런 일들이 발생하는지 Test 환경을 만들어 보자
.

create table t1(c1 varchar2(10), c2 int, c3 int, c4 int);

create table t2(c1 varchar2(10), c2 int, c3 int, c4 int);

 

insert into t1

select decode(mod(level,2),0,'A','B'), level, level, level

from dual connect by level <= 100000

;

insert into t2

select decode(mod(level,2),0,'A','B'), level, level, level

from dual connect by level <= 100000

;

analyze table t1 compute statistics;

analyze table t2 compute statistics;

Merge 문을 사용해보자 

-- case 1
MERGE /*+ gather_plan_statistics */ INTO t2

   USING (SELECT *

            FROM t1

           WHERE c1 IN ('A', 'B')) x

   ON (    x.c1 = t2.c1

       AND x.c2 = t2.c2

       AND x.c3 = t2.c3

       AND t2.c1 = 'A')

   WHEN MATCHED THEN

      UPDATE SET t2.c4 = x.c4

   WHEN NOT MATCHED THEN

      INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

      VALUES (x.c1, x.c2, x.c3, x.c4) ;

 

select *
  from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;


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

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

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

|   1 |  MERGE                  | T2   |      1 |      2 |00:00:12.52 |     105K|    655 |          |

|   2 |   VIEW                  |      |      1 |    100K|00:00:00.65 |     632 |    626 |          |

|*  3 |    HASH JOIN RIGHT OUTER|      |      1 |    100K|00:00:00.65 |     632 |    626 | 4686K (0)|

|*  4 |     TABLE ACCESS FULL   | T2   |      1 |  50000 |00:00:00.02 |     316 |    313 |          |

|*  5 |     TABLE ACCESS FULL   | T1   |      1 |    100K|00:00:00.01 |     316 |    313 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

   4 - filter("T2"."C1"='A')

   5 - filter(("C1"='A' OR "C1"='B'))

 

아주 정상적인 PLAN 이다.

Merge 문에 IN 조건을 사용해보자

-- case 2
MERGE /*+ gather_plan_statistics */ INTO t2

   USING (SELECT *

            FROM t1

           WHERE c1 IN ('A', 'B')) x

   ON (    x.c1 = t2.c1

       AND x.c2 = t2.c2

       AND x.c3 = t2.c3

       AND t2.c1 IN ('A', 'B'))

   WHEN MATCHED THEN

      UPDATE SET t2.c4 = x.c4

   WHEN NOT MATCHED THEN

      INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

      VALUES (x.c1, x.c2, x.c3, x.c4) ;

 

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

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

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

|   1 |  MERGE                 | T2   |      1 |        |      2 |00:25:50.73 |      50M|    821 |

|   2 |   VIEW                 |      |      1 |        |    100K|00:25:52.38 |      50M|    795 |

|   3 |    NESTED LOOPS OUTER  |      |      1 |    100K|    100K|00:25:52.28 |      50M|    795 |

|*  4 |     TABLE ACCESS FULL  | T1   |      1 |    100K|    100K|00:00:00.60 |     316 |    629 |

|   5 |     VIEW               |      |    100K|      1 |    100K|00:25:34.26 |      50M|    166 |

|*  6 |      FILTER            |      |    100K|        |    100K|00:25:33.92 |      50M|    166 |

|*  7 |       TABLE ACCESS FULL| T2   |    100K|      1 |    100K|00:25:33.68 |      50M|    166 |

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

 

Predicate Information (identified by operation id):

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

   4 - filter(("C1"='A' OR "C1"='B'))

   6 - filter(("T1"."C1"='A' OR "T1"."C1"='B'))

   7 - filter(("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3" AND

              INTERNAL_FUNCTION("T2"."C1")))

 

10만번 반복된다
Starts 항목에 주목하라 FTS(Full Table Scan)을 10만번 반복 실행하였다. CASE2 에서 t2.c1 IN ('A', 'B')
조건을 사용하였더니
최악의 PLAN이 만들어 졌으며 Buffers 50M 에 육박하고 시간상으로도 25분 이상 걸렸다. 왜 그럴까? 아래의 SQL처럼 Case1과 Case2 실행시에 Logical Optimizer에 의하여 변경된 SQL을 보면 이유를 알수 있다.
 

SELECT /*+ CASE1 NO_MERGE 상태 */

       lv.RID,

       lv.C1, lv.C2, lv.C3, lv.C4,

       X.C1,  X.C2,  X.C3,  X.C4

  FROM  (SELECT T1.C1 C1,T1.C2 C2,T1.C3 C3,T1.C4 C4

           FROM T1

          WHERE T1.C1='A' OR T1.C1='B'

        ) X,

        LATERAL( SELECT T2.C1, T2.C2, T2.C3, T2.C4, T2.ROWID AS RID

                    FROM T2

                   WHERE X.C1=T2.C1

                     AND X.C2=T2.C2

                     AND X.C3=T2.C3

                     AND T2.C1='A'  )(+) lv

 

 

정상적으로 뷰가 해체되다
Case1의
 LATERAL VIEW 내부의 T2.C1='A' 조건은 아우터 조인으로 바꿀 있으므로 View Merging 발생하여 인라인뷰 X LATERAL VIEW lv 아래처럼 평범한 아우터 조인으로 바뀐다.

 

SELECT /*+ CASE1 MERGE 상태 */

       T2.ROWID RID,

       T2.C1 ,T2.C2 C2,T2.C3 C3,T2.C4 C4,

       T1.C1 ,T1.C2 C2,T1.C3 C3,T1.C4 C4

  FROM T1, T2

 WHERE T1.C3=T2.C3(+)

   AND T1.C2=T2.C2(+)

   AND T1.C1=T2.C1(+)

   AND T2.C1(+)='A'

   AND (T1.C1='A' OR T1.C1='B')

 

이때 옵티마이져가 내부적으로 MERGE 힌트를 사용한다. 10053 Trace에도 다음처럼 쿼리블럭 SEL$2 SEL$3 SEL$1 MERGE 되었다는 정보가 포함되어 있다.

 

Registered qb: SEL$5428C7F1 0x9f7b318 (VIEW MERGE SEL$1; SEL$2 SEL$3)


Out Line
정보에도 이런 사항이 잘 나타난다.

Outline Data

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

  /*+

생략
MERGE(@"SEL$2")      -->
쿼리블럭 2

          MERGE(@"SEL$3")      --> 쿼리블럭 3 MERGE 되어 LATERAL VIEW 가 없어짐
          
생략

  */

 

이제 case 2 분석 해보자.

 

SELECT /*+ CASE2 NO_MERGE 상태 */

       lv.RID,

       lv.C1, lv.C2, lv.C3, lv.C4,

       X.C1,  X.C2,  X.C3,  X.C4

  FROM  (SELECT T1.C1 C1,T1.C2 C2,T1.C3 C3,T1.C4 C4

           FROM T1

          WHERE T1.C1='A' OR T1.C1='B') X,

          LATERAL( SELECT T2.C1, T2.C2, T2.C3, T2.C4, T2.ROWID AS RID

                      FROM T2

                     WHERE X.C1=T2.C1

                       AND X.C2=T2.C2

                       AND X.C3=T2.C3

                       AND (T2.C1='A' OR T2.C1='B') ) (+) lv

 

제약조건 때문에...
위의
SQL LATERAL VIEW 적용한 모습이다. 그런데 (T2.C1='A' OR T2.C1='B') 조건 때문에 아우터 조인으로 바꾸질 못한다. 이것은 오라클 제약사항 이다. 제약조건이 있을 경우는 View Merging이 실패한다. 아래처럼 말이다.

 

SELECT /*+ CASE2 MERGE 상태 */

       lv.RID,

       lv.C1, lv.C2, lv.C3, lv.C4,

       X.C1,  X.C2,  X.C3,  X.C4

  FROM T1,

       LATERAL( SELECT T2.C1, T2.C2, T2.C3, T2.C4, T2.ROWID AS RID

                  FROM T2

                 WHERE X.C1=T2.C1

                   AND X.C2=T2.C2

                   AND X.C3=T2.C3

                   AND (T2.C1='A' OR T2.C1='B') ) (+) lv

 WHERE T1.C1='A' OR T1.C1='B'

 

IN 이 발목을 잡다
인라인뷰
X View Merging이 발생하였다. 결국 IN 혹은 OR  조건이 View Merging이 되지 못하도록 발목을 잡은 셈이다. 그리하여 Lateral View 가 살아남게 되었다. Lateral View
는 스칼라 서브쿼리처럼 동작하게 된다. 다시 말하면 LATERAL VIEW 
Hash 조인으로 실행되지 못한다. 문제의 Plan에서 Nested Loop 조인이 발생한 이유도 여기 있다.
 

그렇다면 이 문제를 어떻게 해결할 수 있을까?

문제 해결방법 3가지
1)
만약 인덱스를 만들 수 있다면 문제가 해결된다. 아래는 T2 (C1, C2, C3) 인덱스를 만든후 CASE 2를 다시 실행한 Plan 이다.

 

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

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

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

|   1 |  MERGE                           | T2     |      1 |      2 |00:00:16.95 |     303K|    934 |

|   2 |   VIEW                           |        |      1 |    100K|00:00:03.92 |     200K|    932 |

|   3 |    NESTED LOOPS OUTER            |        |      1 |    100K|00:00:03.92 |     200K|    932 |

|*  4 |     TABLE ACCESS FULL            | T1     |      1 |    100K|00:00:00.12 |     316 |    313 |

|   5 |     VIEW                         |        |    100K|    100K|00:00:02.51 |     200K|    619 |

|*  6 |      FILTER                      |        |    100K|    100K|00:00:02.29 |     200K|    619 |

|   7 |       TABLE ACCESS BY INDEX ROWID| T2     |    100K|    100K|00:00:02.09 |     200K|    619 |

|*  8 |        INDEX RANGE SCAN          | T2_IDX |    100K|    100K|00:00:01.24 |     100K|    320 |

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

 

Predicate Information (identified by operation id):

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

   4 - filter(("C1"='A' OR "C1"='B'))

   6 - filter(("T1"."C1"='A' OR "T1"."C1"='B'))

   8 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3")

       filter(("T2"."C1"='A' OR "T2"."C1"='B'))

2) 인덱스를 만들 수 없는 경우라면 아래처럼 Between 을 사용하면 된다. Between 은 아우터 조인이 가능하다.

MERGE /*+ gather_plan_statistics */ INTO t2

   USING (SELECT *

            FROM t1

           WHERE c1 IN ('A', 'B')) x

   ON (    x.c1 = t2.c1

       AND x.c2 = t2.c2

       AND x.c3 = t2.c3

       AND t2.c1 between 'A' AND 'B')

   WHEN MATCHED THEN

      UPDATE SET t2.c4 = x.c4

   WHEN NOT MATCHED THEN

      INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

      VALUES (x.c1, x.c2, x.c3, x.c4) ;

     

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

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

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

|   1 |  MERGE               | T2   |      1 |      2 |00:00:14.99 |     104K|    817 |          |

|   2 |   VIEW               |      |      1 |    100K|00:00:00.58 |     816 |    810 |          |

|*  3 |    HASH JOIN OUTER   |      |      1 |    100K|00:00:00.48 |     816 |    810 | 7600K (0)|

|*  4 |     TABLE ACCESS FULL| T1   |      1 |    100K|00:00:00.01 |     316 |    313 |          |

|*  5 |     TABLE ACCESS FULL| T2   |      1 |    100K|00:00:00.01 |     500 |    497 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

   4 - filter(("C1"='A' OR "C1"='B'))

   5 - filter(("T2"."C1"<='B' AND "T2"."C1">='A'))


정상적으로 Hash 조인이 발생하였다.

3) Between 을 사용할 수 없는 경우라면 아래처럼 Decode를 사용하면 된다. Decode 또한 아우터 조인이 가능하다.

MERGE /*+ gather_plan_statistics */ INTO t2

   USING (SELECT *

            FROM t1

           WHERE c1 IN ('A', 'B')) x

   ON (    x.c1 = t2.c1

       AND x.c2 = t2.c2

       AND x.c3 = t2.c3

       AND t2.c1 = decode(t2.c1, 'A', 'A', 'B'))

   WHEN MATCHED THEN

      UPDATE SET t2.c4 = x.c4

   WHEN NOT MATCHED THEN

      INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

      VALUES (x.c1, x.c2, x.c3, x.c4) ;

     

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

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

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

|   1 |  MERGE                  | T2   |      1 |      2 |00:00:15.16 |     104K|    967 |          |

|   2 |   VIEW                  |      |      1 |    100K|00:00:00.72 |     816 |    810 |          |

|*  3 |    HASH JOIN RIGHT OUTER|      |      1 |    100K|00:00:00.72 |     816 |    810 | 8568K (0)|

|*  4 |     TABLE ACCESS FULL   | T2   |      1 |    100K|00:00:00.02 |     500 |    497 |          |

|*  5 |     TABLE ACCESS FULL   | T1   |      1 |    100K|00:00:00.01 |     316 |    313 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

   4 - filter("T2"."C1"=DECODE("T2"."C1",'A','A','B'))

   5 - filter(("C1"='A' OR "C1"='B'))

정상적으로 Hash 조인이 발생하였다.

 

결론

Merge문 사용시 On 절에 Target 테이블의 조건으로 IN 이나 OR를 사용하면 View Merging 이 발생하지 않는다. 따라서 LATERAL VIEW가 해체되지 못하며 LATERAL VIEW의 특성상 Nested Loop 조인이 적용된다. 이때 후행 테이블은 적절한 인덱스가 없다면 Full Table Scan이 발생하여 재앙과 같은 성능저하 현상이 발생된다. 이때 BETWEEN 이나 DECODE등 상황에 맞는 해결책을 사용할 수 있다.  

모르면 못한다
결국 Query Transformation의 원리와 순서 그리고 제약조건을 알게 된다면 누가 해법을 말해주지 않아도 자연스럽게 알 수 있다. IN 과 OR의 아우터 조인 제약조건은 누구나 알고 있으므로 문제가 될수 없다. 문제는 Query Transformation을 모른다면 튜닝을 못하는 시대가 이미 왔다는 사실이다. 안타깝게도 이런 원리를 설명해주는 서적은 어디에도 없다. 물론 몇가지 Query Transformation을 소개한 책은 있지만 Logical Optimizer를 주제로 하는 서적은 없다. 다시 말하면 우리는 튜닝을 하지 못할 환경에 살고 있다.


Posted by extremedb
,