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

댓글을 달아 주세요

  1. 라튜니 2013.05.13 11:32 신고  댓글주소  수정/삭제  댓글쓰기

    DP의 실사례가 궁금했었는데, 관련 예를 포스팅 해주셨네요~ 감사합니다. 6월달 부터 바뻐지신다고 하셨는데, 5월달 중에 또 다른 글을 볼 수 있을까요~?

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2013.05.13 13:53 신고  댓글주소  수정/삭제

      지금 튜닝 프로젝트가 시작되었습니다. 바빠질것 같습니다. 글을 쓰려면 거의 새벽이 되어야 합니다. 아니면 주말에 써야하죠. 확신은 없지만노력해봐야 겠습니다.

  2. feelie 2013.05.14 14:34 신고  댓글주소  수정/삭제  댓글쓰기

    자기가 좋아하는 일을 하면서 바쁘게 사는게 좋은거겠죠...
    업무도 열심히 하시고, 부족한 저같은 중생을 위해 좋은 글 부탁합니다...

  3. Favicon of http://clipper0317.tistory.com BlogIcon clipper 2014.05.27 14:17 신고  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 글 잘 보고 있습니다.

    마음의 빚을 갚는 기분과 책으로 보지않으면 공부가 잘 안되는 저를 위하여 yes24에서 방금 책을 구매신청 하였습니다.

    늘 건승하시길 바랍니다.

    감사합니다.

  4. 김승욱 2015.03.24 17:10 신고  댓글주소  수정/삭제  댓글쓰기

    감동적입니다...!!!!!!!!!!! 항상 건강하세요~~~

집계함수 내부에 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

댓글을 달아 주세요

  1. 혈기린 2013.05.06 18:32 신고  댓글주소  수정/삭제  댓글쓰기

    다시 활동 재계하시는건가요 정말 오랬만에 글이 올라왔네요

  2. feelie 2013.05.07 18:49 신고  댓글주소  수정/삭제  댓글쓰기

    많이 바쁘셨나 보네요...
    그동안 무척 기다렸는데, 엄청 반갑습니다..

  3. 라튜니 2013.05.07 19:46 신고  댓글주소  수정/삭제  댓글쓰기

    정말 2년만의 글이 올라왔네요. 너무 반갑네요~ 자주 올려 주시면 감사하겠지만 또 바빠지신다니
    가끔이라도 부탁드립니다~

  4. 강정식 2013.05.14 09:06 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 오수석님 ^^
    어제 채팅으로 오랜만에 뵈서 반가웠습니다.
    그런데 페북으로 보니 오수석님 포스팅이 올라온걸 보고 바로 달려왔습니다 ㅎㅎ
    역시나 좋은 글을 올려 주셨네요... ^^

    앞으로도 유용한 포스팅 많이 기대 하겠습니다..
    감사합니다.

  5. 2014.02.24 17:08  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다


