가능한가?

SQL문은 실행순서가 있다. 일반적으로 Order By가 가장 마지막에 수행된다. 만약 Order By가 없다면 마지막에 실행되는 것은 Group By이다. 데이터를 처리하는 작업은 Order By Group By 작업을 수행하기 이전에 끝난다. 상식적으로 보더라도 데이터를 읽는 작업과 조인작업을 먼저 처리해야 전체 데이터를 Grouping 할 수 있기 때문이다. 그런데 만약 데이터를 처리하는 작업이 Group By 이후에 발생한다면? 이런 일은 논리적으로 발생할 수 없다. 하지만 오늘 한가지 경우를 보여주려 한다.

 

먼저 오라클 SH 스키마의 Sales 테이블에 Local Partition 인덱스를 하나 생성한다.

Sales 테이블은 Time_id로 분기별 Range Partition이 되어있다.

 

CREATE INDEX SALES_TIME_CHANNEL_IX ON SALES

(TIME_ID, CHANNEL_ID) LOCAL;

                                 

먼저 정상적으로 처리되는 경우를 보자. IN 조건을 Pair로 여러개 주어본다. 

 

SELECT /*+ gather_plan_statistics */ TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 4) )

 GROUP BY TIME_ID;

  

위의 SQL에서 주의해야 될 것은 IN 조건이 TIME_ID CHANNEL_ID 로 동시에 공급된다는 것이다.

 

TIME_ID  COUNT(TIME_ID)

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

98/02/14            391

1 row selected.

 

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

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

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

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

|   1 |  SORT GROUP BY NOSORT   |                       |      1 |      1 |       3 |

|   2 |   PARTITION RANGE SINGLE|                       |      1 |    391 |       3 |

|*  3 |    INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |    391 |       3 |

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

 

Predicate Information (identified by operation id):

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

   3 - access("TIME_ID"=TO_DATE('19980214'))

       filter(("CHANNEL_ID"=2 OR "CHANNEL_ID"=3 OR "CHANNEL_ID"=4))


정상적으로 Grouping 되어 1건이 나왔고 Plan상에 Group By도 마지막에 수행되었다.

 
INLIST ITERATOR ! 니가 나를 배신하다니...

이제 1998 2 12일의 데이터를 추가로 공급해보자.

 

SELECT /*+ gather_plan_statistics */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 4) )

 GROUP BY TIME_ID;

 

TIME_ID  COUNT(TIME_ID)

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

98/02/12             50

98/02/14             54

98/02/14            287

98/02/14             50

 

4 rows selected.

 

답이 틀리다(Wrong Result Bug)

뭔가 이상하다. TIME_ID Grouping 하였으므로 결과건수는 1998 2 12일과 1998 2 14일로 두건만 나와야 한다. 그런데 2 14일 데이터가 Grouping 되지 않고 3건이 나와버렸다. 답이 틀리므로 이것은 버그이다.

 

왜 이런 일이 발생할까?

비밀은 실행계획에 있다.


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

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

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

|   0 | SELECT STATEMENT          |                       |      1 |      4 |       7 |

|   1 |  INLIST ITERATOR          |                       |      1 |      4 |       7 |

|   2 |   PARTITION RANGE ITERATOR|                       |      4 |      4 |       7 |

|   3 |    SORT GROUP BY NOSORT   |                       |      4 |      4 |       7 |

|*  4 |     INDEX RANGE SCAN      | SALES_TIME_CHANNEL_IX |      4 |    441 |       7 |

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

  

상식적으로는 Grouping을 가장 마지막에 한번만 해야 한다. 하지만 실행계획을 보면 그렇지 못하고 INLIST ITERATOR 작업 이전에 Grouping을 해버린다. ID 기준으로 3번이 그것인데 INLIST의 개수만큼 SORT GROUP BY가 반복된다. Starts 항목이 그것을 증명한다. 개념적으로 말하면 각각의 WHERE 조건마다 Group By를 수행하고 그 결과들을 Union 한 것이다. 

 

항상 버그가 발생하는 것은 아니다

파티션과 IN 조건이 만나야 버그가 발생한다. 또한 WHERE 조건이 변경되지 않고 INLIST ITERATOR가 발생해야 버그가 발생한다. 이 글에서 최초로 실행 시킨 SQL WHERE 조건은 TIME_ID = TO_DATE('19980214') AND CHANNEL_ID IN (2,3,4) 로 바뀌어 INLIST ITERATOR로 처리되지 않고 INDEX RANGE SCAND으로 처리되었으므로 버그가 없다.

 

