12. 해시 조인

조회 수 6235 추천 수 0 2010.03.09 13:00:44
헌쓰 *.106.30.28

CHAPTER 12. 해시 조인




시작하면서 398 / 이창헌

PAT(pga_aggregate_target)을 쓰는 경우에는 특정 세션 레벨에서 Manual로 변환 한 후(workarea_size_policy=manual),  hash_area_size 파라미터 값을 직접 지정하여 사용한다.

빌드 테이블(build table) : 테이블 로우로 메모리에 해시 클러스터 생성

탑침 테이블(probe table) :  테이블 로우로 메모리의 해시 클러스터를 탐침

 

 

*8KB크기의 데이터 블록

*지역 관리 테이블스페이스(Locally managed tablespace)

*1MB 크리의 Uniform extent

*수동 세그먼트 공간 관리

*시스템 통계 비활성화

*hash_area_size 수동 설정

 

select

       bu.build_vc,

       pb.probe_vc,

       pb.probe_padding

from

       build_tab    bu,

       probe_tab    pb

where

       bu.id between 1 and 500

and    pb.id = bu.id_probe

 

 

작은 테이블 : 테이블로부터 추출된 작은 테이블

큰테이블 : 테이블로부터 추출된 큰 테이블

 

각 데이터 집합의 크기를 구한 후에야 옵티마이저는 빌드 테이블이 될 작은 집합을 식별하고, 그것으로 해시 테이블을 생성함.

 

 

1. 작은 테이블 집합을 선택한 후 그것을 메모리 내에 단일 테이블 해시 클러스터(충분한 메모리가 있는 것을 가정)로 변환

2. 두 번째 테이블로부터 데이터를 읽어 들임->각 로우를 읽어 들일 때 조인 컬럼 값에 동일한 해싱함수를 적용시키면서, 메모리의 해시 클러스터에서 일치하는 로우가 있는지 체크함

 


 


Optimal 해시 조인 403  /이창헌

 

v$sysstat뷰를 조회해서 optimal, onepass, multipass 통계치를 확인 가능

 

해시 값 충돌( hash collision) 문제 때문에 오라클은 hash_area_size영역의 많은 부분을 해시 버킷(각각1개의비트=비트맵 메모리)으로 할당 받아 많은 버킷을 생성함으로써 결국 해시값 충돌을 최소화 한다.

 

Hash Area Size가 Build Data Set보다 충분히 큰 경우

 

Optimal.jpg

 

 

Onepass 해시 조인 405  / 이창헌

 

Onepass해시 조인(탐침 데이터 집합이 디스크로부터 단 한번 다시 읽혀지기 때문이다.)

테이블 컬럼의 크기를 늘리고, 이렇게 길어진 컬럼을 추출함으로써 두 데이터 집함 모두가 hash_area_size에 다 들어 갈 수 없을 정도로 커짐.

 

 

select

       bu.build_vc,

       bu.build_padding,      --extra length in build row

       pb.probe_vc,

       pb.probe_padding

from

       build_tab    bu,

       probe_tab    pb

where

       bu.id between 1 and 2000  --more rows in build set

and    pb.id = bu.id_probe

;

 

Hash Area Size가 Build Data Set을 한번만 Disk에 기록
  onepass.jpg

 

 

Multipass 해시 조인 412  /이창헌미완성

Hash Area Size가 Build Data Set을 여러 번 Disk에 기록



트레이스 파일 417  /남송휘


해시조인 조사 
- 표준 CBO 이벤트 트레이스 (10053 이벤트 트레이스)
- 해시 조인 트레이스 (10104 이벤트 트레이스)


10104 이벤트 417 /남송휘


 

장점
  • 제공해주는 세세한 항목 하나하나가 매우 유용
  • multipass 조인을 onepass로
  • onepass 조인을 optimal로 하기 위한 hash_area_size 판단 단서를 제공
 

hash_multi.sql
 