책 (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

댓글을 달아 주세요

  1. 리베 2010.10.04 10:35 신고  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 자료 감사합니다. 오동규님 덕분에 실력이 쑤~~~욱 올라가고 있는듯... ^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.10.04 12:38 신고  댓글주소  수정/삭제

      안녕하세요. 리베님
      실력이 향상되었다면 참으로 다행스런 일 입니다.
      제가 이제 좀 쉬었으니 슬슬 다음 주제를 준비해야 할 단계가 온것 같습니다.^^

  2. feelie 2010.10.07 12:39 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 자료 감사합니다

  3. 김시연 2010.10.26 14:15 신고  댓글주소  수정/삭제  댓글쓰기

    오늘 컨설팅 복귀하고, 자료 다운받아서 쭉 보고 있습니다. PPT 만드는게 보통일이 아닌데, 수고 많으셨습니다.
    그리고 혹시 Logical Optimizer에 대한 세미나나 교육 계획이 있으신가요?
    그럼 갑자기 추워진 날씨에 감기 조심하세요~!

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.10.26 14:53 신고  댓글주소  수정/삭제

      시연님 오랜만 입니다. 복귀하셨군요. 고생하셨습니다. 교육에 관하여 말씀 드리겠습니다.
      올해부터 HP 교육센터를 오픈메이드가 운영하게 됨에 따라 logical optimizer 교육은 준비중입니다. 아마도 주말(토, 일)을 이용한 4일 과정이 될것 같습니다. 혹시 짧은 세미나나 출장교육은 수고스럽더라도 저에게 메일로 문의해 주시기 바랍니다.
      감사합니다.

  4. 2010.11.30 09:55 신고  댓글주소  수정/삭제  댓글쓰기

    귀한 자료네요... 책도 읽었는데 이렇게 또 볼수 있어서 좋습니다. 감사합니다.

  5. Favicon of http://blog.naver.com/genisu BlogIcon 김승욱 2013.01.07 10:55 신고  댓글주소  수정/삭제  댓글쓰기

    책을 읽다 놀란것이 의무감에 대한 말씀을 하신거에 대해 참 감동받았는데
    PPT까지 올려주시다니...정말...대단하신것 같습니다.감사합니다!!!


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

댓글을 달아 주세요

  1. 윤상원 2010.09.15 17:07 신고  댓글주소  수정/삭제  댓글쓰기

    파트3, 기다리고 있었는데 감사합니다!
    책 내용을 정리하는데 많은 도움이 될 거 같습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.09.15 17:20 신고  댓글주소  수정/삭제

      반갑습니다.
      의외로 파워포인트를 기다리는 분들이 많이 계시는군요.
      Part 4도 힘을 내서 빨리 작업을 해야겠습니다.
      감사합니다.

  2. 윤상원 2010.09.15 17:30 신고  댓글주소  수정/삭제  댓글쓰기

    방금 보는중에 PDF파일 95페이지에 보니 갑자기 3.8 CVM 내용이 나오네요. 앞내용이 3.15 GBPD인데 말이죠. 카피되는 중에 잘못 들어간거 같습니다~

(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

댓글을 달아 주세요

  1. 썸바디 2010.08.13 09:41 신고  댓글주소  수정/삭제  댓글쓰기

    늘 좋은 정보 감사합니다~~
    근데 다운받은 파일 압축이 잘 안풀리네요 ㅡㅡ

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.13 09:58 신고  댓글주소  수정/삭제

      분할 압축이므로 모두 다운받은 후에 푸셔야 합니다.
      7zip 프로그램을 다운받으시거나 알집으로 압축을 푸시면 됩니다. 7zip 프로그램을 다운받을 수 있게 글을 수정하였습니다. 해결 되셨나요?

  2. 썸바디 2010.08.13 10:21 신고  댓글주소  수정/삭제  댓글쓰기

    7zip 으로 하니 압축 잘 풀리네요~ 감사합니다~^^

  3. 써니 2010.08.16 23:44 신고  댓글주소  수정/삭제  댓글쓰기

    먼저, 좋은 정보 감사드립니다.

    제가 최근 DBUA를 이용한 9i --> 10gR2(10.2.0.4), 11gR1(11.1.0.7) 로 Upgrade를 한 이후에 기존 SQL Plan에 비해
    현저하게 안좋은 Plan을 보이고 있어, 여기 저기 Web Site를 찾다가 우연히 이 Site를 알게 되었습니다.

    올려 주신 정보이외에도 최근 이곳에서 많은 도움을 받고 있습니다.
    이렇게 글을 올리게된 이유는 다름이 아니오라 한가지 궁금한 점이 있어서 입니다.

    Upgrade 한 이후에 업무 특성상 주요 Table들에 대해서, 매일 Analyze를 하고 있습니다.
    그런데, 9i에서 보여 주었던 SQL Plan에 비해 안좋은 결과를 보이고 있어서 원인 분석 중
    Upgrade된 DB에서 해당 Table에 대한 통계정보를 삭제 후, 다시 Plan을 보니 9i와 같은 Plan을 보여주고 있습니다.

    마치, 10gR2 와 11gR1의 Optimizer가 멍청해진것 같은 현상입니다.
    이걸 어찌 받아 들여야 할까요?
    (예로, 심지어는 Index도 안타고 Table Full Scan 하고 있습니다...
    Table에 대한 통계정보를 삭제 후엔 Index Scan 합니다.)

    지금은 SQL문 곳곳에 Hint문을 사용하여 해결하고 있으나, 본질적인 해결책이 아닌 듯 하여
    답답한 마음에 글 올립니다.
    /*+OPT_PARAM('_OPTIMIZER_PUSH_PRED_COST_BASED', 'FALSE') */
    /*+ opt_param('_optimizer_cost_based_transformation', 'off') */
    와 같은 Hints를 사용하고 있습니다.

    한 말씀 남겨주시면 감사하겠습니다.

    감사합니다.
    (딱히, 질문을 올릴만한 곳이 없어 이곳에 올립니다.)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.19 21:41 신고  댓글주소  수정/삭제

      써니님 안녕하세요.
      답변이 늦어 죄송합니다.
      말씀하신 옵티마이져의 문제는 예전부터 많이 있었습니다.
      old 버젼에서 new 버젼으로 upgrade 함에도 불구하고 악성 Plan으로 되는 경우가 있습니다.
      하지만 그것은 SQL의 5% 내외일 것입니다. 다시말하면 성능이 좋아진 것이 많은 부분을 차지하고 있지만 그것은 눈에 띄질 않습니다. 예를들어 0.2초 걸리던 것이 0.1초걸린다면 이런것은 문제가 되지 않지요. 하지만 약 100개중의 5개의 경우는 악성 plan을 만드는 경우가 많습니다.
      이런 경우는 어쩔 수 없습니다. 사람이 개입하여 올바른 길을 알려주는 수 밖에요.

      참고로 위에서 이야기한 5% 라는것은 정확한것이 아닙니다. 어림짐작으로 이야기한것이고 실제로는 시스템과 버젼에 따라 약간은 달라질 수 있습니다.

      먼저 두가지를 점검해 보시기바랍니다.
      1.통계정보를 충실히 수집했는지?
      예륻들어
      건수가 아주 많은 테이블은 0.01%
      건수가 조금 많은 테이블은 0.1%
      건수가 보통인 테이블은 5%
      건수가 적은 테이블은 10%
      건수가 아주 적은 테이블은 100%
      건수에 상관없이 기초성 테이블(고객, 상품, 부서, 직원, 계좌, 공통코드)등은 100%

      이렇게 하시면 됩니다. 이것은 예시 이므로 실제하실때는 구체적으로 하셔야 겠죠. 제가 수행한 사이트에는 통계정보를 수집할때 Oracle10g R2의 경우 AUTO 옵션을 쓰지 않습니다.

      local 파티션통계는 수집하지 않는것이 좋습니다. 즉 Global 통계만 관리하시면 됩니다. 단 전제조건이 있습니다. 각 파티션마다 실행계획이 달라져야 하는 경우는 local 파티션 통계를 수집하시는 것이 옳습니다. 반대로 모든 파티션의 실행계획을 고정시키고자 할때는 global 파티션의 통계정보만 관리해도 충분합니다.

      2.적절한 인덱스가 존재하는지?
      이것 또한 어려운 문제입니다.
      어려움을 토로하시는 걸로 봐서 Query Transformation 문제 같습니다. 각각의 SQL과 PLAN을 보고 적절한 인덱스가 있는지 판단 하셔야 합니다.
      예를 들어 인라인뷰가 있고 그 내부의 where절에 상수조건이 있다고 할때 거기에 JPPD가 발생했다고 치면 조인조건이 인라인뷰 안으로 파고 듭니다. 그런데 상수조건으로만 인덱스를 만들어주면 JPPD의 효과는 줄어들겁니다. 인덱스가 상수조건 + 조인조건으로 결합인덱스를 만들어주어야 JPPD의 효과가 최적으로 나타납니다. 아래의 SQL을 보세요.

      SELECT d.department_id, d.department_name, e.employee_id, e.job_id, e.email_phone_num
      FROM department d,
      (SELECT employee_id, department_id, job_id, phone_number AS email_phone_num
      FROM employee
      WHERE job_id = :v_job2 )e
      WHERE d.department_id = e.department_id(+)
      AND d.location_id = 1700;

      위의 SQL에서 EMPL0YEE 테이블에 존재해야 할 최적의 인덱스는 JOB_ID 가 아니라 JOB_ID + department_id 인덱스 입니다. 변경되지 않은 SQL만 보았을 때는 JOB_ID 인덱스만 있으면 될것 같지만 변경된 SQL을 보면 결합인덱스가 왜 필요한지 아실겁니다. 아래의 변경된 SQL을 보시죠.

      SELECT d.department_id, d.department_name, e.employee_id, e.job_id, e.email_phone_num
      FROM department d,
      LATERAL (SELECT employee_id, department_id, job_id, phone_number AS email_phone_num
      FROM employee e2
      WHERE e2.job_id = :v_job2
      AND e2.department_id = d.department_id ) e
      WHERE d.location_id = 1700 ;

      위의 변경된 SQL을 보신다면 결합인덱스가 최적임을 아실것 입니다. 물론 결합인덱스의 효율이 더 좋은경우를 이야기 하는 겁니다. 쿼리변환의 문제는 통계정보의 적절성과 인덱스의 최적화 문제가 거의 대부분 입니다.

      하지만 이 두가지가 완벽히 되어 있다고 할지라도 옵티마이져가 완벽하지 않으므로 5% 미만의 경우는 악성 PLAN을 생성하기 때문에 사람이 힌트나 쿼리튜닝을 통하여 손을 봐주어야 합니다. 옵티마이져가 아무리 업그레이드 되어도 사람의 손길이 필요하다는 것입니다. 아마도 앞으로 20년간은 그럴것 같습니다.
      감사합니다.

  4. 써니 2010.08.18 13:28 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 말씀 진심으로 감사드립니다.
    앞으로도 많은 공부가 필요할 듯 합니다.

    다시 한 번 감사의 말씀드립니다.

  5. 써니 2010.08.20 00:41 신고  댓글주소  수정/삭제  댓글쓰기

    브라이언 홍님 관심주셔서 고맙습니다.

    그리고 extremedb님 오늘도 좋은 말씀 감사드립니다. ^^

  6. 써니 2010.08.20 11:29 신고  댓글주소  수정/삭제  댓글쓰기

    여기저기 문서를 찾아보니,
    Analyze 와 dbms_stats Procedure의 차이점이 심할 수도 있겠습니다.

    위에서 언급한 Index를 사용하지 못않는 Table을 대상으로 Test한 결과
    Analyze 와 비교해서 dbms_stats Procedure를 이용해서 통계를 구한 결과가
    제가 원하는 Plan을 보여주고 있습니다.

    관련 자료를 참고로 올리고 싶은데.. 올릴 수 있는 방법이 없네요..^^
    혹 다른 분들을 위해서 다음 정보를 남김니다.

    What’s Up With dbms_stats?
    by Terry Sutton
    Database Specialists, Inc.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.20 11:51 신고  댓글주소  수정/삭제

      헉! analyze로 실행하고 계셨나요?
      말씀하신대로 그차이는 엄청 큽니다. 앞으로도 차이가 더 벌어질 것입니다.
      11g에서 dbms_stats는 정확성과 성능면에서 또 한번 진화되었습니다. 아래의 글을 참고하세요.
      http://scidb.tistory.com/entry/11g-DBMSSTATS-개선사항

  7. 브라이언홍 2010.08.23 09:22 신고  댓글주소  수정/삭제  댓글쓰기

    저도 현재 이런 경우를 많이 접하고 있습니다.
    써니님꼐서 사용하시는 힌트는 저의 경우 Long Parse일 경우에 사용합니다.
    "왜 Long Parse가 발생하느냐?"가 관건일 것 같습니다.

    문득 어제 밤에 이런 생각을 해 보았습니다.
    제 친구 중 하나는 물건을 구입할 때 딱 한가지 기준이 있답니다. 그래서 쇼핑할때 시간이 많이 걸리지 않는다고 하더군요.
    그런데 저는 이것저것 비교하기를 좋아합니다. 심지어 이마트에서 본 물건이 롯데마트에서 더 좋은 디자인과 더 좋은 가격 더 좋은 품질을 있었는지 기억을 더듬습니다. 참~~~ 쇼핑하기 힘들지요.. ㅡ,ㅡ; 신중하다라고 말하기엔 너무 오타쿠 같아서ㅋㅋ

    옵티마이저가 비용기반으로 작동하기에 너무 많은 것을 고민하고 있는것은 아닐까요?
    그래서 과감히 그 기능을 꺼버리면 파싱하는 시간이 줄어드는게 당연하겠지요~~ 그러나 실행계획이 최적이 안되면 또 낭패입니다.

    제가 예전에 이런 문제로 엑셈에 올린글이 있어 공유해봅니다. 혹시 보셨는지 모르겠지만 ..
    http://121.254.172.39:8080/pls/apex/f?p=101:11:0::::P11_QUESTION_ID:2470200346608331

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.19 11:17 신고  댓글주소  수정/삭제

      하드파싱시간은 아래의 두가지를 합친것 입니다.
      Logical Optimizing + Pysical Optimizing
      그래서 위의 관련 파라미터를 꺼 놓으면 시간이 줄어들 수는 있으나 또다른 문제가 발생합니다. 실행계획이 악성이 될 수 있습니다. 즉 파라미터를 끄는 방식으로는 두마리 토끼를 다 잡기가 어렵다는 것입니다.

      두마리 토끼를 다 잡는 방법이 있습니다. 하지만 이방법은 100개중에 문제가 되는 SQL에만(5% 미만) 적용하시는것이 좋을것 입니다.

      1.Hard Parsing시간을 고려하지않고 최적의 실행계획을 찾는다.
      2.최적의 실행계획을 유도하는 오라클 내부힌트(Internal Hint)를 찾는다. DBMS_XPLAN.DISPLAY_CURSOR 의 Outline Data를 참조하시면 됩니다.
      3. 그 힌트들을 해당 SQL에 적용한다.

      모든 힌트를 적용할 필요는 없습니다. 두가지 카테고리의 힌트만 적용하시면 됩니다.
      1.LOGICAL 힌트 (unnest, merge, push_pred, USE_CONCT...)
      2.PHYSICAL 힌트 ( 조인순서 (leading), 조인방법(use_nl/hash/merge), 엑세스방법(index, full) )

      환경적 힌트, 예컨데 OPTIMIZER_FEATURES_ENABLE이나 DB_VERSION ,all_ROWS 등의 힌트는 빼셔도 됩니다. 환경적 힌트 또한 Logical 과 Physical Optimization을 결정하기 위한것 입니다. 그러한 것들을 미리 결정해 놓았으므로 환경적 힌트는 필요가 없습니다.

      이렇게 한다면 Hard Parsing시간이 최소화 되면서도 최적의 실행계획을 유지할 수 있습니다. 왜냐하면 옵티마이져가 고민하여 결정해야할 것을 고민할 필요없이 만들어버렸기 때문입니다. 즉 여러마트들을 돌아다니면서 시간을 죽이며 어렵게 쇼핑할 필요가 없습니다. 또한 개발자가 힌트를 적용하지 않는다고 하여도 오라클이 그러한 힌트를 내부적으로 적용할 것입니다.

      제가 집필한 책(The Logical Optimizer)에도 239 페이지에 이부분을 언급하였습니다.
      감사합니다.

      주의사항은 이렇게 적용한 SQL은 별도의 목록을 만들어 관리하는 것이 좋습니다. SQL이 변경될때 다시 1~3번을 적용해야 되기 때문입니다.

이전 글(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

댓글을 달아 주세요

  1. 혈기린 2010.08.02 10:19 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 내용감사 드립니다
    보통 흔히 아는 실행계획대로 읽는다면 6 - filter("E"."DEPARTMENT_ID" IS NULL) 이부분이 제일 마지막에 필터로 풀린느데 여기서는 이부분이 젤일 먼저 실행되는군요
    이런건 어떻게 판단하는건가요? 트레이스 내용을 보고 판단하는지요? 아니면 SQL을 보고 판단하는건가요?

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.02 16:41 신고  댓글주소  수정/삭제

      안녕하세요. 기린님
      이런 경우는 예외에 속하기 때문에 Plan상의 Operation 부분을 보고 판단할 수 없습니다.
      하지만 SQL을 보면 Uncorreated Subquery(비상관서브쿼리)인지 아닌지 판단할 수 있으므로 어려움은 없을것 입니다..

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

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


The Logical Optimizer_Part 1.pdf

The Logical Optimizer_Part 1



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


신고
Posted by extremedb

댓글을 달아 주세요

  1. 혈기린 2010.07.27 10:22 신고  댓글주소  수정/삭제  댓글쓰기

    책을 이제야 완독했네요 ㅎㅎ
    쿼리변형에 대해서 많은지식을 얻고 쿼리변환에 대한 원리도 많이 터득한 좋은계기가 된거 같습니다
    또 이렇게 책에 대한 PPT까지 복받으실겁니다 ㅎㅎ

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.07.27 13:24 신고  댓글주소  수정/삭제

      혈기린님 반갑습니다.
      드디어 책을 정복하셨군요!
      축하드립니다.

      두번정도 더 읽으실 샐각은 없으신지요?
      한 지인이 3번 읽었는데 두번읽을 때는 2배속, 세번째는 8배속으로 이틀만에 읽을 수 있었다는군요. ^^

    • 혈기린 2010.07.27 15:54 신고  댓글주소  수정/삭제

      ㅎㅎ
      당연히 몇번 더읽어야죠
      몇번 읽다 보면 앞에 놓쳤던 부분이라던지 잘못이해한게 나타 나더군요

  2. 박성은 2010.07.28 00:40 신고  댓글주소  수정/삭제  댓글쓰기

    PPT가 노가다죠..ㅎㅎ
    좋은 자료 잘 보겠습니다. ^^

  3. 2010.07.28 08:35  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  4. 신구 2010.08.02 11:08 신고  댓글주소  수정/삭제  댓글쓰기

    감사합니다. 잘보겠습니다!

  5. 김시연 2010.08.03 13:12 신고  댓글주소  수정/삭제  댓글쓰기

    좋은자료감사합니다~! 갑자기 목요일부터 일주일간 컨설팅 나갈일이 생겨서 공부하러 들렀습니다. 늘 건강하세요~

  6. Favicon of http://twitter.com/sensui_ BlogIcon Sensui 2010.08.23 13:53 신고  댓글주소  수정/삭제  댓글쓰기

    책을 보면서 함께 보고 있습니다. 잘 보겠습니다^^

  7. 챔기름 2011.04.13 17:35 신고  댓글주소  수정/삭제  댓글쓰기

    좋은자료 감사합니다.

  8. Favicon of http://www.charmsabosuk.com BlogIcon thomas sabo 2011.10.13 14:03 신고  댓글주소  수정/삭제  댓글쓰기

    좋은자료감사합니다~! 갑자기 목요일부터 일주일간 컨설팅 나갈일이 생겨서 공부하러 들렀습니다. 늘 건강하세요~

  9. 2012.02.22 13:31  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  10. imdrim 2014.03.07 10:42 신고  댓글주소  수정/삭제  댓글쓰기

    어제 교육 감사했습니다.
    블로그도 자주 찾아뵙겠습니다.

영화 <마이너리포트>의 주인공인 톰 크루즈가 사용한 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

댓글을 달아 주세요

  1. Favicon of http://sensui.tistory.com BlogIcon Sensui 2010.04.29 21:29 신고  댓글주소  수정/삭제  댓글쓰기

    2030년이면 제가 40대 중반이 되는데 가공할만한 기능이군요. 학교에서 공부를 하면서 SQL의 파싱과정을 보고 단순한 정도의 Transformation만 이루어지는 줄 알았는데 저 정도인줄은 몰랐습니다. 보는 내내 긴장이 되었네요.. 좋은 내용 감사합니다^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.30 10:00 신고  댓글주소  수정/삭제

      반갑습니다. Query 변환종류는 버젼이 올라갈수록 기능이 점점 더 많아 지므로 지속적인 연구가 필요한 분야 입니다. 2030년이 된다면 프로바둑기사와 컴퓨터가 맞장뜨는 일이 발생할 수 도......
      감사합니다.


The Logical Optimizer



강컴
 2010-04-20
교보 2010-04-22
인터파크 2010-04-26
YES24 2010-04-28
알라딘 2010-04-28
반디앤루니스 2010-04-30
리브로
GMARKET
옥션
신세계몰

주간 교보문고 데이터 베이스 부분 순위

주간 YES24 오라클 순위
신고
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 시 에러가 나는 부분은 무시해도 된다. 정상적으로 처리된 것이다.
 


Schema Generation_ver 11.2.0.1.7z

Schema 생성 Script for Oracle 11.2.0.1

Schema Generation_ver 11.1.0.6.7z

Schema 생성 Script for Oracle 11.1.0.6

Part 1.7z

Scripts for Part1

Part 2.7z

Scripts for Part2

Part 3.7z

Scripts for Part3

Part 4.7z

Scripts for Part4

Appendix.7z

Scripts for Appendix

Proof of CBQT by Complex Subquery.7z

서브쿼리의 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-Script Download  (37) 2010.04.20
The Logical Optimizer-오타와 오류등록  (26) 2010.04.20
저자와의 대화  (36) 2010.04.20
The Logical Optimizer  (61) 2010.04.05
Posted by extremedb

댓글을 달아 주세요

  1. 이전 댓글 더보기
  2. 혈기린 2010.04.20 11:30 신고  댓글주소  수정/삭제  댓글쓰기

    냉큼 주문했습니다 ㅎㅎ
    한동안 이책 들여다 보느라 정신 없겠네요 감사 드립니다 ^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.20 12:21 신고  댓글주소  수정/삭제

      성원에 감사드리며 Logical Optimizer를 꼬고 정복하시기 바랍니다.
      한가지 걱정은 혈기린님(블로그 독자)정도의 수준이라면 이책을 읽을 수 있으나 초보가 띠지의 내용등에 혹 해서 사면 어쩌나 하는것 입니다. 주위에 그런사람들이 있다면 말려주시기 바랍니다.

  3. 2010.04.20 13:51  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  4. 마늘장아찌 2010.04.21 09:08 신고  댓글주소  수정/삭제  댓글쓰기

    강컴 가입하고 바로 구매했습니다.
    더불어 논증의 탄생도 같이 구매했습니다.
    얼마 안남은 4월 독서로 밤을 새워볼까 합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.21 09:29 신고  댓글주소  수정/삭제

      성원에 감사드리며 Logical Optimizer를 정복하셔서 자신이 작성한 SQL이 실재로 실행되는 모습이 어떤지 실행계획은 어떻게 변경된 것인지 아는 개발자가 되시기 바랍니다.

      모든 분들에게 논증의 탄생을 권합니다. TV의 토론 프로그램에 나오는 사람들이 이 책을 읽고 출연한다면 시청자들을 답답하게 만드는 말싸움은 없어질 것입니다.

      감사합니다.

  5. 2010.04.24 03:29 신고  댓글주소  수정/삭제  댓글쓰기

    띠지에 혹해서 샀습니다 .( 농담이구요)
    내용이 재미 있어서 금방 볼거 같네요 (시간 가는줄 몰라서;;)
    좋은책 감사드립니다.

  6. onsider 2010.04.24 12:20 신고  댓글주소  수정/삭제  댓글쓰기

    초보자가 띠지에 혹해서 샀을경우 주회입마 에 빠져 당분간 DB를 멀리하게 될것 같군요..
    ㅎㅎㅎ

  7. Favicon of http://elahi.tistory.com BlogIcon Sensui™ 2010.04.26 10:42 신고  댓글주소  수정/삭제  댓글쓰기

    대학생 입장에서 가끔 extremedb님의 블로그에 와서 글을 읽으면 아직 아는 것은 부족하지만 많은 걸 배워갈 수가 있어서 내용이 참 재미있습니다..^^ Optimizer에 대해 최근에 인지한 대학생입장에서는 후에 꼭 읽어볼게요~! 고생하셨습니다!

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.26 12:41 신고  댓글주소  수정/삭제

      반갑습니다. 대학생인데 이 블로그를 구독 하다니 열정이 대단합니다. 지금처럼 하신다면 멀지않아 선배들이 긴장해야 할듯 합니다.^^ 좋은 하루돠세요.

  8. 2010.04.27 13:23  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  9. Favicon of http://dev4u.tistory.com BlogIcon dev4u 2010.04.28 16:53 신고  댓글주소  수정/삭제  댓글쓰기

    YES24에서는 언제쯤 구매할 수 있나요?
    빨리 책 보고 싶은데 YES24에서 아직 구매할 수 없더군요.
    되도록 구입하고 싶거든요.(여러가지 이유로....)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.28 16:58 신고  댓글주소  수정/삭제

      서점과 계약은 제 능력 밖의 일이라...
      출판사에 문의해보니 이번주 주말이나 다음주 월요일 정도에 가능하다고 합니다.
      불편을 끼쳐 드려 죄송합니다.

  10. Favicon of http://dev4u.tistory.com BlogIcon dev4u 2010.04.28 17:25 신고  댓글주소  수정/삭제  댓글쓰기

    책 덕분에 블러그도 알게 되었네요.
    좋은 정보 감사드립니다.
    다음주가 기다려 지는데요. ^^

  11. 타락천사 2010.05.11 00:52 신고  댓글주소  수정/삭제  댓글쓰기

    샘플 데이타 입력시에 아래와 같이 TIMES 까지 Import 되다가 죽곤 하는데..
    다른 분들은 다들 잘 되시는건지... 여기까지만 Import 해두 실습에는 문제 없는지...
    궁금하네요..
    테스트로 윈도우 7, 11G(R1) 설치 환경 입니다.
    fromuser touser 방식으로 import 진행 했습니다.

    . . 분할 "SALES":"SALES_Q4_2003"(를)을 임포트합니다 0 행이 임포트되었습니다
    . . 테이블 "TIMES"(를)을 임포트 중 1826 행이 임포트되었습니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.05.11 09:41 신고  댓글주소  수정/삭제

      TIMES 에서 에러가 나는 것은 정상 입니다.
      의심이 되시면 아래의 SQL을 돌려 보시기 바랍니다.

      SELECT COUNT(*) AS OBJ_CNT -- 218개면 정상
      FROM DBA_OBJECTS A
      WHERE OWNER = 'TLO';

      SELECT COUNT(*) CONST_CNT --130개면 정상
      FROM DBA_CONS_COLUMNS
      WHERE OWNER = 'TLO';

  12. 전뚜 2010.05.26 15:44 신고  댓글주소  수정/삭제  댓글쓰기

    윈도우 7 이고 압축프로그램은 다집 이라는것을 쓰고 있는데

    압축을 풀면 폴더가 다 빈폴더네요 ㅡ.ㅡ?

  13. Darknet 2010.05.31 11:08 신고  댓글주소  수정/삭제  댓글쓰기

    ^^;; 많은걸 배워 갑니다.

  14. 아삽 2010.06.25 10:55 신고  댓글주소  수정/삭제  댓글쓰기

    옵티마이저가 아주 해부되어 있는 느낌이네요.
    이렇게 깊은 부분까지 연구하시다니 그 열정이 놀랍습니다.
    오선생님 덕분에 저같은 사람들이 많은 도움을 받습니다.
    감사합니다.
    책 즐겁게 읽고 있구요. ^^

  15. 구독자 2010.08.24 20:27 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요
    웹 서핑중 귀중한 책을 구입하여 구독 하게 되었습니다 .
    Script 를 테스트 하고 싶은데
    제가 사용하는 DB 버젼이 10.2.4 이어서 여기 있는 exp file 이 imp 가 안되네요 .
    혹 수고 스럽지만 sample schema 를 script 형태로 제공해 주시면 안되나요
    보안 때문에 export file 의 size 도 부담되고요....

  16. Favicon of http://www.perfectreplicawatch.co.uk/replica-patek-philippe-c-130.html BlogIcon patek philippe replica 2011.08.06 16:35 신고  댓글주소  수정/삭제  댓글쓰기

    책 보면서 실습해보고 궁금한 점 있으면 글 올리겠습니다 ^^

  17. Favicon of http://www.ukburberrysale.org.uk BlogIcon burberry outlet 2011.09.26 19:28 신고  댓글주소  수정/삭제  댓글쓰기

    책 보면서 실습해보고 궁금한 점 있으면 글 올리겠습니다 ^^

  18. Favicon of http://www.abercrombiefitch-saleuk.org.uk BlogIcon abercrombie and fitch uk 2011.09.26 19:28 신고  댓글주소  수정/삭제  댓글쓰기

    이렇게 깊은 부분까지 연구하시다니 그 열정이 놀랍습니다.

  19. Favicon of http://www.uggbootssaleu.co.uk BlogIcon ugg boots sale 2011.11.18 18:00 신고  댓글주소  수정/삭제  댓글쓰기

    다운받은후 User를 생성하고 권한부여 후 Import를 하면 실습 준비가 완료된다. 실습을 진행하려면
    TLO 계정으로 접속해야 한다. TLO 계정의 비밀번호는 transformer이다.

  20. Favicon of http://www.hollisterdeutschland.com.de BlogIcon hollister online shop 2011.12.15 17:07 신고  댓글주소  수정/삭제  댓글쓰기

    This is a great content, I’m so glad that I’ve found this high quality blog!

  21. Favicon of http://www.abercrombiefitchonsale.org.uk BlogIcon abercrombie uk 2012.01.06 11:55 신고  댓글주소  수정/삭제  댓글쓰기


    This is a great content, I’m so glad that I’ve found this high quality blog!

오타와 오류를 발견하신 독자는 댓글을 이용해 주세요.


Page

부분

수정 전

수정 후

53

밑에서 세 번째 줄

~DBA들의 취약점 중 많은 부분이 장의 내용을~

~DBA들의 취약점 중 많은 부분이 장의 내용을~

77

outline data

ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")

ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")

80

제목

2.4 OJE Outer-Join Elimination)

2.4 OJE (Outer-Join Elimination)

104

SQL 윗부분

SELECT e.employee_id, e.email, d.department_id

SELECT /*+ no_merge(@sel$1)  */
         e.employee_id, e.email, d.department_id

104

SQL 아랫부분

SQL의 결과는 아래와 같다.

SQL에 힌트를 사용한 이유는 Lateral View를 보존하기 위해서이다. View Merge 된다면 실행계획에서 Lateral View를 볼 수 없다. SQL의 결과는 아래와 같다.

106

직원구분코드 컬럼생성 부분

ALTER TABLE EMPLOYEE ADD EMP_KIND VARCHAR2(1) DEFAULT '1' NOT NULL;

ALTER TABLE EMPLOYEE ADD EMP_KIND VARCHAR2(1) DEFAULT 1 NOT NULL;

108

SQL 윗부분

SELECT /*+ GATHER_PLAN_STATISTICS ORDERED */

SELECT /*+ GATHER_PLAN_STATISTICS ORDERED NO_MERGE(@SEL$1) NO_MERGE(@SEL$3) */

109

위에서 네 번째 줄

ANSI SQL을 이용함으로써 선택적으로 ~

여기서도 View Merging을 발생하지 않게 하기위해 NO_MERGE 힌트를 사용하였다. ANSI SQL을 이용함으로써 선택적으로~

109

위에서 일곱 번째 줄

ANSI SQL을 사용할 수 없는 경우는 아래와 같이 조인절에 DECODE CASE 문을 사용하여도 같은 효과를 누릴 수 있다.

삭제 후 추가될 내용



내용이 많아 첨부파일로 처리함.

109

밑에서 다섯 번째 줄

Lateral View ANSI SQL 뿐만 아니라
일반적인 뷰를 Outer 조인하는 경우, ~

Lateral View 사용하지 않으면서도 선택적으로 조인하고 있다. 이 방법은 ANSI SQL을 사용할 수 없는 환경에서 훌륭한 해결책이 될 것이다.

Lateral View
ANSI SQL 뿐만 아니라 일반적인 뷰를 Outer 조인하는 경우, ~

120

위에서 두 번째 줄

Subsumtion

Subsumption

121

SQL 윗부분

Subsumtion

Subsumption

158

위에서 아홉 번째 줄

따라서 Null인 데이터를 찾자마자 Scan
 
멈출 수 있는 것이다.

따라서 Null인 데이터를 찾자마자 Scan을 멈출 수 있는 것이다. 추가될 내용의 위치




내용이 많아 첨부파일로 처리함.
관련내용: http://scidb.tistory.com/120

158, 159

158 페이지 위에서 열 번째 줄부터 ~ 159 페이지 세 번째 줄 까지

하지만 모든 Not In 서브쿼리에 Is Null 조건을 추가하면 결과가 틀려지지 않을까?  부터

이처럼 서브쿼리의 조건절이 추가된다면 그에 따라 적응적 탐색(Adaptive Null Aware Scan)을 하므로 걱정할 것이 없다.
까지 SQL과 실행계획 포함하여 모두 삭제

삭제 후 추가될 내용



내용이 많아 첨부파일로 처리함.
관련내용: http://scidb.tistory.com/121

162

10053 Trace 위의 제약사항 부분

두 번째로는 Where 절의 Is Null 조건에는 Outer 쪽 테이블의 PK 컬럼만 올 수 있다. 물론 PK 컬럼으로 조인이 되어야 함은 당연한 것이다.

두 번째로는 Outer Join에 사용된 컬럼과 Is Null 조건에 사용된 컬럼이 동일해야 한다. d.department_idOuter Join하고 d.department_name으로 Is Null 조건을 주면 OJTAJ가 발생되지 않는다.

105

1)번 부분

employee department간의 관계가 N:1 이면서 department 쪽이 Outer Join을 사용하였기 때문이다.

employee department간의 관계가 N:1 이면서 department 쪽이 Outer Join을 사용하였기 때문이다. 반대로 메인쿼리와 Lateral View 1:N의 관계라면 Lateral View는 결과건수에 영향을 미친다.

105

3)번 부분