해결책은 처리순서를 변경하는 것

이런 경우의 해결책은 아주 간단하다. 우리의 상식대로 하면 된다. 즉 데이터를 모두 처리하고 Grouping을 가장 마지막에 실행하는 것이다. 아래의 SQL이 그것이다.

 

SELECT TIME_ID, COUNT(*)

  FROM ( SELECT /*+ NO_MERGE */  TIME_ID

           FROM SALES

           WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                             (TO_DATE('19980214'), 2),

                                             (TO_DATE('19980214'), 3),

                                             (TO_DATE('19980214'), 4) )

       )

GROUP BY TIME_ID; 

 

TIME_ID  COUNT(TIME_ID)

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

98/02/12             50

98/02/14            391

 

2 rows selected.

 

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

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

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

|   0 | SELECT STATEMENT           |                       |      1 |      2 |       9 |

|   1 |  SORT GROUP BY NOSORT      |                       |      1 |      2 |       9 |

|   2 |   INLIST ITERATOR          |                       |      1 |    441 |       9 |

|   3 |    PARTITION RANGE ITERATOR|                       |      4 |    441 |       9 |

|   4 |     VIEW                   |                       |      4 |    441 |       9 |

|*  5 |      INDEX RANGE SCAN      | SALES_TIME_CHANNEL_IX |      4 |    441 |       9 |

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


 

예상대로 Group By는 가장 마지막에 처리되었으므로 결과도 정상적으로 두건이 출력되었다. SQL의 실행순서만 알고 있으면 이 정도의 버그는 패치가 없어도 해결이 가능하다. NO_MERGE 힌트를 사용한 이유는 View Merge(뷰 해체)를 방지하기 위함이다. Simple View 이므로 힌트를 빼면 무조건 인라인뷰가 해체되어 버그가 재생된다.

 

또 다른 해결책을 보자.

 


SELECT /*+ INDEX(SALES SALES_TIME_CHANNEL_IX) */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE    (TIME_ID = TO_DATE('19980214') AND CHANNEL_ID BETWEEN 2 AND 4 )

       OR (TIME_ID = TO_DATE('19980212') AND CHANNEL_ID =4 )         

 GROUP BY TIME_ID; 

 

 

TIME_ID  COUNT(TIME_ID)

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

98/02/12             50

98/02/14            391

 

2 rows selected.

 


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

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

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

|   0 | SELECT STATEMENT         |                       |      1 |      2 |       5 |

|   1 |  HASH GROUP BY           |                       |      1 |      2 |       5 |

|   2 |   CONCATENATION          |                       |      1 |    441 |       5 |

|   3 |    PARTITION RANGE SINGLE|                       |      1 |    391 |       3 |

|*  4 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |    391 |       3 |

|   5 |    PARTITION RANGE SINGLE|                       |      1 |     50 |       2 |

|*  6 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     50 |       2 |

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


 

SQL WHERE절을 수정하여 INLIST ITERATOR가 아닌 CONCATENATION으로 유도 하였다. 위의 실행계획 또한 Group By가 가장 마지막이 실행되므로 버그가 발생되지 않는다. SQL을 수정하기 싫고 힌트만으로 해결하려면 아래의 SQL을 보라. 

 


SELECT /*+ USE_CONCAT(1) */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 4) )

 GROUP BY TIME_ID;

 

TIME_ID  COUNT(TIME_ID)

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

98/02/12             50

98/02/14            391

 

2 rows selected.

 

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

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

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

|   0 | SELECT STATEMENT         |                       |      1 |      2 |       9 |

|   1 |  HASH GROUP BY           |                       |      1 |      2 |       9 |

|   2 |   CONCATENATION          |                       |      1 |    441 |       9 |

|   3 |    PARTITION RANGE SINGLE|                       |      1 |     50 |       2 |

|*  4 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     50 |       2 |

|   5 |    PARTITION RANGE SINGLE|                       |      1 |    287 |       3 |

|*  6 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |    287 |       3 |

|   7 |    PARTITION RANGE SINGLE|                       |      1 |     54 |       2 |

|*  8 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     54 |       2 |

|   9 |    PARTITION RANGE SINGLE|                       |      1 |     50 |       2 |

