SQL이 느린 이유
- SQL이 느린 이유는 디스크 I/O 때문임
- OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 멈춰지고 기다림 (동기방식)
- 인터럽트 없이 실행되던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 WAITING상태가 되어 I/O완료를 기다림 -> I/O가 오래 걸리면 프로세스가 느려짐
- I/O튜닝이 안 된 시스템에서는 수많은 프로세스들이 동시다발적으로 발생하는 I/O Call 때문에 디스크 경합이 심해지고 그만큼 대기 시간도 길어짐 -> SQL이 느려짐 -> 디스크 I/O가 SQL 성능을 좌우함
데이터베이스 저장 구조
- 테이블스페이스 > 세그먼트 > 익스텐트 > 블록(페이지) > 데이터 레코드
(데이터베이스 > 테이블스페이스 > 데이터파일)
- 데이터를 저장하려면 테이블 스페이스를 생성해야 함
- 테이블 스페이스는 세그먼트를 담는 컨테이너로, 여러 개의 데이터 파일(디스크 상의 물리적인 OS파일)로 구성됨
세그먼트
- 데이터 저장공간이 필요한 오브젝트 (테이블, 인덱스, 파티션, LOB 등)
- 파티션 구조가 아니라면 테이블도 하나의 세그먼트이고, 인덱스도 세그먼트
- 여러 익스텐트로 구성됨
익스텐트
- 공간을 확장하는 단위
- 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족하면, 테이블 스페이스로부터 추가 익스텐트를 할당 받음
- 하나의 익스텐트는 하나의 테이블이 독점, 한 익스텐트에 있는 블록은 모두 같은 테이블의 블록
- 연속된 블록의 집합체
- 익스텐트 내 블록은 연속적이지만, 익스텐트 끼리는 연속된 공간이 아님
블록
- 실제 사용자가 입력한 레코드 데이터를 저장하는 공간
- 하나의 블록은 하나의 테이블이 독점, 한 블록에 있는 레코드들은 모두 같은 테이블의 레코드
- 데이터베이스에서 데이터를 읽고 쓰는 단위
- 데이터 I/O의 단위가 블록이므로, 테이블의 레코드 하나, 칼럼 하나만 읽고 싶어도 전체 블록을 통째로 읽음
데이터 파일
- 세그먼트에 공간이 부족하면 테이블 스페이스로부터 익스텐트를 부여받는데, 하나의 세그먼트를 구성하는 익스텐트들은 모두 같은 데이터 파일에 존재하지 않을 수 있고, 그럴 가능성이 더 높음
- 하나의 테이블 스페이스를 파일 경합을 줄이기 위해 여러 개의 데이터 파일로 구성됨
테이블(세그먼트) > 익스텐트 > 블록 접근 방법
- 모든 데이터 블록은 디스크 상에서 몇 번 데이터 파일의 몇 번째 블록인지를 나타내는 고유 주소 값(DBA)을 가짐
- 데이터를 읽고 쓰는 단위가 블록이므로 데이터에 접근하려면 DBA부터 확인해야 함
- 인덱스를 사용하여 테이블 레코드를 읽을 때는 인덱스 ROWID을 이용함, ROWID는 DBA+로우 번호(블록 내 순번)로 구성됨
- 테이블을 스캔할 때는 테이블 세그먼트의 익스텐트 맵(세그먼트에 할당된 익스텐트 목록)을 이용
- 익스텐트 맵에서 각 익스텐트의 첫 번째 블록의 DBA를 알 수 있음
- 익스텐트는 연속된 블록의 집합이므로 첫 번째 블록을 읽고 뒤이어서 연속된 블록을 읽으면 됨
- 데이터 I/O의 단위가 블록이므로, 테이블의 레코드 하나, 칼럼 하나만 읽고 싶어도 전체 블록을 통째로 읽음
시퀀셜 액세스 VS 랜덤 액세스
- 테이블 또는 인덱스 블록을 액세스 하는 방식에는 시퀀셜 액세스와 랜덤 액세스가 있음
- 시퀀셜 : 논리적/물리적으로 연결된 순서에 따라 차례로 블록을 읽음, 주소 값에 따라 앞 또는 뒤로 순차적으로 읽음
- 랜덤 : 논리적/물리적 순서를 따르지 않고, 레코드 하나를 읽기 위해서 한 블록씩 접근
DB 버퍼 캐시
- DB의 성능은 디스크 I/O가 중요
- SQL을 수행하는 과정에서 계속해서 데이터 블록을 읽어오는데, 자주 읽는 블록을 매번 디스크에서 읽는 것을 비효율적
- 데이터 캐싱을 통해 디스크 I/O를 줄임
- DB 버퍼 캐시도 라이브러리 캐시(코드캐시) 처럼 SGA의 구성요소임
- 라이브러리 캐시가 SQL, 실행계획, 함수, 프로시저를 캐싱하는 코드 캐시라면, DB 버퍼 캐시는 데이터 블록을 캐싱해서 동일한 데이터 블록에 대한 반복적인 I/O를 줄여주는 데이터 캐시의 역할을 함
- 데이터 블록을 읽을 때는 항상 버퍼 캐시부터 탐색함
논리적 I/O, 물리적 I/O
- 논리적 I/O는 SQL를 처리하는 과정에서 버퍼 캐시에서 블록을 읽는 경우(메모리 버퍼 캐시에서 읽어 들임)
- 물리적 I/O는 원하는 블록이 버퍼 캐시에 없으므로 디스크에 액세스 하여 블록을 읽어옴(디스크에서 읽음 -> 느림)
- 물리적 I/O는 디스크에 접근하므로 메모리에 접근하는 논리적 I/O에 비해 매우 느림
- 블록을 읽을 때는 해당 블록을 먼저 버퍼 캐시에서 찾아보고 없을 때만 디스크에서 읽는데, 이때도 디스크에서 곧바로 읽는 게 아니라 먼저 버퍼 캐시에 적재하고서 읽음
- SQL 성능을 높이기 위해서는 논리적 I/O를 줄여야 함, SQL 튜닝을 해서 논리적 I/O를 줄여야 물리적 I/O도 줄어듬
- 논리적 I/O을 줄이는 것은, SQL을 튜닝해서 읽어 들이는 총 블록의 개수를 줄이면 됨
Single Block I/O VS Multi Block I/O
- 모든 데이터를 전부 버퍼 캐시에 적재할 수는 없으므로, 전체 데이터 중에서 일부만 캐시에 적재
- 캐시에서 찾지 못한 블록은 디스크 I/O를 통해 디스크에서 버퍼 캐시로 적재하고 읽음
- Single Block I/O : 메모리 적재 시 한 번에 한 블록씩 요청
- Multi Block I/O : 메모리 적재 시 한 번에 여러 블록씩 요청
- 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O를 사용
- 인덱스는 소량의 데이터를 읽을 때 주로 사용(Single Block I/O) 반대로, 많은 데이터 블록을 읽을 때는 Multi Block I/O 가 효율적
- 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 Multi Block I/O을 사용
- Multi Block I/O는 캐시에 찾지 못한 특정 블록을 읽으려고 디스크 상에 해당 블록과 인접하며 같은 익스텐트에 속한 블록들을 한꺼번에 읽어서 미리 적재
'DATABASE > 튜닝' 카테고리의 다른 글
[SQL 튜닝] 인덱스 기본 사용법 (0) | 2020.06.15 |
---|---|
[SQL 튜닝] 인덱스 구조 및 탐색 (0) | 2020.06.15 |
[SQL 튜닝] Table Full Scan VS Index Range Scan VS Index Full Scan (0) | 2020.06.08 |
[SQL 튜닝] SQL 공유 및 재사용(라이브러리 캐시, SQL캐싱) (0) | 2020.06.07 |
[SQL 튜닝] SQL 파싱과 최적화 (0) | 2020.06.07 |