alter session set events '10104 trace name context forever';
show parameter dump 로 저장위치 확인후 내용 확인
 

9.2.0.4
 
*** HASH JOIN STATISTICS (INITIALIZATION) ***
Original memory: 131072
Memory after all overhead: 129554
Memory for slots: 122880
Calculated overhead for partitions and row/slot managers: 6674
Hash-join fanout: 8
Number of partitions: 8
Number of slots: 15
Multiblock IO: 1
Block size(KB): 8
Cluster (slot) size(KB): 8
Minimum number of bytes per block: 8160
Bit vector memory allocation(KB): 4
Per partition bit vector length(KB): 0
Maximum possible row length: 1101
Estimated build size (KB): 2050
Estimated Row Length (includes overhead): 1050
 
  • Memory for slots : in-memory 해시 테이블을 생성하기 위해 최대 얼마나 많은 메모리가 필요한지 보여줌
  • Estimated build size: 옵티마이저가 추정하는 전체 슬롯을 수용하기 위해 요구되는 메모리 크기
  • optimal 해시 조인이 수행되려면 hash_area_size 를 128K 에서 2M이상으로 증가 시켜야함  
 
10.2.0.1

*** RowSrcId: 1 HASH JOIN STATISTICS (INITIALIZATION) ***
Join Type: INNER join
Original hash-area size: 129688
Memory for slot table: 122880
Calculated overhead for partitions and row/slot managers: 6808
Hash-join fanout: 8
Number of partitions: 8
Number of slots: 15
Multiblock IO: 1
Block size(KB): 8
Cluster (slot) size(KB): 8
Minimum number of bytes per block: 8160
Bit vector memory allocation(KB): 0
Per partition bit vector length(KB): 0
Maximum possible row length: 1057
Estimated build size (KB): 2046
Estimated Build Row Length (includes overhead): 1048
 
 
  • Optimal 해시 조인을 위해 hash_area_size로 충분한 메모리를 확보할 수 없다면
    트레이스 파일에서 디스크로 부터 읽은 양을 보는 부분을 확인하여 적어도 onepass 해시 조인은 가능하게 할수 있도록 할수 있음
 
 9.2.0.4
 
*** HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION: nrows:282 size=(37 slots, 296K)
 PROBE PARTION: nrows:291 size=(37 slots, 296K)
*** HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 10
Number of rows left to be iterated over (start of function): 282
Number of rows iterated over this function call: 78
Number of rows left to be iterated over (end of function): 204

 

 
10.2.0.1
*** RowSrcId: 1 HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION: nrows:208 size=(27 slots, 216K)
 PROBE PARTION: nrows:210 size=(27 slots, 216K)
*** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 10
Number of rows left to be iterated over (start of function): 208
Number of rows iterated over this function call: 78
Number of rows left to be iterated over (end of function): 130
 
 
  • 남은로우가 0이 될때까지의 (end of function )이 나타내는 회수를 알면
    multipass 조인을 onepass 조인으로 바꾸기 위한 hash_area_size를 몇배 늘려야 할지 알수 있음
 
 
 
 
파티션 수준 요약
  • 해시 조인의 일반적인 성능에 관한 단서 화인 가능

 
9.2.0.4
 
### Partition Distribution ###
Partition:0    rows:247        clusters:32     slots:1      kept=0
Partition:1    rows:244        clusters:32     slots:1      kept=0
Partition:2    rows:208        clusters:27     slots:1      kept=0
Partition:3    rows:260        clusters:34     slots:1      kept=0
Partition:4    rows:260        clusters:34     slots:1      kept=0
Partition:5    rows:243        clusters:32     slots:1      kept=0
Partition:6    rows:282        clusters:37     slots:1      kept=0
Partition:7    rows:256        clusters:33     slots:7      kept=0
 
 
10.2.0.1
 