|* 10 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     50 |       2 |

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


위의 SQL 또한 정상적으로 2건이 출력된다.

버그를 유발하는 원인을 제거하라
마지막으로 볼 해결책은 파티션과 INLIST ITERATOR의 불편한 만남(?)을 제거하는 것이다.


SELECT /*+ INDEX_COMBINE(a) */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES a

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 4) )

GROUP BY TIME_ID;

 

TIME_ID  COUNT(TIME_ID)

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

98/02/12             50

98/02/14            391

 

2 rows selected.

 

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

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

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

|   0 | SELECT STATEMENT                    |                   |      1 |     2 |      61 |

|   1 |  PARTITION RANGE INLIST             |                   |      1 |     2 |      61 |

|   2 |   HASH GROUP BY                     |                   |      1 |     2 |      61 |

|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |      1 |   441 |      61 |

|   4 |     BITMAP CONVERSION TO ROWIDS     |                   |      1 |   441 |      17 |

|   5 |      BITMAP OR                      |                   |      1 |     1 |      17 |

|   6 |       BITMAP AND                    |                   |      1 |     1 |       4 |

|*  7 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|*  8 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     1 |       2 |

|   9 |       BITMAP AND                    |                   |      1 |     1 |       4 |

|* 10 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|* 11 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     1 |       2 |

|  12 |       BITMAP AND                    |                   |      1 |     1 |       5 |

|* 13 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|* 14 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     2 |       3 |

|  15 |       BITMAP AND                    |                   |      1 |     1 |       4 |

|* 16 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|* 17 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     1 |       2 |

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

 

Bitmap Operation으로 인하여 INLIST ITERATOR가 사라졌으므로 버그는 보이지 않는다.

 

결론

이 버그는 Oracle 10g 에서 발견되었지만 11gR2 버전까지 해결되지 않고 있다. 파티션과 Local Index를 사용하고 Pair IN 조건이 INLIST ITERATOR로 풀리면 발생된다. 해결방법은 SQL을 수정하여 Group By를 마지막에 수행하거나 INLIST ITERATOR를 제거하면 된다. 버그패치가 없다고 해서 잘못된 결과를 보여줄 수는 없다. 패치가 나올 때 까지는 다른 해결책을 강구해야 한다. 잘 생각해보면 위의 경우처럼 원리만 안다면 상식 선에서 해결할 수 있는 문제가 매우 많다.

 

버그, 오라클에 알려야 해
대부분의 경우 버그는 사용자가 오라클에 보고해야 패치를 만들게 된다. 그러므로 버그가 발견되면 반드시 SR을 진행하여 오라클에 알려야 한다. 버그를 발견한 사람은 SQL을 수정하여 버그를 피할 수 있지만 동료나 다른 사이트에 있는 모든 사람들은 해결책을 모를 수 있고 심지어 버그가 있는지 조차 모를 수 있다. 하지만 많은 수의 사람들은 버그를 피해가는 방법만 발견된다면 내 문제는 해결 되었으므로 오라클에 버그를 통보하지 않고 조용히 넘어갈 것이다. 혹시 위의 버그가 몇년간 남아 있는 이유도 조용한(?) 사람들 때문이 아닐까?

한가지 방법은 오라클사에서 버그리포팅을 하는 사람에게 작은 선물을 주는 것이다. 제품의 품질도 높이고 고객의 참여를 유도하는 Win Win 전략이 될 수 있다.


Posted by extremedb

댓글을 달아 주세요

이글을 쓰기전에 필자는 QUEST 사와 아무런 관계가 없음을 먼저 밝혀둔다.
이글의 목적은 개발자, DBA, 튜닝 컨설턴트 들이 이런 리포트들을 뽑기위하여 OEM(엔터프라이즈 매니져) 를
사용할수 없는경우 발을 동동 구르고 있다는 사실이다.
하지만 사실은 우리주위에 있는 가장흔한 쿼리툴(TOAD) 로도 쉽게 출력이 가능하다.
아래처럼 토드에서 Database --> Monitor --> ADDM/AWR 을 클릭하면
1.AWR(Automatic Workload Repository),
2.ADDM(AUTOMATIC DATABASE DIAGNOSTIC MONITOR),
3.ASH(Active Session History) 를 관리및 출력할수 있는 화면이 나온다.
참고로 AWR 리포트는 9i 이하버젼에서 사용한 Statspack 리포트의 진화된 버젼이라고 할수 있다.
 

