오라클에서 기본적으로 Direct Path I/O 는 아래 경우에만 동작함
- CTAS 작업
- APPEND 힌트를 사용한 insert 작업
- PARALLEL 힌트를 사용한 병렬쿼리 FULL 스캔
- PARALLEL_INDEX 힌트를 사용한 병렬쿼리 INDEX 스캔
- PARALLEL 힌트를 사용한 병렬 DML
- TEMP를 읽고 쓰는 작업
- NOCACHE 옵션으로 LOB 컬럼 읽는 작업
- Datapump 사용시
- 전통 EXP/IMP 중 Direct=Y 옵션 사용시
- SQL*Loader 중 Direct=Y 옵션 사용시
이 경우가 아닌 일반 테이블을 FULL 스캔 할때는
테이블 블록이 데이터 파일에서 버퍼 캐시로 읽혀지기 때문에 db file scattered read 이벤트가 발생해야함
그런데 일반 테이블을 FULL 스캔(병렬X 직렬) 할 때 10046 트레이스를 보니 db file scattered read 가 아닌 direct path read 이벤트가 발생하는걸 발견함
원인은 11g 부터 도입된 _serial_direct_read 파라미터 였음
_serial_direct_read 파라미터는 FULL 스캔시(병렬쿼리가 아닌 직렬 방식) direct path read를 가능하게 제어 해주는 파라미터임
파라미터 값이 auto(기본값) 일 경우 _small_table_threshold 파라미터 값보다 대상 테이블의 blocks(dba_tables) 값이 크다면 Direct Path I/O가 동작함
(여기서 참조하는 blocks 갯수는 _direct_read_decision_statistics_driven 파라미터와도 연관이 있다고함 기본값은 true임)
파라미터 값이 never 일 경우 절대 direct path read를 수행하지 않음
이외에 true, false, always 옵션이 존재함
참고로 _small_table_threshold 파라미터 기본값은 _db_block_buffers 파라미터의 2%임
set lines 200 pages 1000
col desc for a70
col name for a40
col current_value for a15
col default_value for a15
col default_t_f for a15
select
ksppinm "name",
ksppstvl "current_value",
b.ksppstdfl "default_value",
b.ksppstdf "default_t_f",
ksppdesc "desc"
from sys.x$ksppi a, sys.x$ksppcv b
where 1=1
and a.indx=b.indx
AND SUBSTR(a.KSPPINM, 1, 1) = '_'
and a.ksppinm in ('_serial_direct_read', '_small_table_threshold',
'_very_large_object_threshold', '_direct_read_decision_statistics_driven',
'_db_block_buffers')
order by 1;
name current_value default_value default_t_f desc
---------------------------------------- --------------- --------------- --------------- ----------------------------------------------------------------------
_db_block_buffers 17622 0 TRUE Number of database blocks cached in memory: hidden parameter
_direct_read_decision_statistics_driven TRUE TRUE TRUE enable direct read decision based on optimizer statistics
_serial_direct_read auto auto TRUE enable direct read in serial
_small_table_threshold 352 TRUE lower threshold level of table size for direct reads
_very_large_object_threshold 500 500 TRUE upper threshold level of object size for direct reads
주의사항
이처럼_serial_direct_read 파라미터가 auto로 설정되어 있을때 테이블 블록이 _small_table_threshold 값보다 큰 경우 Direct Path I/O가 동작함
이 말은 쿼리에서 특정 테이블이 FULL 하는 실행계획으로 풀릴 때 쿼리 수행시마다 캐싱(버퍼캐시)을 전혀 사용하지 않고 매번 Direct Path I/O를 한다는 뜻임
쿼리가 자주 수행되는 경우라면 성능 저하를 겪을 수 있음
참고 : _small_table_threshold 파라미터값 조정을 통한 시스템 성능 향상 사례 ( https://cafe.naver.com/dbian/1673 )
또한 block cleanout 관련 이슈도 발생할 수 있음
참고 : Direct path read 와 block cleanout 그리고 latch: row cache objects ( https://engineering-skcc.github.io/oracle%20tuning/adaptive-direct-path-load/ )
결론 :
_serial_direct_read 파라미터가 auto일때
_small_table_threshold 값보다 대상 테이블의 블록이 더 많은 경우 Direct Path I/O 가 동작함
_small_table_threshold 값보다 대상 테이블의 블록이 더 적은 경우 Direct Path I/O 가 미동작함
_serial_direct_read 파라미터가 never 일때 Direct Path I/O 가 미동작함
direct path read 이벤트가 많이 발생하는 경우 반복적으로 direct path read를 사용하는 FULL 스캔 쿼리가 없는지
, _small_table_threshold 파라미터 값은 적절한지 확인이 필요함
block cleanout 이슈가 발생할 경우 원인 분석 후 변경이 많이 발생되는 테이블들을 대상으로
주기적으로 수동 cleanout 시키는 방법을 고려할 수 있음(non-direct path read 방식으로 FULL 스캔 수행)