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




유수익님이 질문하신글(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 https://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 https://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 https://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 https://scidb.tistory.com BlogIcon extremedb 2010.05.24 17:03 신고  댓글주소  수정/삭제

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

    • Favicon of https://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 https://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 https://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 https://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 https://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 https://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 https://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 https://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 https://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 https://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가 되지 않는지요?