사용자 삽입 이미지































아래는 AWR 의 관리화면이다.
Snapshot Interval 은 10분이며 최장 보관기간은 30 일 임을 알수 있다.
이화면에서 관리및 변경이 가능하다.

사용자 삽입 이미지




아래는 ASH 를 출력할수 있는 탭을 보여준다.
그림 FROM ~ TO 로 START 와 END TIME 을 지정하고 RAC 인경우 인스턴스를 지정하고 상단에 있는 연두색 버트늘 누르면 ASH 보고서가 쉽계 출력된다.

사용자 삽입 이미지



아래는 AWR 보고서 화면이다.
SNAPSHOT 구간을 선택하고 인스턴스를 선택하고 상단의 연두색 버튼을 누르면 쉽게 보고서가 출력된다.
사용자 삽입 이미지


아래는 ADDM 리포트이다.
AWR 리포트와 출력하는 방법이 같다.
사용자 삽입 이미지


이상으로서 거칠것 없이 AWR 과 ASH, ADDM 의 보고서를 출력 해보았다.
복잡한 절차없이 몇번의 클릭만으로 3종류의 보고서를 출력하였다.
이 3개의 보고서를 잘활용하면 특정구간대의 DBMS 차원의 성능진단및 Wait Event, TOP SQL,
TOP Object 등을 도출해 낼수 있다.
특히 ADDM 보고서에는 문제가 무엇이고 어떻게 조치해야 되는지 까지 조언을 해준다.
또한 ASH 나 AWR 의 보고서에는 TOP 이벤트를 발생시키는 SQL 을 찾아줄뿐 아니라
연관된 TOP OBJECT 까지 찾아준다.
예를 들면 특정 SQL 이 Index Contention 을 발생시킬경우에 관련 인덱스가 무엇인지 쉽게 찾을수 있다.
오늘 소개한것은 토드의 일부기능에 불과하다.
우리주위에 흔히 볼수 있는 쿼리툴도 찾아보면 멋진 기능들이 많이 숨어있다.
단지 어디에 뭐가 있는지 몰라서 사용하지 않을뿐.....
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2008.10.16 16:04 신고  댓글주소  수정/삭제  댓글쓰기

    추가적으로 AWR SQL Report 기능도 때로는 대단히 유용하더군요.

    그리고 Oracle 10gR2부터는 dbms_workload_repository 패키지를 통서 동일한 작업이 다 가능합니다.

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm

    그리고 $ORACLE_HOME/rdbms/admin 디렉토리에 있는 ashrpt.sql, addmrpt.sql, awrrpt.sql, awrsqrpt.sql 스크립트를 사용해도 좋구요.

  2. Favicon of https://scidb.tistory.com BlogIcon extremedb 2008.10.16 19:49 신고  댓글주소  수정/삭제  댓글쓰기

    아주 유용한 정보입니다.
    감사합니다.

필자가 7월달에  Parallel Query 의 조인시 Row Distribution 이라는 글을 통하여 Parallel + Join 시에
튜닝방법을 설명 한바 있다.
오늘은 최적의 Parallel Join 을 하기 위하여 또다른 튜닝방법을 제시한다.
필자가 이글을 쓰는 원래의 용도는 사내 DB 컨설턴트 들을 교육시키는데 사용하는 것이다.
그렇기 때문에 어려워도 실망하거나 우울증에 걸리지 말자.
최근 최진실씨 사태등등 해서 심히 걱정된다.^^
Parallel Join Filter 를 설명하려고 하는데 용어설명부터 해야겠다.
왜냐하면 어떤곳에서는 Parallel Join Filter 라고 이야기 하고 또다른 곳에서는 Bloom Filter 라고 하는데
그이유는 알고리즘을 최초로 개발한 사람이 오라클사의 Burton H. Bloom 이라는 사람이고 이는 1970 년의
일이다.
실제로 실행계획상에 Bloom 의 이름을 따서 필터명이 BF0000, BF0001, BF0003 .... 이렇게 생성된다.
어쨋든 이런사유로 인하여 2개의 용어가 혼용되는데 여기서는 Parallel Join Filter (힌트로는 px_join_filter) 만 사용할것이다.
아래는 테이블 생성 스크립트 이다.
테스트를 위하여 2개의 테이블이 필요하다.

