- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4948020&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
01. Nested Loops 조인
(1) 기본 메커니즘
- Nested Loops조인은 아래와 같은 중첩 루프문과 동일한 원리이다.
C,JAVA PL/SQL for(i=0; i<100; i++){ -- outer loop for(j=0; j<100; j++){ -- inner loop ..... } }
for outer in 1..100 loop for inner in 1..100 loop dbms_output.put_line(outer || ':' || inner); end loop; end loop;
- 아래의 PL/SQL과 SQL문은 내부적으로(Recursive하게) 쿼리를 반복 수행하지 않는점 이외에 동일한 처리를 한다.
PL/SQL SQL 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;
select /*+ ordered use_nl(d) */ e.empno, e.ename, d.dname from emp e, dept d where d.deptno = e.deptno
(2) 힌트를 이용해 NL조인을 제어하는 방법
- 힌트 사용예1
select /*+ ordered use_nl(e) */ * from dept d, emp e where e.deptno = d.deptno
- ordered : from절에 써있는 순서대로 테이블을 조인하세요
- use_nl : NL방식으로 조인하세요.
=> 즉, dept테이블을 기준으로, emp테이블을 NL방식으로 조인하세요
![]() | Outer테이블, Inner테이블
|
- 힌트사용예2
select /*+ ordered use_nl(B) use_nl(C) use_hash(D) */ * from A, B, C, D where ...
=> A->B->C->D 순으로 B와 조인할때, C와 조인할 때는 NL로, D와 조인할때는 Hash로..
- 힌트사용예3
ordered대신 leading힌트를 이용하여 조인순서제어가 가능(leading을 사용하면 테이블 순서를 일일이 바꿔줄 필요없이 제어가능)select /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_hash(B) */ * from A, B, C, D where ...
=>C->A->D->B 순으로 A와 조인할때, D와 조인할때는 NL로 B와 조인할때는 Hash로..
- 힌트사용예4
select /*+ use_nl(A,B,C,D)*/ * from A, B, C, D where ...
=> A,B,C,D를 조인할 때 모두 NL로.. 단, 특별히 조인순서를 지정안했으므로, 조인순서는 옵티마이저 판단에 맡김
(3) NL조인 수행과정 분석
SQL | INDEX |
---|---|
select /*+ ordered use_nl(e) */ e.empno, e.ename, d.dname, e.job, e.sal from dept d, emp e 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 |
실행계획 |
---|
------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 | 4 (25)| 00:00:01 | | 1 | SORT ORDER BY | | 1 | 45 | 4 (25)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID | EMP | 4 | 100 | 1 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 1 | 45 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | DEPT_LOC_IDX | 1 | | 1 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- |
1) 사용되는 인덱스는 ? dept_loc_idx, emp_deptno_idx
2) 조건비교순서는? (2) -> (3) -> (1) -> (4)
(실행계획 해석은, 위에서 아래로, 안쪽에서 바깥으로 읽기)
각 단계를 완료하고 나서 다음단계로 넘어가는게 아니라 한 레코드씩 순차적으로 진행한다.
단, order by는 전체집합을 대상으로 정렬해야 하므로 작업을 모두 완료한 후 다음 오퍼레이션을 진행한다.
- NL조인의 수행절차
그림 2-2 참조- dept_loc_idx 인덱스를 스캔하는 양에 따라 전체 일량이 좌우된다.
- gb='2'조건에 의해 필터링 되는 비율이 높다면 dept_loc_idx인덱스에 gb컬럼을 추가하는 방안을 고려해볼것
- sal >= 1500 조건에 의해 필터링 되는 비율이 높다면 emp_deptno_idx인덱스에 sal 컬럼 추가하는 방안고려
OLTP시스템에서 조인을 튜닝할 때는 우선적으로 NL조인부터 고려
(4) NL조인의 특징
- Random 액세스 위주의 조인방식
그러므로, 인덱스 구성이 완벽해도 대량의 데이터 조인시 비효율적 - 조인을 한 레코드씩 순차적으로 진행
아무리 대용량 집합이더라도 매우 극적인 응답속도를 낼 수 있으며, 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정
다른 조인방식보다 인덱스 구성 전략이 특히 중요하며, 소량의 데이터를 처리하거나 부분범위 처리가 가능한 OLTP성 환경에 적합한 조인방식이다.
(5) NL조인 튜닝실습
- SQL트레이스 1 (p220 상단)
테이블을 액세스 한 후에 필터링 되는 비율이 높다면 인덱스에 테이블 필터 조건 컬럼을 추가하는것을 고려
(job_max_sal_ix : max_salary + job_type)
- SQL트레이스 2 (p221 상단)
job_max_sal_ix인덱스로부터 3건을 리턴하기 위해 인덱스 블록을 1000개 읽음. 인덱스 컬럼 순서를 조정
(job_max_sal_ix : job_type + max_salary)
- SQL트레이스 3(p221 하단)
1278번 조인시도했으나 최종성공한 결과집합은 5건뿐
조인순서 jobs -> employees에서 employees -> jobs로 변경고려
조인순서를 바꾸어도 소득이 없다면 소트머지조인과 해시조인을 검토
(6) 테이블 Prefetch
- 테이블 Prefetch란?
인덱스를 경유하여 테이블 레코드를 액세스하는 도중 디스크에서 캐시로 블록을 적재해야하는데, 그때 곧이어 읽을 가능성이 큰 블록들을 미리 적재해 두는 기능
디스크 I/O에 의한 대기횟수 감소를 노림
- 실행계획에 인덱스 rowid에의한 Inner테이블 액세스가 Nested Loops 위쪽에 표시되면, Prefetch기능이 활성화 됨을 의미
- _talble_lookup_prefetch_size를 0으로 설정하면 전통적인 NL조인 실행계획으로 돌아감
- 실행계획에 위와 같이 나타났다고 항상 테이블 Prefetch가 작동하는 것은 아니다. db_file_sequential_read 대기 이벤트 대신 db_file_parallel_reads대기이벤트가 나타나면 실제작동함을 의미
- Prefetch기능이 나타나는 경우
- Inner쪽 Non-Unique인덱스를 Range Scan할 때는 항상 나타남
- Inner쪽 Unique인덱스를 Non-Unique 조건(모든 인덱스 구성컬럼이 '='조건이 아닐때)으로 Range Scan할 때도 항상 나타난다.
- Inner쪽 Unique인덱스를 Unique조건(모든 인덱스 구성컬럼이 '='조건)으로 액세스할 때도 나타날 수 있다. 이대 인덱스는 Range Scan으로 액세스하며, 테이블 Prefetch실행계획이 안 타타날 때는 Unique Scan으로 엑세스한다.
- 예) p224
- 지분보고_PK : 회사코드 + 보고서구분코드 + 최초보고일자 + 보고서id + 보고일련번호
- cardinality힌트를 사용하여 드라이빙 집합의 카디널리티를 변경하면서 이와 같은 실행계획이 나타남을 확인할수 있다.
(7) 배치 I/O
- 오라클 11g에서 시작
- Inner 쪽 인덱스만으로 조인을 하고나서 테이블과의 조인은 나중에 일괄처리하는 메커니즘
- 테이블 엑세스를 나중에 하지만 부분범위처리는 정상적으로 작동한다.
- 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩 나누어 처리
- 배치 I/O방식
- 드라이빙 테이블에서 일정량의 레코드를 읽어 Inner쪽 인덱스와 조인하며 중간 결과집합을 만듬
- 중간결과집합이 일정량 쌓이면 inner쪽 테이블 레코드를 액세스. 테이블 블록이 버퍼 캐시에 있으면 바로 최종 결과집합에 담고, ?찾으면 중간집합에 남겨둠
- 위에서 남겨진 중간 집합에 대한 Inner쪽 테이블 블록을 디스크에서 읽음.
- 버퍼캐시에 올라오면 테이블 레코드를 읽어 최종 결과집합에 담음
- 모든 레코드를 처리하거나 사용자가 Fetch Call을 중단할 때까지 1~4를 반복
- nlj_batching 힌트를 사용하면 됨
- 이방식을 사용하면 데이터 정렬 순서가 달라질 수 있음
(8) 버퍼 Pinning 효과
8i에서 나타난 버퍼 Pinning효과
- 페이블 블록에 대한 버퍼 Pinning기능이 작동
- 하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 다른 레코드를 읽기위해 Outer쪽으로 돌아오는 순간 Pin을 해제
9i에서 나타난 버퍼 Pinning효과
- Inner쪽 인덱스 루트 블록에 대한 버퍼 Pinning효과가 나타나기시작
- 9i부터 Inner쪽이 Non-Unique 인덱스일 때는 테이블 액세스가 항상 NL조건 위쪽으로 올라가므로 이때는 항상 버퍼 Pinning효과가 나타나는 셈
10g에서 나타난 버퍼 Pinning효과
select /*+ ordered use_nl(d) */ count(e.ename), count(d.dname) from scott.t_emp e, scott.dept d where d.deptno = e.deptno call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.04 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 38.75 45.39 8730 1409213 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 38.78 45.43 8730 1409214 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1409213 pr=8730 pw=0 time=45394912 us) 1400000 NESTED LOOPS (cr=1409213 pr=8730 pw=0 time=53215534 us) 1400000 TABLE ACCESS FULL T_EMP (cr=9211 pr=8730 pw=0 time=9815520 us) 1400000 TABLE ACCESS BY INDEX ROWID DEPT (cr=1400002 pr=0 pw=0 time=32299603 us) 1400000 INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=12912986 us)(object id 51250)
- Inner쪽 테이블을 Index Range Scan을 거쳐 NL조인 위쪽에서 액세스 할 때는 , 하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 Outer를 돌아오더라도 테이블 블록에 대한 Pinning상태을 유지
11g에서 나타난 버퍼 Pinning효과
- User Rowid로 테이블 액세스할 때도 버퍼 Pinning효과가 나타남
- NL조인에서 inner쪽 루트 아래 인덱스 블록들도 Pinning하기 시작
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4948020&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.