이러한 모든 상황에서 Later View 내부의 테이블에 ~

이러한 모든 상황에서 Lateral View 내부의 테이블에 ~

197

Column Projection Information 윗부분

10053 Trace에는 파라미터 Pivot2를 적용할 경우 SQL 정보가 없다. 부터 197페이지 마지막 까지 모두 삭제

삭제 후 추가될 내용

 

내용이 많아 첨부파일로 처리함.




빨강색은 삭제이고 파랑색은 추가 입니다.


신고

'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-오타와 오류등록  (26) 2010.04.20
저자와의 대화  (36) 2010.04.20
The Logical Optimizer  (61) 2010.04.05
Posted by extremedb

댓글을 달아 주세요

  1. 2010.04.21 22:54  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  2. 2010.04.28 22:13  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  3. Favicon of http://www.interpark.com BlogIcon 피아체 2010.04.30 14:51 신고  댓글주소  수정/삭제  댓글쓰기

    테스트 버전은 오라클 11g R2 입니다.
    108page lateral view 관련 내용입니다.

    덕분에 아주 좋은 내용을 접한것 같습니다.
    ansi sql은 잘몰랐는데..많은 도움이 되네요.
    두번째 쿼리에서 buffers 가 0이 되어야 하는데 1로 첫번째 쿼리랑 같은데 확인 부탁 드립니다.

    EMPLOYEE.EMP_KIND 가 VARCHAR2(1) 인데 DEFAULT 값이 1로 되어 있어서 '1' 수정했습니다.
    물론 그전에는 1 로 테스트 했었구요.

    ALTER TABLE EMPLOYEE ADD EMP_KIND VARCHAR2(1) DEFAULT '1' NOT NULL;

    UPDATE EMPLOYEE SET EMP_KIND = CASE WHEN MOD(EMPLOYEE_ID, 2) = 1 THEN '1' ELSE '2' END;
    COMMIT;

    CREATE TABLE EMPLOYEE_KIND1 AS
    SELECT EMPLOYEE_ID, PHONE_NUMBER||TO_CHAR(ROWNUM) AS HOME_PHONE_NUMBER
    FROM EMPLOYEE
    WHERE EMP_KIND = '1';

    CREATE TABLE EMPLOYEE_KIND2 AS
    SELECT EMPLOYEE_ID, PHONE_NUMBER||TO_CHAR(ROWNUM) AS OFFICE_PHONE_NUMBER
    FROM EMPLOYEE
    WHERE EMP_KIND = '2';

    ALTER TABLE EMPLOYEE_KIND1 ADD CONSTRAINT PK_EMPLOYEE_KIND1 PRIMARY KEY (EMPLOYEE_ID) USING INDEX;
    ALTER TABLE EMPLOYEE_KIND2 ADD CONSTRAINT PK_EMPLOYEE_KIND2 PRIMARY KEY (EMPLOYEE_ID) USING INDEX;






    1번쿼리
    SELECT /*+ GATHER_PLAN_STATISTICS ORDERED */
    E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.EMP_KIND
    , K1.HOME_PHONE_NUMBER
    , K2.OFFICE_PHONE_NUMBER
    FROM EMPLOYEE E, EMPLOYEE_KIND1 K1, EMPLOYEE_KIND2 K2
    WHERE E.EMPLOYEE_ID = K1.EMPLOYEE_ID(+)
    AND E.EMPLOYEE_ID = K2.EMPLOYEE_ID(+)
    AND E.EMPLOYEE_ID = 133;

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

    ----------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
    ----------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 5 |
    | 1 | NESTED LOOPS OUTER | | 1 | 1 |00:00:00.01 | 5 |
    | 2 | NESTED LOOPS OUTER | | 1 | 1 |00:00:00.01 | 4 |
    | 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 1 |00:00:00.01 | 2 |
    |* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 |00:00:00.01 | 1 |
    | 5 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_KIND1 | 1 | 1 |00:00:00.01 | 2 |
    |* 6 | INDEX UNIQUE SCAN | PK_EMPLOYEE_KIND1 | 1 | 1 |00:00:00.01 | 1 |
    | 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_KIND2 | 1 | 0 |00:00:00.01 | 1 |
    |* 8 | INDEX UNIQUE SCAN | PK_EMPLOYEE_KIND2 | 1 | 0 |00:00:00.01 | 1 | ->비효율이 발생한다고 하셨는데.
    ----------------------------------------------------------------------------------------------------

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

    4 - access("E"."EMPLOYEE_ID"=133)
    6 - access("K1"."EMPLOYEE_ID"=133)
    8 - access("K2"."EMPLOYEE_ID"=133)


    2번쿼리
    SELECT /*+ GATHER_PLAN_STATISTICS ORDERED */
    E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.EMP_KIND
    , K1.HOME_PHONE_NUMBER
    , K2.OFFICE_PHONE_NUMBER
    FROM EMPLOYEE E
    LEFT OUTER JOIN
    EMPLOYEE_KIND1 K1 ON (
    E.EMPLOYEE_ID = K1.EMPLOYEE_ID AND E.EMP_KIND = '1'
    )
    LEFT OUTER JOIN
    EMPLOYEE_KIND2 K2 ON (
    E.EMPLOYEE_ID = K2.EMPLOYEE_ID AND E.EMP_KIND = '2'
    )
    WHERE E.EMPLOYEE_ID = 133;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -ROWS +PREDICATE'));
    ----------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
    ----------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 5 |
    | 1 | NESTED LOOPS OUTER | | 1 | 1 |00:00:00.01 | 5 |
    | 2 | NESTED LOOPS OUTER | | 1 | 1 |00:00:00.01 | 4 |
    | 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 1 |00:00:00.01 | 2 |
    |* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 |00:00:00.01 | 1 |
    | 5 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_KIND1 | 1 | 1 |00:00:00.01 | 2 |
    |* 6 | INDEX UNIQUE SCAN | PK_EMPLOYEE_KIND1 | 1 | 1 |00:00:00.01 | 1 |
    | 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_KIND2 | 1 | 0 |00:00:00.01 | 1 |
    |* 8 | INDEX UNIQUE SCAN | PK_EMPLOYEE_KIND2 | 1 | 0 |00:00:00.01 | 1 | -- 여기에서는 1로 잡히네요
    ----------------------------------------------------------------------------------------------------


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

    4 - access("E"."EMPLOYEE_ID"=133)
    6 - access("K1"."EMPLOYEE_ID"=133)
    filter(NVL("E"."EMP_KIND",'1')=CASE WHEN ("K1"."EMPLOYEE_ID" IS NOT NULL) THEN '1' ELSE '1' END )
    8 - access("K2"."EMPLOYEE_ID"=133)
    filter(NVL("E"."EMP_KIND",'1')=CASE WHEN ("K2"."EMPLOYEE_ID" IS NOT NULL) THEN '2' ELSE '2' END )
    3번쿼리
    SELECT /*+ GATHER_PLAN_STATISTICS ORDERED */
    E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.EMP_KIND
    , K1.HOME_PHONE_NUMBER
    , K2.OFFICE_PHONE_NUMBER
    FROM EMPLOYEE E, EMPLOYEE_KIND1 K1, EMPLOYEE_KIND2 K2
    WHERE DECODE(E.EMP_KIND, '1', E.EMPLOYEE_ID) = K1.EMPLOYEE_ID(+)
    AND DECODE(E.EMP_KIND, '2', E.EMPLOYEE_ID) = K2.EMPLOYEE_ID(+)
    AND E.EMPLOYEE_ID = 133;

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

    ----------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
    ----------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 4 |
    | 1 | NESTED LOOPS OUTER | | 1 | 1 |00:00:00.01 | 4 |
    | 2 | NESTED LOOPS OUTER | | 1 | 1 |00:00:00.01 | 4 |
    | 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 1 |00:00:00.01 | 2 |
    |* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 |00:00:00.01 | 1 |
    | 5 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_KIND1 | 1 | 1 |00:00:00.01 | 2 |
    |* 6 | INDEX UNIQUE SCAN | PK_EMPLOYEE_KIND1 | 1 | 1 |00:00:00.01 | 1 |
    | 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE_KIND2 | 1 | 0 |00:00:00.01 | 0 |
    |* 8 | INDEX UNIQUE SCAN | PK_EMPLOYEE_KIND2 | 1 | 0 |00:00:00.01 | 0 | -- 0으로 나타납니다.
    ----------------------------------------------------------------------------------------------------

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

    4 - access("E"."EMPLOYEE_ID"=133)
    6 - access("K1"."EMPLOYEE_ID"=DECODE(NVL("E"."EMP_KIND",'1'),'1',"E"."EMPLOYEE_ID";))
    8 - access("K2"."EMPLOYEE_ID"=DECODE(NVL("E"."EMP_KIND",'1'),'2',"E"."EMPLOYEE_ID";))

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.30 15:15 신고  댓글주소  수정/삭제

      2번째 쿼리에서 비효율이 발생한 이유는 옵티마이져의 잘못입니다. view merging 이 발생하여 view 가 해체되었기 때문입니다. 108 페이지의 Plan을 보시면 view가 해체되지 않고 남아 있습니다.

      dbms_xplan.display_cursor의 옵션에서 +outline 옵션을 추가하시면 오라클이 merge 힌트를 사용한 것을 볼 수 있을 것입니다. 이 힌트를 no_merge 로만 바꿔주시면 문제가 해결될 것입니다.

      이것은 옵티마이져의 실수인데 이때 튜너가 개입하여 더좋은 실행계획을 만들어 주어야 합니다.

      제가 일하고 있는곳의 프로젝트의 환경이 10g이므로 11.2 에서 직접 테스트해보지 못한점을 양해바랍니다.
      감사합니다.

    • Favicon of http://www.interpark.com BlogIcon 피아체 2010.04.30 15:33 신고  댓글주소  수정/삭제

      잘 됩니다^^.
      NO_MERGE 힌트를 어디다 써야 할지를 모르겠네요..그냥 AND ROWNUM >= 0 으로 처리 했는데...
      암튼 책 너무 너무 잘 보고 있습니다.

  4. 타락천사 2010.05.11 11:41 신고  댓글주소  수정/삭제  댓글쓰기

    P.120
    Subsumtion => subsumption 이 맞는거죠 ?
    영어 단어 찾다가 ㅡ_ㅡ;;
    네이버 영어 사전에 없는데...
    포섭 (subsumtion) 라는 의미가 있나보네요..
    다른분들도 찾을까 그냥 댓글 둡니다.

  5. 타락천사 2010.05.11 17:09 신고  댓글주소  수정/삭제  댓글쓰기

    P.138
    2.18 부분이요
    CRSW 가 제목이나, 내용에서 언급되는 부분(P.141)은 RSW 로 언급되고 있습니다.
    ( 제목이나 내용을 통해서 보면, CRSW 가 맞는 것으로 생각되네요 )
    그리구 2.19 에서는 새로 추가된 URSW 를 소개 하고 있습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.05.11 17:22 신고  댓글주소  수정/삭제

      지적에 감사드립니다.
      해당 부분은 오타가 아닙니다.
      CRSW 와 URSW 모두 RSW로 묶을 수 있습니다.
      141 페이지 에 설명된 부분은 RSW 로 될 수밖에 없습니다. 왜냐하면 파라미터는 CRSW 와 URSW를 구분하지 않으므로 RSW라고 한것이고 11.2 부터 RSW라는 약어가 사용되고 세분화 되었다는 것을 이야기한 것이기 때문 입니다.

      감사합니다.

  6. 뽀로로 2010.07.14 02:18 신고  댓글주소  수정/삭제  댓글쓰기

    책 p.194에 "Pivot 절을 Case + Group By로 변환하라" 라는 타이틀이요.
    혹시 Group By + Transpose 가 맞는 것은 아닌지요.
    문의 드립니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.07.14 10:31 신고  댓글주소  수정/삭제

      안녕하세요. 뽀로로님
      문의 하신 제목은 파라미터가 pivot2 인 경우에 해당하는 것입니다.

      책의 제목은 pivot1 에 해당하는 것 입니다.
      Default로 적용할 경우 pivot1 이 적용되기 때문에 타이틀은 "Pivot 절을 Case + Group By로 변환하라" 이 맞습니다.

      하지만 pivot2 인 경우는 말씀하신것처럼 별도의 타이틀이 필요한데 책을 집필할 때에도 이부분에서 고민을 하였습니다. pivot1과 povot2를 별도의 장으로 만들고 각각의 타이틍을 달면 해결되기는 하는데 반대 의견이 많아 그러지 못햇습니다.

  7. 2010.08.26 11:43  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.26 11:12 신고  댓글주소  수정/삭제

      oraking님 반갑습니다.
      unique 인덱스가 존재해야 하는 이유는 답이 달라지기 때문입니다. unique 인덱스로 엑세스하면 단 한건만 나오므로 건수에 영향을 미치지 않습니다. 하지만 unique 인덱스가 아니라면 여러건이 나올 수 있으므로 건수가 달라지지요.

      가령 부서와 직원을 조인할때 부서에 해당하는 직원은 여러명일 수 있습니다. 직원쪽 테이블에 아우터 조인을 걸었고 select 절에도 직원쪽 컬럼을 사용하지 않았다고 하더라도 직원쪽 테이블은 제거할 수 없겠습니다. 건수가 달라지니까요.

      감사합니다.

  8. 2010.08.26 11:51  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.26 11:50 신고  댓글주소  수정/삭제

      옵타마이져는 답이달라지는 경우(건수가 달라지거나 값이다른 경우)는 아우터 쪽을 삭제하지 않습니다. 공통코드 쪽에 조인컬럼으로 unique 인덱스가 없으면 조인을 하여 결과건수가 같음을 보장하지 않기 때문에 아우터 쪽을 삭제할 수 없게된다는 뜻입니다. 77 페이지 를 실행해 보시기 바랍니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.26 12:51 신고  댓글주소  수정/삭제

      아... 질문이 바뀌었네요.
      제대로 이해하셨습니다. 설명하신 대로 조인결과가 한건임을 보장해야 제거가 가능하다는 겁니다. unique 인덱스가 필수 겠지요.

  9. 2010.08.26 12:06  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.26 12:48 신고  댓글주소  수정/삭제

      Unique 인덱스가 존재해야 OE가 일어납니다. FK는 필요없습니다.

      77페이지는 je가 아니고 oe 입니다. oe는 11g 부터 가능하다고 밝히고 있고 fk 가 필요없다고 나와있습니다. 아마 다른 페이지를 보신듯 합니다. 페이지와 버젼확인 바랍니다.

      10g에서 없는 기능이 많으므로 테스트가 되지 않습니다. 효울적인 실습을 위하여 11g R2로 테스트 하시기 바랍니다.

      감사합니다.

  10. 무궁무진 2011.02.24 17:45 신고  댓글주소  수정/삭제  댓글쓰기

    오타/오류 정정해서 책을 쭈욱 보다가 p170 10번째줄 "E1에 대하여 JESS가 수행되어" 부분에 JESS -> JESJ 오타인것 같아서 적어봅니다.

  11. 오수영 2011.08.09 11:00 신고  댓글주소  수정/삭제  댓글쓰기

    105 Page
    3) Lateral View는 마치 스칼라 서브쿼리처럼 동작하므로 Sort Mege Join이나 hash Join을 사용할 수 없고

    에서 Sort Merge Join이 Sort Mege Join으로 표기 되었네요 ^^

  12. 최진수 2011.11.29 00:56 신고  댓글주소  수정/삭제  댓글쓰기

    P 35에서 위의 SQL에 해당하는 SQL은 구절에서 위의 QB에 해당하는 SQL은 으로 변경되어야 할 것 같네요

  13. 이재현 2013.04.15 18:52 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 이재현입니다.

    한가지 궁금한게있습니다. ㅠ

    P.132 ) SSU 는 PART 2 Heuristic Query Transformation 이라고 되어있는데..

    해당 트레이스 파일에는.. p.135

    ***********************************************************
    Cost-Based Subquery Unnesting
    ***********************************************************
    SU: Unnestiong query ....

    이렇게 나오는데요.. 먼가좀 이상해요..ㅠㅠ

    설명좀 부탁드려도 될까요?? ㅠ

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