제약사항
 Parallel Join Filter 는 10gR2 이상에서 실행가능함.

테스트용 테이블 생성 스크립트

create table emp_1
as
with a as
(select /*+ materialize */ level + 10000000 as empno,
       chr(mod(level,90)) as big_ename, chr(mod(level,90)) as big_addr
 from dual
 connect by level <= 100000)
 select empno, 
           lpad(big_ename, 3000,big_ename) as big_ename ,
           lpad(big_addr, 3000,big_addr)  as big_addr
 from a ;
 
create table emp_2
as
select * from emp_1 ;

EXEC dbms_stats.gather_table_stats(user,'EMP_1');
EXEC dbms_stats.gather_table_stats(user,'EMP_2');


테이블이 생성 되었으므로 테스트 스크립트를 실행시켜보자.

explain plan for
 SELECT /*+ full(t1) full(t2) parallel(t1 8) parallel(t2 8) leading(t1) use_hash(t2) NO_PX_JOIN_FILTER(t2) */
        *
  FROM emp_1 t1,
            emp_2 t2
 WHERE t1.empno = T2.empno
       and t1.BIG_ENAME > '1'  ;

아래 PLAN 을 설명하기전에 일단 TQ(Table queues) 개념을 알아야 한다.
복잡한 plan 같지만 원리를 알고 나면 간단하게 해석 할수 있다.
TQ 는 processes간의 데이터를 주고받는 기능을 한다.
하나의 TQ 는 여러개의 parallel Slave 를 가진다.
아래 PLAN 을 보면 TQ 가 3개(:TQ10000, :TQ10001, TQ10002 ) 생성되어 있다.(파란색 부분)


--------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          | 12132   (1)|        |      |            |
|   1 |  PX COORDINATOR         |          |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002 | 12132   (1)|  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED   |          | 12132   (1)|  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE          |          |  3054   (0)|  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10000 |  3054   (0)|  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          |  3054   (0)|  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS FULL| EMP_1    |  3054   (0)|  Q1,00 | PCWP |            |
|   8 |     PX RECEIVE          |          |  3054   (0)|  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH       | :TQ10001 |  3054   (0)|  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR |          |  3054   (0)|  Q1,01 | PCWC |            |
|  11 |        TABLE ACCESS FULL| EMP_2    |  3054   (0)|  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------

각 Id 단위의 설명 :
1. Q1,00 의 slave process 들은 emp_1  테이블을 full scan 하면서 t1.BIG_ENAME > '1'  조건을 FILTER
   하였고 process 간의 통신을 위하여 걸러진 데이터를 Q1,02 에 보낸다.
    (Id 기준으로 5~7 이 여기에 해당된다)
2. Q1,02 의 slave process 들은 1번에서 받은 데이터들을 이용해 hash table 을 만든다.
    (Id 기준으로 3~4 가 여기에 해당된다)
3. Q1,01 의 slave process 들은 emp_1  테이블을 full scan 하고 읽은 데이터를 Q1,02 에 보낸다.
    (Id 기준으로 9~11 가 여기에 해당된다)
4.  Q1,02 의 slave process 들은 3번에서 던진 데이터를 받아서 미리 만들어진 hash 테이블을
     검색하면서 조인작업을 진행하고 결과를 Query Cordinator 에 보낸다.
     (Id 기준으로 2~3 이 여기에 해당된다)
5. Query Cordinator 는 각 TQ 로 부터 데이터를 받아서 취합한후에 결과를 Return 한다.
     (Id 기준으로 0~1 이 여기에 해당된다)

위설명을 도식화 하면 아래그림과 같다.
다만 위의 SQL 대로라면 각 TQ 내의 SALVE 는 8개 여야 하지만 화면관계상 2개로 줄여서 나타 내었다.

사용자 삽입 이미지

위그림을 보면 무언가 비효율적인 것을 발견하게 된다.
Q1,01 의 모든 SLAVE 들은 Q1,02 의 모든 SLAVE 들에게 똑같은 데이터를 던져서 체크한후에 만족하면
조인에 성공하고 그렇지 않으면 조인에 실패하는 프로세스를 가지게 된다.
위쿼리를 예를들면 사번 10000100을  Q1,02 의 SLAVE 가 8개라면 8번 던져서 1/8 확률로 조인에 성공하면
다행이지만 아예조인에 실패할 확률도 있는것이다.
이런 비효율을 없애는 것이 Parallel Join Filter 이다.
Parallel Join Filter 의 개념은 Q1,01(후행테이블의 TQ) 이 Q1,02 에게 데이터를 전달하기전에 불필요한
데이터를 걸러 낸다는 것이다.
이제 parallel join filter 를 적용시켜보자.

