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


사용자 삽입 이미지


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

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

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

댓글을 달아 주세요

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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