포스트

PLAN Right Deep Tree VS Left Deep Tree (공식화)

게시
By 반지현
읽는 시간 11분
PLAN Right Deep Tree VS Left Deep Tree (공식화)

동일한 4 테이블 hash join 도 build / probe 순서 (LEADING) 에 따라 PGA 사용량이 6 배 이상 차이 납니다. Right Deep Tree 는 큰 hash table 을 동시에 여러 개 메모리에 유지해야 해서 대부분 악성 plan 입니다. Left Deep Tree 로 유도하면 작은 결과만 점진적으로 hash table 로 유지되어 PGA 가 압도적으로 절감됩니다.

핵심 정리

  1. Right Deep Tree 는 대부분 악성 plan 입니다. 내부 테이블의 데이터를 메모리에 오랫동안 유지해야 하므로 PGA / I/O 비용이 증가합니다.
  2. Left Deep Tree 는 조인 결과가 점진적으로 생성되어 메모리 활용도가 높습니다. 첫 번째 join 부터 작은 결과 집합만 hash table 로 유지됩니다.
  3. 튜닝 포인트: 적은 집합을 지속적으로 hash table 로 유지하도록 LEADING 힌트로 build 순서를 잡고, 그 결과 PGA 사용량을 최대한 줄이는 것이 핵심입니다.

시나리오

같은 구조의 테이블 4 개 (T1, T2, T3, T4) 를 ID 로 chain 조인합니다. 각 테이블은 10,000 건이고 ID 에 PK 인덱스를 가집니다.

테이블건수인덱스
T110,000T1_PK (ID)
T210,000T2_PK (ID)
T310,000T3_PK (ID)
T410,000T4_PK (ID)

T1.N1 < 50 필터가 핵심입니다. N1 이 0 ~ 5,000 random uniform 분포이므로 10,000 건 중 약 90 건만 살아남습니다. 이 불균형 (T1: 90 건, T2 / T3 / T4: 10,000 건) 이 LEADING 선택에 따라 PGA 사용량을 좌우합니다.

테스트 데이터 생성 SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- 테이블 4 개 모두 동일 구조, 각 10,000 건
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;

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

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

1) 원본 — Right Deep Tree (LEADING(T3 T4 T2))

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;

옵티마이저는 가장 안쪽부터 hash join 을 쌓아 올립니다. T3 과 T4 를 먼저 join 하고, 그 결과(10,000 건) 와 T2(10,000 건) 를 join 한 뒤, 마지막에 T1(90 건) 을 probe 합니다. 가운데 단계의 hash table 들이 모두 10,000 건을 담은 채 메모리에 유지됩니다.

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 처리(괄호 0)지만 Hash Area 11M 사용
|   4 |    TABLE ACCESS FULL | T2   |      1 |  10000 |00:00:00.03 |          |
|*  5 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|  ← Optimal 처리지만 Hash Area 11M 사용
|   6 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

Used-Mem 컬럼 합산: 1,212K + 11M + 11M ≈ 23.2 MB. 모든 단계가 Optimal 처리(괄호 안 0) 되었지만, 여러 hash table 이 동시에 큰 결과를 담고 있어 PGA 사용량 자체가 큽니다.

2) 수정 — Left Deep Tree (LEADING(T1 T2 T3))