explain plan for
 SELECT /*+ full(t1) full(t2) parallel(t1 8) parallel(t2 8) leading(t1) use_hash(t2) PX_JOIN_FILTER(t2) */
        *
  FROM emp_1 t1,
       emp_2 t2
 WHERE t1.empno = T2.empno
       and t1.BIG_ENAME > '1'  ;

필자의 연구결과 t1.ename > '1' 등 t1 의 filter predicate 가 없으면 Parallel Join Filter 는 결코 작동하지 않는다.
그럴때는 t1.empno > 0 등의 결과값의 영향을 끼치지 않는 filter 조건을 주는 트릭을 생각할수 있다.
또하나의 Tip 은 PX_JOIN_FILTER 사용시 후행테이블을 사용하여야 한다는것이다.
왜냐하면 아래의 PLAN 을 보면 Filter 의 생성은 t1 에서 하지만(id 가 4번) 사용은 t2 쪽(id 11번)에서
하기때문에 PX_JOIN_FILTER(t1) 을 주면 절대 filter operation 이 생기지 않는다.

---------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          | 12132   (1)|        |      |            |
|   1 |  PX COORDINATOR          |          |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002 | 12132   (1)|  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED    |          | 12132   (1)|  Q1,02 | PCWP |            |
|   4 |     PX JOIN FILTER CREATE| :BF0000  |  3054   (0)|  Q1,02 | PCWP |            |
|   5 |      PX RECEIVE          |          |  3054   (0)|  Q1,02 | PCWP |            |
|   6 |       PX SEND HASH       | :TQ10000 |  3054   (0)|  Q1,00 | P->P | HASH       |
|   7 |        PX BLOCK ITERATOR |          |  3054   (0)|  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| EMP_1    |  3054   (0)|  Q1,00 | PCWP |            |
|   9 |     PX RECEIVE           |          |  3054   (0)|  Q1,02 | PCWP |            |
|  10 |      PX SEND HASH        | :TQ10001 |  3054   (0)|  Q1,01 | P->P | HASH       |
|  11 |       PX JOIN FILTER USE | :BF0000  |  3054   (0)|  Q1,01 | PCWP |            |
|  12 |        PX BLOCK ITERATOR |          |  3054   (0)|  Q1,01 | PCWC |            |
|  13 |         TABLE ACCESS FULL| EMP_2    |  3054   (0)|  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------

위 plan 은 원래의 PLAN(filter 적용전 plan) 에서 parallel join filter 부분만이 추가 되었다.(파란색 부분)
1. id 4 에서 parallel Join filter 를 생성(create) 하였고 filter 명은 :BF0000 이다.
2. id 11 에서 생성된 :BF0000 filter 를 사용하였다.

주의사항은  parallel Join filter 를 무조건 사용하지말라는 것이다.
걸러지는 데이터가 별로 없을경우 빨라지지도 않을 뿐더러  filter 부하가 더클수 있기 때문이다.
다음의 2가지 경우에  parallel Join filter 를 사용하여야 한다.
1. 많은양의 데이터가 조인에 실패하는경우
2. 1번을 만족하면서 RAC 에서 multi-node 로 Parallel Query 를 실행한경우.
    이경우는대부분 DOP(Degree Of Parallelism)가 클때 발생하며 추가적인 Network I/O 가 발생하므로
    parallel join filter 를 적용할경우 획기적인 성능향상을 기대할수 있다.

parallel Join filter에 의해서 filter 된 데이터를 보려면 아래와 같이 v$sql_join_filter 뷰를 사용하면된다.

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

결론 :
Parallel Join distribution 과 Parallel join filter 을 적절히 이용하면 최적화된 Parallel Join Query를 만들수 있다.
다시한번 말하지만 꼭필요한 경우에만 이런종류의 힌트를 사용하여야 한다.
 
편집후기 :만약 parallel Join filter 로직에 관심이 있어서 직접 구현하려면 아래의 1번 문서를 참조하기 바란다.

Reference :
1.Bloom Filters ( Cristian Anatognini )
2.Oracle Corp Manual (Data Warehousing Guide 11g)

