3 부작의 마지막 편
첫 번째,
Oracle Data Access Pattern을 정복하라
두 번째, Data Access Pattern중의 파티션에 관련된
Partition Access Pattern 에 이어서 마지막 편이다.

지난 글에서 Data Access Pattern 과 Join Method 이 두 가지는 기본 중에 기본이라고 하였다.
또한 이 두 가지를 정복한다면 SQL 튜닝중의 많은 부분을 커버할 수 있다고 하였다.
튜닝에서 이것보다 중요한 것이 있을까? 이것들 중에 하나라도 빠트린다면 제대로 된 튜닝을 할 수 없다.

단순 분류 5가지
데이터의 연결방법은 단순분류해 보면 다음과 같다.

1.Nested Loop Join
2.Sort Merge Join
3.Hash Join
4.Outer Join
5.Using Subquery

세분화
하지만 이것으로는 부족하다. Join Method를 좀더 자세히 나타내면 다음과 같다.

01. Nested Loop Join
02. Sort Merge Join
03. Hash Join
04. Cartesian Join (혹은 Cross Join)
05. Sub Query-In,
06. Sub Query-Any
07. Sub Query-All
08. Sub Query-Exists
09. Subquery Factoring
10. Semi Join-Nested Loop
11. Semi Join-Sort Merge
12. Semi Join-Hash
13. Semi Join-Hash Join Right
14. Anti Join-Nested Loop
15. Anti Join-Sort Merge
16. Anti Join-Hash
17. Anti Join-Hash Join Right
18. Index Join
19. Outer Join-Full
20. Outer Join-Nested Loop
21. Outer Join-Sort Merge
22. Outer Join-Hash
23. Outer Join-Hash Join Right
24. Partition Outer Join
25. Star Query Transformation
 
극한의 세분화
물론 여기서 더 세분화 시킬 수 있다. 예를 들면 Nested Loop Join은 아래와 같이 분류할 수 있다.

Full(선행집합)-Unique (후행집합)
Full(선행집합)-Range (후행집합)
Range(선행집합)-Range (후행집합)
Unique(선행집합)-Unique (후행집합)
....중간생략

이런 방법으로 Sort Merge Join과 Hash Join까지 계속 나열한다면 아마 끝이 없을 것이다.

단 한 줄도 놓치지 마라 
아래의 첨부파일에는 Nested Loop Join도 위와 같은 방법으로 가능한 세분화 하였다. 따라서 이 파일에 담긴 Join method는 25가지가 넘는다. 오늘 이야기하는 조인방법들은 튜닝을 하려면 반드시 정복해야 할 주제이니 꼼꼼히 보기 바란다.


invalid-file

Oracle Data Join Method



PS
Star Join은 Star Query Transformation이 나온 후로 설 땅을 잃었으므로 나타내지 않았다.

Posted by extremedb
,
필자가 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
,

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

기획실 이대리:"월별 부서별로 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
,