Hint ex2)

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로..

Hint ex3)

Ordered대신 Leading힌트를 이용하여 조인순서제어가 가능

- 9i까지는 Leading 힌트에 조인할 때 가장 처음에 읽을 기준 집합(=Driving Table)하나만 입력 가능했기 때문에 조인순서를 세밀하게 제어 할 수 없음
  때문에 9i까지는 Ordered를 많이 사용 하였다.
- 10g부터는 Leadiing 힌트에 2개이상의 테이블을 기술할 수 있도록 기능이 개선되서 From절에서 순서를 바꾸지 않아도 힌트절에서 바로 순서를 바꿀수 있게 되어 많이 쓰는 편이다.

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로..

Hint ex4)

SELECT /*+ use_nl(A,B,C,D)*/ *
FROM   A, B, C, D
WHERE  ...

=> A,B,C,D를 조인할 때 모두 NL로.. 단, 특별히 조인순서를 지정안했으므로, 조인순서는 옵티마이저 판단에 맡김

(3) NL조인 수행과정 분석

 |

1) 사용되는 인덱스는 ? dept_loc_idx, emp_deptno_idx

2) 조건비교순서는? (2) \-> (3) \-> (1) \-> (4)
(실행계획 해석은, 형제 노드일 경우, 위에서 아래로, 부모-자식 노드일 경우, 안쪽에서 바깥으로 읽기)

!NL 순서.jpg!


- 각 단계를 완료하고 나서 다음단계로 넘어가는게 아니라 {color:red}한 레코드씩 순차적으로 진행.{color}
- 단, order by는 전체집합을 대상으로 정렬해야 하므로 {color:red}작업을 모두 완료한 후 다음 오퍼레이션을 진행.{color}
- NL조인의 수행절차
!조인순서.jpg!

- 문제점 발생요인

1. dept_loc_idx 인덱스를 스캔하는 양에 따라 전체 일량이 좌우된다.
만약 DEPT 테이블로 많은 양의 Random 액세스가 있었는데 gb = 2 조건에 의해 필터링되는 비율이 높다면 어떻게 해야 할까?
dept_loc_idx이 idx에 gb 컬림을 추가하는 방안을 고려
2. emp_deptno_idx 인텍스를 탐색하는 부분이며， Outer 태이블인 dept를 읽고 나서 조인 액세스가 얼만큼 발생하느냐에 의해 결정된다.
이것 역시 Random 액세스에 해당하며， 그림 2 - 2에서는 gb = 2 조건을 만족하는 건수만큼 3 번의 조인시도가 있었다.
만약 emp_deptno_idx의 높이(height)가 3이면 매 건마다 그만큼의 블록 1/0가 발생하고(버퍼 Pinning 효과를 논외로 한다면)，
리프 블록을 스캔하면서 추가적인 블록 I/O가 더해진다.
3. emp_deptnojdx를 읽고 나서 emp 테이블을 액세스하는 부분이다. 여기서도 sal >= 1500 조건에 의해 필터링되는 비율이 높다면
emp_deptno_idx 인텍스에 sal 컬럼을 추가하는 방안을 고려

{color:red}{*}OLTP시스템에서 조인을 튜닝할 때는 우선적으로 NL조인부터 고려{*}{color}

h3. (4) NL조인의 특징

*Random 액세스 위주의 조인방식*
그러므로, 인덱스 구성이 완벽해도 대량의 데이터 조인시 비효율적
*조인을 한 레코드씩 순차적으로 진행*
아무리 대용량 집합이더라도 매우 극적인 응답속도를 낼 수 있으며, 먼저 액세스되는 테이블(Driving Table)의 처리 범위에 의해 전체 일량이 결정


{color:red}다른 조인방식보다 인덱스 구성 전략이 특히 중요하며, 소량의 데이터를 처리하거나 부분범위 처리가 가능한 OLTP성 환경에 적합한 조인방식이다.{color}

h3. (5) NL조인 튜닝실습

*튜닝예제*

- Jobs, Employees Table
- 인덱스 구성

• pk_jobs : jobs.job_id
• jobs_max_sal_ix : jobs.max_salary
• pk_employees : employees,employee_id
• emp_job_ix : employees.job_id
• emp_hiredate_ix : employees.hire_date

- 튜닝쿼리

