SQL 병목 원인
- 디스크 I/O가 일어나는 동안 프로세스는 잠을 자게 된다.
- 프로세스가 CPU에서 실행 중일 때 디스크에서 데이터를 읽어야 할 경우 waiting 상태에서 I/O가 끝나기를 기다린다.
데이터 저장 구조 - 오라클 기준
- Table Space(테이블스페이스)
- Segment를 담는 Container, 여러 개의 데이터 파일(디스크 상의 물리적인 OS 파일)로 구성된다.
- Segment(세그먼트)
- 데이터 저장 공간이 필요한 오브젝트(테이블, 인덱스…), 여러 개의 Extent로 구성
- 파티션이 아닐 경우 → 하나의 테이블/인덱스는 하나의 Segment
- 파티션 구조일 경우 → 하나의 파티션이 하나의 Segment
- Extent(익스텐트)
- 공간을 확장하는 단위, 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 Table Space로부터 Extent를 추가로 할당받는다
- 연속된 블록들의 집합
- Data File
- 디스크 상의 물리적인 OS 파일
ROWID - DBA + 로우 번호(블록 내 순번), 인덱스 스캔시 사용된다.
💡 테이블을 스캔 할 경우는 테이블 Segment Header에 저장된 Extent Map을 이용한다.Extent Map을 통해 각 Extent의 첫 번째 Block DBA를 알 수 있다.
Extent은 연속된 Block집합이므로 첫번째 블록 뒤에 연속해서 Block을 읽으면 된다.
Block 단위 I/O
- Database가 데이터를 읽고 쓰는 단위
- 특정 레코드 하나를 읽고 싶어도 레코드가 포함된 블록을 통째로 읽는다.
- 오라클 기준으로 Block의 크기는 8KB이다.
- Index도 Block단위로 데이터를 읽고 쓴다.
Access 종류
- Sequential Access
- 논리적/물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
- Index 리프블록에 앞뒤로 연결된 주소값에따라 리프블록을 스캔하는 경우
- Segment의 Extent Map에서 Extent의 첫번째 블록을 읽어들어 연속적으로 블록들을 스캔하는 경우
Table Full Scan
- Random Access
- 물리적인 순서를 따르지 않고 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식
Buffer Cache
- 데이터 캐시
- 디스크에서 읽은 데이터 블록을 캐싱하여 같은 블록에 대한 반복적인
I/O Call을 줄임
논리적 I/O
- SQL을 처리하는 과정에서 발생하는 총 블록 I/O
- 일반적으로 메모리상의 버퍼 캐시를 경유하므로
메모리 I/O==논리적 I/O라고 생각하여도 무방하다- 메모리를 경유하지 않는
Direct Path I/O를 고려한다면 메모리I/O==논리적 I/O+Direct Path I/O
- 메모리를 경유하지 않는
- 데이터의 삽입/삭제가 없는 상황에서 동일한 SQL을 여러번 실행하더라도 매번 읽는 블록 수는 같다
물리적 I/O
- 디스크에서 발생한 총 블록 I/O
- SQL처리 도중 읽어야 할 블록을 Buffer Cahce에서 찾지 못할 경우에만 디스크를 액세스 하므로
논리적 I/O의 일부이다. - 데이터의 삽입/삭제가 없는 상황에서 동일한 SQL을 여러번 샐행할 경우 디스크에서 읽어들이는 블록수는 매번 다르다(점점 버퍼 캐에서 해당 테이블 블록의 점유율이 높아지기 때문)
버퍼캐시 효율을 측정하는 데 사용하는 지표
BCHR
= (캐시에서 바로 찾은 블록 수 / 총 읽은 블록 수) * 100
= ((논리적 I/O - 물리적 I/O) / 논리적 I/O) * 100
= (1 - (물리적 I/O) / (논리적 I/O)) * 100
물리적 I/O = 논리적 I/O * (100% - BCHR)
💡 논리적 I/O는 일정하므로 물리적 I/O는 BCHR에 의해 결정된다BCHR은 시스템 상황에 따라 달라지므로 물리적 I/O는 결국 통제 불가능한 외생변수 이다
즉 SQL 성능을 높이기 위해서 할 수 있는 일은 논리적 I/O를 줄이는 일뿐이다.
논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.
Single Block I/O
한번에 한 블록씩 요청해서 메모리에 적재하는 방식
- 인덱스 루트 블록을 읽을 경우
- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치블록을 읽을 경우
- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 경우
- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 경우
Multi Block I/O
한번에 여러 블록씩 요청하여 메모리에 적재하는 방식
캐시에서 찾지 못한 특정 블록을 읽기 위해 I/O Call을 할 경우 해당 블록과 인접한 블록(같은 Extent에 속한 블록)들을 한번에 읽어 캐시에 미리 적재
- 참고 💡 보통 OS 단에서는 IMB 단위로 I/O Call을 한다.(OS 마다 다름) 오라클 레벨 I/O단위는 보통 8KB이다.(DB마다 다름) 즉 db_file_multiblock_read_count = 128로 할 경우 담을 수 있는 만큼 최대한 담게 된다.(128 * 8KB = 1MB)
인덱스를 사용하지 않고 테이블 전체를 스캔할 경우
Table Full Scan
테이블 전체를 스캔해서 테이블에 저장된 데이터를 읽는 방식
- Sequential Access, Multi Block I/O방식으로 디스크 블록을 읽는다.
- 한 블록에 속한 모든레코드를 한번에 읽고, 캐시에서 찾지 못하면 한번의 수면(I/O Call)을 통해 인접한 수십~수백개의 블록을 한번에 I/O하는 메커니즘이다.
- 수십~수백건의 소량의 데이터를 검색 할 경우에는 비효율적이다.
Index Range Scan
인덱스를 이용하여 테이블의 데이터를 읽는 방식
ROWID를 이용하여 테이블 레코드를 찾아감
- Random Access, Single Block I/O 방식으로 디스크 블록을 읽는다.
- 캐시에서 블록을 못찾으면 레코드 하나를 찾기 위해 매번 잠을 자는 I/O메커니즘이다.
- 많은 데이터를 읽을 경우는 Table Full Scan보다 불리하다.
- 읽었던 블록을 반복해서 읽을 수 도 있는 비효율이 있다.
캐시 탐색 메커니즘
Direct Path I/O를 제외한 모든 블록 I/O는 버퍼캐시를 경유한다.
- 인덱스 루트 블록을 읽을 경우
- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 경우
- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 경우
- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 경우
- 테이블 블록을 Full Scan 할 경우
DBMS에서는 버퍼캐시를 해시 구조로 관리한다.
캐시 탐색 과정
- 블록 번호의 해시 결과를 가지고 그에 해당하는 해시 체인을 탐색
- 없을 경우 디스크에서 읽어들어 해시 체인에 연결
- 메모리 공유 자원에 대한 액세스 직렬화 💡 메모리 공유 자원에 대한 액세스 직렬화 버퍼 캐시는 SGA의 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다. **하나의 블록**을 두 개 이상의 프로세스가 동시에 접근하려고 할때의 문제를 방지하기 위해 **직렬화(Serialization)** 메커니즘을 사용한다. 이를 **Latch(래치)**라고 한다. 💡 캐시버퍼 체인 래치 대량의 데이터를 읽을 때 모든 블록에 대해 해시 체인을 탐색한다. DBA를 해시 함수에 입력하고 거기서 반환된 값으로 스캔해야 할 해시체인을 찾는다. 이때, 다른 프로세스가 체인 구조를 변경하면 곤란하기 때문에, **해시 체인 래치**가 존재한다. SGA를 구성하는 서브 캐시마다 별도의 래치가 존재한다. 버퍼캐시에는 **캐시버퍼 체인 래치**, **캐시버퍼 LRU 체인 래치** 등이 작동한다. 💡 버퍼 Lock 캐시버퍼 체인 뿐만 아니라 버퍼 블록 자체에도 직렬화 메커니즘이 필요하다. 읽고자 하는 블록을 찾았을 경우 캐시버퍼 체인 래치를 바로 해제해야 한다. 래치를 해제한 상태로 버퍼블록 데이터를 읽고 쓰는 도중 후행 프로세스가 같은 블록에 접근하여 데이터를 읽고 쓴다면 데이터 정합성에 문제가 생길 가능성이 있다. 이를 방지하기 위해 **버퍼 Lock**을 사용한다. 로우 Lock이 있어도, 로우 Lock을 설정하는 행위도 블록을 변경하는 작업니다. 로우 Lock을 설정하는 순간 다른 프로세스가 해당 블록을 읽는다면 문제가 생긴다. 같은 블록에서 다른 로우를 동시에 읽고 쓰는 경우를 막기 위해서도 버퍼 Lock은 필요하다.
안녕하세요