SQL Tuning 4
SQL 튜닝
조인 튜닝
NL조인
일반적으로 NL조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 사용.
Outer는 테이블 사이즈가 크지 않으면 인덱스를 이용하지 않을 수 있음.
그러나, Inner는 거의 Index사용이 강제됨.
Outer 건건이 풀테이블 스캔을 반복해야함. 이러한 비효율로 Inner 테이블은 거의 인덱스를 사용함.
이 조인 방식은 특히 인덱스 구성 전략이 중요.
소트 머지 조인
공유 메모리 영역인 SGA에 동시에 액세스하려는 프로세스 간 액세스를 직렬화 하기 위해 래치(latch) 존재.
SGA내부의 DB 버퍼 캐시에 블록을 캐싱하는 데, 이를 읽고 쓰기 위해 버퍼 lock도 얻어야함.
각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA라 하며 자신 프로세스의 고유 메모리 영역.
그러므로 래치가 필요없음.
같은 양의 데이터를 읽더라도 PGA에서 읽는 것이 SGA버퍼 캐시에서 읽을 때보다 훨씬 빠르다.
소트머지가 NL조인보다 빠른 이유:
NL조인은 블록을 건건이 찾으며, 건건이 읽어들인다.
(래치,락 획득 과정등을 반복함.)
소트 머지는 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA(또는 Temp 테이블 스페이스)에 저장한 후 조인.
(독립적인 메모리 공간이므로 래치획득과정이 없음. 일괄적으로 블록을 요청. 물론 PGA로 읽어 들일때, SGA DB버퍼캐시 경유.)
더 빠른 해시 조인으로 소트머지는 주로 다음과 같은 상황에 쓰임.
- 조인 조건식이 ‘=’조건이 아닌 대량 데이터 조인.
- 조인 조건식이 아예 없는 조인(카티시안 곱)
소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름 없다. 따라서 소트 부하만 감수한다면, 건건이 버퍼캐시를 경유하는 NL조인보다 빠름.
해시 조인
조인 컬럼을 해시 테이블 키 값으로 사용하여 해시 테이블 생성.
해시 테이블에 조인 키값만 저장한다고 알고 있지만, 조인 키값 뿐만 아니라 SQL에 사용한 컬럼을 모두 저장한다.
해시 조인이 인덱스 기반의 NL조인보다 빠른 이유:
해시 테이블은 PGA 영역에 Hash Area(또는 Temp 테이블스페이스)에 저장.
해시 조인이 소트머지 조인 보다 빠른 이유:
소트 머지 조인에서 사전 준비작업은 ‘양쪽’ 집합을 모두 정렬해서 PGA에 담는 작업.
해시 조인에서 사전 준비작업은 어느 ‘한쪽’을 읽어 해시 맵을 만드는 작업.
(이때, 작은 집합을 build input으로 정하는데 Hash Area에 담을 수 없을 정도로 큰 경우가 아니면, Temp테이블 스페이스, 즉 디스크에 쓰는 작업은 전혀 일어나지 않는다.)
즉, 인메모리 해시 조인일 때 가장 효과적이다.
설령 Temp 테이블 스페이스를 쓰게 되더라도 대량 데이터 조인시 일반적으로 해시 조인이 가장 빠르다.
해시 조인에서 leading 힌트 첫 번째 파라미터로 지정한 테이블은 무조건 build input으로 선택.
swap_join_inputs, no_swap_join_inputs으로 해시 조인에 대한 빌드 인풋을 제어
해시조인 사용시 조건
- 수행 빈도가 낮고
- 쿼리 수행 시간이 오래 걸리는
- 대량 데이터 조인할 때
이 조건들은 배치 프로그램,DW,OLAP성 쿼리의 특징.
OLTP환경에서 최적화된 NL조인으로 0.1초 걸리는 쿼리를 0.01초로 단축할 목적으로 해시 조인쓰는 것을 자제해야 한다.
해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 바로 소멸. (=>자주 쿼리를 사용하면 엄청난 비효율 발생)
서브쿼리 조인
쿼리 변환: 옵티마이저가 SQL을 분석해 의미적으로 동일하면서 더 나은 성능이 기대되는 형태로 재작성하는 것
서브 쿼리를 풀어내지 않으면(no_unnest), 필터 방식으로 처리
필터오퍼레이션(FILTER)은 기본적으로 NL조인과 처리 루틴이 비슷하다. 다른점은
- 메인쿼리의 한 로우가 서브쿼리 한로우와 조인에 성공하면 진행을 멈추고, 메인쿼리의 다음 로우를 계속 처리한다.
- 필터는 캐싱기능을 가짐. 서브쿼리 입력 값에 따른 반환 값을 캐싱하는 기능.
- 항상 메인쿼리가 드라이빙 집합(=Outer)
캐싱은 쿼리 단위로 이루어짐. 쿼리를 시작할 때 PGA공간에 할당. 쿼리를 마치는 순간 공간을 반환.
서브쿼리 Unnesting으로 메인쿼리와 같은 레벨로 만들면, 다양한 조인 메소드,순서를 마음껏 정함.
서브쿼리 Unnesting을 방지하려는 목적이 아니면 rownum을 서브쿼리에 함부로 쓰지 말자.
서브쿼리 Pushing (push_subq)
서브쿼리 필터링을 가능한 앞 단계에서 처리하도록 강제하는 기능.
이 기능은 Unnesting되지 않은 서브쿼리에만 작동
(no_unnest와 같이 기술)
merge힌트를 이용해 뷰를 메인 쿼리와 머징
반대는 no_merge
조인 조건 pushdown
메인 쿼리실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능.
VIEW PUSHED PREDICATE 오퍼레이션을 통해 이 기능의 작동 여부를 알 수 있음.
(push_pred, 뷰를 머징하면 힌트가 작동을 안함. no_merge와 함께 뷰에 기술)
스칼라 서브쿼리는 조인 횟수를 최소화 하려고 입력값과 출력값을 내부 캐시(Query Execution Cache)에 저장.
캐시에서 찾으면 저장된 값을 사용, 아니면 쿼리 실행
SELECT-LIST에 사용한 함수는 메인쿼리 결과건수 만큼 반복수행.
아래와 같이 스칼라 서브쿼리를 덧씌우면 호출 횟수를 최소화.
select GET_DNAME(e.deptno) from emp e...
=>select
(select GET_DNAME(e.deptno) from dual) dname
from emp e
...
스칼라 서브쿼리의 캐싱효과.
단, 입력값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과가 있다.
반대의 경우에는 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠짐.
메인쿼리 집합이 클수록 재사용성이 높아 효과도 크다.
메인쿼리 집합이 작을수록 재사용성이 낮다.
오히려 성능을 떨어뜨린다.
스칼라 서브 쿼리로 2개이상의 값을 반환하고 싶을 때
‘성능에 비효율이 존재하므로’
=> 인라인뷰로 바꿈
‘Gruop by로 인한 부분범위 처리불가’
=> 조인 조건 pushdown (no_merge, push_pred)
병렬 쿼리에서는 될 수 있으면 스칼라 서브쿼리를 사용하지 않아야한다.