테이블 Full Scan 하는 경우는 db file scattered read 이벤트가 발생하고, 인덱스 Scan 하는 경우는 db file sequential read 이벤트가 발생한다고만 알고 있었는데, SQL 을 수행하다 보니 계속 db file parallel read 만 나오길래 좀 찾아보고 비교해 본 결과를 엿다가 좀 정리해 봅니다.
db file parallel read 는 /*+ parallel(x) */ 힌트와는 아무 상관이 없습니다.
매번 한건씩 한건씩 읽어오는 것을 prefetch 기능을 써서 한번에 읽어올때 주변의 것까지 더 읽어오는 기능입니다.
인덱스를 타더라도 Range 가 좀 많이 넓은 경우 이 기능이 효과를 발휘합니다.
아래의 SQL로 성능차이가 얼마나 나는지 테스트를 해봤습니다.
select /*+ index(t1 emp_1_deptno) index(t2 emp_2_deptno) use_nl(t2) */ max(substr(t2.big_addr,3,1))
from emp_1 t1, emp_2 t2
where t1.empno = t2.empno
and t1.deptno <= 3
and t2.deptno <= 19
and t2.salary >= 100
;
emp_1, emp_2 테이블은 각각 20만건 1.6GB 인 테이블들입니다.
아래와 같이 Index Range Scan 을 하고 있습니다.
Oracle 19c 의 경우, default 가 parallel read 기능이 on 되어 있기 때문에 off 할때는 아래와 같이 파라메타를 세팅해서 테스트할 수 있습니다.
SQL> alter system set "_db_block_prefetch_quota" = 0; -- parallel read 기능 off
SQL> alter system reset "_db_block_prefetch_quota"; -- parallel read 기능 on
아래는 parallel read 기능이 on 되어 있는 상태에서 실행한 결과 입니다.
아래는 parallel read 기능을 끄고 실행한 결과입니다.
4.53초 vs 53.62초 엄청나네요^^
10배가 넘게 차이가 납니다.
