포스트

HASH JOIN 시 Left Deep Plan 이 안될 때 Bushy Tree Plan 유도

4-way HASH JOIN 의 Left Deep Tree 플랜에서 똑똑한 필터(N1<50) 가 트리 끝쪽 T1 에 박혀 있어 매번 1만 건 해시 영역이 만들어집니다. 두 inline view 로 쪼개 Bushy Tree 로 유도하면 똑똑한 필터가 한쪽 가지 안에서 먼저 적용되어 PGA 사용량이 절반 수준으로 떨어집니다.

게시
By 반지현
읽는 시간 6분
HASH JOIN 시 Left Deep Plan 이 안될 때 Bushy Tree Plan 유도

Left Deep Tree 가 풀리지 않을 때 Bushy Tree 로 유도하면, 각 inline view 안에 똑똑한 필터를 가둬 해시 영역(PGA) 사용량을 크게 줄일 수 있습니다.

핵심 정리

  1. Left Deep Tree Plan 이 가장 베스트 이지만 풀리지 않을 때 Bushy Tree Plan 으로 차선책을 마련할 수 있습니다.
  2. Bushy Tree Plan 은 Inline View 2 개를 만들고 각 Inline View 내에서 조인이 발생하여 독자적인 똑똑한 filter 조건이 있을 때, 각각의 Inline View 를 먼저 실행시키고 조인이 완료된 Inline View 끼리 다시 조인하는 것입니다.
  3. Hash Join 시에 Left Deep Tree Plan 으로 유도가 안 될 때 (조인 조건의 문제가 제일 많음) Bushy Tree Plan 으로 유도해야 합니다.
  4. Hash Join 은 적은 집합을 지속적으로 Hash Table 로 유지하는 것이 중요 하고, 그 결과는 PGA 사용량까지 최대한 줄여야 합니다 (In-Memory 처리를 위해).
  5. 또한 Optimal Pass 로 Disk I/O 를 줄이는 것도 기본 사항입니다.

시나리오

1) 테이블 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

create table T1 as
with generator as (   select  /*+ materialize */  rownum as id
                        from all_objects
                       where rownum <= 3000  )
 select /*+ ordered use_nl(v2) */
      10000 + rownum                            id,
      trunc(dbms_random.value(0,5000))         n1,
      rpad(rownum,20)                        probe_vc,
        rpad('x',1000)                         probe_padding
 from generator    v1,
      generator    v2
 where rownum <= 10000;

create table T2 as select * from T1;
create table T3 as select * from T1;
create table T4 as select * from T1;

2) 인덱스 생성

1
2
3
4

alter table T1 add constraint T1_PK primary key(id);
alter table T2 add constraint T2_PK primary key(id);
alter table T3 add constraint T3_PK primary key(id);
alter table T4 add constraint T4_PK primary key(id);

3) 통계 정보 수집

1
2
3
4

EXEC dbms_stats.gather_table_stats(user, 'T1', cascade => true);
EXEC dbms_stats.gather_table_stats(user, 'T2', cascade => true);
EXEC dbms_stats.gather_table_stats(user, 'T3', cascade => true);
EXEC dbms_stats.gather_table_stats(user, 'T4', cascade => true);

원본 쿼리 — Left Deep Tree

1
2
3
4
5
6
7

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 조건 (대부분의 데이터를 걸러냅니다)

실행 계획

1

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

1
2
3
4
5
6
7
8
9
10
11

-------------------------------------------------------------------------------
| 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)|  ← Optimal (Temp 활용 없이 In-Memory 처리) 이지만 11M 사용
|   4 |    TABLE ACCESS FULL | T2   |      1 |  10000 |00:00:00.03 |          |
|*  5 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|  ← Optimal (Temp 활용 없이 In-Memory 처리) 이지만 11M 사용
|   6 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

Hash Area 사용량이 23.2 MB (11M + 11M + 1212K) 에 달합니다. 똑똑한 필터 T1.N1 < 50 이 트리 끝의 T1 에 박혀 있어 그 위 모든 HASH JOIN 단계에서 1만 건씩 해시 영역이 만들어집니다.

수정 쿼리 — Bushy Tree

T1·T2 와 T3·T4 를 각각 inline view 로 묶고, 두 inline view 결과만 다시 HASH JOIN 합니다. T12 inline view 안에 T1.N1 < 50 필터를 가두면, 그 가지의 HASH JOIN 부터 이미 90 건짜리 작은 집합만 다루게 됩니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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;

실행 계획

1
2
3
4
5
6
7
8
9
10
11
12
13

-------------------------------------------------------------------------------
| 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 |          |
-------------------------------------------------------------------------------

Hash Area 사용량이 거의 절반 수준 (≈ 13.4 MB) 으로 떨어집니다. T12 가지의 HASH JOIN (Id 3) 이 90 건 × 90 건으로 1.19M 만 사용했고, 최상위 HASH JOIN (Id 1) 도 1.19M 로 끝났습니다.

Bushy Tree 의 본질은 “똑똑한 필터를 한쪽 가지 안에 가두기” 입니다. 필터가 일찍 적용될수록 그 위 단계의 해시 영역이 작아져 PGA 가 절약됩니다.

SQL Tuning, 케이스 스터디
oracle hash-join bushy-tree left-deep-tree pga-tuning
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.