SELECT /* ordered use_nl(e) index(j) index(e) * /
j., e.
FROM jobs j
,emplooyees e
WHERE e.job_id = j.job_id ...........(1)
AND j.max_salary >= 1500 ...........(2)
AND j.job_type = 'A' ...........(3)
AND e.hire_date >= to_date('19960101','yyyymmdd') ...........(4)
;

- 사용된 Index = jobs_max_sal_ix, emp_job_ix
- 실행 순서 = (2) -> (3) -> (1) -> (4)

- 참고

index 힌트에 어떤 인덱스를 사용하라고 명시하지 않았으므로 emp_hiredate_ix 인덱스를 이용하게 되면 (2) -> (3) -> (4) -> (1) 순으로 처리 된다.
hire_date 조건으로 카디겹 곱이 만들어지고 나서 조인 조건 job_id를 필터링 하는 방식 이다.

!1.jpg!

- jobs_max_sal_ix 인덱스를 스캔하고 Jobs 테이블을 액세스한 횟수가 278인데, 테이블에서 job_type = 'A' 조건을 필터링 한 결과는 3건!!
- 테이블을 액세스한 후 필터링되는 비율이 높다면 인덱스에 테이블 필터 조건 컬럼을 추가하는 것을 고려
*jobs_max_sal_ix + job_type*

!2.jpg!

- Rows에 표시 된 숫자만 보면 비효율적인 액세스가 없어 보이지만, 실제로 인덱스 스캔 하는 과정에서 일량의 여부를 알수가 없다.
- 조건절을 보면 인덱스 선수 컬럼이 부등호 조건이다. [max_salary >= 1500]
- [max_salary >= 1500] 조건에 해당하는 레코드가 엄청 많다면 많은 양의 인덱스 블록을 스캔 하면서 job_type = 'A' 조건을 필터링 했을 것이다.

- 참고

1. 오라클 7 버전에서는 Rows 부분에 각 단계의 처리한 건수(Processing Count)를 보여 주었으므로 실제 스캔량을 쉽게 확인 가능 했다.
2. 8i부터 조금씩 바뀌기 시작해서 9i에서 완전히 출력 건수를 보여주는 방식으로 바뀌다 보니 각 단계의 처리 일량을 따로 분석해야 하는
불편함이 생김.
3. 9iR2부터는 각 처리 단계별 논리적인 블록 요청횟수(cr)와 디스크에서 읽은 블록 수(pr) 그리고 디스크에 쓴 블록 수(pw)등을 표시

!3.jpg!

- jobs_max_sal_ix 인덱스로 부터 3건을 리턴하기 우해 인덱스 블록을 1,000개 읽은 것을 확인!!

- *튜닝방법은?? 지룡아~ 설명해봐!!*

jobs_max_sal_ix 인덱스 컬럼 순서를 조정해서 job_type + max_salary 순으로 구성해주면 된다.
물론, 다른 쿼리에 미치는 영향도 분석 이 선행되어야 하는 건 다 아시죠???

!4.jpg!

- jobs Table을 읽는 부분을 보면 인덱스에서 스캔한 블록이 4건뿐이고, 테이블을 액세스 하고서 필터링 되는 레코드도 전혀 없다.
  일량은 많지만 비효율은 없어 보인다.
- 문제는 Jobs Table을 읽고 Employees Table과 조인 시도 횟수!! 총 1,278번 시도 해서 겨우 5건만 나왔다.

- 해결방법은??

1. 조인순서를 바꾸는 것을 고려.
2. 만약 hire_date 조건절에 부합하는 레코드가 별로 없다면 튜닝에 성공할 가능성이 높다.
3. 하지만 그 반대의 결과가 나타날 수도 있다.
4. 위에서 employees와 조인 후에 5건으로 줄어든 것은 jobs로 부터 넘겨받는 job_id와 hire_date 두 컬럼을 조합했을 때 그런 것이지
hire_date 단독으로 조회 했을 대는 데이터량이 생각보다 많을 수 있기 때문이다.
5. 소트머지조인, 해시조인을 검토!!

h3. (6) 테이블 Prefetch

- 테이블 Prefetch란?
인덱스를 경유하여 테이블 레코드를 액세스하는 도중 디스크에서 캐시로 블록을 적재해야하는데, 그때 곧이어 읽을 가능성이 큰 블록들을 미리 적재해 두는 기능
디스크 I/O에 의한 대기횟수 감소를 노림

*P222참조*
- 실행계획에 인덱스 rowid에의한 Inner테이블 액세스가 Nested Loops 위쪽에 표시되면, Prefetch기능이 활성화 됨을 의미.
  (\_talble_lookup_prefetch_size를 0으로 설정하면 전통적인 NL조인 실행계획으로 돌아감.)
- 실행계획에 위와 같이 나타났다고 항상 테이블 Prefetch가 작동하는 것은 아님. 단지 그 기능이 활성화 되었음을 의미
  (Prefetch 방식으로 디스크 블록을 읽었는데 실제 버퍼 블록 액세스로 연결되지 못한 채 메모리에서 밀려나는 비율이 높다면, 실행계획은 그대로인채
   내부적으로 기능이 비활성화되기 때문)
*참고로.. db_file_sequential_read 대기 이벤트 대신 db_file_parallel_reads대기이벤트가 나타나면 실제작동함을 의미*

- Prefetch기능이 나타나는 경우

1. Inner쪽 Non-Unique인덱스를 Range Scan할 때는 항상 나타남
2. Inner쪽 Unique인덱스를 Non-Unique 조건(모든 인덱스 구성컬럼이 '='조건이 아닐때)으로 Range Scan할 때도 항상 나타난다.
3. Inner쪽 Unique인덱스를 Unique조건(모든 인덱스 구성컬럼이 '='조건)으로 액세스할 때도 나타날 수 있다.
이때 인덱스는 Range Scan으로 액세스하며, 테이블 Prefetch실행계획이 안 타타날 때는 Unique Scan으로 엑세스한다.

- 3번과 같은 경우는 거의 나타나지 않는데.. p224 예제를 참고해서 얘기해 보자.


- 지분보고_PK : 회사코드 + 보고서구분코드 + 최초보고일자 + 보고서id + 보고일련번호
- cardinality힌트를 사용하여 드라이빙 집합의 카디널리티를 변경하면서 이와 같은 실행계획이 나타남을 확인할수 있다.
*결론*
- 저자도 정확한 규칙을 찾는데 실패!!

h3. (7) 배치 I/O
- 오라클 11g에서 시작
- Inner 쪽 인덱스만으로 조인을 하고나서 테이블과의 조인은 나중에 일괄처리하는 메커니즘.
- 테이블 엑세스를 나중에 하지만 부분범위처리는 정상적으로 작동한다.
- 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩 나누어 처리(부분범위처리)

*p225 참조*

*배치 I/O방식*

1. 드라이빙 테이블에서 일정량의 레코드를 읽어 Inner쪽 인덱스와 조인하며 중간 결과집합을 만듬.
2. 중간결과집합이 일정량 쌓이면 inner쪽 테이블 레코드를 액세스.
테이블 블록이 버퍼 캐시에 있으면 바로 최종 결과집합에 담고, 못 찾으면 중간집합에 남겨둠.
3. 위에서 남겨진 중간 집합에 대한 Inner쪽 테이블 블록을 디스크에서 읽음.
4. 버퍼캐시에 올라오면 테이블 레코드를 읽어 최종 결과집합에 담음.
5. 모든 레코드를 처리하거나 사용자가 Fetch Call을 중단할 때까지 1~4를 반복 수행.

- Outer Table로부터 액세스되는 Inner 쪽 테이블 블록에 대한 디스크 I/O Call 횟수를 줄이기 위해, 테이블 Prefetch에 이어 추가로 도입 된 메커니즘.
- nlj_batching힌트를 사용
- 위 방법을 원치 않을 경우(Prefetch 방식으로전환), no_nlj_batching 또는 nlj_prefetch 힌트를 사용.
- 위 방법을 사용할 때 Inner쪽 테이블 블록이 모두 버퍼 캐시에서 찾아지지 않으면(버퍼 캐시 히트율<100%)
  즉, 실제 배치 I/O가 작동한다면 데이터 정렬순서가 달라질수 있음.
- 모두 버퍼 캐시에서 찾을 때는(버퍼 캐시 히트율 = 100%)이전 메커니즘과 똑같은 정렬 순서를 보임.
- 테이블 Prefetch 방식이나 전통적인 방식으로 NL조인할 때는 디스크 I/O가 발생하든 안하든 데이터 정렬 순서가 항상 일정.

h3. (8) 버퍼 Pinning 효과

*8i에서 나타난 버퍼 Pinning효과*
- 테이블 블록에 대한 버퍼 Pinning기능이 작동
- 하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 다른 레코드를 읽기위해 Outer쪽으로 돌아오는 순간 Pin을 해제

*9i에서 나타난 버퍼 Pinning효과*
- Inner쪽 인덱스 루트 블록에 대한 버퍼 Pinning효과가 나타나기시작
- 9i부터 Inner쪽이 Non-Unique 인덱스일 때는 테이블 액세스가 항상 NL조건 위쪽으로 올라가므로 이때는 항상 버퍼 Pinning효과가 나타나는 셈
  (Profetch 기능 활성화)

*10g에서 나타난 버퍼 Pinning효과*

*p228 참조*
- Inner쪽 테이블을 Index Range Scan을 거쳐 NL조인 위쪽에서 액세스 할 때는 , 하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 Outer를 돌아오더라도 테이블 블록에 대한 Pinning상태을 유지

*11g에서 나타난 버퍼 Pinning효과*

*p231 참조*
- User Rowid로 테이블 액세스할 때도 버퍼 Pinning효과가 나타남
- NL조인에서 inner쪽 루트 아래 인덱스 블록들도 Pinning하기 시작