- Chapter 02. 조인 원리와 활용
01. Nested Loops 조인
(1) 기본 메커니즘
- PL/SQL 로 구현된 NL 조인 수행 구조
begin
for outer in (select deptno, empno, rpad(ename, 10) ename from emp)
loop
for inner in (select dname from dept where deptno = outer.deptno)
loop
dbms_output.put_line(outer.empno || ' : ' || outer.ename || ' : ' || inner.dname);
end loop;
end loop;
end;
※ 위 PL/SQL 문은 아래 쿼리와 100% 같은 순서로 데이터를 액세스 한다.
select /*+ ordered use_nl(d) */ e.empno, e.ename, d.dname
from emp e, dept d
where d.deptno = e.deptno;
(2) 힌트를 이용해 NL 조인을 제어하는 방법
select /*+ ordered use_nl(e) */ *
from dept d, emp e
where e.deptno = d.deptno;
※ ordered 힌트는 from 절에 기술된 순서대로 조인하라고 옵티마이저에게 지시할 때 사용하고, use_nl 힌트는 NL 방식으로
조인하라고 지시할 때 사용한다.
위에서는 ordered 와 use_nl(e) 힌트를 같이 사용했으므로 dept 테이블(Driving 또는 Outer Table)을 기준으로 emp 테이블
과 조인할 때 NL 방식으로 조인하라는 뜻이 된다.
※ 10053 트레이스 파일을 열어보면 조인방식에 상관없이 실행계획상 위쪽에 있는 dept 테이블을 Outer, 아래쪽에 있는 emp
테이블을 Inner로 표시하고 있다.
NL과 소트 머지 조인은 위쪽에 있는 dept 테이블을 스캔하면서 아래쪽 emp 테이블을 탐색하는 메커니즘이므로 각각 outer
와 inner로 칭하는 것이 부자연스럽지 않다.
하지만 해시 조인의 처리 메커니즘은 아래쪽에 있는 emp 테이블을 스캔하면서 dept 테이블을 탐색하는 방식이다.
여러 개의 테이블을 조인하는 경우
※ 해석해 보면, A -> B -> C -> D 순으로 조인하되, B와 조인할 때 그리고 이어서 C와 조인할 때는 NL 방식으로 조인하고,
D와 조인할 때는 해시 방식으로 조인하라는 뜻이다.
ordered 대신 leading 힌트를 사용해 조인 순서를 제어할 수도 있다.
오라클 9i 까지는 leading 힌트에 인자를 하나만 입력할 수 있었다.
조인할 때 가장 처음에 읽을 기준 집합 하나만 명시하는 것이다.
그러다 보니 leading 힌트만으로는 조인 순서를 세밀하게 제어할 수 없어 9i 이전 버전에서는 ordered 힌트를 주로 사용했다.
10g부터는 leading 힌트에 2개 이상 테이블을 기술할 수 있도록 기능이 개선돼, from 절을 바꾸지 않고도 마음껏 순서를 제어
할 수 있게 되었다.
(3) NL 조인 수행 과정 분석
-- 쿼리
-- 인덱스
※ 사용되는 인덱스는 dept_loc_idx 와 emp_deptno_idx 인 것을 위 실행계획을 보고 알 수 있다.
조건비교는 2 -> 3 -> 1 -> 4 순이다.
실행계획을 해석할 때, 형제 노드 간에는 위에서 아래로 읽는다.
부모-자식 노드 간에는 안쪽에서 바깥쪽으로, 즉 자식 노드부터 읽는다.
위 실행계획의 실행 순서
1. dept_loc_idx 인덱스 범위 스캔 (ID=4)
2. 인덱스 rowid로 dept 테이블 액세스 (ID=3)
3. emp_deptno_idx 인덱스 범위 스캔 (ID=6)
4. 인덱스 rowid로 emp 테이블 액세스 (ID=5)
5. sal 기준 내림차순(desc) 정렬 (ID=1)
1. dept.loc = 'SEOUL' 조건을 만족하는 레코드를 찾으려고 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)는 테이블 필터 조건에 의해 레코드가 걸러지지 않은 것을 의미하고, 반대로 (x)는 테이블 필터 조건에 의해 걸러진 것을
의미한다.
dept_loc_idx 인덱스를 스캔하는 양에 따라 전체 일량이 좌우됨을 이해하기 바란다.
여기서는 단일 컬럼 인덱스를 '=' 조건으로 스캔했으므로 비효율 없이 6(5+1) 건을 읽었고, 그만큼 테이블 Random 액세스가
발생했다.
우선 이 부분이 NL 조인의 첫 번째 부하지점이다.
만약 dept 테이블로 많은 양의 Random 액세스가 있었는데 gb = '2' 조건에 의해 필터링되는 비율이 높다면 dept_loc_idx 에
gb 컬럼을 추가하는 방안을 고려해야 한다.
두 번째 부하지점은 emp_deptno_idx 인덱스를 탐색하는 부분이며, Outer 테이블인 dept를 읽고 나서 조인 액세스가 얼만큼
발생하느냐에 의해 결정된다.
이것 역시 Random 액세스에 해당하며, 그럼에서는 gb = '2' 조건을 만족하는 건수만큼 3번의 조인시도가 있었다.
만약 emp_deptno_idx의 높이가 3이면 매 건마다 그만큼의 블록 i/o가 발생하고, 리프 블록을 스캔하면서 추가적인 블록 i/o
가 더해진다.
세 번째 부하지점은 emp_deptno_idx를 읽고 나서 emp 테이블을 액세스하는 부분이다.
여기서도 sal >= 1500 조건에 의해 필터링 되는 비율이 높다면 emp_deptno_idx 인덱스에 sal 컬럼을 추가하는 방안을 고려
해야 한다.
OLTP 시스템에서 조인을 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서다.
우선, NL 조인 메커니즘을 따라 각 단계의 수행 일량을 분석해 과도한 Random 액세스가 발생하는 지점을 파악한다.
조인 순서를 변경해 Random 액세스 발생량을 줄일 수 있는 경우가 있고, 그렇지 못할 때는 인덱스 컬럼 구성을 변경하거나
다른 인덱스의 사용을 고려해야 한다.
(4) NL 조인의 특징
- 오라클은 블록 단위로 I/O를 수행하며, 하나의 레코드를 읽으려고 블록을 통째로 읽는 Random 액세스 방식은 설령 메모리 버퍼
에서 빠르게 읽더라도 비효율이 존재한다.
그런데 NL 조인의 첫 번째 특징이 Random 액세스 위주의 조인 방식이라는 점이다.
따라서 인덱스 구성이 아무리 완벽하더라도 대량의 데이터를 조인할 때 매우 비효율적이다.
두 번째 특징은, 조인을 한 레코드씩 순차적으로 진행한다는 점이다.
첫 번째 특징 때문에 대용량 데이터 처리 시 매우 치명적인 한계를 드러내지만, 반대로 이 두번째 특징 때문에 아무리 대용량 집합
이더라도 매우 극적인 응답 속도를 낼 수 있다.
부분범위처리가 가능한 상황에서 그렇다.
그리고 순차적으로 진행하는 특징 때문에 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정된다.
이런 여러 가지 특징을 종합할 때, NL 조인은 소량의 데이터를 주로 처리하고나 부분범위처리가 가능한 온라인 트랜잭션 환경에
적합한 조인 방식이라고 할 수 있다.
(5) NL 조인 튜닝 실습
-- 환경 구축
CREATE TABLE JOBS
AS
SELECT *
FROM HR.JOBS;
CREATE TABLE EMPLOYEES
AS
SELECT *
FROM HR.EMPLOYEES;
CREATE UNIQUE INDEX PK_JOBS ON JOBS(JOB_ID);
ALTER TABLE JOBS ADD CONSTRAINT PK_JOBS PRIMARY KEY (JOB_ID) USING INDEX DISABLE VALIDATE;
CREATE INDEX JOBS_MAX_SAL_IX ON JOBS(MAX_SALARY);
CREATE UNIQUE INDEX PK_EMPLOYEES ON EMPLOYEES(EMPLOYEE_ID);
ALTER TABLE EMPLOYEES ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID) USING INDEX DISABLE VALIDATE;
CREATE INDEX EMP_JOB_IX ON EMPLOYEES(JOB_ID);
CREATE INDEX EMP_HIREDATE_IX ON EMPLOYEES(HIRE_DATE);
-- 튜닝 쿼리
SELECT /*+ ORDERED USE_NL(E) INDEX(J) INDEX(E) */
J.JOB_TITLE,
E.FIRST_NAME,
E.LAST_NAME,
E.HIRE_DATE,
E.SALARY,
E.EMAIL,
E.PHONE_NUMBER
FROM JOBS J, EMPLOYEES E
WHERE E.JOB_ID = J.JOB_ID
AND J.MAX_SALARY >= 1500
AND J.JOB_TITLE = 'Accountant'
AND E.HIRE_DATE >= TO_DATE('19960101', 'YYYYMMDD');
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 8 0 5
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.001 0 8 0 5
Misses in library cashe during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : TEST (ID=93)
Rows Row Source Operation
------- -----------------------------------------------------------------------
5 NESTED LOOPS (cr=8 pr=0 pw=0 time=0 us)
5 NESTED LOOPS (cr=6 pr=0 pw=0 time=16 us cost=3 size=540 card=6)
1 TABLE ACCESS BY INDEX ROWID JOBS (cr=4 pr=0 pw=0 time=0 us cost=2 size=31 card=1)
19 INDEX RANGE SCAN JOBS_MAX_SAL_IX (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=19)
5 INDEX RANGE SCAN EMP_JOB_IX (cr=2 pr=0 pw=0 time=12 us cost=0 size=0 card=6)
5 TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=2 pr=0 pw=0 time=0 us cost=1 size=354 card=6)
조인시 트레이스 결과 - 1
- jobs_max_sal_ix 인덱스를 스캔하고서 jobs 테이블을 액세스한 횟수가 278인데, 테이블에서 job_type = 'A' 조건을 필터링한
결과는 3건에 그친다.
불필요한 테이블 액세스를 많이 한 셈이고, 이처럼 테이블을 액세스한 후에 필터링되는 비율이 높다면 인덱스에 테이블 필터 조건
컬럼을 추가하는 것을 고려해 볼 필요가 있다.
jobs_max_sal_ix 인덱스에 job_type 컬럼을 추가하고서 트레이스를 다시 확인해 보니 아래와 같이 불필요한 테이블 액세스가
없어졌다.
- Rows에 표시된 숫자만 보면 비효율적인 액세스가 없어 보이지만 테이블을 액세스하기 전 인덱스 스캔 단계에서의 일량을 확인
하지 못했으므로 튜닝이 끝났다고 볼 수 없다.
인덱스가 [max_salary + job_type] 순이고, 조건절을 보면 인덱스 선두 컬럼이 부등호 조건이다.
max_salary >= 1500 조건에 해당하는 레코드가 엄청 많다면 많은 양의 인덱스 블록을 스캔하면서 job_type = 'A' 조건을 필터링
했을 것이다.
- 여기서 보면 jobs_max_sal_ix 인덱스로부터 3건을 리턴하기 위해 인덱스 블록을 1,000개 읽은 것을 알 수 있다.
튜닝 방법은 ? jobs_max_sal_ix 인덱스 컬럼 순서를 조정해 [job_type + max_salary] 순으로 구성해 주면 된다.
- job 테이블을 읽는 부분에서는 비효율이 없어 보인다. 인덱스에서 스캔한 블록이 4개뿐이고 테이블을 액세스하고서 필터링되는
레코드도 전혀 없다.
일량은 많지만 비효율은 없다고 볼 수 있다.
문제는 jobs 테이블을 읽고 나서 employees 테이블과의 조인 시도 횟수다.
1,278 번 조인 시도를 했지만 최종적으로 조인에 성공한 결과집합은 5건 뿐이다.
이럴 때는 조인순서를 바꾸는 것을 고려해 볼 수 있다.
만약 hire_date 조건절에 부합하는 레코드가 별로 없다면 튜닝에 성공할 가능성이 높다.
(6) 테이블 Prefetch
- 우선, 오라클 9i부터 조인 실행계획에 변화가 생겼다.
아래와 같이 인덱스 rowid에 의한 Inner 테이블 액세스가 Nested Loops 위쪽에 표시되건 하는데, 이는 해당 테이블 액세스
단계에서 Prefetch 기능이 제공되었다.
- 테이블 Prefetch를 제어하는 파라미터 중 하나인 _table_lookup_prefetch_size를 0으로 설정하면, 똑같은 sql인데 아래와 같이
전통적인 NL 조인 실행계획으로 되돌아간다.
-- 히든 파라미테 조회
SELECT A.KSPPINM PARAMETER
, A.KSPPDESC DESCRIPTION
, B.KSPPSTVL SESSION_VALUE
, C.KSPPSTVL INSTANCE_VALUE
FROM X$KSPPI A, X$KSPPCV B, X$KSPPSV C
WHERE A.INDX = B.INDX
AND A.INDX = C.INDX
AND LOWER(A.KSPPINM) LIKE '_table%';
PARAMETER DESCRIPTION SESSION_VA INSTANCE_V
----------------------------------- -------------------------------------------------- ---------- ----------
_tablespaces_per_transaction estimated number of tablespaces manipulated by eac 10 10
h transaction
_table_lookup_prefetch_size table lookup prefetch vector size 40 40
_table_lookup_prefetch_thresh table lookup prefetch threshold 2 2
_table_scan_cost_plus_one bump estimated full table scan and index ffs cost TRUE TRUE
by one
- 새로운 포맷의 실행계획이 나타난다고 항상 테이블 Prefetch가 작동하는 것은 아니다.
단지 그 기능이 활성화되었음을 의미할 뿐이다.
Prefetch 방식으로 디스크 블록을 읽었는데 실제 버퍼 블록 액세스로 연결되지 못한 채 메모리에서 밀려나는 비율이 높다면,
실행계획은 그대로인 채 내부적으로 기능이 비활성화되기 때문이다.
참고로, Prefetch 기능이 실제 작동할 때면 db file sequential read 대기 이벤트 대신 db file parallel reads 대기 이벤트가 나타
난다.
Prefetch는 디스크 I/O를 수행하려면 비용이 많이 들기 때문에 한 번 I/O Call 이 필요한 시점에, 곧이어 읽을 가능성이 큰 블록
들을 캐시에 미리 적재해 두는 기능이다.
한 번의 I/O Call로써 여러 Single Block I/O를 동시에 수행한다.
NL 조인에서 항상 새 포맷의 실행계획이 나타나는 것은 아니다.
기본적으로 Outer 쪽 인덱스를 Unique Scan 할 때는 작동하지 않는다.
※ 새 포맷의 실행계획이 나타날 수 있는 경우 ※
● Inner 쪽 Non-Unique 인덱스를 Range Scan 할 때는 테이블 Prefetch 실행계획이 항상 나타난다.
● Inner 쪽 Unique 인덱스를 Non-Unique 조건으로 Range Scan 할 때도 테이블 Prefetch 실행계획이 항상 나타난다.
● Inner 쪽 Unique 인덱스를 Unique 조건으로 액세스할 때도 테이블 Prefetch 실행계획이 나타날 수 있다.
이때 인덱스는 Range Scan으로 액세스 한다.
테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan으로 액세스한다.
지분보고_PK 인덱스
[회사코드 + 보고서구분코드 + 최초보고일자 + 보고서id + 보고일련번호]
- 위 sql에 cardinality 힌트를 사용한 것을 볼 수 있는데, 드라이빙 집합의 카디널리티를 조금씩 증가시키며 테스트했을 때 110
까지는 Inner 테이블 액세스가 전통적인 방식대로 NL 조인 아래쪽에 위치한다.
그러다가 드라이빙 집합의 카디널리티를 111로 지정하는 순간 아래와 같이 실행계획이 바뀌는 것을 관찰할 수 있다.
(7) 배치 I/O
- 오라클 11g에서 시작된 배치 I/O 메커니즘에 대해서는 아직 공식적으로 알려진 바가 없지만, 아래 실행계획이 의미하는 바와 같이
Inner 쪽 인덱스만으로 조인을 하고 나서 테이블과의 조인은 나중에 일괄 처리하는 메커니즘인 것으로 추정된다.
테이블 액세스를 나중에 하지만 부분범위처리는 정상적으로 작동된다.
따라서 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩 나누어 처리하는 것을 알 수 있다.
1. 드라이빙 테이블에서 일정량의 레코드를 읽어 Inner 쪽 인덱스와 조인하면서 중간 결과집합을 만든다.
2. 중간 결과집합이 일정량 쌓이면 inner 쪽 테이블 레코드를 액세스한다.
이때 테이블 블록을 버퍼 캐시에서 찾으면 바로 최종 결과집합에 담고, 못 찾으면 중간 집합에 남겨 둔다.
3. 2번 과정에서 남겨진 중간 집합에 대한 Inner 쪽 테이블 블록을 디스크로부터 읽는다.
이때 Multiple Single Block I/O 방식을 사용한다.
4. 버퍼 캐시에 올라오면 테이블 레코드를 읽어 최종 결과집합에 담는다.
5. 모든 레코드를 처리하거나 사용자가 Fetch Call을 중단할 때까지 1~4번 과정을 반복한다.
- 이것은 Outer 테이블로부터 액세스되는 Inner 쪽 테이블 블록에 대한 디스크 I/O Call 횟수를 줄이기 위해, 테이블 Prefetch에
이어 추가로 도입된 메커니즘이다.
이 메커니즘이 작동하도록 유도하려면 nlj_batching 힌트를 사용하면 된다.
만약 이 방식을 원치 않을 때는 no_nlj_batching 또는 nlj_prefetch 힌트를 사용하면 된다.
그러면 테이블 Prefetch 방식으로 전환된다.
주목할 것은, 위 방식을 사용할 때 Inner 쪽 테이블 블록이 모두 버퍼 캐시에서 찾아지지 않으면 즉, 실제 배치 i/o가 작동한다면
데이터 정렬 순서가 달라질 수 있다는 사실이다.
모두 버퍼 캐시에서 찾을 때는 이전 메커니즘과 똑같은 정렬 순서를 보인다.
테이블 Prefetch 방식이나 전통적인 방식으로 nl 조인할 때는 디스크 I/O가 발생하든 안 하든 데이터 정렬 순서가 항상 일정하다.
(8) 버퍼 Pinning 효과
8i에서 나타난 버퍼 Pinning 효과
- 테이블 블록에 대한 버퍼 Pinning 기능이 작동하기 시작했다.
단, 하나의 버퍼 블록만 Pinning 한다.
그리고 하나의 Fetch Call을 완료하는 순간 Pin을 해제한다.
이 기능은 NL 조인에서 Non-Unique 조건으로 Inner 쪽 테이블을 액세스할 때도 똑같이 작용한다.
따라서 Inner 쪽 인덱스를 통해 액세스되는 테이블 블록이 계속 같은 블록을 가리키면 논리 i/o가 추가로 발생하지 않는다.
중요한 사실은, 하나의 Outer 레코드에 대한 Inner 쪽과의 조인을 마치고 다른 레코드를 읽기 위해 Outer 쪽으로 돌아오는 순간
Pin을 해제한다는 점이다.
따라서 8i에서 Pinning 기능을 정확히 테스트하려면 Inner 쪽을 한 번 액세스할 때마다 여러 개의 테이블 레코드를 읽도록 데이터
를 구성해야 한다.
클러스터링 팩터가 좋아야 그 효과도 확실하며, 9i 이상 버전에서도 마찬가지다.
NL 조인에서도 하나의 Fetch Call을 완료하면 Pin을 해제한다.
9i에서 버퍼 Pinning 효과
- 9i부터 Inner 쪽 인덱스 루트 블록에 대한 버퍼 Pinning 효과가 나타나기 시작했다.
단, 두 번째 액세스되는 순간 Pinning 한다.
테이블 블록 버퍼에 대한 Pinning도 8i와 똑같이 작동한다.
앞에서도 얘기했지만 Inner 쪽을 한 번 액세스할 때마다 Non-Unique 인덱스로 여러 개 테이블 레코드를 읽을 때라야 이 기능이
효과를 발휘한다.
9i부터 inner 쪽이 non-unique 인덱스일 때는 테이블 액세스가 항상 NL 조인 위쪽에 올라가므로 이때는 항상 버퍼 Pinning 효과가
나타나는 셈이다.
반면, 테이블 Prefetch에서 설명했듯이 Inner 쪽 Unique 인덱스를 Unique 조건으로 액세스할 때는 테이블 액세스가 nl 조인 위쪽
으로 잘 올라가지 않는다.
그리고 이때는 Unique 액세스이므로 Inner 쪽에서 한 건만 읽고 바로 Outer 테이블 쪽으로 돌아간다.
따라서 버퍼 Pinning 효과가 나타날 수가 없다.
Inner 쪽 Unique 인덱스를 Unique 조건으로 액세스할 때도 테이블 액세스가 NL 조인 위쪽으로 올라가는 경우가 있다고 했고,
그냥 지나쳐 왔지만 그때의 버퍼 Pinning 효과를 이미 앞에서 보았다.
(6)항에서 테이블 Prefetch를 설명하면서 예시했던 트레이스 결과에서 '지분보고' 테이블을 NL 조인 아래쪽에서 액세스할 때보다
위쪽에서 액세스할 때 53개 블록을 덜 읽은 것을 확인하기 바란다.
10g에서 버퍼 Pinning 효과
- Inner 쪽 인덱스 루트 블록과 테이블 블록을 Pinning 하는 기능이 여전히 작동하면서도 한 가지 새로운 기능이 추가되었다.
Inner 쪽 테이블을 Index Range Scan을 거쳐 NL 조인 위쪽에서 액세스할 때는, 하나의 Outer 레코드에 대한 Inner 쪽과의 조인을
마치고 Outer 쪽으로 돌아오더라도 테이블 블록에 대한 Pinning 상태를 유지한다.
만약 Inner 쪽 테이블이 한 블록뿐일 때 10g에서의 새로운 버퍼 Pinning 기능이 작동한다면 NL 조인이 진행되는 동안 논리적인
블록 I/O는 단 1회만 발생할 것이다.
create table dept as select * from scott.dept;
alter table dept add constraint dept_pk primary key(deptno);
create table t_emp
as
select * from scott.emp, (select rownum no from dual connect by level <= 100000
order by dbms_random.value;
select /*+ ordered use_nl(d) */ count(e.ename), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 1.950 1.997 9200 1409208 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 1.950 1.997 9200 1409208 0 1
Misses in library cashe during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : TEST (ID=93)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=1409208 pr=9200 pw=0 time=0 us)
1400000 NESTED LOOPS (cr=1409208 pr=9200 pw=0 time=2112414 us)
1400000 NESTED LOOPS (cr=9208 pr=9200 pw=0 time=1073742 us cost=1154589 size=48365604 card=1151562)
1400000 TABLE ACCESS FULL T_EMP (cr=9204 pr=9200 pw=0 time=158840 us cost=2552 size=23031240 card=1151562)
1400000 INDEX UNIQUE SCAN DEPT_PK (cr=4 pr=0 pw=0 time=0 us cost=0 size=0 card=1)
1400000 TABLE ACCESS BY INDEX ROWID DEPT (cr=1400000 pr=0 pw=0 time=0 us cost=1 size=22 card=1)
※ 우선 인덱스 루트 블록을 버퍼 Pinning 한 사실이 쉽게 확인된다.
Inner 쪽 인덱스로 140만 번 액세스가 일어났는데, 논리적인 블록 i/o는 고작 2회뿐이다.
두 번째 액세스되는 순간부터 Pinning 되므로 최소한 두 블록 i/o는 일어난다.
10g까지는 루트 블록만 Pinning 대상이지만, 여기선 dept 테이블이 4건짜리 작은 테이블이므로 인덱스 루트 블록이 곧 리프
블록이다.
그래서 두 블록 외에 추가적인 I/O는 발생하지 않은 것이다.
테이블 블록은 Inner 쪽을 액세스할 때마다 한 건씩만 읽고 Outer 쪽으로 돌아가므로 테이블 블록에는 Pinning 효과가 나타
나지 않았다.
따라서 140만 번 액세스하는 동안 고스란히 140만번 블록 I/O를 일으켰다.
이번에는 위 SQL과 결과는 똑같으면서 Unique 인덱스를 Range Scan 하도록 조건절을 아래와 같이 between 으로 바꿔보자.
select /*+ ordered use_nl(d) index(d) */ count(e.ename), count(d.dname)
from t_emp e, dept d
where d.deptno between e.deptno and e.deptno + 1;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 2.028 2.047 9200 9208 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 2.028 2.048 9200 9208 0 1
Misses in library cashe during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : TEST (ID=93)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=9208 pr=9200 pw=0 time=0 us)
1400000 NESTED LOOPS (cr=9208 pr=9200 pw=0 time=2187162 us)
1400000 NESTED LOOPS (cr=9207 pr=9200 pw=0 time=1355457 us cost=2306509 size=483672 card=11516)
1400000 TABLE ACCESS FULL T_EMP (cr=9204 pr=9200 pw=0 time=151160 us cost=2552 size=23031240 card=1151562)
1400000 INDEX RANGE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
1400000 TABLE ACCESS BY INDEX ROWID DEPT (cr=1 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
※ Unique 인덱스이지만 Range Scan 방식으로 액세스하였고, 테이블 액세스가 NL 조인 위쪽에 위치했다.
그리고 dept 테이블 블록은 단 하나뿐이다.
따라서 140만개 레코드를 모두 처리하는 동안 버퍼 Pinning 상태가 유지됨으로써 블록 i/o는 단 1회 발생하였다.
그렇지만 버퍼를 Pinning 한 상태에서 내부적으로 140만 번이나 액세스했기 때문에 시간은 25초에 이른다.
9,214개 블록 i/o에 비하면 과도한 시간이다.
여기서 count 함수를 사용한 이유는, 버퍼 Pinning 효과는 하나의 데이터베이스 Call 내에서만 유효하기 때문이다.
만약 count 함수를 쓰지 않아 여러 번 Fetch Call이 발생한다면 Pin을 해제했다가 다시 Pin을 걸어야 하기 때문에 약간의
블록 i/o가 추가로 발생한다.
230