1
2
3
4
5
6
7

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1 T2 T3) USE_HASH(T2 T3 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;

T1 부터 시작합니다. 필터가 먼저 적용된 90 건이 첫 hash table 의 build input 이 되고, 이후 단계마다 chain 의 결과 (90 건) 가 다음 hash table 의 build input 이 됩니다. 모든 hash table 이 90 건 단위로만 유지됩니다.

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.07 | 1220K (0)|
|*  2 |   HASH JOIN          |      |      1 |     90 |00:00:00.10 | 1229K (0)|
|*  3 |    HASH JOIN         |      |      1 |     90 |00:00:00.07 | 1229K (0)|
|*  4 |     TABLE ACCESS FULL| T1   |      1 |     90 |00:00:00.04 |          |
|   5 |     TABLE ACCESS FULL| T2   |      1 |  10000 |00:00:00.03 |          |
|   6 |    TABLE ACCESS FULL | T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |   TABLE ACCESS FULL  | T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

Used-Mem 합산: 1,220K + 1,229K + 1,229K ≈ 3.6 MB. 동일한 4 테이블 join 인데 PGA 가 약 6.4 배 절감되었습니다. 처리 시간도 0.13 초에서 0.07 초로 단축됩니다.

분석

트리 모양 비교

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

Right Deep Tree (원본)              Left Deep Tree (수정)

         HJ  Id 1                          HJ  Id 1
         /\                                /\
        /  \                              /  \
       T1   HJ  Id 3                    HJ   T4
       (90)  /\                         /\
            /  \                       /  \
          T2   HJ  Id 5              HJ   T3
          (10K) /\                   /\
               /  \                 /  \
             T3   T4              T1   T2
             (10K)(10K)           (90)(10K)

오른쪽으로 (Right Deep) 점점 깊어지면 가장 안쪽 join 부터 큰 hash table 을 쌓아 올라가게 되고, 각 단계의 build input 이 모두 10,000 건짜리 큰 집합입니다. 왼쪽으로 (Left Deep) 점점 깊어지면 첫 join 부터 작은 결과 (90 건) 가 build input 이 되고, 이후 단계도 90 건만 담은 hash table 이 됩니다.

Hash Join 의 build 와 probe

Hash join 은 두 입력 중 build (왼쪽 자식) 를 메모리에 hash table 로 만들고, probe (오른쪽 자식) 를 한 row 씩 흘려보내며 매칭합니다. 따라서 build 가 작아야 PGA 가 절약됩니다.

  • Right Deep: 안쪽 join 의 build 들이 모두 큰 집합 (T2, T3)
  • Left Deep: 첫 build (T1 90 건) 부터 시작해서 점진적으로 만들어진 결과 (90 건) 가 다음 build 가 됩니다

LEADING 힌트의 첫 번째 테이블이 곧 가장 깊은 build 가 되므로, 작은 (필터 결과가 작은) 테이블을 LEADING 선두에 두는 것이 일반적인 정답입니다.

모두 Optimal 인데 왜 차이가 나는가

원본 plan 의 Used-Mem 옆 괄호가 모두 0 이라 두 plan 모두 in-memory hash join (Optimal) 으로 처리되었습니다. 즉 temp table 로 spill 되지 않았습니다. 그럼에도 불구하고 PGA 사용량 자체가 23.2 MB → 3.6 MB 으로 차이가 나는 이유는, Optimal 여부와 PGA 절대량은 별개 이기 때문입니다. PGA 가 부족한 환경에서는 Right Deep 이 Optimal 에서 OnePass / MultiPass 로 떨어져 temp 사용으로 이어지고, 응답 시간이 급증할 수 있습니다.

Right Deep Tree 는 대부분 악성 plan 입니다. 4 테이블 이상 hash join 에서 LEADING 힌트로 build 순서를 잡을 때는 filter 결과가 작은 테이블을 선두에 두어 Left Deep Tree 모양을 유도하세요. PGA 절감뿐 아니라 응답 시간도 줄어듭니다.

Used-Mem 비교

단계Right Deep (원본)Left Deep (수정)
가장 위 HASH JOIN1,212K1,220K
가운데 HASH JOIN11 M1,229K
가장 안쪽 HASH JOIN11 M1,229K
합계≈ 23.2 MB≈ 3.6 MB
A-Time0.13 초0.07 초

정리

세 줄로 압축하면:

  1. Right Deep Tree 는 대부분 악성 plan. 안쪽 hash table 들이 모두 큰 집합을 동시에 담고 있어 PGA / I/O 부하가 큽니다.
  2. Left Deep Tree 는 점진적 join. 첫 build 부터 작은 결과만 hash table 로 유지되어 PGA 사용량이 압도적으로 작습니다.
  3. 튜닝 룰: LEADING 힌트 선두에 filter 결과가 작은 테이블을 두어 Left Deep Tree 를 유도. 진단 시그널은 plan 의 Used-Mem 컬럼이며, 큰 값이 여러 단계에 동시 등장하면 Right Deep 을 의심합니다.
SQL Tuning, 케이스 스터디
oracle hash-join left-deep-tree right-deep-tree pga-tuning
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.