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



bypass_ujvc 힌트와 관련하여 필자와 의견을 달리하는 전문가도 있음을 밝혀둔다. 특정 버젼에서 특정 상황에서 힌트를 확실히 이해하고 성능문제가 큰 경우일 때만 사용한다면 된다는 것이다. 제약사항을 4가지나 붙였으므로 공감이 가는 부분이 있다. 아래의 댓글을 반드시 읽어보기 바란다. 2010-04-19 (추가)

ANSI SQL
UPDATE문은 오라클과 달리 FROM 절이 존재하며 여러 테이블 혹은 뷰와 자유로이 조인할 수 있다.
아래의 SQL을 보자.

 

UPDATE DEPT

    SET DEPT_COUNT = E.CNT

FROM DEPT D,

(SELECT DEPTNO, COUNT(*) CNT

FROM EMP

WHERE JOB = ‘CLERK’

GROUP BY DEPTNO) E

WHERE D.DEPTNO = E.DEPTNO ;

 

위의 SQL MS-SQL 서버에서 사용할 수 있는 UPDATE문이지만 오라클에서 사용할 수 없다. 위의 SQL을 오라클로 바꾼다면 조인이 불가능하므로 아래처럼 스칼라 서브쿼리와 서브쿼리를 사용해야 한다.

 

UPDATE DEPT D

SET DEPT_COUNT = (SELECT COUNT(*)  

FROM EMP E

WHERE E.DEPTNO = D.DEPTNO

AND E.JOB = ‘CLERK’)

WHERE EXISTS (SELECT 1

FROM EMP E

WHERE E.DEPTNO = D.DEPTNO

AND E.JOB = ‘CLERK’) ;

 

중복 조인을 피할 수 있나? 

언뜻 보기에도 비효율이 극심하게 드러난다. EMP와 조인이 두 번 발생한 것이다. 중복된 조인을 피하기 위해서 아래처럼 인라인뷰와 스칼라 서브쿼리를 혼합하여 사용할 수 있지만 이 또한 중복 조인을 피할 수 없다. 보기에는 중복조인이 없는 것처럼 보이지만 Query Transformation을 공부하였다면 중복 조인이 보일 것이다.


UPDATE (SELECT d.deptno, d.dept_count,
               (SELECT COUNT (*)
                  FROM emp e
                 WHERE e.deptno = d.deptno
                   AND e.job = 'CLERK') cnt
          FROM dept d)
   SET dept_count = cnt
 WHERE cnt > 0;


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

| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)|

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

|   0 | UPDATE STATEMENT               |                |     4 |    64 |     5   (0)|

|   1 |  UPDATE                        | DEPT           |       |       |            |

|*  2 |   FILTER                       |                |       |       |            |

|   3 |    TABLE ACCESS FULL           | DEPT           |     4 |    64 |     3   (0)|

|   4 |    SORT AGGREGATE              |                |     1 |    11 |            |

|*  5 |     TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    11 |     2   (0)|

|*  6 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |     1   (0)|

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

Predicate Information (identified by operation id):

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

   2 - filter( (SELECT COUNT(*) FROM "SCOTT"."EMP" "E" WHERE "E"."DEPTNO"=:B1

              AND "E"."JOB"='CLERK')>0)

   5 - filter("E"."JOB"='CLERK')

   6 - access("E"."DEPTNO"=:B1)

 

Predicate Information을 보면 ID 기준으로 2번에서 서브쿼리가 FILTER로 사용되었고 6번에서 다시 스칼라 서브쿼리가 사용되었으므로 두 번 조인이 발생한 것이다. 이해가 되지 않는 독자는 스칼라 서브쿼리를 서브쿼리로 변환하라 글을 읽기 바란다.

 

또 다른 제약

조인이 안 된다는 제약을 피하기 위해 VIEW 혹은 인라인뷰를 UPDATE 하곤 한다. 하지만 아래의 새로운 예제를 본다면 또 다른 제약이 있음을 알 수 있다.

 

create or replace view v_emp

as

select  e.empno, e.ename, e.job, e.sal, d.dname, d.deptno

from   emp e, dept d