### Partition Distribution ###
Partition:0    rows:247        clusters:32     slots:1      kept=0
Partition:1    rows:244        clusters:32     slots:1      kept=0
Partition:2    rows:208        clusters:27     slots:1      kept=0
Partition:3    rows:260        clusters:34     slots:1      kept=0
Partition:4    rows:260        clusters:34     slots:1      kept=0
Partition:5    rows:243        clusters:31     slots:1      kept=0
Partition:6    rows:282        clusters:36     slots:1      kept=0
Partition:7    rows:256        clusters:33     slots:7      kept=0
 
옵티마이져는 hash table을 8개의 파티션으로 분할
 
Rows:
최저 208개의 로우에 최고 282개의  로우를 가진 파티션이 존재하여 분포의 분균형이 있음
 
파티션간의 심각한 불균형의 원인
- 조인 컬럼에 특정한 몇개의 값이 아주 반복적으로 나타나는 비정상적인 데이터 분포
  (조인 수행중 CPU가 과도하게 소모)
- 여러개의 테이블을 조인하는경우 조인 순서가 조정되도록 쿼리를 재 작성하게 하는 단서가 됨
 
 
주요 해시함수 (Primary hash function)이 서로 값이 다른 로우간에 과도한 충돌을 유발한다면
실행코드가 보조 해시 함수 (Secondary hash function))을 사용하여 메모리의 데이터를 rehah
 
Cluster:
 해당 파티션의 모든 로우를 수용하기 위한 클러스터 (slot) 개수
 각 클러스터는 정확히 하나의 블록으로 구성
 
Slots:
 현재 메모리에 위치한 파티션의 슬롯 (클러스터) 개수 
 빌드 단계가 완료되고, 해시 테이블을 정리하기 전에 기록
 파티션당 적어도 하나의 슬롯이 메모리에 유지되어야하지만
  0~6번까지 파티션은 슬롯이 부족해 메모리에 덤핑되도록 압박을 받았으며
  7번 파티션은 필요한 33개중 7개 슬롯을 메모리에 유지
 
Kept:
 0 - 전체 파티션이 메모리에 없음
 1 - 전체 파티션이 메모리에 있음
 모든 파티션이 kept (1) 일경우 optimal 해시 조인임을 의미


 
정리)
 multipass 해시 조인을 onepass 해시 조인으로 바꾸기 위한 hash_are_size를 얼마나 증가시켜야 할지 단서제공 
 7번 파티션이 최대 7개의 슬롯을 메모리에 유지 (33개 필요한 상황에서 7개슬롯유지)
 9i의 6번 파티션은 37개의 슬롯을 필요 (37개의 슬롯을 필요)
 
 onepass 해시 조인 수행을 위해서는
 hash_area_size 를 대략 37/7 = 5.3 배 만큼 증가 시켜야 함
 (가장큰 6번 파티션이 7개 슬롯 * 5.3배 만큼 슬롯을 할당 받아 onepass로 파티션간 조인을 끝낼수 있다면 나머지역시 가능)


 
 
 

10053 이벤트 419 /남송휘



hash_opt.sql
 
9.2.0.4
 
HA Join
  Outer table:
    resc: 39  cdn: 500  rcz: 30  deg: 1  resp: 39
  Inner table: PROBE_TAB
    resc: 39  cdn: 5000  rcz: 527  deg:  1  resp: 39
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 4   Deg: 1
      hash_area:  128 (max=128)  buildfrag:  129                probefrag:   329 ppasses:    2
  Hash join   Resc: 82   Resp: 82
Join result: cost: 82  cdn: 500  rcz: 557
 
 
10.2.0.1
 
HA Join
  Outer table:
    resc: 39.00  card 500.10  bytes: 30  deg: 1  resp: 39.00
  Inner table: PROBE_TAB  Alias: PB
    resc: 39.00  card: 5000.00  bytes: 527  deg: 1  resp: 39.00
    using dmeth: 2  #groups: 1
    Cost per ptn: 5.00  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 83.00  Resp: 83.00  multiMatchCost=0.00
  HA cost: 83.00
     resc: 83.00 resc_io: 83.00 resc_cpu: 0
     resp: 83.00 resp_io: 83.00 resp_cpu: 0
