필자는 Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) 이라는 이전글에서 Right Deep Tree,
Left Deep Tree Plan 의 개념 및 튜닝에 관한 글을 작성하였는데 Bushy Tree Plan 에 관한 질문을 받았다.
질문은 "Bushy Tree Plan 은 무엇이며 어떨때 사용하는가?" 였다.
간단히 설명하면 T1 과 T2 를 조인하고 T3 와 T4 를 조인하여 2개의 결과 집합을 조인하는 방식이다.
실제로 Bushy Tree Plan SQL 을 실행시켜보자

테이블 생성 스크립트는 Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) 시 사용했던 스크립트와 동일하다.
먼저 지난번에 언급 되었던  Right Deep Tree PLAN 을 보자.

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T3 T4 T2) USE_HASH(T1 T2 T4) */
       T1.*, T2.*, T3.*, T4.*
  FROM T1, T2, T3, T4
 WHERE T1.ID = T2.ID
   AND T2.ID = T3.ID
   AND T3.ID = T4.ID
   AND T1.N1 < 50;     --> filter 조건 (대부분의 데이터를 걸러낸다)


-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.13 | 1212K (0)|
|*  2 |   TABLE ACCESS FULL  | T1   |      1 |     90 |00:
00:00.04 |          |
|*  3 |   HASH JOIN          |      |      1 |  10000 |00:00:00.18 |   11M (0)|
|   4 |    TABLE ACCESS FULL | T2   |      1 |  10000 |00:00:00.03 |          |
|*  5 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   6 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------


아래는 위의 SQL 을 Bushy Tree Plan 으로 유도하는 예제이다.

SELECT /*+ GATHER_PLAN_STATISTICS USE_HASH(T34) */
            *
  FROM (SELECT /*+ NO_MERGE LEADING(T1) USE_HASH(T2) */
                T1.ID, T1.N1, T1.PROBE_VC, T1.PROBE_PADDING,
                T2.ID ID2, T2.N1 N12, T2.PROBE_VC PROBE_VC2, T2.PROBE_PADDING PROBE_PADDING2
          FROM T1, T2
         WHERE T1.ID = T2.ID
           AND T1.N1 < 50 ) T12,
       (SELECT /*+ NO_MERGE LEADING(T3) USE_HASH(T4) */
                T3.ID, T3.N1, T3.PROBE_VC, T3.PROBE_PADDING,
                T4.ID ID4, T4.N1 N14, T4.PROBE_VC PROBE_VC4, T4.PROBE_PADDING PROBE_PADDING4
          FROM T3, T4
         WHERE T3.ID = T4.ID ) T34
WHERE T12.ID = T34.ID   ;  

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

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.09 | 1190K (0)|
|   2 |   VIEW               |      |      1 |     90 |00:00:00.06 |          |
|*  3 |    HASH JOIN         |      |      1 |     90 |00:00:00.06 | 1191K (0)|
|*  4 |     TABLE ACCESS FULL| T1   |      1 |     90 |00:00:00.04 |          |
|   5 |     TABLE ACCESS FULL| T2   |      1 |  10000 |00:00:00.03 |          |
|   6 |   VIEW               |      |      1 |  10000 |00:00:00.10 |          |
|*  7 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   8 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   9 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------


Right Deep Tree PLAN 과 비교해보면 메모리 사용량이 거의 절반수준으로 떨어졌다.

참고로 DBMS_XPLAN.DISPLAY_CURSOR 사용시 ADVANCED 를 명시하면 튜닝차원에서 필요한 모든정보가
빠짐없이 출력된다.
예를 들면 Outline Data, Query Block Name, Predicate Information, Column Projection Information
등이 모두 출력이 되나
여기서는 지면관게상 생략 하였다.

결론:
Bushy Tree Plan 의 원래의 활용도는 인라인뷰 2개를 만들고 각인라인뷰 내에서 조인이 발생하며
독자적인 똑똑한 FILTER 조건이 있을때 각각의 인라인뷰를 먼저 실행시키고 조인이 완료된 인라인뷰 끼리
다시 조인하는 것이 Bushy Tree Plan의 최적 활용 방안이다.
하지만 원래의 목적과는 상관없이 Driving 테이블에 훌륭한 Filter 조건이 있는경우 Nested Loop Join 이나
Hash Join 시에 Left Deep Tree Plan 으로 유도가 안될때(조인조건의 문제가 제일 많음)
Bushy Tree Plan 으로 유도하여야 한다.
오늘의 예제는 후자를 나타낸것이다.

저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. 혈기린 2008.11.06 10:50 신고  댓글주소  수정/삭제  댓글쓰기

    좋은자료 잘보았습니다 오늘도 하나를 알게되는군요 근데
    Right Deep Tree PLAN 이나 Left Deep Tree PLAN Bushy tree plan이라는게 정식명칭인가요 오라클에서 사용하는 ?

  2. Favicon of http://scidb.tistory.com BlogIcon extremedb 2008.11.06 17:46 신고  댓글주소  수정/삭제  댓글쓰기

    ~ TREE PLAN 은 오라클 뿐아니라 전체 RDB 에서 사용하는 공통적인 용어 입니다.
    용어를 알면 의사소통 하기가 편한데 안타깝게도 주로 학계에서만 사용하고 현장에서는 사용하지 않고 있습니다.
    장점이 많은데도 말이지요.
    예를들면 튜너끼리 이야기할 경우에도 "plan 을 bushy tree plan 으로 바꾸면 됩니다" 이라고 짧게 이야기 할수 있으나 용어를 모를경우 의사소통할때 힘이 들게 됩니다.

  3. Favicon of http://orapug.tistory.com BlogIcon 오라퍼그 2010.12.06 14:35 신고  댓글주소  수정/삭제  댓글쓰기

    이글만 3번째 봤습니다. 저한테는 어렵던 내용이 튜닝업무를 시작하면서 점점 가슴에 와닿고 있습니다.
    감사합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.12.06 16:13 신고  댓글주소  수정/삭제

      3번이나 읽으셨네요. 이글을 어렵게 느끼셨다면 제 불찰일 겁니다.^^ 가슴에 와 닿으셨다니 다행입니다.
      튜너가 되셨네요. 축하드립니다. 건승하세요.