'Heuristic Query Transformation'에 해당되는 글 2건

  1. 2010.04.20 The Logical Optimizer-Script Download (37)
  2. 2010.01.25 Distinct Elimination : 불필요한 Distinct를 제거하라 (6)

오라클 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 https://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 https://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 https://elahi.tistory.com BlogIcon Sensui™ 2010.04.26 10:42 신고  댓글주소  수정/삭제  댓글쓰기

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

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

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

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

    비밀댓글입니다

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

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

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

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

  10. Favicon of https://dev4u.tistory.com BlogIcon 데브포유 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 https://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!

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

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

SELECT distinct d.department_id, l.location_id

  FROM department d, location l

 WHERE d.location_id = l.location_id ;

 

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

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

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

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

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

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

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

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

Predicate Information:

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

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

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

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

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

Order-by elimination (OBYE)

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

OBYE:     OBYE bypassed: no order by to eliminate.

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

이후생략


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

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

SELECT distinct d.department_id, d.location_id

  FROM department d, location l

 WHERE d.location_id = l.location_id ;


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

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

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

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

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

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

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

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

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

Predicate Information:

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

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

 

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

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

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

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

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

Posted by extremedb

댓글을 달아 주세요

  1. feelie 2010.01.25 09:38  댓글주소  수정/삭제  댓글쓰기

    select list에 기술되는 컬럼명 사용에 이런 분명한 차이도 있었군요.
    사소한 쿼리라도 실행계획을 꼭확인하는 습관을 들이라는 말씀이 이런경우일까요?
    내용잘봤습니다..

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.01.25 10:53 신고  댓글주소  수정/삭제

      핵심을 잘 파악하셨습니다.
      실행계획을 잘 살펴보면 비효율 적인 부분이 보이지요. 이때 Query Transformation이 하는 일을 알고 본다면 문제의 해결이 가능 합니다.

  2. Eddy 2010.01.26 19:19  댓글주소  수정/삭제  댓글쓰기

    "책보다는 블로그가 우선이다."라는 말씀에 감사드립니다.
    책내용의 90%가 블로그에 있어도 아마도 독자의 대부분은 책을 구입할 것입니다.

    좋은 책을 세상에 내어 놓았지만 블로그가 없는 경우가 있어 섭섭함이 있었는데,
    extremedb님의 결정은 참 좋아보입니다. 참 감사한 결정이라고 생각합니다. ㅎㅎ

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.01.27 00:17 신고  댓글주소  수정/삭제

      방형욱님 반갑습니다.
      저와 블로그 구독자에게는 좋은 결정이지만 회사에서 뭐라고 할지 걱정이 되네요...
      감사합니다.

  3. 서상서 2010.02.10 16:15  댓글주소  수정/삭제  댓글쓰기

    버전에 따라 약간의 차이가 있는것 같은 생각이 듭니다. 버전 11.1.0.7.0입니다.
    제가 테스트한 버전에서는 이렇게 풀리면서 더욱 효율적으로 옵티마이저가 생각을 하는것 같은데요.
    옵티마이저가 부서테이블에 DEPT_LOCATION_IX 아마도 이것이 결합인덱스인것 같읍니다.
    인덱스만 가지고 끝내는것 같군요.(location_id가 not null제약조건이 있는것으로 판단)

    location테이블은 엑세스를 하지 않네요.비용도 제시한 것보다 위의것보다 작은것 같구요.
    한번 돌려보았읍니다. 제 로컬PC에서 말이죠.
    SQL> EXPLAIN PLAN FOR
    2 SELECT distinct d.department_id,l.location_id
    3 FROM departments d,locations l
    4 WHERE d.location_id = l.location_id;

    해석되었습니다.

    SQL> SELECT * FROM table(dbms_xplan.display(null,null,'all'));

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3680985111

    ----------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
    -----------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 27 | 270 | 2 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 270 | 2 (0)| 00:00:01 |
    |* 2 | INDEX FULL SCAN | DEPT_LOCATION_IX | 27 | | 1 (0)| 00:00:01 |

    -----------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    1 - SEL$120E9FF1 / D@SEL$1
    2 - SEL$120E9FF1 / D@SEL$1

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    2 - filter("D"."LOCATION_ID" IS NOT NULL)

    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
    1 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]
    2 - "D".ROWID[ROWID,10], "D"."LOCATION_ID"[NUMBER,22]

    26 개의 행이 선택되었습니다.

    결국은 실제로 돌려보지 않고 이런것이 있구나 이렇게 넘어가면 안되겠구나 하는 그런 생각을 갖게 됩니다.
    고맙읍니다.

    버전은 혹시 어떻게 되시나요? 11R1버전에서는 이렇게 실행되었읍니다.
    그리고 많이 감사합니다. 좋은글이요.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.02.10 17:09 신고  댓글주소  수정/삭제

      반갑습니다.
      통계정보의 상태에 따라 인덱스만 탈 수도 있고 FULL TABLE SCAN을 할 수도 있지만 Distinct Elimination이 테이블을 제거할 수는 없습니다. 테이블을 제거하는 기능은 JE(Join Elimination) 이라고 하며 7가지로 분류할 수 있습니다. 자세한 내용은 3월달에 나올 책을 참조 하시기 바랍니다.

      서상서님이 보여주신 기능은 Primary Key-Foreign Key 관계를 이용한 JE 입니다. FK를 삭제하시면 location을 access 할것 입니다.
      감사합니다.