Best:: JoinMethod: Hash
       Cost: 83.00  Degree: 1  Resp: 83.00  Card: 500.10  Bytes: 557




주요사항


항목 설명
Outer table 해당조인 순서(join order)에서 직전 단계까지의 데이터 획득과 관련한 몇 가지 수치.
build_tab의 500개 로우를 얻는 비용
Inner table 해당 조인 순서에서 현단계의 테이블 또는 뷰 데이터 획득과 관련한 몇 가지 수치,
probe_tab의 5,000개 로우를 얻는 비용
resc 어떤 단계를 직렬 수행할때의 비용
deg 어떤 단계의 병렬도
resp 어떤 단계를 병렬 수행 할 때의 비용
hash_area 블록 개수로 표시된 명목상의 hash_area_size 크기,
9i 에서는 조인 시의 최소 크기,_smm_min_size (kb) 를 통해 설정
hash_area
(max = )
블록 개수로 표시된 명목상의 최대 hash_area_size크기,
9i 에서는 workarea_size_policy 가 작동되는 상황에서 조인 시 사용 가능한 최대값
공식적으로 _smm_max_size 설정값으로 정해짐
메뉴얼상 하나의 작업공간에 최대값이 pga_aggregate_target의 5%이지만
여기에 기록된 값은 10%임 (2* _smm_max_size)
(hash_area_size의 디폴트 값이 sort_area_size의 2배가 되는 원래의 습성을 따른결과일듯)
런타임시는 5%의 제약 규칙이 준수(약간 융통성있음)
_pga_max_size(디폴트 200M) - 전체 PGA중 하나의 프로세스에 대한 예산배정 차원의 제약
pga_aggregate_target가 2GB를 넘지 않을 때 _smm_max_size는 _pga_max_size의 반을 넘지 않아야 
하함 (넘을 경우 _smm_max_size는 100M까지 증가하며 
_pga_max_size또는 _smm_max_size를 직접조정하지않는한 증가하지 않음)
buildfrag 블록 개수로 표시된 빌드 데이터 집합의 크기
8i에서 optimal 해시 조인을 한경우 부정환한 값이 나옴(8i 에뮬레이트 했으면 9i,10g마찬가지)
optimal hash 조인인 경우 'hash_area + 1'로 기록
현 사례의서의 정확한 값은 3
probefrag 블록 개수로 표시된 탐침 데이터 집합의 크기
ppasses 조인을 완료하는데 요구되는 탐침 pass 회수
Optimal 조인 사례의 경우 디스크로 썻다가 읽을 일이 없으므로 확실히 부정확한 값임
ppasses 값은 절대 0으로 기록되지 않음
이미 'hash join one ptn' 항목을 통해 제공되는 값이므로 단순 참고의 목적 (실발생과 일치하지않았음)
hash_area_size를 사용하고 cpu costing을 비활성, ppases값은 계산식 'buildfrag/hash_area'로 
나온듯하며 다른환경에서는 항상 1
hash
join one ptn
가장 중요한 수치
하나의 빌드 파티션을 다루는데 드는 단위 비용
Resc(직렬비용) 로 분류된것이 있음에도 이값은 병렬쿼리 에서도 사용됨
옵티마이져는 resc에 deg를 곱한 값을 조인의 총비용에 더하는 것으로 추측
Multipass 조인에서 이 수치의 구성항목 중 하나는 ppases값 만큼 미리 곱해짐
Hash join 모든 직렬 조인에서의 조인 총비용은 'Hash join one ptn'에 inner및 outer 테이블로부터 데이타를 얻는데 
소요되는 비용을 더한것
 실테스트) 9i 82=39+39+4  10g 83=39+39+5  교제) 106=4+60+42 값이 다름

 



골칫거리 421 /남송휘