Posted by extremedb

댓글을 달아 주세요

  1. 금땡이 2008.10.13 19:28  댓글주소  수정/삭제  댓글쓰기

    전체적 내용은 이해하겠는데, 세부적으로 보면 제 지식의 한계가..ㅠㅠ
    잘 읽고, 잘 담아 갑니다.

  2. Favicon of https://scidb.tistory.com BlogIcon extremedb 2008.10.13 21:23 신고  댓글주소  수정/삭제  댓글쓰기

    10 그램 에서 나온 새기능이므로 아직 사용하는 사람이 없지만 정확한 용도와 용법을 안다면 적용할곳이
    적지않을 걸로 생각 합니다.
    참 제가 농담삼아 oracle 10g 대신에 오라클 10 그램으로 발음 합니다.^^
    오해 없으시길...

  3. Favicon of https://dbfactory.tistory.com BlogIcon ...., 2008.10.15 10:55 신고  댓글주소  수정/삭제  댓글쓰기

    좋은글 잘 보고 갑니다. 감사합니다~!

  4. sid 2011.01.27 21:13  댓글주소  수정/삭제  댓글쓰기

    예전에 실행계획 보면서 이건 도대체 뭔 소리야!!
    했던 실행계획의 비밀이 풀렸습니다.
    감사합니다 ^^

기획팀에서 이대리가 전산실에 와서 부탁을 한다.

기획실 이대리:"월별 부서별로 2002년도 실적을 좀 뽑아주실수 있나요?"

전산실 김대리:"네 오늘저녁 6시쯤 오시면 실적 보고서를 드릴수 있습니다."

기획실 이대리:"실적을 만드실때 주의사항이 있습니다.
                     월별 부서별로 실적을 뽑을때 만약 20번 부서에서 5월, 7월에
                     실적이 없다고 하더라고 5월,7월 실적을 0 으로 표시해주세요."

전산실 김대리:"네 알겠습니다. 그것은 별로 어렵지 않습니다."   

년월만 들어있는 테이블과 월별부서별실적 테이블의 구조는 아래와 같다.

사용자 삽입 이미지

















월별 부서별 실적테이블의 2002년 실적은 다음 그림과 같다.
모든 월에 실적이 있는것은 아니다.(예를 들면 10번 부서는 2002년도에 1,3,6,7,8,10,11 월에 실적이 없다. )
사용자 삽입 이미지














30번 부서부터는 지면관계상 그림에서 생략하였다.




기획실 이대리의 요구사항은 아래그림과 같다.
(실적이 없는달은 실적을 0 으로 표시함)
사용자 삽입 이미지
































30번 부서의 실적부터는 지면관계상 그림에서 생략하였다.


다행히 월별, 부서별 실적 테이블이 존재하기 때문에 김대리는 묵묵히 월별 실적 SQL을 아래처럼 작성하였다.
아래처럼 작성한 이유는 부서가 20개(10번부터 200번까지) 있기 때문에 부서별로 무조건 12건(1월~12월)을 만들기 위해서 이다.

SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 10     --> 10번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 20     --> 20번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 30     --> 30번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'
Union all
...........................................................................................중간생략
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 200     --> 200번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'


우연히 김대리의 작업을 지켜보던 전산실 박과장이 한마디 한다.
전산실 박과장 :"김대리 그작업할때 200번이나 노가다(Union All) 할생각이냐?
                     "SQL 공부좀해라"
              
김대리에게 호통을 친 박과장은 자신감 있게 아래의 SQL을 1분만에 만들었다.    
           

SELECT dept_month.deptno, dept_month.yymm, NVL(e.sale_amt,0)
   FROM (SELECT d.deptno, m.yymm
                 FROM ( SELECT c.deptno
                                FROM DEPT c
                               WHERE EXISTS (SELECT 1
                                                          FROM dept_sale_history d
                                                        WHERE d.deptno = c.deptno
                                                            AND d.yymm like '2002%')) d,
                             ( SELECT m.yymm
                                  FROM year_month m         
                                 WHERE m.yymm like '2002%' ) m 
             ) dept_month,                                          --> 월별 부서별 집합을 먼저 만든다.
            dept_sale_history e        
 WHERE dept_month.deptno(+) = e.deptno
      AND dept_month.yymm(+) = e.yymm              

