[Oracle] FULL SCAN HINT

1. FULL HINT

실무를 뛰다보면 한테이블에 수백만에서 많개는 수천만의 데이터를 가진 테이블을 조회해서 데이터를 추출해야할 일들이 많이 있습니다. 이럴 경우 FULL과 HASH를 이용해서 데이터 추출을 많이 하였습니다. 하지만 왜 FULL을 써야하는지에 대해서는 잘 알지 못해서 이번 기회에 한번 정리를 하려고합니다.

 

 FULL SCAN은 CPU와 디스크의 사용률을 높이기 때문에 DBA들이 좋아하지 않습니다. 하지만 데이터량이 너무 많고, 적당한 인덱스가 없는 경우 FULL 과 HASH를 조합해서 데이터를 종종 추출하곤합니다. 

 

FULL SCAN 방식은 다중 블록단위 I/O를 수행합니다. ‘db_file_multiblock_read_count’ 파라미터에 설정된 값만큼 EXTENT의 블록들을 한번의 I/O Call에 읽어 들임으로써 인덱스를 활용한 단일 블록단위 I/O보다 적은 I/O Call을 발생시킵니다. 그래서 대량의 블록을 읽어야 할 경우에는 단연 FULL SCAN 방식이 유리합니다.

 

예를 들어 살펴보면 TABLE FULL SCAN 방식은 첫 번째 EXTENT를 시작으로 HWM(High Water Mark) 표기된 지점까지 읽기를 수행하는데, [그림 1]과 같이 EXTENT가 20 Block이고 ‘db_file_multiblock_read _count’ 파라미터가 8로 설정되어 있다면 총 7번의 I/O Call 이 발생하게 됩니다. 여기서 특이사항은 해당 파라미터값이 아무리 높게 설정되어 있어도 EXTENT의 단위를 넘어서도록 읽을 수는 없다는 것입니다.

 

추가로 오라클 데이터 버퍼캐시는 디스크 I/O를 줄이기 위해 최근에 사용했던 블록에 우선순위를 두는 LRU(Least Recently Used) 알고리즘을 사용합니다. 하지만 본래 LRU 알고리즘의 사상대로라면 FULL SCAN 방식으로 읽힌 대량의 블록들은 LRU 리스트를 점령해 기존에 관리되던 블록들을 밀어내게 됩니다.

 

이럴 경우 데이터 버퍼캐시의 적중률이 현저하게 낮아지는데 이것을 막기 위해 오라클은 TABLE FULL SCAN의 경우에는 LRU 리스트의 우선순위가 낮은 쪽의 블록들을 할당해주고 반복 재활용하게 함으로써 TABLE FULL SCAN으로 인한 악영향을 최소화합니다.

 

출처: http://www.gurubee.net/lecture/2778