해시 조인 비용 계산식의 예외적인 사항에 대해서 알아본다.

 

전통적인 비용계산법 421   /박우창

? hash_area_size를 크게할 경우 어떤 값 영역에서 다중블록I/O에서 단일블록I/O로 전환하여 비용값이 더 커지는 현상이 있다.

? 예제 - (hash_one.sql)

- probe_tab : 10,000개 튜플

Column

type

비고

id

number

10000+(0..10000) rownum

n1

number

0..5000 (random)

probe_vc

number

1..10000 (rownum)

probe_padding

varchar2(60)

x

- build_tab : 10,000개 튜플

Column

type

비고

id

number

1..10000 (rownum)

id_probe

number

10001+(0..5000) random

build_vc

number

1..10000 (rownum)

build_padding

varchar2(60)

x

- 실행계획 비교 : (10g에서는 책에서 설명한대로 안됨)

9i의 경우 1081(Hash area size 1,100KB) -> 2769(Hash area size 2,200KB)

10g의 경우 901(Hash area size 1048576) -> 692(Hash area size 2048576)

 

새로운 비용계산법 422  /박우창

? 동적작업영역을 크게 늘려보기 : pga_aggregate_target을 크게할 경우 비용값이 더 커지는 현상이 있다.

- 실행계획 비교 : (10g에서는 책에서 설명한대로 안됨)

9i의 경우 719(pga_aggregate_target 20,000KB) -> 951(pga_aggregate_target 22,000KB)

10g의 경우 520(pga_aggregate_target 20,000KB) -> 520(pga_aggregate_target 22,000KB)


비교 테스트 422  /박우창

? 메모리할당과 기능사용 여부에 따른 비용 변화를 실험한다. 오라클의 버전을 바꿀때 비용변화가 있기 때문에 필요하다.

- 표 12-3 참조

? 실험결과(8i)

- 목표 해시 테이블을 메모리가 할당한 상황에서 비용값을 계산하였다.

- 실험은 각 목표치에 대하여 4가지 (수동관리, 자동관리) * (CPU 비활성, CPU 활성)를 실험하였다.

- CPU costing 활성화 비용 < CPU costing 비활성화 비용

- 해시메모리 증가에도 비용이 증가하는 경우는 hash_area_size가 첫 번째 데이터 집합에 근접하는 경계부분에서 발생한다.

- 자동작업관리 영역에서는 optimal 해시 비용을 비현실적으로 높게 산정한다.

- 해시조인 비용이 어느부분에서 계단식으로 점프하는 이유는 클러스터 N개에 1블록을 증가시키려면 N만큼의 메모리 증가가 필요하며, 파티션을 읽는 메커니즘에 그 이유가 있다.


 

다중 테이블 해시 조인 430  / 위충환


2개 테이블 조인 만으로 쿼리 요구조건을 충족시킬 수 없어

다중 테이블 해시 조인을 수행이 요구 될 수 있음 ☞ 메모리 요구 최고조에 이름


▼ treble_hash_auto.sql

SQL> set autotrace traceonly explain
                               
 select                        
  /*+                          
   ordered                     
   use_hash(t2)                
   use_hash(t3)                
   use_hash(t4)                
   swap_join_inputs(t2)        
   swap_join_inputs(t3)        
   full(t1)                    
   full(t2)                    
   full(t3)                    
   full(t4)                    
  */                           
  count(t1.small_vc),          
  count(t2.small_vc),          
  count(t3.small_vc),          
  count(t4.small_vc)           
 from                          
  t1,                          
  t4,                          
  t2,                          
  t3                           
 where                         
  t4.id1 = t1.id               
 and t4.id2 = t2.id            
 and t4.id3 = t3.id            
 ;                             