위의 SQL 의 핵심은 모든 부서에 대하여 1월~12월 까지 와꾸?(틀)를 만들어 놓고
부서별 월별실적 테이블과 아우터 조인을 하기위해서 이다.
위의 SQL 에서 EXISTS 를 사용한 이유는 2002 년도에 실적이 있는 부서만 뽑기 위해서다.
하지만 위의 SQL 도 비효율이 있다.
부서별 월별 실적테이블을 2번이나 ACCESS 하였다.

박과장의 작업을 옆에서 지켜보던 신입사원이 고개를 기우뚱 하며 박과장에게 말을 건낸다.
전산실 신입사원:"dept_sale_history" 테이블을 2번 사용하지 않고도 같은 실적을 뽑을수 있습니다."
전산실 박과장 :"그래? 그럼 한번해봐"

신입사원을 지켜보던 박과장은 경악을 금치 못한다.
신입사원이 20초만에 SQL 을 작성하고도 성능은 신입사원의 SQL이 우수했기 때문이다.
단 4줄의 SQL 로 기획팀 이대리의 요구사항을 해결하였다.
박과장은 SQL 을 사용한지 10년이 넘는 배테랑 개발자 이지만 10g 신기능은 써보지 못한 상태였다.
아래의 SQL이 신입사원의 SQL 이다.

SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m LEFT OUTER JOIN dept_sale_history e
           PARTITION BY (e.deptno) ON (m.yymm = e.yymm )
WHERE m.yymm like '2002%';

신입사원이 위의 SQL 을 사용할수 있었던건 처음 배운 SQL 문법이 Oracle 10g 기준이었고
박과장은 Oracle 8 버젼의 SQL을 공부 해었기 때문이다.

위의 Partition Outer Join 은 10g 의 새기능이다.
Partition Outer Join 의 기능을 요약하면 부서별로 중간중에 빠진 월의 실적을 생성해주는 기능이다.     

결론 :
Partition Outer Join 은 10g 의 신기능중 일부에 불과하다.
버전별로 New Features의 중요성을 다시한번 강조하지만 위의 경우와 같이
신기능을 모르면 작업량이 늘어날수 밖에 없고 대부분 성능도 느리다.
또한 Oracle 8.0 시절에 최적화된 SQL 이 항상 Oracle 10g 에서 최적화된 SQL 이라고 볼수 없다.
Oracle 9i 가 나온지는 10년이 됬으며 Oralce 10g 가 나온지도 6년이 지났고 2년전에 Oracle 11g 가 나왔다.
신버젼이 나올때 마다 알라딘의 요술램프처럼 주인님이 사용해주기를 기다리는 마술 같은 여러가지 신기능이 숨어있다는 점을 기억하자.

Posted by extremedb

댓글을 달아 주세요

  1. 금땡이 2008.08.31 10:44  댓글주소  수정/삭제  댓글쓰기

    100% 공감하는 내용입니다. 공부 좀 해야겠네요.

  2. 투덜이 2011.02.22 18:30  댓글주소  수정/삭제  댓글쓰기

    안녕하세요... 항상 좋은글을 읽고있는 애독자입니다...
    현재는 오동규님께서 추천해주신 Mastering Oracle SQL 2nd Edition 을 잘 보고있습니다.
    근데 한가지 궁금한게 있어 질문을 드립니다.
    ansi 표준의 sql을 이용하여 partition outer join을 구현하셨는데(또 제가 보는 책에서도) 이 문장을 Oracle표준 문장으로
    바꿀 수는 없는지요...
    예로 dept a LEFT OUTER JOIN EMP b ON a.dept_id = b.dept_id를 a.dept_id = b.dept_id(+) 이렇게요...
    아니면 partition outer join은 ansi 표준으로만 구현이 가능한가요??

    제가 아직 초보수준을 벗어나지 못해 무지한 질문을 드린게 아닌지 모르겠네요...

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

      투덜이님 반갑습니다.
      투덜이님 말씀대로 partition outer join은 ANSI 표준만 사용할 수 있습니다.

      보시고 계시는 Mastering Oracle SQL 2nd Edition 은 좋은 책입니다. 꼭 정복하시기 바랍니다.

  3. 투덜이 2011.02.23 10:34  댓글주소  수정/삭제  댓글쓰기

    감사합니다...
    꼭 정복하도록 노력하겠습니다...
    그리고 항상 좋은내용 잘 보고있습니다..