where  e.deptno = d.deptno;

 

update V_EMP

   set dname = ‘NO_DEPT’ --> DEPT 쪽을 UPDATE 하고 있으므로 에러

 where empno = ‘7369’;

 

ERROR at line 2:

ORA-01779: cannot modify a column which maps to a non key-preserved table

 

Deptemp 1:N의 관계인데 1쪽을 Update 하지 못하는 제약을 만나게 된다. 이 제약을 피하기 위해서 bypass_ujvc 힌트를 사용하는 사람이 있는데 절대 사용하면 안 되는 힌트이다. 힌트를 해석하자면 Updatable Join View Check By-Pass(무시) 하겠다는 뜻이다. 실제로 Wrong Result(답이 잘못됨)가 나오므로 사용해서는 안 된다.

 

끝없는 제약

ODS 시스템이나 데이터를 이행하기 위한 Temp성 테이블에는 Key가 없는 경우가 많다. 아래는 PK를 제거한 상태에서 인라인뷰를 UPDATE 해보았다. 단순히 사번이 들어오면 부서번호가 부서 테이블에 존재하는지 체크하여 급여를 UPDATE 하는 SQL이다. 
 

ALTER TABLE SCOTT.DEPT MODIFY CONSTRAINT PK_DEPT DISABLE; 

Update (select a.empno, a.ename, a.sal, b.dname

          from emp a, dept b

         where a.deptno = b.deptno

           and a.empno = 7369)
set sal = 5000;


ORA-01779: cannot modify a column which maps to a non key-preserved table

 

Key를 사용할 수 없으므로 뷰 혹은 인라인뷰를 update할 때 키 보존 제약이 걸리게 된다. 이때 마찬가지로 bypass_ujvc를 사용하면 에러는 피할 수 있지만 결과를 보장 하지 않는다. 이 힌트는 건널목 신호등에 빨강 불이 들어왔지만 알아서 건너가시오. 자동차에 부딪혀도 책임지지 않습니다.” 로 비유할 수 있다.

 

Bypass_ujvc 힌트를 사용하지 않고 해결해야 해

위에서 언급한 세가지 제약조건(조인이 안됨, 뷰에서 1 update 안됨, 뷰에서 키가 없으면 update 불가)과 한가지 문제(중복 조인)를 피할 수 있는 방법이 있다.  

1) 조인이 안 되는 문제와 중복 조인문제 해결

 

MERGE INTO dept d

USING (SELECT deptno, COUNT (*) cnt

         FROM emp

        WHERE job = 'CLERK'

        GROUP BY deptno) e

   ON (e.deptno = d.deptno)

 WHEN MATCHED THEN

UPDATE SET d.dept_count = e.cnt;

 

Merge successfully completed.

 

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

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

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

|   1 |  MERGE                         | DEPT    |      1 |00:00:00.01 |      18 |

|   2 |   VIEW                         |         |      3 |00:00:00.01 |      13 |

|   3 |    NESTED LOOPS                |         |      3 |00:00:00.01 |      13 |

|   4 |     NESTED LOOPS               |         |      3 |00:00:00.01 |       9 |

|   5 |      VIEW                      |         |      3 |00:00:00.01 |       7 |

|   6 |       SORT GROUP BY            |         |      3 |00:00:00.01 |       7 |

|*  7 |        TABLE ACCESS FULL       | EMP     |      4 |00:00:00.01 |       7 |

|*  8 |      INDEX UNIQUE SCAN         | PK_DEPT |      3 |00:00:00.01 |       2 |

|   9 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |00:00:00.01 |       4 |

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

Predicate Information (identified by operation id):

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

   7 - filter("JOB"='CLERK')

   8 - access("E"."DEPTNO"="D"."DEPTNO")

 

2) 1쪽이 UPDATE 안 되는 문제 해결

 

MERGE INTO dept d

USING emp e

   ON (e.deptno = d.deptno AND e.empno = '7369')

 WHEN MATCHED THEN
UPDATE SET d.dname = 'NO_DEPT' ;

 

Merge successfully completed.

 

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

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

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

