-
1.기본 매커니즘
begin for outer in (select deptno,empno, rpad(ename,10) ename from emp ) loop --outer 루프 for inner in (select dname from dept where deptno=outer.deptno ) loop --inner 루프 dbms_output.put_line(outer.empno||':'||outer.ename||':'||inner.dname); end loop; end loop; end;2. NL 조인 수행 과정 분석
select /*+ ordered use_nl(e) */ e.empno ,e.ename ,d.dname ,e.job ,e.sal where e.deptno = d.deptno --------------1 and d.loc = 'SEOUL' --------------------2 and d.gb = '2' -------------------------3 and e.sal > 1500 -----------------------4 order by sal desc; ==인덱스== PK_DEPT : dept.deptno DEPT_LOC_IDX : dept.loc PK_EMP : emp.empno EMP_DEPTNO_IDX : emp.deptno EMP_SAL_IDX : emp.sal조건절 비교 순서와 위 5개 인덱스 중 어떤 것이 사용될까?
비교 순서는 위 조건절에 매겨진 번호가 2 > 3 > 1 > 4 며,
EMP, DEPT 테이블의 DEPTNO 가 있는 INDEX 가 이용된다.
0 SELECT STATEMENT 1 0 SORT ORDER BY 2 1 NESTED LOOPS 3 2 TABLE ACCESS BY INDEX ROWID DEPT 4 3 INDEX RANGE SCAN DEPT_LOC_IDX 5 2 TABLE ACCESS BY INDEX ROWID EMP 6 5 INDEX RANGE SCAN EMP_DEPTNO_IDX
위 그림으로 살펴보면
1. dept_loc 조건을 만족하는 레코드를 찾기 위해 dept_loc_idx 범위 스캔
2. dept_loc_idx 로 읽은 rowid 로 dept 테이블에 액세스래 dept_gb=2 필터를 를 만족하는 레코드를 찾는다.
3. dept 에서 읽은 deptno 로 emp 테이블에서 emp_deptno_idx 인덱스를 범위스캔한다.
4. emp_deptno_idx 인덱스에서 읽은 rowid 를 가지고 emp 테이블을 액세스해 sal>=1500 필터를 만족하는 레코드를 찾는다.
5. 1~4 과정을 통과한 레코드들을 sal 컬럼 기준 내림차순으로 정렬한 뒤 결과를 리턴한다.
여기서 기억할 것은 각 단계를 완료하고 넘어가는게 아니라 한 레코드씩 순차적으로 진행하며,
order by 는 전체 집합을 대상으로 정렬해야하기 때문에 작업이 모두 완료되면 실행된다.
11,19,31,32 는 스캔할 데이터가 더 있는지 확인하는 one-plus 스캔을 표시한 것이다.
o 표시는 filter 에 통과한 레코드, x표시는 필터에 걸러진 레코드를 의미한다.
위 그림을 보면 dept_loc_idx 인덱스를 스캔하는 양에 따라 전체 일량이 좌우됨을 이해할 수 있다.
첫번째 부하지점은 여기서 단일 컬럼 인덱스를 '=' 조건으로 스캔했으므로 비효율 없이 6 건을 읽었고, 그만큼 테이블에 랜덤 액세스가 발생했다.
만약 dept 테이블로 많은 양의 랜덤 액세스가 있었는데 gb=2 조건에 의해 필터링되는 비율이 높다면 어떻게 해야할까?
이미 배웠듯이 dept_loc_idx 에 gb 컬럼을 추가하는 방안을 고려해야한다.
두번째 부하지점은 emp_deptno_idx 인덱스를 탐색하는 부분이다. outer 테이블인 dept 를 읽고 나서 조인 액세스가 얼마나 발생하느냐에 의해 결정된다. 이것 역시 랜덤 엑세스에 해당한다.
위 그림에서는 gb=2 조건을 만족하는 건수만큼 3번의 조인 시도가 있었다.
만약 emp_deptno_idx 의 높이가 3이면 건마다 그만큼의 블록 I/O 가 발생하고, 리프 블록을 스캔하면서 추가적인 블록 I/O 가 더해진다.
세번째 부하지점은 emp_deptno_idx 를 읽고 나서 emp 테이블을 액세스하는 부분이다. 여기서도 sal>=1500 조건에 의해 필터링되는 비율이 높다면, emp_deptno_idx 인덱스에 sal 컬럼을 추가하는 방안을 고려해야 한다.
OLTP 시스템에서 조인을 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서다. 우선 NL 조인 매커니즘을 따라 각 단계의 수행 일량을 분석해 과도한 랜덤 액세스가 발생하는 지점을 파악한다. 조인 순서를 변경해 랜덤 액세스 발생량을 줄일 수 있는 경우가 있지만, 그렇지 못할 때는 인덱스 컬럼 구성을 변경하거나 다른 인덱스의 사용을 고려해야 한다.
여러 가지 방안을 검토한 결과 NL 조인이 효과적이지 못하다고 판단될 때 해시 조인이나 소트 머지 조인을 검토한다.
NL 조인의 특징
대부분 DBMS 가 블록(또는 페이지) 단위로 I/O 를 수행한다. 하나의 레코드를 읽으려고 블록을 통째로 읽는 랜덤 액세스 방식은 설령 메모리 버퍼에서 빠르게 읽더라도 비효율이 존재한다.
그런데 NL 조인의 첫번째 특징이 랜덤 액세스 위주의 조인 방식이라는 점이다. 따라서 인덱스 구성이 아무리 완벽하더라도 대량의 데이터를 조인할 때 매우 비효율적이다.
두번째 특징은 조인을 한 레코드씩 순차적으로 진행한다는 점이다. 첫번째 특징 때문에 대용량 데이터 처리시 매우 치명적인 한계를 드러내지만, 반대로 두번째 특징 때문에 아무리 대용량 집합이더라도 매우 극적인 응답 속도를 낼 수 있다.
부분범위처리가 가능한 상황에서 그렇다. 그리고 순차적으로 진행하는 특징때문에 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정된다.
다른 조인 방식과 비교했을 때, 인덱스 구성 전략이 특히 중요하다는 것도 NL 조인의 특징이다. 조인 컬럼에 대한 인덱스가 있느냐 없느냐, 있다면 컬림이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라진다.
이런 여러가지 특징을 종합할 때, NL 조인은 소량의 데이터를 주로 처리하거나 부분범위처리가 가능한 온라인 트랜젝션 환경에 적합한 조인 방식이라고 할 수 있다.
NL 조인 확장 매커니즘
버전이 올라가면서 오라클은 NL 조인 성능을 높이기 위해 테이블 Prefetch, 배치 I/O 기능을 도입했다.
'테이블 Prefetch' 는 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 곧 이어 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능이다.
'배치I/O' 는 디스크 I/O call 을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능이다. 두 기능 모두 읽는 블록마다 건건이 I/O call 을 발생시키는 비효율을 줄이기 위해 고안됐다.
내부 원리까지 자세히 알 필요는 없지만, 튜닝하는 과정에 이들 기능을 표현한 실행계획을 자주 보게 되므로 표현 방식은 익혀둘 필요가 없다.
가. 전통적인 실행계획
오라클이 NL 조인을 표현하기 위해 전통적으로 사용해 온 방식은 다음과 같다.
Rows Row Source Operation --------------------------------- 5 NESTED LOOPS 3 TABLE ACCESS BY INDEX ROWID OF 사원 5 INDEX RANGE SCAN OF 사원_x1 5 TABLE ACCESS BY INDEX ROWID OF 고객 8 INDEX RANGE SCAN OF 고객_X1나. 테이블 Prefetch 실행계획
오라클 9i 부터 다음과 같은 표현방식도 같이 나타나기 시작했다.
Rows Row Source Operation --------------------------------- 5 TABLE ACCESS BY INDEX ROWID OF 고객 12 NESTED LOOPS 3 TABLE ACCESS BY INDEX ROWID OF 사원 3 INDEX RANGE SCAN OF 사원X_1 8 INDEX RANGE SCAN OF 고객_X1다. 배치 I/O 실행계획
오라클 11g부터 다음과 같은 표현 방식도 같이 나타나기 시작했다.
Rows Row Source Operation --------------------------------- 5 NESTED LOOPS 8 NESTED LOOPS 3 TABLE ACCESS BY INDEX ROWID OF 사원 3 INDEX RANGE SCAN OF 사원_x1 8 INDEX RANGE SCAN OF 고객_X1 5 TABLE ACCESS BY INDEX ROWID OF 고객이는 Inner 쪽 테이블에 대한 디스크 I/O 과정에서 배치 I/O 기능이 작동할 수 있음을 표시하기 위함이다.
nlj_batching, no_nlj_batching 힌트를 이용해 이 실행계획이 나오게 할 수도 있고, 안나오게 할 수도 있다.
오라클 11g 이후로 위 세가지 실행계획이 모두 나타나는데, inner 쪽 테이블 블록을 모두 버퍼캐시에서 읽는다면 어떤 방식으로 수행하든 성능의 차이가 없다. 테이블 출력 순서도 100% 같다. 다만 '일부를 디스크에서 읽게 되면' 성능의 차이가 나타날 수 있고, 배치 I/O 실행계획이 나타날 때는 결과 집합의 정렬 순서도 다를 수 있어 특별한 주의가 필요하다.
11g부터 NL 조인 결과 집합이 항상 일정한 순서로 출력되기를 원한다면, 배치 I/O 기능이 작동하지 못하도록
no_nlj_batching 힌트를 추가하거나 order by 절에 정렬 순서를 명시해야 한다.
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형