DB버퍼캐시에 해당 테이블의 데이터 있는지 확인
- 있으면 캐시에서 데이터 접근 (메모리 I/O)
- 없으면 디스크에 접근해서 해당 테이블 데이터를 DB버퍼캐시로 복사 (디스크 I/O)
DB버퍼캐시의 데이터 중 원하는 결과만 추출하여 유저프로세스로 전송 (블록 단위)
SELECT문 실행시 다음과정을 거친다고 했었다. 오라클은 데이터를 블록
단위로 읽어오고, 결국 모든 블록은 DB버퍼캐시를 통해 접근하게된다. (direct path 접근을 제외하고) 이때, 캐시에 없는 데이터들은 디스크에서 읽어오는 과정이 필요하다.
이 디스크 I/O 과정에서 프로세스는 CPU를 OS에 반환하고 I/O가 끝날 때 까지 대기해야하기 때문에 캐시에서 읽는 메모리 I/O에 비해 10000배 정도 느리다. 그러나 DB버퍼캐시는 한정적이기 때문에 읽어올 블록의 양이 많다면 디스크 I/O가 어느정도 발생할 수 밖에 없다.
이러한 과정을 생각해보면 결국 I/O 효율화를 위해 우리가 해야하는 것은 다음 2가지로 요약할 수 있다.
BCHR (Buffer Cache Hit Ratio)
버퍼캐시의 효율을 측정하는 지표
(캐시에서 곧바로 찾은 블록수 / 총 읽은 블록수 ) * 100
싱글블록I/O와 멀티블록I/O
인덱스가 빠른 이유는 B-Tree구조로 데이터가 저장되어있기 때문이다. (이진 탐색의 원리)
인덱스는 많은 데이터에서 소량의 데이터를 추출할 때 가장 효율적이다. (추출 건수가 많을수록 느려진다)
인덱스는 정렬된 상태로 저장되어 인덱스로 찾은 데이터는 자동으로 정렬된 상태이다.
인덱스 칼럼에 저장된 값은 ROWID
인덱스 개수가 늘어날수록 DML 부하에 따른 트랜잭션 성능이 저하되고 관리 비용이 증가해서 인덱스를 추가할 때는 주의해야한다.
인덱스 수직적 탐색
인덱스 수평적 탐색
인덱스를 수직 탐색하여 조건의 시작 지점을 찾고, 조건의 종료지점까지 순차적으로 탐색하여 테이블에 접근한다.
기본적인 인덱스 스캔 방식
INDEX RANGE SCAN사용하지 못하는 경우
인덱스의 시작과 끝 지점을 정확히 찾을 수 없을 때 INDEX RANGE SCAN을 사용할 수 없다.
인덱스의 선두 칼럼을 가공한 경우
인덱스의 선두 칼럼이 조건절에 없는 경우
인덱스의 선두 칼럼 조건이 OR
혹은 IN
으로 되어있는 경우
/*+ use_concat */
을 사용해 OR-Expansion
을 유도하거나 직접 UNION ALL 사용하여 실행계획 분리
옵티마이저가 INLIST ITERATOR
방식으로 자동 최적화 혹은 직접 UNION ALL 사용하여 실행계획 분리
인덱스 선두칼럼이 조건절에 없는 경우 전체 인덱스를 모두 탐색하여 테이블에 접근한다.
테이블에 액세스하는 데이터의 양이 아주 적다면 TABLE FULL SCAN보다 좋다.
SORT 연산을 생략하고 인덱스에서 처음 일부 데이터만 찾고 싶은 경우 효율적일 수 있다. (first_rows
힌트 사용)
인덱스 수직탐색만으로 모든 탐색이 종료되는 경우
유일한 1건의 데이터만 찾는다.
오라클 9i 버전부터 등장
인덱스 선두 컬럼이 조건절이 없어야한다. (있어도 범위 조건)
인덱스 선두 컬럼의 Distinct value가 적고 후행칼럼의 Distinct value가 많을 때 효과적이다.
인덱스 스캔 방식 중 유일하게 멀티블록 I/O방식으로 스캔한다.
정렬 순서가 보장되지 않는다.
쿼리에 포함된 칼럼이 모두 인덱스에 있을 때만 사용할 수 있다.