유수익님이 질문하신글(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-오타와 오류등록  (26) 2010.04.20
저자와의 대화  (36) 2010.04.20
The Logical Optimizer  (61) 2010.04.05
Posted by extremedb

댓글을 달아 주세요

  1. 2010.04.21 08:54  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  2. 2010.04.26 17:11  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  3. 피아체 2010.04.30 09:04 신고  댓글주소  수정/삭제  댓글쓰기

    전부다 비밀 댓글 인데 오타와 오류도 전부 비밀댓글로 달아 놓으셨는데..
    공유차원에서 오픈하면 안될까요?

    책의 다른 버전에서의 차이점이라든지는 오타는 공유되어져야 할것 같은데..

    095 PAGE 에서 FPD 설명중에 ROWNUM, RANK 등의 분석 함수를 사용할 경우 FPD 가 실패한다고 했는데
    제가 11g R2를 깔았는데 여기에서는 FPD가 성공하네요.
    물론 ROWNUM 은 실패 하는데 분석함수에서는 성공하는것 같습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.30 09:29 신고  댓글주소  수정/삭제

      비밀댓글인 이유는 독자가 비밀로 하길 원해서 입니다.
      제가 임의로 설정한것이 아니지요. 그리고 개인적인 내용이 있어서 공유하기는 좀 그렇습니다. 이점 양해 바랍니다.

      비밀 댓글이라 하더라도 오타라고 인정되는 것은 저자와 집필진의 심사를 거쳐 결과를 올리기 때문에 큰문제는 없을것 입니다.

      11.2 에서 FPD가 되는 문제도 테스트를 거쳐 11.2 에서 문제가 해결되었다면 올리도록 하겠습니다.
      감사합니다.

  4. 조장환 2010.05.12 13:32 신고  댓글주소  수정/삭제  댓글쓰기

    영광입니다.

  5. 유수익 2010.05.20 13:40 신고  댓글주소  수정/삭제  댓글쓰기

    아래의 플랜을 볼때 customer테이블을 읽고 각 row별로 scalar subquery를 읽는것 같은데
    customer에서 151건에 대하여 index를 4228번을 읽는데 어떻게 해서 4228번인가요?
    (내용. 2.11 LV중 110페이지 query결과중에서)

    SELECT s.cust_id, s.cust_year_of_birth,
    s.sales_cust.prod_count,
    s.sales_cust.channel_count,
    s.sales_cust.amount_tot
    FROM (SELECT c.cust_id, c.cust_year_of_birth,
    (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
    FROM customers c
    WHERE c.cust_year_of_birth= 1987) s ;

    -------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 151 |00:00:00.01 | 149 | | | |
    | 1 | SORT GROUP BY | | 151 | 151 |00:00:00.15 | 9313 | 4096 | 4096 | |
    | 2 | PARTITION RANGE ALL | | 151 | 3230 |00:00:00.14 | 9313 | | | |
    | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 4228 | 3230 |00:00:00.11 | 9313 | | | |
    | 4 | BITMAP CONVERSION TO ROWIDS | | 4228 | 3230 |00:00:00.06 | 6678 | | | |
    |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | 4228 | 134 |00:00:00.03 | 6678 | | | |
    | 6 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 151 |00:00:00.01 | 149 | | | |
    | 7 | BITMAP CONVERSION TO ROWIDS | | 1 | 151 |00:00:00.01 | 3 | | | |
    |* 8 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 | 1 |00:00:00.01 | 3 | | | |
    -------------------------------------------------------------------------------------------------------------------------------------

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

    1 - SEL$3
    3 - SEL$3 / S@SEL$3
    6 - SEL$F5BB74E1 / C@SEL$2

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.05.20 14:57 신고  댓글주소  수정/삭제

      customer 의 결과는 151건 이지만 sales 테이블에 조인하려면 파티션 28개에 모두 엑세스 해야 합니다. 즉 특정월을 지정 하지 않았으므로 모든파티션을 엑세스 하는것 입니다.

      151(고객수) x 28(파티션갯수) = 4228 입니다.
      감사합니다.

  6. 유수익 2010.05.24 15:43 신고  댓글주소  수정/삭제  댓글쓰기

    - 실행계획 순서가 어떻게 되나요?
    d department와 v를 sort merge join 한 후 max(v.sal)을 filter 체크하는건가요?
    아니면 id기준 5번의 view 안에 한번 묶이는건가요? start 항목을 보면 후자 같은데..
    설명 부탁드립니다.(요즈음 재미있게 보고 있습니다.)
    - 10g에서 실행
    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) ;


    -------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------------
    | 1 | MERGE JOIN | | 1 | 1 |00:00:00.01 | 8 | 1 | | | |
    | 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 6 |00:00:00.01 | 4 | 0 | | | |
    | 3 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 6 |00:00:00.01 | 2 | 0 | | | |
    |* 4 | SORT JOIN | | 6 | 1 |00:00:00.01 | 4 | 1 | 2048 | 2048 | 2048 (0)|
    |* 5 | VIEW | | 1 | 1 |00:00:00.01 | 4 | 1 | | | |
    | 6 | HASH GROUP BY | | 1 | 1 |00:00:00.01 | 2 | 1 | 801K| 801K| 296K (0)|
    | 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 20 |00:00:00.01 | 2 | 1 | | | |
    |* 8 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 20 |00:00:00.01 | 1 | 1 | | | |
    | 9 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
    | 10 | VIEW | | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
    | 11 | SORT GROUP BY | | 1 | 1 |00:00:00.01 | 2 | 0 | 2048 | 2048 | 2048 (0)|
    | 12 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 20 |00:00:00.01 | 2 | 0 | | | |
    |* 13 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 20 |00:00:00.01 | 1 | 0 | | | |
    -------------------------------------------------------------------------------------------------------------------------------------

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

    4 - access("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID";)
    filter("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID";)
    5 - filter("V"."SAL"=)
    8 - access("JOB_ID"='ST_CLERK')
    13 - access("JOB_ID"='ST_CLERK')

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.05.24 16:00 신고  댓글주소  수정/삭제

      10g에서는 해당 기능(URSW)이 작동하지 않습니다.
      plan 이 삐뚤하게 나오네요.
      번거로우시겠지만 메일로 다시한번 저에게 내용을 보내주시기 바랍니다.
      주실때 dbms_xplan.display_cursor 의 포맷을 'advanced allstats last' 로 주시기바랍니다.
      감사합니다.

  7. 유수익 2010.05.24 16:52 신고  댓글주소  수정/삭제  댓글쓰기

    메일 주소를 제가 몰라서요.
    네. 11g에서는 URSW가 작동하는것을 확인했습니다. 10g에서는 안되는것도 확인했구요.
    다만 제가 플랜을 이해하지 못해서 글을 올렸습니다.위 실행계획의 순서가 어떻게 되는지 이해가
    안가서요. 즉 sort aggregation이 sort merge join 이후에 filter처럼 조인된느지 아니면 view(5번) 안데 포함 된후
    조인되는지 궁굼합니다. 메일 주소를 알려주시면 제가 정리된 플랜으로 보내드릴게요. tistory의 게시판이 공백을 제거하는
    것 같습니다..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.05.24 17:03 신고  댓글주소  수정/삭제

      메일 주소는 블로그 우측상단의 그림 밑에 있습니다.
      plan이 삐뿔게 나와서 파악하기가 어렵네요.
      메일로 보내주시기 바랍니다.
      유수익님이 예전에 제게 이메일 주셨던 걸로 기억합니다만.....

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.05.25 09:33 신고  댓글주소  수정/삭제

      아래는 실행순서 입니다.
      1) 먼저 2~3 번을 수행하고
      2) 5~13번을 수행해서
      3) id 4번에서 sort merge 조인을 하게 됩니다.

      한가지 유의 사항은 id 5번에서 서브쿼리집합의 결과로 filter 가 수행됩니다.
      즉 9~13 번의 서브쿼리 결과집합을 이용하여 6~8 의 결과집합을 filter 하는것입니다.
      아래는 최종적으로 Transformation 된 SQL 입니다.

      SELECT d.department_id, d.department_name, v.sal
      FROM department d, (SELECT /*+ no_merge */
      department_id, SUM (salary) AS sal
      FROM employee
      WHERE job_id = 'ST_CLERK'
      GROUP BY department_id ) v
      WHERE d.department_id = v.department_id
      AND v.sal = (SELECT MAX (v.sal) --> filter 수행
      FROM (SELECT department_id, SUM (salary) AS sal
      FROM employee
      WHERE job_id = 'ST_CLERK'
      GROUP BY department_id ) v) ;


      -----------------------------------------------------------------------------------
      | Id | Operation | Name | Starts A-Rows | Buffers |
      -----------------------------------------------------------------------------------
      | 1 | MERGE JOIN | | 1 1 | 8 |
      | 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 6 | 4 |
      | 3 | INDEX FULL SCAN | DEPT_ID_PK | 1 6 | 2 |
      |* 4 | SORT JOIN | | 6 1 | 4 |
      |* 5 | VIEW | | 1 1 | 4 |
      | 6 | HASH GROUP BY | | 1 1 | 2 |
      | 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 20 | 2 |
      |* 8 | INDEX RANGE SCAN | EMP_JOB_IX | 1 20 | 1 |
      | 9 | SORT AGGREGATE | | 1 1 | 2 |
      | 10 | VIEW | | 1 1 | 2 |
      | 11 | SORT GROUP BY | | 1 1 | 2 |
      | 12 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 20 | 2 |
      |* 13 | INDEX RANGE SCAN | EMP_JOB_IX | 1 20 | 1 |
      -----------------------------------------------------------------------------------

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

      4 - access("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID";)
      filter("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID";)
      5 - filter("V"."SAL"=)
      8 - access("JOB_ID"='ST_CLERK')
      13 - access("JOB_ID"='ST_CLERK')

  8. 2010.06.22 14:24  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.06.23 13:27 신고  댓글주소  수정/삭제

      안녕하세요.

      문의하신 SQL을 보면 가장 바깥쪽 뷰때문에 SORT UNIQUE STOPKEY 이 발생되고 전체건을 SCAN 하고 또한 SORT 작업이 이루어 짐으로써 성능이 저하 되었습니다.

      SELECT /*+ gather_plan_statistics */ *
      FROM (SELECT col1
      FROM ( SELECT /*+ INDEX(T1 TEST1_TEST) */
      DISTINCT col1
      FROM test1 t1
      WHERE col1 IS NOT NULL
      ORDER BY col1)
      WHERE rownum <= 1);

      ----------------------------------------------------------------------------------------
      | Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
      ----------------------------------------------------------------------------------------
      | 1 | VIEW | | 1 |00:00:00.03 | 28 | |
      |* 2 | COUNT STOPKEY | | 1 |00:00:00.03 | 28 | |
      | 3 | VIEW | | 1 |00:00:00.03 | 28 | |
      |* 4 | SORT UNIQUE STOPKEY| | 1 |00:00:00.03 | 28 | 2048 (0)|
      |* 5 | INDEX FULL SCAN | TEST1_TEST | 10000 |00:00:00.01 | 28 | |
      ----------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------
      2 - filter(ROWNUM<=1)
      4 - filter(ROWNUM<=1)
      5 - filter("COL1" IS NOT NULL)


      위의 SQL에서 안쪽의 DISTINCT 와 바깥쪽의 ROWNUM 을 만나지 않게 하면 SORT UNIQUE STOPKEY 가 발생하지 않습니다. 가장 쉬운 방법은 아래와 같습니다.

      아래처럼 해보시기 바랍니다.

      SELECT /*+ gather_plan_statistics */ *
      FROM (SELECT col1
      FROM ( SELECT /*+ INDEX(T1 TEST1_TEST) */
      DISTINCT col1, dense_rank() over(order by col1) as rank
      FROM test1 t1
      WHERE col1 IS NOT NULL
      ORDER BY col1)
      WHERE rank <= 1);

      ------------------------------------------------------------------------------------------
      | Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
      ------------------------------------------------------------------------------------------
      | 1 | VIEW | | 1 |00:00:00.01 | 2 | |
      | 2 | SORT UNIQUE NOSORT | | 1 |00:00:00.01 | 2 | |
      |* 3 | VIEW | | 10 |00:00:00.01 | 2 | |
      |* 4 | WINDOW NOSORT STOPKEY| | 10 |00:00:00.01 | 2 | |
      |* 5 | INDEX FULL SCAN | TEST1_TEST | 11 |00:00:00.01 | 2 | |
      ------------------------------------------------------------------------------------------

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

      3 - filter("RANK"<=1)
      4 - filter(DENSE_RANK() OVER ( ORDER BY "COL1";)<=1)
      5 - filter("COL1" IS NOT NULL)

      정확히 11건만 SCAN 하였고 sort 도 발생하지 않습니다.

  9. 2010.06.23 13:24 신고  댓글주소  수정/삭제  댓글쓰기

    자세한 설명 감사드립니다., 분석함수를 이용하는 방법이 있었네요 ;ㅂ;

  10. 아삽 2010.06.29 17:55 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하십니까.
    수석님의 글들로부터 많은 도움을 받고 있습니다.
    감사드리면서 질문 하나 드릴까 합니다.
    p.95 에서의 언급과는 달리 p.253 에서는 FPD 기능이 분석함수에서 사용되고 있다고 되어 있고 실행결과 트레이스도 보았습니다.
    그래서 반가운 마음에 테스트해 보았는데 잘 되지 않네요.
    버전은 11.1.0.7 입니다.
    아래에 실행결과 트레이스와 10053 트레이스 붙였습니다.
    왜 그런 것일까요?

    ---------------------------------------------------------------------------------------------
    | Id | Operation | Name | A-Rows | A-Time | Buffers |
    ---------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 108 |00:00:00.01 | 36461 |
    |* 1 | VIEW | | 108 |00:00:00.01 | 36461 |
    | 2 | WINDOW NOSORT | | 918K|00:00:15.62 | 36461 |
    | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 918K|00:00:15.62 | 36461 |
    | 4 | INDEX FULL SCAN DESCENDING | IDX_SALES_01 | 918K|00:00:11.94 | 2459 |
    ---------------------------------------------------------------------------------------------

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

    1 - SEL$2 / from$_subquery$_001@SEL$1
    2 - SEL$2
    3 - SEL$2 / SALES@SEL$2
    4 - SEL$2 / SALES@SEL$2

    Outline Data
    -------------

    /*+
    BEGIN_OUTLINE_DATA
    IGNORE_OPTIM_EMBEDDED_HINTS
    OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
    DB_VERSION('11.1.0.7')
    FIRST_ROWS(1)
    OUTLINE_LEAF(@"SEL$2";)
    OUTLINE_LEAF(@"SEL$1";)
    NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1";)
    INDEX_DESC(@"SEL$2" "SALES"@"SEL$2" ("SALES"."AMOUNT_SOLD";))
    END_OUTLINE_DATA
    */

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

    1 - filter("AMT"<=TO_NUMBER(:V_TOP_RANK))


    ============
    10053 trace
    ============
    CBQT: Considering cost-based transformation on query block SEL$1 (#0)
    ********************************
    COST-BASED QUERY TRANSFORMATIONS
    ********************************
    FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
    FPD: Current where clause predicates "from$_subquery$_001"."AMT"<=:B1

    try to generate transitive predicate from check constraints for query block SEL$1 (#0)
    finally: "from$_subquery$_001"."AMT"<=:B1

    FPD: Considering simple filter push (pre rewrite) in query block SEL$2 (#0)
    FPD: Current where clause predicates ??

    .
    .

    ***********************************
    Cost-Based Join Predicate Push-down
    ***********************************
    JPPD: Checking validity of push-down in query block SEL$1 (#1)
    JPPD: Checking validity of push-down from query block SEL$1 (#1) to query block SEL$2 (#2)
    Check Basic Validity for Non-Union View for query block SEL$2 (#2)
    JPPD: JPPD bypassed: No valid join condition found.
    JPPD: No valid views found to push predicate into.
    kkqctdrvTD-cleanup: transform(in-use=5288, alloc=8580) :
    call(in-use=900, alloc=16360), compile(in-use=109776, alloc=123340), execution(in-use=2364, alloc=4060)

    kkqctdrvTD-end:
    call(in-use=900, alloc=16360), compile(in-use=101220, alloc=123340), execution(in-use=2364, alloc=4060)

    JPPD: Applying transformation directives
    JPPD: Checking validity of push-down in query block SEL$1 (#1)
    JPPD: No valid views found to push predicate into.
    query block SEL$1 (#1) unchanged
    FPD: Considering simple filter push in query block SEL$1 (#1)
    "from$_subquery$_001"."AMT"<=TO_NUMBER(:B1)
    try to generate transitive predicate from check constraints for query block SEL$1 (#1)
    finally: "from$_subquery$_001"."AMT"<=TO_NUMBER(:B1)

    FPD: Considering simple filter push in query block SEL$2 (#2)
    ??
    Final query after transformations:******* UNPARSED QUERY IS *******
    SELECT "from$_subquery$_001"."CUST_ID" "CUST_ID","from$_subquery$_001"."AMOUNT_SOLD" "AMOUNT_SOLD","from$_subquery$_001"."AMT" "AMT" FROM (SELECT "SALES"."CUST_ID" "CUST_ID","SALES"."AMOUNT_SOLD" "AMOUNT_SOLD",RANK() OVER ( ORDER BY "SALES"."AMOUNT_SOLD" DESC ) "AMT" FROM "TLO"."SALES" "SALES";) "from$_subquery$_001" WHERE "from$_subquery$_001"."AMT"<=TO_NUMBER(:B1)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.06.30 09:41 신고  댓글주소  수정/삭제

      그렇군요.
      P. 95는 분석함수의 값으로 FILTER 하지 않았기 때문에
      FPD가 발생되지 않은것 입니다. P.253은 분석합수의 값으로 인라인뷰 바깥에서 FILTER 로 이용하고 있습니다.

      PLAN을 보면 FPD 가 어떤 이유에서 실행되지 않았습니다. 일단 amount_sold 컬럼이 not null 로 되어 있어야 FPD가 발생됩니다. 이부분을 먼저 체크 바랍니다.
      그래도 안된다면 저에게 10053 TRACE 파일를 메일로 보내주시기 바랍니다. 메일 주소는 블로그 우측 상단에 있습니다.

      그리고 실행 하실때 책과 다른 부분이 있는데 Predicate Information 에 보면 변수에 TO_NUMBER 함수를 사용하고 있습니다. 변수를 NUMBER 형으로 변경시켜 실행해 주세요.
      감사합니다.

    • 아삽 2010.06.30 21:08 신고  댓글주소  수정/삭제

      답변 감사합니다.
      변수타입을 NUMBER 로 바로잡으니 해결되었습니다. ^^

  11. KT 2010.08.11 10:56 신고  댓글주소  수정/삭제  댓글쓰기

    2.11. LV(Lateral View)의 정의를 보면(page.105),
    "Lateral View"는 결과 건수에 영향을 미치지 못하는 스칼라 인라인뷰 이다."라고
    정의하였습니다.
    3.11절에서 JPPD기능을 사용시 Lateral View가 필수라고 하였는데,
    이때... JPPD의 인라인뷰의 경우는 내용에 따라 얼마든지 결과건수에 영향을 줄 수 있는
    것으로 보는데... 이것이 말씀하신 SCALAR INLINE VIEW 형태의 LV 정의에 합당한건가요?

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.11 11:20 신고  댓글주소  수정/삭제

      KT님 반갑습니다.
      좋은 질문입니다.
      LV의 정의는 105페이지가 아니라 104 페이지의 윗부분 입니다.
      말씀하신부분은 105페이지의 가장 윗부분에 나오는 SQL을 설명하는 부분입니다. 즉 해당 SQL에 한정된다는 겁니다. 책에서도 이렇게 범위를 한정하고 있습니다. "1) 위 SQL의 경우 Lateral view는 결과건수에 영향을 미치지 못하는 스칼라인라인 뷰이다" 그뒤에 이유가 따라 나옵니다.

      위에서 언급한 SQL 이외에 다른 SQL 이라면 말씀하신것처럼 내용에 따라 얼마든지 결과건수에 영향을 줄 수 있습니다.
      도움이 되셨나요?

  12. KT 2010.08.11 13:45 신고  댓글주소  수정/삭제  댓글쓰기

    흠. 그렇쿤요. 감사합니다.

    "위 SQL의 경우"란 단서가 있어서 그렇게 생각했지만 확실히 하기위해 확인해봤습니다.

    하지만 글 자체가 예제를 통해서 LV의 정의를 정리하는 듯한 문맥이라 충분히 오해의 소지가 있습니다.

  13. salvationism 2011.01.26 14:54 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 책을 산지는 좀 된거같은데
    그동안 스마트폰으로 지하철에서 틈틈히 책PPT를
    봤었는데 이제서야 책을 보고있습니다. 책이 역시
    훨 낫군요.. 로지컬질문한 다른분 댓글을 보면
    2010년 초반인데 저는 이제 보고있으니 제가 좀 게을렀음을
    느끼면서 자극받고 있습니다.
    앞으로 질문이 많을거 같은데 자주 들리겠습니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.27 17:03 신고  댓글주소  수정/삭제

      반갑습니다.
      PPT를 보시고 책을 보신다면 예습을 하고 들어오신 셈이네요. 아직 늦지 않앗으니 꼭 정복하시기 바랍니다.

  14. salvationism 2011.01.28 23:14 신고  댓글주소  수정/삭제  댓글쓰기

    _optimizer_native_full_outer_join 이 10204에서는 default 가 off 인데...
    만일 10204에서 native_full_outer_join 쓸 상황이 있다면 오동규님은 어떻게 하시나요?
    확신이 없을때는 과감하게 쓰기도 그렇고 정보를 찾아도 side effect가 없다고 할 수 있는건 아니니까요
    히든 파라메터의 default를 바꾸는건 session level 이라고 쳐도 항상 조심 스럽네요.

    확신이 없는 파라메터를 보면 default 값이 왜 저거 일까? 저는 항상 이유가 있으니까 default 값이 off 겠지라는 base를 깔고 들어가는데.. (default 라는건 대부분의 상황에서 무난한 값이라는 전제?)
    잘못된 관점일까요?? 물론 설정을 바꿔야하는 잘알려진 히든 파라메터도 좀 있지만요.

    오동규님의 관점을 한번 듣고 싶습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.29 16:57 신고  댓글주소  수정/삭제

      반갑습니다.
      저는 문서화 된 파라미터는 철저한 테스트 후에 변경합니다. 하지만 문서화 되지 않은 파라미터(히든 파라미터)의 Default 값을 바꾸지 않습니다. 왜냐하면 그 값을 바꾸었을때 어떤 일(Bug)이 발생할 지 누구도 알 수 없습니다. 일단 10.2.0.4에서 적용사례가 없다는 것입니다. 적용사례가 있어야 버그를 발견할 수 있고, 버그패치가 나올 수 있겠죠.

      변경을 하실려면 방법이 없는것은 아닙니다. 오라클사의 Confirm을 받으시면 됩니다만, 경험상으로 보면 Confirm을 해주지 않았습니다.

      결국 Default 값을 그대로 둔다는 의견입니다.
      감사합니다.

  15. salvationism 2011.01.31 11:17 신고  댓글주소  수정/삭제  댓글쓰기

    [Hint 사용 패러다임]
    과거에는 주로 책이건 실무건 대부분의 사람들이 이런식의 Hint를 사용 했었습니다.
    select /*+ READING(a b) USE_NL(a b) */ a.employee_id, b.department_name
    from employee a, department b;

    어느 때 부터인가 트랜드를 따라가는 사람들은 이런식으로 Hint를 줍니다.
    select /*+ READING(a) USE_NL(b) */ a.employee_id, b.department_name
    from employee a, department b;

    제가 보기엔 그 어느 때 부터가 query transformation에 의하여 실제로 Hint가 저렇게 사용되는 것을 보면서
    저렇게 쓰기 시작했고 그것이 확산된 것으로 보고 있는데 제 생각이 맞는지요?

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.31 13:12 신고  댓글주소  수정/삭제

      salvationism 님 반갑습니다.
      생각하신것이 맞습니다.
      use_nl(a b)로 사용했더라도 옵티마이져가 내부적으로 leading(a b) use_nl(b) 로 바꾸어 버립니다.
      이블로그의 글Internal Hint Transformation을 참조하시기 바랍니다. 아래의 주소로 가시면 됩니다.
      http://scidb.tistory.com/entry/Internal-Hint-Transformation

      감사합니다.

  16. salvationism 2011.02.08 15:12 신고  댓글주소  수정/삭제  댓글쓰기

    (수행환경 11gR2 window XP)

    SELECT department_id, job_id, SUM (sum_sal) / SUM (cnt) AS avg_sal
    FROM (SELECT department_id, job_id, COUNT (salary) cnt,
    SUM (salary) sum_sal
    FROM employee
    GROUP BY department_id, job_id) --> 미리 Group By 하여 건수를 줄임
    GROUP BY GROUPING SETS (department_id, job_id);

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | 31 |00:00:00.01 | 46 | 3 | 3 | | | |
    | 1 | TEMP TABLE TRANSFORMATION | | 1 | | 31 |00:00:00.01 | 46 | 3 | 3 | | | |
    | 2 | LOAD AS SELECT | | 1 | | 0 |00:00:00.01 | 11 | 0 | 1 | 264K| 264K| 264K (0)|
    | 3 | HASH GROUP BY | | 1 | 107 | 20 |00:00:00.01 | 7 | 0 | 0 | 755K| 755K| 1138K (0)|
    | 4 | TABLE ACCESS FULL | EMPLOYEE | 1 | 107 | 107 |00:00:00.01 | 7 | 0 | 0 | | | |
    | 5 | LOAD AS SELECT | | 1 | | 0 |00:00:00.01 | 10 | 1 | 1 | 264K| 264K| 264K (0)|
    | 6 | HASH GROUP BY | | 1 | 1 | 12 |00:00:00.01 | 6 | 1 | 0 | 751K| 751K| 850K (0)|
    | 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662C_2240CB | 1 | 1 | 20 |00:00:00.01 | 6 | 1 | 0 | | | |
    | 8 | LOAD AS SELECT | | 1 | | 0 |00:00:00.01 | 8 | 0 | 1 | 264K| 264K| 264K (0)|
    | 9 | HASH GROUP BY | | 1 | 1 | 19 |00:00:00.01 | 3 | 0 | 0 | 743K| 743K| 1238K (0)|
    | 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662C_2240CB | 1 | 1 | 20 |00:00:00.01 | 3 | 0 | 0 | | | |
    | 11 | VIEW | | 1 | 1 | 31 |00:00:00.01 | 8 | 2 | 0 | | | |
    | 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662D_2240CB | 1 | 1 | 31 |00:00:00.01 | 8 | 2 | 0 | | | |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------

    미리 group by 해서 건수를 줄여 temp table의 buffer를 줄이는 튜닝방법에서 확인결과 group by를 미리하지 않는것보다
    memory를 더 소모하는 것으로 보이는데 미리 group by 해서 temp에 적재하여 그것을 계속 사용하는 것이라면
    왜 memory 사용량이 왜이리 클까요??
    수치를 보면 계속 group 함수를 쓰는 것처럼 보입니다만

    최종적인 쿼리는 다음과 같이 변함이 없네요.
    SELECT /*+ NO_STATS_GSETS */ "from$_subquery$_001"."DEPARTMENT_ID" "DEPARTMENT_ID","from$_subquery$_001"."JOB_ID" "JOB_ID",SUM("from$_subquery$_001"."SUM_SAL";)/SUM("from$_subquery$_001"."CNT";) "AVG_SAL" FROM (SELECT "EMPLOYEE"."DEPARTMENT_ID" "DEPARTMENT_ID","EMPLOYEE"."JOB_ID" "JOB_ID",COUNT("EMPLOYEE"."SALARY";) "CNT",SUM("EMPLOYEE"."SALARY";) "SUM_SAL" FROM "TLO"."EMPLOYEE" "EMPLOYEE" GROUP BY "EMPLOYEE"."DEPARTMENT_ID","EMPLOYEE"."JOB_ID";) "from$_subquery$_001" GROUP BY GROUPING SETS ("from$_subquery$_001"."DEPARTMENT_ID", "from$_subquery$_001"."JOB_ID";)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.09 13:03 신고  댓글주소  수정/삭제

      반갑습니다.
      질문하신 메모리 사용량 증가는 Buffers가 아니라 Used-Mem 항목입니다. Buffers는 감소했으므로 제가 의도한 결과와 일치합니다. 다만 아래의 SQL의 경우는 group by를 두번만 하면 되기 때문에 Hash Area를 많이 소모하지 않을 것입니다.

      SELECT department_id, job_id, avg (salary)AS avg_sal
      FROM employee e
      GROUP BY GROUPING SETS (department_id, job_id);

      글의 의도는 PGA 사용량이 아니라 수행시간과 BUFFERS에 대한 관점으로 이해하시면 됩니다. emp 테이블의 건수가 작기 때문에 먼저 group by 하는것이 별로 효율적이지 못한것 처럼 보일 수 있습니다. 하지만 건수가 몇억건 정도 된다면 미리 group by를 하여 건수를 확 줄여놓으면 GROUP BY를 두번더 하게 되더라도 수행시간과 Block I/O 량은 획기적으로 줄어들 것입니다.



      먼저 group by 할것이냐 말것이냐는 일종의 trade off(block I/O 관점 vs PGA 사용량 관점 )가 될 수 있습니다. 즉 먼저 GROUP BY를 하여 건수를 획기적으로 줄일 수 있다면 PGA 사용량이 조금 증가해도 Block I/O가 차이가 클것 이므로 적용할 수 있습니다.

      건수(92만건)가 충분하지 않지만 trade off 의 관점에서 아래의 SQL 두개를 비교해 보시기 바랍니다.

      SELECT channel_id, promo_id, SUM (sum_amt) / SUM (cnt) AS avg_sal
      FROM (SELECT channel_id, promo_id, COUNT (amount_sold) cnt,
      SUM (amount_sold) sum_amt
      FROM sales
      GROUP BY channel_id, promo_id)
      GROUP BY GROUPING SETS (channel_id, promo_id);

      SELECT channel_id, promo_id, AVG (amount_sold)
      FROM sales
      GROUP BY GROUPING SETS (channel_id, promo_id);

      Block I/O와 수행시간의 두가지 관점은 먼저 group by하는 것이 나을 것이며 PGA 사용량 관점으로는 두번째가 날을 것입니다. 3.14 절의 GBP도 같은 관점으로 보시면 됩니다.

  17. salvationism 2011.02.10 12:31 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요^^ 질문을 자주 드리네요.
    outline data에 나오는 Hint들.. 즉 undocument hint에 대한 종류와 용도를
    알려고 한다면 해당 정보를 얻을 수 있는 좋은 곳이 있을까요??

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.10 12:31 신고  댓글주소  수정/삭제

      salvationism님 열정이 대단하십니다.
      11gR2 에서 v$sql_hint를 조회해보시기 바랍니다.
      거기에 모든 힌트가 다 있습니다. 하지만 용도는 분명치 않은 것이 단점입니다. 하지만 힌트의 카테고리는 있습니다.

      감사합니다.

  18. Stargazer 2012.01.16 14:25 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 .. 좋은내용 몰래몰래 잘보고 있습니다.

    다름이 아니라 11g 부터 _optimizer_unnest_all_subqueries 히든 파라미터가 생겼습니다.
    내용은 단지 :enables unnesting of every type of subquery 이렇게만 나와 있습니다.

    이 파라미터가 저희 사이트에서 미치는 영향이 좀 많아서요..
    내용을 알려고 합니다.

    말그대로 변환 가능한 모든 subquery들을 unnest하는건지..
    비용은 고려하지 않고 HQT처럼 진행을 하는지...

    혹시 _optimizer_unnest_all_subqueries 파라미터에 대한 내용과
    어떤경우에 동작하는지 알수 있을까해서
    질문을 드립니다.

    좋은 정보 늘 감사드립니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2012.01.17 17:26 신고  댓글주소  수정/삭제

      반갑습니다. 말씀하신 파라미터는 모든 종류의 서브쿼리에 unnesting을 가능하게 합니다. 여기서 중요한것은 unnesting을 하는것이 아니라 단지 가능하게 한다는 것입니다. Cbqt라면 costing을 해야하기 때문입니다. 반대로 hqt라면 무조건 unnesting 됩니다. 물론 파라미터가 true일때의 이야기 입니다. 반대로 파라미터를 false라면 모든 서브쿼리는 unnesting이 불가능합니다.

      파라미터를 false로 하는것은 모든서브쿼리가 filter로 처리됨으로 매우 위험합니다. 실행계획이 한가지 operation으로 고정되므로 권장하지 않습니다. 파라미터가 false인 상태에서 Unnesting 할수있는 방법은 서브쿼리에 unnest 힌트를 사용하면 되기는 합니다. 하지만 모든 서브쿼리에 힌트를 쓴다는 것은 비효율적 이겠죠.

      감사합니다.

  19. Stargazer 2012.01.20 12:02 신고  댓글주소  수정/삭제  댓글쓰기

    기존 _unnest_subquery 히든파라미터가 있는데..
    왜 굳이 _optimizer_unnest_all_subqueries 이 파라미터가 나왔는지요..



    참고로 db버젼입니다.

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE 11.2.0.3.0 Production
    TNS for HPUX: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production


    해당 버전에서 파라미터 comment는 아래와 같습니다.

    _unnest_subquery : enables unnesting of complex subqueries
    _optimizer_unnest_all_subqueries : enables unnesting of every type of subquery

    두 파라미터간 관계가 있는건지요.

    _optimizer_unnest_all_subqueries fale로 했을때 _unnest_subquery 값이 true로 되어 있어도 subquery가 unnest가 되지 않는지요?



원래 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)파일을 올린다. 에피소드, 서문, 감사의 글, 책의 구성과 책을 읽는 방법, 목차, 종문, 참조문서, 색인 등을 볼 수 있다.
   

The Logical Optimizer_Preview.pdf

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-오타와 오류등록  (26) 2010.04.20
저자와의 대화  (36) 2010.04.20
The Logical Optimizer  (61) 2010.04.05
Posted by extremedb

댓글을 달아 주세요

  1. 이전 댓글 더보기
  2. 디비딥 2010.04.06 01:02 신고  댓글주소  수정/삭제  댓글쓰기

    앗 눈팅만 하다가 책이 출판된다고 하길래 너무 기대되서 댓글 남깁니다.
    내공은 별로 없어 이해가 될지 모르겠지만 잘 보겠습니다. 어서 출판해 주세요^^

  3. 홍택현 2010.04.06 10:51 신고  댓글주소  수정/삭제  댓글쓰기

    정말 고생하셨습니다 수석님~~
    어서 쾌차하시길 기원하겠습니다. ^^

  4. daemon 2010.04.06 15:34 신고  댓글주소  수정/삭제  댓글쓰기

    오라클을 공부하며 오동규님의 블로그에 항상 도움을 받아 왔었습니다.
    이번으로 2번째 리플을 달게 되는데요 감사한 마음을 가지면서도 감사의 글을 자주 못올려 죄송했습니다.
    정말 감사한 책이 이제 다음달이면 나오는군요 .. 오랜 시간 책을 쓰시느라 정말 고생하셨습니다.
    블로그도, 책도 항상 감사하다는 말뿐이 드릴말씀이 없습니다.

  5. 타락천사 2010.04.06 16:05 신고  댓글주소  수정/삭제  댓글쓰기

    축하드립니다.
    꼭 봐야겠네요 !!
    항상 건강하시구요 !!
    고고씽

  6. 초보DBA 2010.04.06 18:32 신고  댓글주소  수정/삭제  댓글쓰기

    아직 시장에는 안풀린것인가요? yes24나 인터파크등에서 보이지가 않네요
    출간 축하드립니다.

  7. 마늘장아찌 2010.04.07 10:05 신고  댓글주소  수정/삭제  댓글쓰기

    항상 책에 대한 갈증이 있지만 막상 서점에 가보면
    딱히 손에 잡히는 책은 별로 없더라구요.
    가끔 사이트에 들어와, 실무 경험이 느껴지는 글을 읽으며
    제가 모르는 부분에 대하여 생각을 많이하고 또 부족한 저자신을 더욱 채찍질 하는 계기가 되곤 합니다.
    출간을 다시한번 축하드리며, 조만간 꼭 구입해서 읽어 보겠습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.07 13:18 신고  댓글주소  수정/삭제

      마늘장아찌님의 이야기처럼 이 책은 현장의 문제점에 대한것 입니다.
      내공을 확장시키는 기회가 되었으면 합니다.
      감사합니다.

    • 마늘장아찌 2010.04.07 17:51 신고  댓글주소  수정/삭제

      올리신 preview 화일 잘읽었습니다.
      개인적으로 조금 아쉬운 부분은 epilog에 있는 명제,필자,독자 라는 단어에 조금 불만입니다. 조금 딱딱한 느낌이 옵니다. 그때그때의 상황에 적절한 가상의 시나리오로 처리하고 결론을 도출한다면 좀더 쉽게 와닿을수 있을것 같아요. 예를들면 존고든의 에너지버스나 마케팅의 천재가된 맥스 같은 책을 보면 가상의 인물이 처하는 상황에 대한 문제들에 대해 해결책을 제시함으로써 저자가 이해시키고자 하는 결론을 독자가 쉽게 이해할수있도록 진행해 주는 부분이 있습니다.물론 그런류의 책과 분야가 좀 다른건 인정하지만 향후 그러한 시나리오로 e-learning등 다양한 컨텐츠로도 제작이 되길 바라는 마음에 몇자 적어 봤습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.07 21:01 신고  댓글주소  수정/삭제

      논증은 epilog에만 있습니다. 전체적인 집필 의도를 밝힌 부분이기 때문에 그렇습니다. 본문의 내용은 그렇게 딱딱하지 않습니다. 자기개발서의 특징이 감성을 자극하는 면이 있습니다. 하지만 논리가 약한 면도 있지요. 논리와 논증을 익히시려면 입문서로 '논증의 탄생'을 읽어 보시기 바랍니다.
      조언에 감사드립니다.

  8. baind 2010.04.07 15:13 신고  댓글주소  수정/삭제  댓글쓰기

    책의 출판을 축하드립니다. 귀한 책 누구보다도 즐거이. 그리고 깊게 읽을 것을 약속드립니다.^^
    4월20일 그날이 기대 되는군요^^
    수고많으셨습니다. ㅎㅎ

  9. 눈팅독자 2010.04.08 21:27 신고  댓글주소  수정/삭제  댓글쓰기

    항상 오동규님의 블로그에서 좋은 정보를 얻어 가고 있는 많은 오라클 스터디생중에 한명입니다. 책 출간 진심으로 축하 드립니다. logical optimizer에 대한 내용에 너무 목말라 있었습니다. 반드시 구입해서 토시 하나 빼지 않고 완독 하며 공부 하겠습니다. 감사합니다.

  10. 봉봉아빠 2010.04.10 21:02 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 비법서를 내놓으심을 진심으로 축하드립니다. 튜닝이라고 수박 겉핥기만 하던 저에게는 가뭄 속 단비같은 보물입니다. 꼭 구입해서 수박 속 까지 싹싹 비워 먹도록 하겠습니다 ^^

  11. Favicon of http://imnews.tistory.com BlogIcon XOXOSQL 2010.04.11 08:49 신고  댓글주소  수정/삭제  댓글쓰기

    드디어 책이 나오는군요

    작가 친필사인 이벤트 같은건 안하시나요? ^^

    수고하셨습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.13 01:21 신고  댓글주소  수정/삭제

      안녕하세요. 오동규 입니다.
      출간세미나를 하려고 했지만 허리가 별로 좋지 않아서 힘들것 같습니다.
      방문과 성원에 감사드립니다.

  12. Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.21 12:52 신고  댓글주소  수정/삭제  댓글쓰기

    한가지 주의사항은 초보자가 띠지 내용에 혹 해서 사면 안된다는 것입니다. 초보자 용이 아니기 때문입니다. 하지만 이 블로그의 구독자라면 충분히 보실 수 있을것 입니다.

  13. 김시연 2010.04.23 16:33 신고  댓글주소  수정/삭제  댓글쓰기

    책 출간을 진심으로 축하드립니다~! Preview만 봐도 얼마나 많은 정성과 노력을 투자하셨는지 잘 알겠네요. 그리고 논증의 탄생이란 책도 구매를 해봐야겠습니다. ㅎㅎ 그럼 주말 잘보내세요~!

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.23 16:57 신고  댓글주소  수정/삭제

      감사합니다.
      시연님께서 좋은 평가를 해주시니 부끄럽습니다.
      출간을 하고나니 아쉽기도 하고 그렇습니다.
      좋은 주말 되세요.

  14. 로또 2010.05.16 09:11 신고  댓글주소  수정/삭제  댓글쓰기

    너무 많이 늦었지만 출간을 축하드립니다.
    오랜 고통이 결실을 보셨군요.

    글 끝부분에 1인 4역하셨다는 부분...
    정말 어마어마한 노력과 인내심에 감탄을 금할 수 없습니다.
    제일먼저 건강부터 챙기셔야겠네요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.05.17 00:21 신고  댓글주소  수정/삭제

      로또님 반갑습니다.
      일요일임에도 불구하고 방문하셨네요.

      한방쿼리의 댓글 의견은 저도 공감합니다. 노스트라 다무스도 미래를 맞추지 못한것 같습니다. 하지만 아쉽게도 ERP 패키지를 설계하는 분들 중의 일부가 유지보수를 생각하지 않는 경우가 가끔 있었습니다.

      다행히 허리는 출간직후에 좋아졌습니다.
      저처럼 오래 않아 계신분들은 하루에 한번의 가벼운 체조가 도움이 된다고 합니다.
      감사합니다.

  15. Favicon of http://blog.naver.com/david2kim BlogIcon [리베™] 2010.05.24 12:36 신고  댓글주소  수정/삭제  댓글쓰기

    수요에 비해서 책을 너무 조금 출판하신게 아닌지??
    일이 있어서 나갔다가 서점에서 직접 구매하려고 하니, 생각보다 쉽지 않더군요.
    대형 서점 3곳을 뒤진 후에야 허탕을 치고, 예약을 해서 다음날 방문해서 받았습니다.
    아무래도 좋은 내용의 도서인 만큼 수요자분들이 많은듯 합니다.
    항상 블로그 글귀들을 보면서 많은 도움들을 받았었는데... 이번 도서를 통해서 또 한번 정리를 하는 기회를 갖게
    되는 것 같습니다. 대박나시길 기원합니다. 감사합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.05.24 15:51 신고  댓글주소  수정/삭제

      제 책을 구하기 위해 고생을 많이 하셨네요.
      참고로 오픈메이드는 오프라인 서점중에는 교보문고와 반디앤루니스만 거래합니다. 아마 리브로나 영풍문고를 가신듯 합니다.

      그리고 리베 tm님 말씀처럼 수요예측을 잘못한것 같습니다. 회사에서 이 추세 대로 라면 3~4개월 후에 재고가 바닥 날것 같다고 하더군요. 너무 빨리 절판 되는 것이 아닌지 걱정입니다.

      솔직히 옵티마이져라는 주제가 너무 무겁고 어려운 내용이라 수요가 이렇게 많을지 예측하지 못하였습니다.
      여러가지로 수고를 끼쳐드려 죄송합니다.

  16. 김시연 2010.06.11 10:44 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요~. 외부 컨설팅 수행하고 어제 회사에 복귀했습니다. 컨설팅시에 환경이 11gR2였는데 Logical Optimizer책이 많은 도움이 됬습니다. 늦었지만 감사 인사드립니다.~ 그럼 주말 잘보내세요.

  17. 2010.12.22 11:08  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.12.22 11:17 신고  댓글주소  수정/삭제

      반갑습니다.
      우선 책의 예제는 HR 과 SH 스키마를 이용한 것 입니다. 말씀하신 예제는 hr 스키마 입니다. hr 에 있는 COUNTRIES 테이블은 sh에 있는것과 다릅니다. 왜냐하면 IOT로 되어 있기 때문입니다. 테이블 자체가 인덱스의 몸체이므로 테이블을 방문하지 않아도 됩니다.

      즐거운 성탄절 보내시기 바랍니다.
      감사합니다.

  18. Favicon of http://blog.naver.com/darkturtle BlogIcon 타락천사 2010.12.22 14:37 신고  댓글주소  수정/삭제  댓글쓰기

    역시나... 감사합니다.

  19. Favicon of http://www.perfectreplicawatch.co.uk/ BlogIcon wrist watches 2011.08.06 16:33 신고  댓글주소  수정/삭제  댓글쓰기

    항상 책에 대한 갈증이 있지만 막상 서점에 가보면
    딱히 손에 잡히는 책은 별로 없더라구요.

  20. Favicon of http://bestshoppingbox.com BlogIcon Air Jordan Shoes 2011.11.18 00:17 신고  댓글주소  수정/삭제  댓글쓰기

    Glad to visit your blog. Thanks for great post that you share to us!

  21. Favicon of http://www.minnikesko.dk/ BlogIcon Nike Shox sko 2012.03.30 11:36 신고  댓글주소  수정/삭제  댓글쓰기

    Glad to visit your blog. Thanks for great post that you share to us!