Execution Plan
----------------------------------------------------------
Plan hash value: 2600142053
--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    38 |   592 |
|   1 |  SORT AGGREGATE       |      |     1 |    38 |       |
|*  2 |   HASH JOIN           |      |   343K|    12M|   592 |
|   3 |    TABLE ACCESS FULL  | T3   |    70 |   420 |     2 |
|*  4 |    HASH JOIN          |      |   343K|    10M|   588 |
|   5 |     TABLE ACCESS FULL | T2   |    70 |   420 |     2 |
|*  6 |     HASH JOIN         |      |   343K|  8708K|   584 |
|   7 |      TABLE ACCESS FULL| T1   |    70 |   420 |     2 |
|   8 |      TABLE ACCESS FULL| T4   |   343K|  6699K|   580 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T4"."ID3"="T3"."ID")
   4 - access("T4"."ID2"="T2"."ID")
   6 - access("T4"."ID1"="T1"."ID")


     ☞  실행계획을 보면

오라클이 t3, t2,t1 순으로 해시테이블 생성 후 t4로 탐침한다는 설명이나

오라클은 odered 힌트에 순종함

From 절에 기술된 테이블 순서는 t1, t4, t2, t3인데 오라클은 힌트대로 작동함.

  • 조인순서는 t1, t4, t2, t3
  • t4와 조인 (t1,t4)
  • t2와 조인 ((t1, t4), t2)
  • swap_join_inputs(t2) 사용으로, 해시 조인의 두 번째 테이블 t2와의 조인 시 그 순서를 뒤집음 (t2, (t1, t4))
  • t3과 조인 ((t2, (t1, t4)), t3)
  • swap_join_inputs(t3) 사용으로, 해시 조인의 두 번째 테이블 t3과의 조인 시 그 순서를 뒤집음 (t3, (t2, (t1, t4)))

    (t3, (t2, (t1, t4)))   ☞   실행계획의 것과 일치

           

▼ 위의 Sql문의 Select 결과

COUNT(T1.SMALL_VC) COUNT(T2.SMALL_VC) COUNT(T3.SMALL_VC) COUNT(T4.SMALL_VC)
------------------ ------------------ ------------------ ------------------
            343000             343000             343000             343000



■ 자동방식의 workarea_size_policy 사용에 따른 이점 (테스트환경 10gR2)


hash_area_size 설정 값                           자동                  수동


Name                                                    Value                 Value

----                                           ------               ------

session logical reads                                   6,057                             6,057         
session uga memory max                              3,290,168               11,470,792
session pga memory max                              3,473,408
                     13,631,488

consistent gets                                         6,057                    6,057
physical reads                                            254
                                 257


Hash_area_size 수동으로 설정 시 해당 쿼리가 얼마나 더 많은 메모리를 사용했는지 주목

☞ 작은 크기의 로우를 가진 3개 테이블에 대한 해시 테이블로서는 너무 많은 메모리 할당

  • 수동 hash_area_size 관리시 오라클은 각 해시 조인을 시작할 때 적어도 hash_area_size 크기의 반을 즉시 할당

  • 할당된 메모리의 일부 만이 실제로 필요한 것으로 판명되더라도 절대 메모리 할당을 부분적으로 해제하지 않음

  • pga_aggregate_target 을 크게 설정 시, 해시 테이블 슬롯에 처음 다소 큰 메모리를 할당했다가 필요 없을 시 다시 해제하는 것과 같음

  • 10104 트레이스 파일 곳곳에 해시 조인이 수행되는 동안 크기 재조정이 있었음을 보여주는 기록이 있음.

교재p.433 예시 -  슬롯이 더작게 재조정 되었음을 보여줌       


Slot table resized: old = 23 wanted = 12 got = 12 unload = 0


적은 메모리로 시작해서 더 커지는 경우도 볼 수 있음.


만일 조인 비용이 높아지는 것을 피하려고 오라클이 더 많은 메모리를 필요로 하면,

첫 번째 빌드 단계가 끝나는 시점에 해시 테이블 크기를 증가 시킬 것임.




요약 433  /이창헌



테스트 스크립트 434  /이창헌