정답의 결과가 틀리다. WHERE 절에 s.sales_cust.prod_cnt > 0 조건이 추가되어야 한다.
자세한 내용은 oraking 님의 댓글을 참조하기 바란다.(2010.09.03)


좋지 않은 상황

한 고객 사에서 전화가 걸려왔는데 성능 문제였다.

특정 SQL이 성능이 좋지 않은데 더욱 상황을 악화시키는 것은 Peak Time에 수십 만 번 실행된다는 것이다. 그런데 전체 건을 조회하는 것이 아니라 고객번호 순으로 10명의 고객에 대한 통계와 실적을 보는 SQL이라고 한다. SQL은 아래와 같으며 환경은 Oracle10g R2 이다. 오라클 Install시 생성되는 SH 스키마를 이용하면 된다.

 

--인덱스 생성

CREATE INDEX ix_cust_birth ON customers (cust_year_of_birth, cust_id);

 

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(c IX_CUST_BIRTH_CUST) LEADING(c) */

       c.cust_id, c.cust_first_name, c.cust_last_name,

       s.prod_cnt, s.channel_cnt, s.tot_amt

  FROM customers c,

       (SELECT s.cust_id,

               COUNT (DISTINCT s.prod_id) AS prod_cnt,

               COUNT (DISTINCT s.channel_id) AS channel_cnt,

               SUM (s.amount_sold) AS tot_amt

          FROM sales s

         GROUP BY s.cust_id) s

 WHERE c.cust_year_of_birth = 1987

   AND s.cust_id = c.cust_id

   AND ROWNUM <= 10;

 

-------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                |               |     10 |    5035 |          |
|*  2 |   HASH JOIN                   |               |     10 |    5035 | 1150K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS     |    151 |     153 |          |
|*  4 |     INDEX RANGE SCAN          | IX_CUST_BIRTH |    151 |       2 |          |
|   5 |    VIEW                       |               |    523 |    4882 |          |
|   6 |     SORT GROUP BY             |               |    523 |    4882 | 8288K (0)|
|   7 |      PARTITION RANGE ALL      |               |    918K|    4882 |          |
|   8 |       TABLE ACCESS FULL       | SALES         |    918K|    4882 |          |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   2 - access("S"."CUST_ID"="C"."CUST_ID")
   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)


CUSTOMERS 테이블은 10건을 정확히 Scan 하였지만 Salse 테이블을 Full Scan하여 비효율이 발생 하였다. 또한 위의 SQL은 Hash Join을 사용 하였으므로 정렬을 보장 하지 않는다.

좋지 않은 상황 + 악조건

먼저 위의 SQL을 보면 Rownum을 사용하므로 CVM(Complex View Merging)이 발생되지 않는다. 만약 발생된다고 해도 Group By가 전체범위로 처리되므로 TOP SQL에서는 대부분 JPPD(Join Predicate Push Down)의 성능이 우월하다. 또한 Nested Loop Join이 아닌 Hash Join이 수행 되었으므로 고객번호가 작은 것부터 나오지 않는다. Sort 문제도 해결해야 하지만 Oracle10g이므로 JPPD Extension 기능이 수행되지 않는다. JPPD Extension이란 Group By + 집계함수나 Distinct가 존재해도 JPPD가 수행되며 Semi/Anti Join시에도 JPPD가 수행되는 획기적인 기능이다. 이 기능은 Oracle11g부터 사용할 수 있다.

 

정답부터 보지말자 실력이 늘지 않는다
지금부터 여러분이 이 문제를 해결해야 한다. 여러분이 해결사 이다. 퀴즈라고 생각하고 문제를 풀어보라. 하지만 절대 답을 먼저 보아서는 안 된다. 충분히 고민한 후에 답을 풀어보고 정답을 보도록 하자.

힌트가 있다

JPPD
기능을 사용할 수 없다면 JPPD의 흉내를 내면 된다. JPPD의 효과를 만들면 된다.
아래쪽의 실행계획과 결론 부분을 보는것도 힌트가 될 수 있다.

제약사항
Sort 가 되어야 하며 Rownum 조건으로 Customers 테이블에 10건만 Scan되어야 한다. 그 10건에 해당하는 고객만 Sales 테이블에 Access 하는 것이 정답이다.
스칼라 서브쿼리를 세 번 사용하는 것은 정답이 아니다. 그것은 막노동에 가깝다. 필자의 블로그를 꾸준히 구독한 독자라면 어렵지 않게 문제를 풀 수 있다.
 

정답: 아래를 드래그 하면 된다.

 

CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT

(prod_cnt NUMBER(5),

 channel_cnt NUMBER(2),     

 tot_amt NUMBER(15,2));

/

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       s.cust_id, s.cust_first_name, s.cust_last_name,

       s.sales_cust.prod_cnt,  -- Alias 가 여기에 사용 되었다.

       s.sales_cust.channel_cnt,

       s.sales_cust.tot_amt

  FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) */

               c.cust_id, c.cust_first_name, c.cust_last_name,

               (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 -- 추후 메인쿼리에서 Alias 가 사용된다.

          FROM customers c

         WHERE c.cust_year_of_birth= 1987

           AND ROWNUM <= 10) s  ;

    

위의 SQL은 JPPD를 수행시킨 효과와 비슷하다. 다른말로 바꾸면 수동으로 Lateral View를 생성한 것이다.
          

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); 

 

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

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

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

|   1 |  VIEW                         |                    |     10 |    1104 |

|*  2 |   COUNT STOPKEY               |                    |     10 |      13 |

|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS          |     10 |      13 |

|*  4 |     INDEX RANGE SCAN          | IX_CUST_BIRTH_CUST |     10 |       3 |

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

 

Predicate Information (identified by operation id):

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

   2 - filter(ROWNUM<=10)

   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)


읽은 블럭수가 무려 5배 정도 차이가 나며 PGA는 사용 하지도 았았다. 자주 수행될수록 성능의 차이는 급격히 벌어질 것이다.
 

결론:

JPPD 기능을 사용할 수 없으므로 JPPD의 흉내를 낸 것이다. JPPD등이 수행되지 않을 때 Logical Optimizer의 한계를 극복할 수 있는 방법은 여러분의 응용력에 달려있다. 옵티마이져가 한계를 드러낼 때 더 좋은 방법을 적용하는 것이 사람의 할 일이 되었다. 물론 그러기 위해서는 Logical Optimizer의 허와 실을 알아야 할 것이다.

 

PS:

만약 Oracle9i Oracle 8.1.7 버전을 사용하고 있다면 위의 정답이 실행되지 않으므로 http://scidb.tistory.com/69 글을 참조하여 사용하기 바란다.


Posted by extremedb

