batch i/o, table prefetch, 옵티마이저

■ batch i/o 
- inner 쪽 인덱스와 조인하면서 중간 결과 집할을 만든 후에 inner 쪽 테이블과 일괄 (batch) 처리한다.
- 힌트: nlj_batching(일괄 처리), no_nlj_batching(일괄처리 X)

#) d.location_id = 2500 의 건수 | d.location_id에 index 확인

SELECT /*+ gather_plan_statistics leading(d, e) use_nl(e) */ e.last_name, e.first_name, e.salary, e.job_id, d.department_name
FROM departments d, employees e
WHERE e.department_id = d.department_id
AND d.location_id = 2500;

#) index 확인

select * from user_ind_columns where table_name = 'EMPLOYEES';

#) 실행 계획

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

■ table prefetch 
- 디스트 i/o 를 수행하려면 비용이 많이 들기 때문에 한번에 i/o call 이 필요한 시점에 곧이어 읽을 가능성이 높은 block들을 data buffer cache에 미리 적재해 두는 기능
- inner 쪽에 non unique index를 range scan 시에 발생한다.
- index range scan은 single block i/o가 발생한다. 이때 발생하는 이벤트는 db file sequential read가 발생 할 수 있다. table prefetch 기능이 수행되면 db file paralle reads 이벤트가 발생한다. 

#) optimizer_features_enable('버전') : 옵티마이저 버전 낮추기 -> 사용하는 버전보다는 낮은 버전으로만 사용가능

- 버전 낮추는 경우: 실행계획이 달라졌을 경우 

SELECT 
        /*+ optimizer_features_enable('10.2.0.5') 
            gather_plan_statistics 
            leading(d, e) 
            use_nl(e) 
        */ 
        e.last_name, e.first_name, e.salary, e.job_id, d.department_name
FROM departments d, employees e
WHERE e.department_id = d.department_id
AND d.location_id = 2500;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

 

#) 옵티마이저 버전 일부러 오류 내기. -> 사용가능한 버전 확인해보기

alter session set optimizer_features_enable = '9.2.100';

#) 옵티마이저 버전 9버전

SELECT 
        /*+ optimizer_features_enable('9.0.0') 
            gather_plan_statistics 
            leading(d, e) 
            use_nl(e) 
        */ 
        e.last_name, e.first_name, e.salary, e.job_id, d.department_name
FROM departments d, employees e
WHERE e.department_id = d.department_id
AND d.location_id = 2500;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

#) 옵티마이저 버전 8버전

SELECT 
        /*+ optimizer_features_enable('8.0.0') 
            gather_plan_statistics 
            leading(d, e) 
            use_nl(e) 
        */ 
        e.last_name, e.first_name, e.salary, e.job_id, d.department_name
FROM departments d, employees e
WHERE e.department_id = d.department_id
AND d.location_id = 2500;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

#) no_nlj_batching(inner table) 힌트 사용
- 이전 버전 으로 batching 요청

SELECT /*+ gather_plan_statistics leading(d, e) no_nlj_batching(e) */ e.last_name, e.first_name, e.salary, e.job_id, d.department_name
FROM departments d, employees e
WHERE e.department_id = d.department_id
AND d.location_id = 2500;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

table prefetch 기법