Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝)

HASH 조인은 DW 뿐아니라 OLTP 의 배치업무및 심지어 OLTP 의 조회성 업무에까지 적용범위를
넓혀가고 있기 때문에 그중요성은 매우크다고 할수 있다.
한가지 아쉬운점은 개발자및 DBA, 튜너 들이 PLAN 을 보고 Driving 테이블만 제대로 나오면 검증하지 않고
그냥 넘어간다는 것이다.
좀더 꼼꼼한 튜너는 SWAP_JOIN_INPUTS 이나 LEADING, ORDERED 등의 힌트로 SIZE가 적은 집합순으로
HASH 조인을 하고 실행후 결과에서 모든 HASH 조인이 Optimal Pass(주1) 가 나오면 그것으로 튜닝을 끝낸다.
하지만 HASH 조인은 다른 조인들과 달라서 몇가지 더 검증해야될 부분이 있다.
오늘은 그중에 한가지에 대하여 소개한다.

실행환경 : Oracle 10g R2

아래는 테스트시 필요한 테이블과 인덱스 생성및 통계정보를 gathering 하는 스크립트 이다.

1.테스트 테이블및 인덱스 생성

-- 테이블 생성
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);

2 통계정보 생성

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

 

3.Right Deep Tree, Left Deep Tree Plan 의 개념

먼저 테스트를 수행하기전에 간단하게 Right Deep Tree, Left Deep Tree Plan 에 대하여 알아보겠다.

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|*  1 |  HASH JOIN           |      |
|*  2 |   TABLE ACCESS FULL  | T1   |
|*  3 |   HASH JOIN          |      |
|   4 |    TABLE ACCESS FULL | T2   |
|*  5 |    HASH JOIN         |      |
|   6 |     TABLE ACCESS FULL| T3   |
|   7 |     TABLE ACCESS FULL| T4   |
-------------------------------------

위와 같은 PLAN 을 오라클을 많이 사용하는 사람이라면 자주 보았을 것으로 예상한다.
아래로 내려갈수록 Operation 이 오른쪽으로 밀려난다.
이것이 Right Deep Tree Plan 이다.
필자는 Hash 조인을 사용하면서  T1 을 엑세스 할때 많은 양의 데이터가 filter 되는 경우 
Right Deep Tree Plan 이 나오면 일단 부정적으로 보고 튜닝을 시작한다.
왜냐하면 대부분의 경우 악성 plan 이기 때문이다.
위 plan 의 조인순서는 아래와 같다.
1. T3 와 T4를 조인한다.
2. T2 와 1번의 결과집합을 조인한다.
3. T1 과 2번의 결과집합을 조인한다.
따라서 실제 조인순서는 T3 --> T4 --> T2 --> T1 이지만 많은수의 개발자나 DBA 들은 이점을 놓치고 있다.
즉 T1 이 Driving 이면서 많은 양의 데이터가 filter 되는 경우 위와 같은 plan 이나오면 T1 과의 조인은
맨마지막에 실행되기 떄문에 T3 --> T4 --> T2 조인이 처리될때 까지 데이터의 범위를 줄일수가 없다.
다시말하면 전체건에 대하여 2번을 조인한 후에 T1 과 조인 하기 때문에 최악의 Hash 조인이라는 것이다.

아래의 PLAN 은 위의 plan 과 반대이며 Left Deep Tree PLAN 이라고 한다.
아래 plan 의 조인순서는 당연히 T1 --> T2 --> T3 --> T4 이며 T1 테이블의 엑세스 및 Filter 과정에서
많은수의 데이터가 FILTER 된다면 최적의 PLAN 이다.

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|*  1 |  HASH JOIN           |      |
|*  2 |   HASH JOIN          |      |
|*  3 |    HASH JOIN         |      |
|*  4 |     TABLE ACCESS FULL| T1   |
|   5 |     TABLE ACCESS FULL| T2   |
|   6 |    TABLE ACCESS FULL | T3   |
|   7 |   TABLE ACCESS FULL  | T4   |
-------------------------------------

이제 2개의 plan 을 테스트 해보자.
아래의 Right Deep Tree Plan 테스트는 실제환경에서 나오지 말아야 할 PLAN 을 테스트 한것이다.
테스트를 위하여 힌트를 주어 일부러 악성 plan 을 만들었다.
특히 힌트부분을 주목하라.

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

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

-------------------------------------------------------------------------------
| 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 은 FILTER 조건 (T1.N1 < 50) 이 있지만 마지막 조인시 까지 조인건수를 줄일수 없으므로
hash area size 를 많이 쓰게 된다. 
그결과 수행속도가 느리며 특히 메모리 사용량(Used-Mem 참조)이 23.2(11MB + 11MB + 1212K) MB 에 달한다.
Hash 조인 3번의 수행시간(A-Time 참조) 또한 0.13 + 0.18 + 0.10 으로 0.41 초나 걸렸다.
3번의 조인 모두 Optimal Pass(빨강색 부분이 0 이면 Optimal 임) 라고 방심하면 안된다.
여기에는 엄청난 비효율이 숨어 있다.

아래는 Left Deep Tree PLAN 으로 튜닝하여 테스트를 수행한 결과이다.

4.Left Deep Tree PLAN 테스트

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

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

-------------------------------------------------------------------------------
| 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 컬럼의 사용량이 Right Deep Tree Plan 에 비해 대폭 줄어든것에 주목해야 한다.
Hash 조인시 사용한 총 hash area size 사용량 = 1220K + 1229K + 1229K (약 3.6 MB) 이다.
Left Deep Tree PLAN 을 사용하는것으로 바꾸니 놀랍게도 메모리 사용량이 6.5 배나 줄었다.
또한 A-Time 의 수행시간도 약 2배나 차이가 난다.
테스트를 짧게 끝내기위해 각 테이블을 만건으로 한정 했지만 건수가 많아 질수록 차이는
더 벌어질 것이다.

결론 :
T1.N1 < 50 과 같은 훌륭한 filter 조건이 있는 경우에 통계정보의 부재, 부적절한 조인조건등 여러가지
이유로 인하여 Right Deep Tree Plan 이 나온다면 튜닝을 하여 Left Deep Tree  Plan 으로 만들어야 한다.
물론 예외적인 경우 Left Deep Tree PLAN 으로 유도 하는것이 불가능 할수도 있다.
하지만 그때에도 Bushy Tree Plan(주2) 등으로 유도하여 Hash 조인을 튜닝 하여야 한다.
그렇지 않을 경우 과도한 메모리 사용과 수행속도 저하를 막을수 없다.


주1 : Optimal Pass 란 Hash 조인시 build 테이블(Driving)을 Scan 하면서 메모리에 적재하는 과정을
       거치는데 이때 메모리 공간(Hash Area Size) 가 부족하면 메모리에 적재하지 못하고
       Temp 영역(DISK)에 적재를 하는 비효율이 있지만  Optimal Pass 는 메모리 공간이 충분하여
       Disk 작업이 없는 상태를 말한다.
주2: 간단히 설명하면 T1 과 T2 를 조인하고 T3 와 T4 를 조인하여 2개의 결과 집합을 조인하는 방식임.
     자세한 내용은 Hash 조인시 Bushy tree Plan 유도하기 를 참조하기 바란다.