Prefetch (by hrson81) [2017.04.03]
h1.04 Prefetch
- 오라클을 포함한 모든 DBMS는 디스크 블록을 읽을 때 곧이어 읽을 가능성이 높은 블록을 미리 읽어오는 Prefetch 기능 제공한다.
- Multiblock I/O도 Prefetch 라고 할 수 있으나 다음의 Prefetch는 인접하지 않은 블록을 미리 읽어오는 기능이다.
- 미리 읽어오고도 사용하지 않아 캐시에서 밀려나는 비율이 높다면 비효율적이므로 기능을 사용하지 않는 것도 고려해볼 수 있다.
prefetch 건수와 prefetch후 사용하지 않은 블록량 조회
select name, value from v$sysstat
where name in ('physical reads cache prefetch'
,'prefetched blocks aged out before use');
NAME VALUE
-------------------------------------------------
physical reads cache prefetch 696866478
prefetched blocks aged out before use 2073208
h3.(1) 인덱스 Prefetch
- 오라클 7.2부터 사용되었다.
- 브랜치 블록에서 앞으로 읽게 될 리프 블록을 미리 캐싱해두는 기능이다.
- 블록 5번을 읽으려는 시점에서 5번 블록이 버퍼캐시에 없으면 5번 뿐만 아니라 6번, 7번도 같이 적재해놓으면 DISK I/O 대기
가능성을 줄일 수 있다.
h3.(2) 테이블 Prefetch
- 테이블 Lookup Prefetch 또는 데이터 블록 Prefetch라고 한다.
- 인덱스를 경유해 테이블 레코드를 액세스하는 도중 디스크에서 캐시로 블록을 적재할 때 다른 블록까지 미리 적재해두는 기능이다.
- 그림에서 진행순서가 1->2->3->4->5->6->7일 때 12번 블록을 읽으려고할 때 13,15,18번 블록까지 같이 적재한다.
- 버퍼피닝은 random액세스에 의한 논리적 블록 요청 횟수를 감소시킨다.
- 테이블 Prefetch는 디스크 I/O에 의한 대기 횟수를 감소시킨다
- 이 기능은 클러스터링 팩터가 나쁠 때 효과를 발휘한다.
클러스터링 팩터가 나쁘면 디스크에 있는 블록을 버퍼캐시로 올려달라는 요청을 할 확률이 높아지기 때문이다.
테이블 prefetch 테스트
--prefetch 기능을 비활성화하여 조회
SELECT /*+GATHER_PLAN_STATISTICS INDEX(A TABLE_A_IDX1) NO_NLJ_PREFETCH(B) */
*
FROM TABLE_A A, TABLE_B B
WHERE A.COL1 = '2016-12-31'
AND A.COL2 LIKE 'O%'
AND A.COL3 BETWEEN 1 AND 5000
AND A.COL4 = B.COL4
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106K|00:00:00.72 | 65050 |
| 1 | NESTED LOOPS | | 1 | 77 | 106K|00:00:00.72 | 65050 |
| 2 | TABLE ACCESS BY INDEX ROWID| TABLE_A | 1 | 3 | 12985 |00:00:00.14 | 12117 |
|* 3 | INDEX RANGE SCAN | TABLE_A_IDX1 | 1 | 3 | 12985 |00:00:00.03 | 2486 |
| 4 | TABLE ACCESS BY INDEX ROWID| TABLE_B | 12985 | 28 | 106K|00:00:00.55 | 52933 |
|* 5 | INDEX RANGE SCAN | TABLE_B_IDX6 | 12985 | 28 | 106K|00:00:00.23 | 39622 |
-------------------------------------------------------------------------------------------------------
--table prefetch로 조회
SELECT /*+GATHER_PLAN_STATISTICS INDEX(A TABLE_A_IDX1)*/
*
FROM TABLE_A A, TABLE_B B
WHERE A.COL1 = '2016-12-31'
AND A.COL2 LIKE 'O%'
AND A.COL3 BETWEEN 1 AND 5000
AND A.COL4 = B.COL4
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 |
| 1 | TABLE ACCESS BY INDEX ROWID | TABLE_B | 1 | 28 | 106K|00:00:00.78 | 65050 |
| 2 | NESTED LOOPS | | 1 | 77 | 119K|00:00:00.43 | 51739 |
| 3 | TABLE ACCESS BY INDEX ROWID| TABLE_A | 1 | 3 | 12985 |00:00:00.15 | 12117 |
|* 4 | INDEX RANGE SCAN | TABLE_A_IDX1 | 1 | 3 | 12985 |00:00:00.03 | 2486 |
|* 5 | INDEX RANGE SCAN | TABLE_B_IDX6 | 12985 | 28 | 106K|00:00:00.24 | 39622 |
--------------------------------------------------------------------------------------------------------