트러블슈팅 오라클 퍼포먼스 2판 (2017년)
조인 최적화 0 0 47,067

by 구루비스터디 조인 최적화 [2018.09.27]


조인 최적화

조인유형
  • 크로스 조인 (카티전 프로덕트) = 한테이블의 모든 로우와 다른 테이블의 모든 로우를 한 번씩 결합
  • 세타 조인 (이너 조인) = 모든 로우에 대한 결합 이후 필요한 로우만 반환하는 결합
  • 이쿼 조인 (동치 조인) = '=' 연산자를 이용한 결합
  • 셀프 조인 (자기 조인) = 테이블 자신과 조합되는 경우
  • 아우터 조인 = 세타 조인과 동일하며 보존하고자 하는 테이블의 모든 데이터도 같이 표시한다.
  • 구형 문법에는 "( " "+" " )"를 이용하여 아우터 조인을 사용한다. 신형문법은 LEFT , RIGHT , FULL OUTER JOIN 사용한다.
  • LEFT , RIGHT JOIN PARTITION BY (파티션화 아우터 조인) 특정 컬럼을 기준으로 부분집합으로 분할되고 이를 기준으로 다른 테이블과 아우터 조인을 한다.
  • 세미 조인 = 한 테이블의 로우중에 다른 테이블과 일치하는 로우만 반환한다.
  • 안티 조인 = 한 테이블의 로우중에 다른 테이블과 일치하지않는 로우만 반환한다.
  • 래터럴 인라인 뷰 = FROM절의 앞에 오는 다른 테이블을 참조하는 상관관계가 포함된 인라인뷰이다.


제한 조건 VS 조인 조건

SELECT <column>
FROM <table1> [OUTER] JOIN <table2> ON ( <조인 조건> )
WHERE <제한 조건>


  • 두데이터의 집합이 조인 조건을 사용하여 조인된다.
  • 조인에 의해 반환된 결과 집합에 대해 제한 조건이 적용된다.


네스트드 루프 조인

특징
  • 좌측 입력(아우터 루프)은 한번만 수행된다. 우측 입력(이너 루프)은 여러번 수행 될수 있다.
  • 전체 로우의 처리가 끝나기 전에 첫 로우를 반환할 수 있다.
  • 제한 조건과 조인 조건 모두에 적용되는 인덱스를 활용할 수 있다.
  • 모든 유형의 조인을 지원한다.


2개 테이블 간 조인
  • 상관결합유형 - 첫번째 자식이 두번째 자식의 실행을 제어한다.
  • 아우터루프(드라이빙 테이블)의 개수만큼 이를 이너루프(드리븐 테이블)에 반환하며 이너루프의 선택도에 따라 풀스캔 또는 인덱스 스캔을 선택해야 한다.


버퍼 캐시 프리페치
  • 풀테이블 스캔을 제외한 모든 액세스 패스는 캐시 미스 이벤트를 만나면 싱글 블록 물리적 읽기를 수행한다.
  • 이는 네스티드 루프 조인에 최악의 성능을 제공한다. 따라서 이를 개선하기위해서 싱글블록 I/O를 멀티블록 I/O로 바꾸는 최적화 기법이 필요하다.


  • 테이블 프리페칭 (NLJ_PREFETCH, NO_NLJ_PREFETCH)
  • 배칭(NLJ_BATCHING , NO_NLJ_BATCHING)
  • 버퍼캐시 프리웜(Prewarm) = 인스턴스 재기동 후 네스티드 루프 조인이 수행될 ? 잠시 동안 발생한다.
  • 최적화 기법이 사용되었는지 확인하는 유일한 기법은 서버프로세스가 수행한 wait event를 분석하는 것이다.


  • db file sequential read 단일블록 읽기 / 최적화기법이 사용되지 않은 상태
  • db file scattered read or db file parallel read 멀티블록 읽기 / 최적화 기법이 사용된 상태


머지 조인

  • 개념 : 조인 조건의 컬럼을 기준으로 정령한다. 이후 두개의 영역에 데이터가 병합된다.


특징
  • 좌측 입력은 한번만 실행
  • 우측은 한번 이하 실행
  • 조인 컬럼 기준의 정렬
  • 양측 입력을 모두 읽고 정렬완료 후에 첫 로우 반환
  • 모든 유형의 조인을 지원한다.


작업 영역
  • 머지 조인은 2개의 작업 영역
  • 1) 메모리 내 정렬
  • 2) 디스크 기반 정렬 (원패스 , 멀티패스)


해시 조인

  • 개념 : 해시조인 처리 1. 빌드 입력 , 2. 프루브 입력
  • 빌드 입력은 모든 로우를 메모리상의 해쉬테이블을 구성한다.
  • 해쉬 키는 조인조건의 컬럼을 사용하여 계산된다.
  • 일단 빌드 입력의 모든 데이터가 해시 테이블에 들어가면 프루브 입력 데이터의 처리가 시작된다.
  • 해시 테이블을 대상으로 각 로우들을 검색(prob)하여 조인조건을 만족시키는지 확인한다.
  • 만족시키는 로우만 반환한다.


  • 빌드 입력은 단한번만 이루어진다.
  • 프루브 입력은 한번이하로 발생한다.
  • 해시 테이블은 빌드 입력에 대해서만 구성된다.
  • 첫번째 로우를 반환하기 전에 처리가 완료되어야 하는 입력은 빌드 입력뿐이다.
  • 크로스 조인과 세타 조인 , 파티션화 아우터 조인은 지원하지 않는다.