댓글을 달아 주세요

  1. 혈기린 2010.02.04 09:55  댓글주소  수정/삭제  댓글쓰기

    문제를 보는순간 JPPD를 생각했는데 11g부터 지원한다는걸 잊고 있었네요
    스칼라 서버쿼리를 사용하셨군요 참 배울게 많은 블로그 입니다
    얼릉 책 출판해주세요 ㅎㅎ 책이 고픕니다~~

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

      안녕하세요.
      말씀대로 Type을 이용한 스칼라 서브쿼리 입니다.
      책은 내용이 Freezing 되었고 표지 디자인이 진행 되고 있습니다. 설 대목이 기다리고 있으므로 3월 초나 되어야 시중에 풀리게 될 것입니다.

  2. feelie 2010.02.04 12:27  댓글주소  수정/삭제  댓글쓰기

    스칼라서버쿼리를 사용해야겠구나 생각했습니다.
    정답을 보지않고 해보려고 했는데.....
    type을 이런경우에 사용할수 있군요.
    지난내용중 cast(mutiple 인가 하는 내용을 다시 한번 봐야겠네요.
    내용잘봤습니다..

  3. TeLl2 2010.02.05 17:27  댓글주소  수정/삭제  댓글쓰기

    프로젝트 성격상 type을 선언할 수 없는 경우나,
    권한이 없는 개발자의 경우는 쿼리를 수정해서 할 수도 있을거 같습니다.

    SELECT /*+ USE_NL(C S) INDEX(C IX_CUST_BIRTH) */C.CUST_ID,
    C.CUST_FIRST_NAME,
    C.CUST_LAST_NAME,
    COUNT (DISTINCT S.PROD_ID) PROD_CNT,
    COUNT (DISTINCT S.CHANNEL_ID) CHANNEL_CNT,
    SUM (S.AMOUNT_SOLD) TOT_AMT
    FROM (SELECT CUST_ID,
    CUST_FIRST_NAME,
    CUST_LAST_NAME
    FROM CUSTOMERS
    WHERE CUST_YEAR_OF_BIRTH = 1987
    AND ROWNUM <= 10) C,
    SALES S
    WHERE S.CUST_ID(+) = C.CUST_ID
    GROUP BY C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME;

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

      JPPD의 흉내를 내는 것이 아니지만 정답입니다.
      말씀 하신 type을 선언할 수 없는 경우나,
      권한이 없는 개발자의 경우에 최적의 해결책 입니다. 제 책에도 이 내용이 있습니다. 좋은 의견 감사합니다.

  4. 백면서생 2010.02.08 08:30  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 글이 올라오네요.^^
    패키지에서 말고 타입 사용을 참으로 오랜만에 보는거 같습니다.

    가장 일반적인 sql이 안올라온거 같아 올려봅니다.^^

    SELECT /*+ GATHER_PLAN_STATISTICS */
    s.cust_id, s.cust_first_name, s.cust_last_name,
    to_number(substr(sales_cust,1,20)) prod_cnt,
    to_number(substr(sales_cust,21,20)) channel_cnt,
    to_number(substr(sales_cust,41,20)) tot_amt
    FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) NO_MERGE */
    c.cust_id, c.cust_first_name, c.cust_last_name,
    (
    SELECT lpad(COUNT (DISTINCT s.prod_id),20,'0')||
    lpad(COUNT (DISTINCT s.channel_id),20,'0')||
    lpad(SUM (s.amount_sold),20,'0')
    FROM sales s
    WHERE s.cust_id = c.cust_id
    ) AS sales_cust
    FROM customers c
    WHERE c.cust_year_of_birth= 1987
    AND ROWNUM <= 10) s ;

    돌려보지않고 extremedb님 sql을 수정해서 올려서 실제 쿼리가 될지는 모르겠네요.

    값이 20자리를 넘는다면 아시다시피 자리수를 늘여 주던지 제가 별루 좋아하지 않는 ^^
    COUNT (DISTINCT s.prod_id),20,'0')||','||COUNT (DISTINCT s.channel_id)||','||SUM (s.amount_sold)

    후에 substr,instr조합으로 해야 겠죠.^^;

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

      오랜만 입니다.
      백면서생님 말씀대로 가장 일반적인 방법이지요.
      Tomas kyte가 Effective Oracle By Design에서 소개하기도 했었지요.
      감사 합니다.

  5. 2010.02.10 17:16  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  6. oraking 2010.09.06 15:46  댓글주소  수정/삭제  댓글쓰기

    위 내용을 보면서 궁금한게 생겼습니다.


    맨 위 쿼리에서

    WHERE c.cust_year_of_birth = 1987 AND s.cust_id = c.cust_id AND ROWNUM <= 10;

    의 의미는 조인이 성공한 즉 sale 테이블에 customer가 있는 경우의 10건 인데....

    그 아래 정답이나 다른 글을 보면 sale 테이블에 없는 것이 포함된 10건입니다. ....

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

      그렇군요.
      스칼라 인라인뷰나 스칼라서브쿼리는 아우터조인의 의미를 가지게 되므로 아래처럼 WHERE 절에 s.sales_cust.prod_cnt > 0 조건을 추가해야 합니다.

      SELECT s.cust_id, s.cust_first_name, s.cust_last_name,
      s.sales_cust.prod_cnt, -- Alias 가 여기에 사용 되었다.
      s.sales_cust.channel_cnt,
      s.sales_cust.tot_amt
      FROM (SELECT /*+ INDEX(c ix_cust_birth) */
      c.cust_id, c.cust_first_name, c.cust_last_name,
      (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 -- 추후 메인쿼리에서 Alias 가 사용된다.
      FROM customers c
      WHERE c.cust_year_of_birth= 1987) s
      WHERE s.sales_cust.prod_cnt > 0 --> 조건추가
      AND ROWNUM <= 10 ;

      좋은 의견에 감사드립니다.

  7. oraking 2010.09.06 18:30  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 LTO책 카테고리에 써야 할지 몰라 이곳에 문의를 드려 봅니다.

    아래 환경은 11gR2 입니다. 힌트 때문에 동일 쿼리의 수행 시간이 다른데 원인을 모르겠습니다.

    ############################
    # 소요 시간 : 00:00:00.29
    ############################
    SELECT /*+ GATHER_PLAN_STATISTICS INDEX(c IX_CUST_BIRTH_CUST) LEADING(c) */
    c.cust_id, c.cust_first_name, c.cust_last_name,
    s.prod_cnt, s.channel_cnt, s.tot_amt
    FROM customers c,
    (SELECT s.cust_id,
    COUNT (DISTINCT s.prod_id) AS prod_cnt,
    COUNT (DISTINCT s.channel_id) AS channel_cnt,
    SUM (s.amount_sold) AS tot_amt
    FROM sales s
    GROUP BY s.cust_id) s
    WHERE c.cust_year_of_birth = 1987
    AND s.cust_id = c.cust_id
    AND ROWNUM <= 10;

    select * from table(dbms_xplan.display_cursor(null,null,'all ALLSTATS LAST -rows'));

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | 1543 (100)| | | | 10 |00:00:00.20 | 5212 | | | |
    |* 1 | COUNT STOPKEY | | 1 | | | | | | 10 |00:00:00.20 | 5212 | | | |
    |* 2 | HASH JOIN | | 1 | 870 | 1543 (3)| 00:00:19 | | | 10 |00:00:00.20 | 5212 | 836K| 836K| 1232K (0)|
    | 3 | JOIN FILTER CREATE | :BF0000 | 1 | 6493 | 35 (0)| 00:00:01 | | | 151 |00:00:00.01 | 146 | | | |
    | 4 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 6493 | 35 (0)| 00:00:01 | | | 151 |00:00:00.01 | 146 | | | |
    | 5 | BITMAP CONVERSION TO ROWIDS| | 1 | | | | | | 151 |00:00:00.01 | 2 | | | |
    |* 6 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 | | | | | | 1 |00:00:00.01 | 2 | | | |
    | 7 | VIEW | | 1 | 303K| 1507 (3)| 00:00:19 | | | 12 |00:00:00.20 | 5066 | | | |
    | 8 | SORT GROUP BY | | 1 | 172K| 1507 (3)| 00:00:19 | | | 12 |00:00:00.20 | 5066 | 99328 | 99328 |88064 (0)|
    | 9 | JOIN FILTER USE | :BF0000 | 1 | 21M| 1478 (1)| 00:00:18 | | | 7979 |00:00:00.24 | 5066 | | | |
    | 10 | PARTITION RANGE ALL | | 1 | 21M| 1478 (1)| 00:00:18 | 1 | 28 | 7979 |00:00:00.21 | 5066 | | | |
    |* 11 | TABLE ACCESS FULL | SALES | 28 | 21M| 1478 (1)| 00:00:18 | 1 | 28 | 7979 |00:00:00.20 | 5066 | | | |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

    1 - filter(ROWNUM<=10)
    2 - access("S"."CUST_ID"="C"."CUST_ID";)
    6 - access("C"."CUST_YEAR_OF_BIRTH"=1987)
    11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID";))


    Column Projection Information (identified by operation id):
    -----------------------------------------------------------

    1 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40], "S"."TOT_AMT"[NUMBER,2
    "S"."CHANNEL_CNT"[NUMBER,22]
    2 - (#keys=1) "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40], "S"."TOT_AMT
    "S"."CHANNEL_CNT"[NUMBER,22]
    3 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40]
    4 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40]
    5 - "C".ROWID[ROWID,10], "C"."CUST_YEAR_OF_BIRTH"[NUMBER,22]
    6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7924], "C"."CUST_YEAR_OF_BIRTH"[NUMBER,22]
    7 - "S"."CUST_ID"[NUMBER,22], "S"."PROD_CNT"[NUMBER,22], "S"."CHANNEL_CNT"[NUMBER,22], "S"."TOT_AMT"[NUMBER,22]
    8 - (#keys=1) "S"."CUST_ID"[NUMBER,22], COUNT(DISTINCT "S"."CHANNEL_ID";)[22], COUNT(DISTINCT "S"."PROD_ID";)[22], SUM("S"."A
    9 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]
    10 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]
    11 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]


    ############################
    # 힌트 추가 NO_PX_JOIN_FILTER
    # 소요 시간 : 00:00:03.03
    ############################
    SELECT /*+ GATHER_PLAN_STATISTICS INDEX(c IX_CUST_BIRTH_CUST) LEADING(c) NO_PX_JOIN_FILTER(@"SEL$1" "S"@"SEL$1";) */
    c.cust_id, c.cust_first_name, c.cust_last_name,
    s.prod_cnt, s.channel_cnt, s.tot_amt
    FROM customers c,
    (SELECT s.cust_id,
    COUNT (DISTINCT s.prod_id) AS prod_cnt,
    COUNT (DISTINCT s.channel_id) AS channel_cnt,
    SUM (s.amount_sold) AS tot_amt
    FROM sales s
    GROUP BY s.cust_id) s
    WHERE c.cust_year_of_birth = 1987
    AND s.cust_id = c.cust_id
    AND ROWNUM <= 10;

    select * from table(dbms_xplan.display_cursor(null,null,'all ALLSTATS LAST -rows'));

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | 1543 (100)| | | | 10 |00:00:02.88 | 5212 | | | |
    |* 1 | COUNT STOPKEY | | 1 | | | | | | 10 |00:00:02.88 | 5212 | | | |
    |* 2 | HASH JOIN | | 1 | 870 | 1543 (3)| 00:00:19 | | | 10 |00:00:02.88 | 5212 | 836K| 836K| 1176K (0)|
    | 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 6493 | 35 (0)| 00:00:01 | | | 151 |00:00:00.01 | 146 | | | |
    | 4 | BITMAP CONVERSION TO ROWIDS| | 1 | | | | | | 151 |00:00:00.01 | 2 | | | |
    |* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 | | | | | | 1 |00:00:00.01 | 2 | | | |
    | 6 | VIEW | | 1 | 303K| 1507 (3)| 00:00:19 | | | 523 |00:00:02.88 | 5066 | | | |
    | 7 | SORT GROUP BY | | 1 | 172K| 1507 (3)| 00:00:19 | | | 523 |00:00:02.88 | 5066 | 10M| 3330K| 9496K (0)|
    | 8 | PARTITION RANGE ALL | | 1 | 21M| 1478 (1)| 00:00:18 | 1 | 28 | 918K|00:00:05.19 | 5066 | | | |
    | 9 | TABLE ACCESS FULL | SALES | 28 | 21M| 1478 (1)| 00:00:18 | 1 | 28 | 918K|00:00:01.82 | 5066 | | | |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

    1 - filter(ROWNUM<=10)
    2 - access("S"."CUST_ID"="C"."CUST_ID";)
    5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

    Column Projection Information (identified by operation id):
    -----------------------------------------------------------

    1 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40], "S"."TOT_AMT"[NUMBER,22], "S"."PROD_CNT"[NUMBER,22],
    "S"."CHANNEL_CNT"[NUMBER,22]
    2 - (#keys=1) "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40], "S"."TOT_AMT"[NUMBER,22], "S"."PROD_CNT"[NUMBER,22],
    "S"."CHANNEL_CNT"[NUMBER,22]
    3 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40]
    4 - "C".ROWID[ROWID,10], "C"."CUST_YEAR_OF_BIRTH"[NUMBER,22]
    5 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7924], "C"."CUST_YEAR_OF_BIRTH"[NUMBER,22]
    6 - "S"."CUST_ID"[NUMBER,22], "S"."PROD_CNT"[NUMBER,22], "S"."CHANNEL_CNT"[NUMBER,22], "S"."TOT_AMT"[NUMBER,22]
    7 - (#keys=1) "S"."CUST_ID"[NUMBER,22], COUNT(DISTINCT "S"."CHANNEL_ID";)[22], COUNT(DISTINCT "S"."PROD_ID";)[22], SUM("S"."AMOUNT_SOLD";)[22]
    8 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]
    9 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.09.06 22:20 신고  댓글주소  수정/삭제

      Bloom Filter 때문입니다. 불필요한 조인을 방지하는 효과가 있습니다. 아래의 링크를 참조하세요.

      http://scidb.tistory.com/entry/Parallel-Query-의-조인시-또다른-튜닝방법pxjoinfilter

  8. oraking 2010.09.07 15:23  댓글주소  수정/삭제  댓글쓰기

    저도 위 글을 읽었습니다만 현재 제가 테스트한 테이블은 파티션테이블이며 parallel은 사용하지 않고 있습니다.

    buffers를 보면 두 쿼리가 동일한 buffer를 엑세스 합니다.

    또한 아래 결과 또한 no rows selected 입니다.

    select filtered, probed, probed - filtered as sent from v$sql_join_filter
    where qc_session_id = sys_context('userenv', 'sid');

    제 개인적인 생각으로는 bloom filter와 FPD의 결합된 형태가 나타난듯합니다.

    FPD: Considering simple filter push in query block SEL$639F1A6F (#2)
    "S"."CUST_ID"=:B1
    try to generate transitive predicate from check constraints for query block SEL$639F1A6F (#2)
    finally: "S"."CUST_ID"=:B1

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

      Parallel을 사용할때만 Bloom Filter가 생성되는것은 아닙니다. 파티션을 사용할 때도 생성됩니다.
      아래글의 pdf 파일을 참조하세요.
      http://scidb.tistory.com/entry/Partition-Access-Pattern

      Bloom Filter가 생성되어 Group By 량이 줄어들어듦으로 해서 PGA 사용량(Used-Mem)이 100배이상 차이가 납니다. 즉 Bloom Filter에 의해서 group by량이 급격히 줄어든것 입니다.

      다만 파티션 Bloom filter인 경우 v$sql_join_filter에 결과가 나타나지 않는 현상이 발견됩니다.