1.3.7 Table Full Scan vs Index Range Scan
테이블에 저장된 데이터를 읽는 방식은 두가지가 있다.
-
Table Full Scan
말 그대로 테이블에 속한 블록 '전체'를 읽어서 사용자가 원하는 데이터를 찾는 방식이다. -
Index Range Scan
랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는 방식이다. 인덱스에서 '일정량'을 스캔하면서 ROWID로 테이블 레코드를 찾아가는 방식이다. ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치 정보이다.
Table Full Scan은 피해야 한다는 많은 개발자의 인식과 달리 인덱스가 SQL 성능을 떨어뜨리는 경우도 상당히 많다.
Table Full Scan은 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다.
시퀀셜 액세스와 Multiblock I/O가 아무리 좋아도 수십 ~ 수백건의 소량 데이터를 찾을 때 수백만 ~ 수천만 건 데이터를 스캔하는 건 비효율적이라고 할 수 있다.
따라서 큰 테이블에서 소량 데이터를 검색할때는 반드시 인덱스를 이용해야 한다.
Table Full Scan이 항상 나쁜것도 아니다. 인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일 뿐이므로 모든 성능 문제를 인덱스로 해결하려 해선 안된다.
데이터가 일정량을 넘으면 Table Full Scan이 유리하다.
1.3.8 캐시 탐색 메커니즘
Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼 캐시를 경유한다.
아래와 같은 오퍼레이션은 모두 버퍼 캐시 탐색 과정을 거친다.
- 인덱스 루트 블록을 읽을 때
- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을ㄷ 때
- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
- 테이블 블록을 Full Scan 할 때
버퍼캐시에서 블록을 찾을때는 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터(Pointer)로 버퍼 블록을 액세스하는 방식을 사용한다.
해시 구조의 특징
- 같은 입력 값은 항상 동일한 해시 체인(=버킷)에 연결됨
- 다른 입력 값(예를 들어, 4와 9)이 동일한 해시 체인(=버킷)에 연결 될 수 있음
- 해시 체인 내에서는 정렬이 보장되지 않음
메모리 공유자원에 대한 액세스 직렬화
공유자원은 모두에게 권한이 있기 때문에 누구나 접근할 수 있다.
문제는 하나의 버퍼블록을 두 개 이상 프로세스가 동시에 접근하려고 할때 발생한다.
자원을 공유하는 것처럼 보여도 내부에선 한 프로세스씩 순차적으로 접근하도록 구현해야 한다.
이를 위해 직렬화 메커니즘이 필요하며, 쉽게 말하면 '줄 세우기' 다.
공유캐시 또한 동시에 접근할때 문제가 생기는데, 이를 해결하기 위해 나온것이 직렬화다.
직렬화를 지원하는 매커니즘이 래치(Latch)다.
SGA를 구성하는 서브 캐시마다 별도의 래치가 존재한다.
빠른 데이터베이스를 구현하려면 버퍼캐시 히트율을 높여야 하지만, 캐시 I/O도 생각만큼 빠르지 않을 수 있다. 래치에 의한 경합이 생길 수 있기 때문이다.
직렬화 메커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄여야 한다.
버퍼 Lock
래치를 해제한 상태로 버퍼블록 데이터를 읽고 쓰는 도중에 후행 프로세스가 같은 블록에 접근해서 데이터를 읽고 쓴다면 데이터 정합성에 문제가 생길 수 있다.
이를 방지하기 위해 오라클은 버퍼 Lock을 사용한다. 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더에 Lock을 설정함으로써 버퍼블록 자체에 대한 직렬화 문제를 해결하는 것이다.
출처 : 친절한 SQL 튜닝(디비안, 조시형 저)