작업영역
  • 해시 조인의 처리에는 해시 테이블을 저장하기 위한 메모리상의 작업 영역이 사용된다.
  • 만약 부족할 경우 임시 세그먼트를 이용한다.


인덱스 조인
  • index join은 해쉬 조인을 통해서 이루어진다.
  • 테이블의 2개의 인덱스를 이용하여 큰 테이블 스캔을 피하는 것인다.
  • 두 집합간의 조인은 rowid를 기반으로 하고 있다.
  • index_join 힌트를 통해서 구현한다.


아우터 조인

  • 3기지 방법의 조인을 모두 지원하며 실행계획에 OUTER란 키워드만 추가될 뿐이다.


조인 방법의 선택

  • 쿼리 옵티마이저의 최적화 목표 , 즉 first-rows 와 all-rows 최적화
  • 최적화될 조인의 유형과 제한 조건의 선택도
  • 조인의 병렬 수행 여부


FIRST-ROWS 최적화
  • 첫번째 로우를 얼마나 빠른시간에 반환하느냐를 결정 따라서 NESTED LOOP JOIN을 통한 최적화가 많다.


ALL-ROWS 최적화
마지막 로우를 얼마나 빠른시간에 반환하느냐를 결정


지원되는 조인 방법
조인NESTED LOOPHASHMERGE
크로스 조인v
v
세타 조인v
v
이퀄 조인vvv
세미/안티 조인vvv
아우터 조인vvv
파티션화 아우터 조인v
v


병렬 조인
  • 모든 조인은 병렬로 수행이 가능하다.


파티션 와이즈 조인

  • 쿼리 옵티마이저가 MERGE / HASH JOIN 시 사용하는 최적화 기법
  • CPU , Memory , Network 등 리소스를 줄이는 목적이며 full과 partial 중 하나를 사용한다.


완전 파티션 와이즈 조인 (full)
  • 이는 동일한 파티션키로 구분되는 각각의 테이블 파티션별로 슬레이브 프로세스를 두어 각 파티션별로 조인 작업을 수행한다.
  • 완전 파티션 와이즈 조인은 모든 파티셔닝 방법을 지원하며 파티션과 서브 파티션의 조인도 지원한다. (조인키 = 파티션키)


부분 파티션 와이즈 조인 (partial)
  • 2개의 테이블을 동일 기준으로 파티션할 필요가 없다.
  • 한쪽 테이블만 조인 키를 기준으로 하여 파티션되어 있으면 충분하고, 다른 쪽 테이블은 파티션 여부에 관계없이 조인 키를 기준으로 하여 동적으로 파티션된다.
  • 실제로 부분 파티션 와이즈 조인을 사용한다고 해서 항상 성능이 개선되는 것은 아니다.
  • 대체로 일반 조인이 부분 파티션 와이즈 조인보다 빠를 것이다.


스타 변환

  • 스타변환은 스타스키마에서 사용되는 최적화 기법이다.
  • 이 유형의 스키마는 커다란 중심 테이블인 팩트(fact)테이블과 여러 개의 다차원 테이블로 구성된다.
  • 팩트 테이블이 차원테이블을 참조한다.


스카 스키마 최적화
  • 제한 조건을 가지는 각 차원 테이블을 평가한다.
  • 결과 차원 키들을 조합하여 목록을 생성한다.
  • 이 목록을 사용하여 팩트 테이블에서 일치하는 로우를 추출한다.


오라클에서 이기능을 사용하기 위핸 조건
  • star_transformation_enabled 파라미터 (temp_disable 또는 TRUE)
  • 각각의 조인 조건에 대해 팩트 테이블에 인덱스가 생성되어야 한다. (최적화 : 외래키 + 비트맵인덱스)


스타 변환의 수행 단계 요약
  • 차원 테이블들과 이에 대응하는 팩트 테이블의 비트맵 인덱스를 조인한다. 제한조건이 적용된 차원테이블에만 필요하다.
  • 결과 비트맵을 병합 rowid를 반환한다. 이 rowid를 사용하여 팩트 테이블을 액세스한다.
  • 차원 테이블과 팩트 테이블에 추출된 데이터를 조인한다. where절 이외의 select , group by , order by 절에서 참조하는 컬럼을 가진 차원 테이블에서만 필요하다.


또한 2가지 최적화를 적용할 수 있다.
  • temporary table : 차원 테이블의 이중 처리 방지하기 위해서 결과 로우를 임시 테이블에 저장한다. (star_transformation_enabled = TRUE)
  • 비트맵 조인 인덱스 : 차원 테이블과 팩트 테이블 간의 비트맵 인덱스 간의 조인을 피하는 것이다.


  • 스타 변환은 비용 기반의 쿼리 변환이다. 쿼리 옵티마이저가 논리적으로 합당한지 판단 및 임시 테이블 / 비트맵 조인 인덱스 생성 등을 고려한다. (star_transformation , no_star_transformation hint)
"데이터베이스 스터디모임" 에서 2017년에 "전문가를 위한 트러블슈팅 오라클 퍼포먼스(Second Edition) " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4095

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입