|   1 |  MERGE                         | DEPT    |      1 |00:00:00.01 |       5 |

|   2 |   VIEW                         |         |      1 |00:00:00.01 |       4 |

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

|   4 |     TABLE ACCESS BY INDEX ROWID| EMP     |      1 |00:00:00.01 |       2 |

|*  5 |      INDEX UNIQUE SCAN         | PK_EMP  |      1 |00:00:00.01 |       1 |

|   6 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |00:00:00.01 |       2 |

|*  7 |      INDEX UNIQUE SCAN         | PK_DEPT |      1 |00:00:00.01 |       1 |

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

Predicate Information (identified by operation id):

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

   5 - access("E"."EMPNO"=7369)

   7 - access("E"."DEPTNO"="D"."DEPTNO")

 

 

3) 키가 없으면 UPDATE가 불가한 문제 해결

 

ALTER TABLE SCOTT.DEPT MODIFY CONSTRAINT PK_DEPT DISABLE;

 

MERGE /*+ USE_HASH(D) */ INTO emp e

USING dept d

   ON (e.deptno = d.deptno AND e.empno = 7369)

 WHEN MATCHED THEN
UPDATE SET e.sal = 5000;

 

Merge successfully completed.

 

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

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

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

|   1 |  MERGE                         | EMP    |      1 |00:00:00.01 |      12 |          |

|   2 |   VIEW                         |        |      1 |00:00:00.01 |       9 |          |

