0. SQL 최적화와 전체 과정
SQL은 원하는 결과 집합을 선언할 때, 구조적, 집합적이지만, 결과 집합을 만드는 과정은 절차적일 수밖에 없다.
절차적이라는 뜻은 결국 프로시저가 필요하고 이 프로시저를 만들어내는 DBMS의 내부 엔진이 바로 SQL 옵티마이저이다.
SQL 최적화는 바로 여기서 나오는 개념이다.
프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정이 바로 SQL 최적화이다.
전체 과정은 다음과 같다.
-
SQL 파싱
- SQL파서가 진행
- 파싱 트리 생성, Syntax 체크(문법 오류 확인), Semantic 체크(의미상 오류 확인)
-
SQL 최적화
- 옵티마이저가 진행
- 다양한 실행경로를 생성해서 가장 효율적인 하나 선택/ 데이터베이스 성능 결정의 핵심 엔진
-
로우 소스 생성
- 로우 소스 생성기가 진행
- SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 혹은 프로시저 형태로 변환하는 단계
1. SQL 공유 및 재사용 - 소프트 파싱 VS 하드 파싱
앞선 과정으로 내부 프로시저를 생성하면 반복적으로 재사용할 수 있게 SGA(System Global Area)의 라이브러리 캐시에 캐싱해둔다.
그 후론, 사용자가 sql문을 작성할 때마다 파싱 이후에 라이브러리 캐시에 해당 sql이 존재하는지 확인한다.
만약, 캐시에 존재하면 바로 실행단계로 넘어가고, 캐시에 없다면 최적화 단계, 로우 소스 생성의 단계를 거친다.
여기서 전자의 경우를 소프트 파싱, 후자의 경우를 하드 파싱이라고 한다.
2. I/O 메커니즘 - 디스크 I/O와 성능의 관계
인터럽트 없이 작업 중인 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 반환한 채 대기큐에서 대기하는 시간이 분명히 존재한다.
더군다나 실제 프로세스는 한 개 정도에서 그치는 것이 아니라 수 많은 프로세스가 존재한다.
이 때, 수 많은 프로세스에 의해 동시다발적으로 발생하는 I/O Call 때문에 디스크 경합이 심해지고 그만큼 대기 시간이 늘어난다.
즉, 디스크 I/O가 SQL 성능을 결정한다고 해도 과언이 아니다.
3. 데이터베이스 저장 구조
-
데이터파일 : 디스크 상의 물리적인 OS 파일
-
테이블 스페이스 : 세그먼트를 담는 컨테이너, 여러 개의 데이터파일로 구성
-
세그먼트 : 데이터 저장공간이 필요한 오브젝트 (ex. 테이블, 인덱스, 파티션 LOB)
-
익스텐트 : 공간을 확장하는 단위, 연속된 블록의 집합
-
블록 : 데이터를 읽고 쓰는 기본 단위
1에서 5로 갈수록 저장 단위가 작아진다.
오라클에서 블록 사이즈를 확인하려면 다음과 같은 명령어를 입력하면 된다.
show parameter block_size
or
select value from v$parameter where name = 'db_block_size'
공간은 익스텐트 단위로 확장하지만, 실제 레코드는 블록에 저장한다.
한 블록은 한 테이블이 독점하고, 한 익스텐트도 하나의 테이블이 독점한다. 즉, 한 블록이나 한 익스텐트에 저장된 레코드는 모두 같은 테이블 레코드이다.
하지만, 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지는 않는다.
일반적으로 하나의 테이블 스페이스를 여러 데이터파일로 구성하면, 파일 경합을 줄이기위해 가능한 한 여러 데이터파일로 분산 저장한다.
익스텐트는 연속된 블록의 집합이기에 익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 그렇다고 익스텐트끼리는 연속된 공간이라는 소리는 아니다.
DBA(Data Block Address)
- 데이터 블록이 디스크 상에서 몇 번 데이터 파일의 몇 번째 블록인지를 나타내는 고유 주소값으로 데이터를 읽기 위해 제일 먼저 확인한다.
DBA 활용 방식
-
인덱스를 사용할 땐 인덱스 ROWID를 이용하는데, ROWID = DBA + 로우 번호(블록 내 순번)이다. 따라서, ROWID 분해를 통해 읽어야 할 테이블 레코드의 DBA를 확인한 뒤, 인덱스에 사용한다.
-
테이블 스캔 시엔 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 통해 각 익스텐트의 첫 번째 블록 DBA를 알 수 있고, 그 뒤의 연속해서 저장된 블록을 읽으면 된다.
4. 데이터를 읽는 방법 - 시퀀셜 엑세스 VS 랜덤 엑세스
시퀀셜 엑세스는 논리적/물리적으로 연결된 순서에 따라 블록을 읽는 방식이다.
인덱스의 경우 인덱스 리프 블록엔 앞뒤를 가리키는 주소값이 존재해 논리적으로 연결되어 있고, 이 값에 따라 앞 뒤로 순차적으로 스캔한다.
테이블의 경우 테이블 블록 간에는 서로 논리적인 연결고리가 없기에 세그먼트 헤더에 세그먼트에 할당된 익스텐트 목록을 맵으로 관리하고, 이 익스텐트 맵은 각 익스텐트의 첫 블록의 주소값(DBA)을 가진다.
익스텐트 맵을 통해 각 익스텐트의 첫 번째 블록 뒤의 연속하는 블록을 순서대로 읽게되는데 이것이 full table scan이자 시퀀셜 액세스를 말한다.
랜덤 엑세스는 논리적/물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식이다.
5. 논리적 I/O VS 물리적 I/O
논리적 블록 I/O는 SQL을 처리하는 과정에 발생한 총 블록 I/O을 말한다.
메모리상의 버퍼 캐시를 경유하므로 메모리 I/O가 곧 논리적 I/O라고 할 수 있지만, 정확히는 논리적 I/O = 메모리 I/O + Direct Path I/O임을 알아 두자. 또한, 논리적 블록 I/O는 전기신호를 통해 이루어지므로 매우 빠르다.
반면에, 물리적 블록 I/O는 디스크에서 발생한 총 블록 I/O이며, 액세스 암을 통한 물리적 작용으로 논리적 블록 I/O보다 10000배 느리다.
자주 읽는 블록을 매번 디스크에서 읽는 것은 비효율적이기에 SGA의 DB 버퍼캐시란 곳에 데이터 블록을 캐싱해둔다.
이렇게 함으로써 반복적인 I/O Call을 줄인다. 데이터 블록을 읽고자 할 땐 항상 버퍼캐시부터 탐색한다.
Direct Path Read 방식을 제외하곤 모든 데이터 블록은 DB 버퍼캐시를 경유해서 읽기 때문에 논리적 I/O 횟수 = DB 버퍼캐시에서 블록을 읽은 횟수라고 할 수 있다. 물론, 개념이 같지는 않지만 수치는 같다.
또한, 블록을 읽을 때는 항상 먼저 버퍼캐시를 찾아보고 없을 때만 디스크 탐색을 진행하는데, 이 때도 디스크에서 곧바로 읽는 것이 아닌 버퍼캐시에 적재하고서 읽는다.
버퍼캐시 히트율(BCHR)은 전체 블록 중에서 물리적인 디스크 I/O를 수반하지 않고 메모리에서 바로 찾은 비율을 말하며,
수식으로 나타내면 물리적 I/O = 논리적 I/O * (100-BCHR)이다. 온라인 트랜잭션 애플리케이션에선 평균 99% 히트율 달성이 일반적이다.
따라서, 물리적 I/O가 성능을 결정하지만, 물리적 I/O는 통제 불가능한 외생변수이고, 내생변수인 논리적 I/O를 줄이는 것이 결국 성능을 높이는것임을 알 수 있다.
그리고, 이 논리적 I/O는 읽는 총 블록 개수를 줄이면 되며, 이것이 SQL 튜닝 과정이라고 할 수 있다.
6. Single Block I/O VS Multiblock I/O
캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 버퍼 캐시에 적재하는데, I/O Call 요청 시 한 번에 한 블록씩이냐 여러 블록씩이냐에 따라 둘의 차이가 생긴다.
인덱스를 이용할 때는 기본적으로 테이블, 인덱스 블록 모두 SIngle Block I/O 방식을 사용한다.(소량의 데이터를 읽을 때 효율적)
많은 데이터 블록을 읽을 땐, Multiblock I/O가 효율적(테이블 전체 스캔 시)이다.
대용량 테이블이면 수많은 블록을 디스크에서 읽는 동안 대기 큐에 여러번 갔다올텐데 기왕 갔다오는거 한꺼번에 많은 양을 요청하는 것이 성능에 좋기 때문에 테이블이 클수록 MultiBlock I/O 단위도 크면 좋다.
좀더 자세히는 MultiBlock I/O의 경우 디스크 상에 읽고자 하는 블록과 ‘인접한’ 블록을 한꺼번에 읽어 캐시에 적재해둔다.
DBMS 블록 사이즈와 관계없이 OS 단에선 보통 1MB 단위로 I/O를 수행한다. 오라클 레벨 I/O 단위가 8KB이므로 한 번에 읽는 단위(MultiBlock I/O 단위)를 128(8 * 128 = 1MB)로 설정하면 최대로 담을 수 있다.
단, 인접한 블록을 한꺼번에 읽는 것이기 때문에 익스텐트 경계를 넘지는 못한다. 한 익스텐트에 20개 블록이 있고, MultiBlock I/O 단위가 8이라고 할 때, 세 번째 I/O Call에서는 4개 블록만 얻고 끝난다. 다음 익스텐트의 4개 블록을 추가로 읽지 않는다는 의미다.
7. Table Full Scan VS Index Range Scan
Table Full Scan : 시퀀셜 액세스 + MultiBlock I/O 방식, 읽을 데이터가 일정량을 넘을 경우 유리
Index Range Scan : 랜덤 액세스 + Single Block I/O 방식, 큰 테이블에서 소량 데이터를 검색할 때 유리
8. 캐시 탐색 매커니즘
해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 프록을 액세스하는 방식이다.
버퍼캐시는 SGA 구성요소이므로 공유자원이고, 공유자원을 두 개 이상 프로세스가 접근할 떄 블록 정합성을 보장하기위해 래치(줄 세우기) 매커니즘이 존재한다.
References
- 친절한 SQL 튜닝
Leave a comment