|*  3 |    HASH JOIN                   |        |      1 |00:00:00.01 |       9 |  316K (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| EMP    |      1 |00:00:00.01 |       2 |          |

|*  5 |      INDEX UNIQUE SCAN         | PK_EMP |      1 |00:00:00.01 |       1 |          |

|   6 |     TABLE ACCESS FULL          | DEPT   |      4 |00:00:00.01 |       7 |          |

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

Predicate Information (identified by operation id):

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

   3 - access("E"."DEPTNO"="D"."DEPTNO")

   5 - access("E"."EMPNO"=7369)

 


결론

위의 SQL 세가지는 큰 문제 4가지를 해결한 것이므로 익혀서 적재적소에 활용하기 바란다.
MERGE
문의 문법은 매우 간단하다. 하지만 이 문법을 보고 그것을 어디에 어떻게 활용할 것인가는 전혀 다른 문제이다. DBMS의 버전이 올라가면 신기능이 탄생한다. 그때마다기능을 어디에 사용하면 가장 큰 효과가 나타날 것인지를 생각해 보라. 오늘보다 더 발전된 내일의 당신을 위해서.

 

신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blitz.egloos.com BlogIcon blitz고양이 2010.04.14 15:41 신고  댓글주소  수정/삭제  댓글쓰기

    bypass_ujvc 힌트가 그런 문제점이 있었군요. 왜 오라클에서 권장하지 않는지 이제 알겠습니다.
    저 힌트를 이용해서 짠 업데이트 문이 굉장히 빨라서 시간이 많이 걸리는 쿼리에서는 남발하고 있었는데,
    다시 생각해 볼 문제군요.

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

      그렇습니다. 이 힌트를 사용한다는 것은 굉장히 위험 하지요. merge 문을 대신 사용하시면 성능을 유지할 수 있을것 입니다. 감사합니다.

  2. Favicon of http://proud0.tistory.com BlogIcon proud 2010.04.14 17:53 신고  댓글주소  수정/삭제  댓글쓰기

    update 중에서 컬럼으로 조인해서 해당 컬럼을 업데이트하고자 할때는 merge문으로는 안되는 것인가요?
    예를 든다면

    merge into emp e
    using (select deptno
    ,deptno_new
    from dept) d
    on (e.deptno = d.deptno)
    when matched then
    update set e.deptno = d.deptno_new
    ;

    이런 상황일 경우에는 merge를 사용할수 없나요?

  3. 나그네 2010.04.15 11:51 신고  댓글주소  수정/삭제  댓글쓰기

    1. '또 다른 제약'과 '끝없는 제약'으로 구분하셔서 마치 Updatable Join View에 대단히 많은 제약이 있는 것처럼 언급하셨는데, 사실은 둘 다 같은 원인(ORA-01779 : 키 보존 테이블이 아닌 쪽을 Update)에 기인하므로 좀 과장되게 기술했다고 느껴지네요.

    2. "bypass_ujvc 힌트를 사용하는 사람이 있는데, 절대 사용하면 안 되는 힌트"라고 하셨는데, 아래와 같은 경우도 해당되는지 의견을 듣고 싶습니다.

    update (select d.일별매출합, m.매출
    from (select 고객번호, sum(매출) 일별매출합
    from 고객별일별매출
    group by 고객번호) d
    , 고객별월별매출 m
    where d.고객번호 = m.고객번호
    )
    set 매출 = 일별매출합 ;


    3. merge문에 update와 insert를 선택적으로 기술할 수 있게 된 건 10g부터인데, 오라클 버전이 9i일 땐 어떤 방법으로 튜닝하시는지요?


    4. "1쪽이 UPDATE 안 되는 문제를 merge문으로 해결한다"고 하셨는데, bypass_ujvc 힌트 사용에 주의가 필요한 이유가 바로 이런 경우 때문인 것으로 압니다. 만약 예시하신 아래 문장이 수행된 결과를 상상해 보시죠. SQL 작성자는 empno = '7369' 사원의 부서명(dname)을 'NO_DEPT'로 변경하고자 했지만 이 사원과 같은 부서 소속원의 부서명이 일괄적으로 변경된 결과를 낳습니다.

    MERGE INTO dept d
    USING emp e
    ON (e.deptno = d.deptno AND e.empno = '7369')
    WHEN MATCHED THEN
    UPDATE SET d.dname = 'NO_DEPT' ;

    물론 SQL 작성자가 의도한 결과일 수 있으며, 그런 경우라면 문제가 안 되겠죠. 하지만 만약 사용자가 테이블간 카디널리티를 알지 못한 상태에서 단순히 empno = '7369' 사원의 부서명만 바꾸려고 한 것이라면 merge문의 처리 결과는 문제가 될 수 있습니다. 만약 empno 컬럼이 Unique하지 않다면 어떤 결과를 낳을까요? job = 'MANAGER'와 같은 조건이었다면요? 결과를 예측할 수 없죠. 이런 위험한 상황을 피하려고 오라클이 Updatable Join View에 ORA-01779 제약을 두었고, 사용자가 bypass_ujvc 힌트를 명시한 때만 수행되도록 한 것 아닌가요?
    그럼에도 merge문을 이용해 이를 회피할 수 있다고 권고하신다면 앞뒤가 맞지 않습니다. "bypass_ujvc 힌트를 절대 사용해선 안 된다"고 하셨는데, 방금 merge문의 결과는 Updatable Join View에 bypass_ujvc 힌트를 사용한 것과 동일하기 때문이죠.
    이 문제는 어떻게 생각하시는지요?

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

      먼저 의견을 주셔서 감사드립니다. 저는 논리적인 비판을 환영합니다. 말씀을 안하셔도 대충 어느회사 분인지 알 것 같군요.^^

      답변을 드리겠습니다.

      1번:
      제약을 두개로 나눈것은 서로 다르기 때문입니다. key 가 있지만 1쪽을 update할 수 없다는 것과 임시성 테이블에 key가 없는 상황을 구분한 것입니다. 물론 에러는 같은 번호로 나오지만 개념이 다르지요. 이게 왜 문제가 되는지 이해할수 없군요.

      2번:
      오라클사에서 Unsupport Hint 라고 밝힌 상황에서 마치 사용해야 한다고 주장 하시는것 같습니다. 그 이유가 무었입니까? 보여주신 예제는 이유가 되지 못합니다. 약간 느리더라도 안전한 방법을 선택하시길 바랍니다.

      시스템이 업그레이드 되어 힌트가 없어진다면 큰일이 아닐수 없습니다. 그리고 그 힌트에 대해서 버그가 있더라도 버그페치가 나오지 않습니다. 아주 위험 하지요.
      물론 나그네님이 모든 책임을 진다면 할말은 없습니다.

      3번:
      말씀하신대로 9i 에서는 안됩니다. 그래서 약간 느려지는 것은 막을 수 없습니다. 3번에서도 앞뒤 문맥상으로 보면 bypass_ujvc 힌트를 사용할 수 밖에 없다는 것을 간접적으로 이야기 하시는것 같아 안타깝습니다.

      4번:
      그것은 저의 실수 입니다.
      아래처럼 하시면 됩니다. ON 절에만 조건을 추가하고 WHERE 절에 조건이 빠졌군요.

      MERGE INTO dept d
      USING emp e
      ON (e.deptno = d.deptno AND e.empno = '7369')
      WHEN MATCHED THEN
      UPDATE SET d.dname = 'NO_DEPT'
      WHERE e.empno = '7369' ;

      WHERE 절을 추가하는 것이 옳겠죠. bypass_ujvc 힌트를 사용하는것은 위험한 발상입니다.

    • 나그네 2010.04.16 23:59 신고  댓글주소  수정/삭제

      즐거운 금요일 저녁입니다. 어제오늘 너무 바빠 이제야 침착하게 답변할 시간이 생기네요.

      저는 Undocumented 기능을 가급적 쓰지 않으려고 가장 노력하는 사람 중 한 사람입니다. 반면, 숨겨진 Undocumented 기능을 끄집어내 사용하기를 즐기는 사람들도 있습니다. 그런 사람들도 bypass_ujvc 힌트를 사용하는 것만큼은 꺼리죠. 이유는 간단합니다. 이 힌트가 DML 문장에서 사용되기 때문입니다. 요컨대, 잘못 사용하면 데이터를 망가뜨릴 수 있기 때문입니다.
      그래서 저는 이 힌트와 관련된 이슈의 핵심은 데이터 훼손 가능성에 있다고 봅니다. 아래와 같은 쿼리문에서 1쪽 집합(dept)의 조인 컬럼(deptno)에 Unique 인덱스가 없다면 저도 절대 이 힌트를 사용하지 않습니다.

      update /*+ bypass_ujvc */ (
      select e.ename, d.dname from emp e, dept d
      where e.deptno = d.deptno
      )
      set ename = dname;

      당장은 dept 테이블에 중복 레코드가 없더라도 언제고 그럴 가능성이 있기 때문입니다. 오라클 사(社)가 자신이 만들고도 이 힌트 대신 merge문을 사용하라고 권고하는 이유도 여기에 있다고 보는 것이 맞습니다. (우려하시는 것처럼 나중에 혹시 오라클이 이 힌트를 없애더라도, 그것은 이 힌트가 불완전해서가 아니라 데이터를 잘못 갱신할 위험성을 차단하기 위해서일 겁니다.)

      반면, 제가 예로 든 아래 문장의 경우엔 절대 데이터를 잘못 갱신할 가능성이 없습니다. 그래서 저는 업무적으로 성능이 아주 중요할 때, 이 힌트를 요긴하게 사용하곤 합니다. (제가 이 힌트를 적극적으로 사용하라고 권하는 것처럼 호도하셨는데, 질문 내용을 다시 잘 읽어보시면 아래와 같은 패턴의 쿼리에서만 제한적으로 사용하면 문제가 없지 않겠느냐는 의미임을 이해하실 것입니다. 물론 10g에선 가급적 merge문을 사용하는 것이 바람직합니다.)

      update /*+ bypass_ujvc */
      (select d.일별매출합, m.매출
      from (select 고객번호, sum(매출) 일별매출합
      from 고객별일별매출
      where 매출일자 like '201004%'
      group by 고객번호) d
      , 고객별월별매출 m
      where d.고객번호 = m.고객번호
      and m.매출월 = '201004'
      )
      set 매출 = 일별매출합 ;

      이 힌트를 사용하지 않았을 때 약간 느려지는 수준이라고 과소평가(2~3번 답변)하셨는데, 몇 시간 수행되는 프로그램을 1분으로 줄일 때도 많습니다. merge문을 이용해 튜닝해 보셨다면 그 효과가 얼마나 powerful한지 잘 아실 것이라 믿습니다.

      다시 말씀드리지만 문제의 핵심은 데이터에 있다고 생각합니다. Undocumented 힌트라는 이유로, 또는 나중에 이 힌트가 지원되지 않을지 모른다는 이유로 "절대 사용하면 안 된다"는 논리는 너무 확대하여 해석한 것이 아닌가 싶습니다.
      혹시 미래의 어떤 버전에서 이 힌트가 지원되지 않게 되면 어떤 문제가 발생할까요? ORA-01779 에러가 발생하겠죠. 에러 메시지를 만나게 될지언정 값이 잘못 갱신되는 일은 생기지 않습니다. 새로 업그레이드하는 시점에 프로그램을 뜯어고쳐야 하는 게 부담스럽겠지만 오라클 버전을 업그레이드한다면 항상 그 정도의 공수는 늘 수반됩니다. (참고로, 최근 유명한 모 회사는 오라클 버전을 9i에서 10g로 업그레이드하는 프로젝트를 한달 간 진행한 바 있습니다.) 미처 못 고친 프로그램이 있더라도 예외(Exception) 처리를 통해 Alerting이 발생할 뿐, 데이터를 망가뜨리지는 않습니다.

      "1쪽이 UPDATE 안 되는 문제"를 merge문으로 해결할 수 있다고 하셨는데, M쪽 집합을 읽어 1쪽 집합을 update해선 안 됩니다. 이를 막으려고 ORA-01779 제약이 생겼고, 이 제약을 피하려고 bypass_ujvc 힌트까지 생긴 것 아닙니까. 이런 상황에서 merge문이 해법이다? 다행히 M쪽 집합의 조인컬럼에 중복 값이 없다면 잘 수행되겠지만 중복 건이 존재한다면 에러가 발생합니다. 중복 값이 없더라도 제가 앞서 말씀드린 4번 항목 때문에라도 그렇게 사용하는 것은 바람직하지 않습니다. (4번 항목에 대해 실수라고 답변하셨지만, 제 설명을 잘못 이해하신 거 같습니다. WHERE절에 e.empno = '7369' 조건을 추가해도 결과는 마찬가지입니다.)

      "키가 없으면 UPDATE가 불가한 문제"도 merge문으로 해결할 수 있다고 하셨는데, 맞습니다. 그렇게 하면 1쪽 집합에 PK가 정의돼 있지 않아도 갱신이 잘 처리되며, 예상치 못하게 1쪽 집합에 중복 레코드가 입력되더라도 데이터를 잘못 갱신하는 문제만큼은 발생하지 않습니다. ORA-30926 에러가 막아주기 때문이죠. 오라클이 bypass_ujvc 힌트보다 merge문 사용을 권장하는 이유입니다.
      그런데 이것은, bypass_ujvc 힌트가 미래의 어느 버전에서 갑자기 사라졌을 때 우리가 겪게 될 위험성과 같은 정도의 위험성이군요. 데이터를 망가뜨리지는 않지만 예외(Exception) 처리를 통해 Alerting !

      혹시 deprecated 될지도 모른다고 손 놓고 있기보다 적극적으로 문제를 해결하는 것이 더 큰 가치를 주는 상황은 얼마든지 있을 수 있습니다. 그럼에도 "절대 사용하면 안 되는 힌트"라고 경고한다면, 꼭 필요로 하는 분(특히 9i 시스템을 사용하는 분)들을 위축시킬 수 있을 거 같아 주제넘게 의견을 개진해 보았습니다.
      오동규 님도 프로젝트를 수행할 때, A가 정답인 줄 알면서도 눈물을 머금고 B를 선택해야 하는 상황에 자주 맞닥뜨리실 것입니다. 오라클 Hidden 파라미터를 사용하는 것이 바람직하지 않음을 알면서도 어쩔 수 없이 값을 조정해 문제를 해결했던 사례도 있을 것입니다. 마찬가지로, Updatable Join View의 특징과 bypass_ujvc 힌트의 올바른 활용 용도를 숙지하고 적재적소에 사용한다면, 그것이 비록 차선책이어서 꺼림칙할지는 몰라도 눈물을 머금을 일은 아닌 것 같습니다.

      PS) 이 글을 읽는 모든 분께 한마디! 10g 이상 버전을 사용하고 계시다면 오동규 님의 충고를 가볍게 여기지 마시고 가급적 merge문을 사용해 주는 센스, 잊지 마시길..^^

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

      공감이 가는 부분이 많이 있습니다.
      잘 아시는 내용이겠지만 이글을 보는분들을 위하여 두가지는 짚고 넘어가야 할것 같습니다.
      1.
      먼저 Undocument 힌트라고 다 같은 것은 아닙니다.
      예를 들면 BITMAP_TREE 힌트는 Undocument 힌트라도 오라클이 내부적으로 사용하기 때문에 버그등이 있을경우 오라클사에서 지원(버그페치)이 가능합니다. 하지만 bypass_ujvc와 같은 Unsupport 힌트는 그렇지 못하지요.

      2. 1:N 문제 입니다. N쪽을 참조할 필요가 있는 경우 대부분 권장하지 않는 bypass_ujvc를 사용하므로 그렇게 하지말고 MERGE 문을 사용하자는 것입니다. 물론 말씀하신대로 의도적으로 그렇게 해야겠지요. 그런데 어떻게 보면 이것은 당연한 것입니다. 말씀하신 empno = '7369' 사원의 부서명만 update 할수 있는 방법은 세상에 없기 때문입니다.

  4. feelie 2010.04.16 14:02 신고  댓글주소  수정/삭제  댓글쓰기

    내용도 좋고 위의 두분의 질문과 답변에도 무척 감동적 이네요..
    이해하기 힘든 내용이라 다시 책을 보고, 블러그를보니 그래도 정리가 되네요..
    그래도 궁금한점이 있어 물어봅니다.
    1.
    위의 답글 내용에서
    "말씀하신대로 9i 에서는 안됩니다. 그래서 약간 느려지는 것은 막을 수 없습니다" 라고 하셨는데
    9i에서는 insert , update를 구별해서 처리한다는 말씀인가요?

    2.
    또다른 제약에서 1쪽의 집합을 업데이트 할수 업다.
    해결방법은 힌트사용 : bypass_ujvc 사용
    힌트 사용말고 다른 방법이 있는지 궁금합니다..

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

      반갑습니다.
      1번
      9i 에서는 merge 문에 update만 사용할 수 없고 무조건 insert도 있어야 한다는 뜻 입니다.
      10g 에서는 merge 에서 insert 없는 update 문만 사용할 수 있죠.

      2번 아래처럼 사용하시면 됩니다.
      아래는 1쪽을 update 하고 있죠.

      MERGE INTO dept d
      USING emp e
      ON (e.deptno = d.deptno AND e.empno = '7369')
      WHEN MATCHED THEN
      UPDATE SET d.dname = 'NO_DEPT'
      WHERE e.empno = '7369' ;

  5. Ejql 2010.07.30 10:14 신고  댓글주소  수정/삭제  댓글쓰기

    이제는. 무엇을 말씀하시는지 알겠습니다. 하나하나 배운다는것이 정말 힘든것도 느낍니다.
    이해가 되면 기쁘지만. 이해가 안되는 날에는.. 앞날 생각이 먼저 나는군요.

    감사합니다.

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

      너무 급하게 하지 마시기 바랍니다.
      비관적으로 생각하지 마시고 천천히 하나씩 보신다면 내공이 쌓이면서 이해가 가실겁니다. 건승하세요.

  6. 세모변천일 2011.03.08 14:48 신고  댓글주소  수정/삭제  댓글쓰기

    MERGE INTO dept d
    USING emp e
    ON (e.deptno = d.deptno AND e.empno = '7369')
    WHEN MATCHED THEN
    UPDATE SET d.dname = 'NO_DEPT'
    WHERE e.empno = '7369' ;
    위 쿼리에서 꼭 where e.empno = '7369' 이 조건이 들어가야 하나요 ? on절에 기술한 조건이 있는데,
    만약 안들어가면 결과가 어떻게 되나요 ?

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