SQL 처리 과정과 I/O
SQL 처리 과정
SQL은 구조적, 집합적, 선언적 질의 언어로 사용자가 작성한 SQL은 옵티마이저를 통해 최적의 실행 계획으로 프로시저로 작성된다.
SQL 최적화
최적화 = 파싱 + 최적화 + 로우 소스 생성
- 파싱 : 사용자로부터 전달 받은 SQL을 SQL 파서가 파싱을 진행한다.
- 파싱 트리 생성 : SQL 문의 개별 구성 요소 분석 및 파싱 트리 생성
- Syntax 체크 : 문법상의 오류 확인
- Semantic 체크 : 의미상의 오류 확인 (존재하지 않는 테이블이나 컬럼을 사용하는지 혹은 권한이 부족한지)
- 최적화 : 옵티마이저가 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 실행 경로를 생성해서 비교한 후 가장 효율적인 하나를 선택
- 쿼리를 실행하기 위한 실행 계획 후보군 선정
- 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계 정보를 이용해 각 실행 계획의 예상 비용 산정
- 최적 비용을 나타내는 실행 계획 선택
- 로우 소스 생성 : 로우 소스 생성기가 실행 경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅 하는 단계
실행 계획과 비용
예상 실행 계획을 통해 옵티마이저가 선택한 실행 계획이 어떻게 작성되었는지 확인할 수 있고, 해당 경로와 비용을 근거로 적합한 테이블과 인덱스를 결정할 수 있다.
실행 비용은 쿼리 수행 시간 동안 발생할 것으로 예상 되는 I/O 횟수 및 예상 소요 시간으로 어디까지나 예상치이기 때문에 실측과는 차이가 존재한다.
옵티마이저 힌트
SQL 옵티마이저가 항상 최선의 선택을 하는 것은 아니고, 복잡한 SQL일수록 실수가 발생할 가능성이 높기 때문에 개발자가 직접 옵티마이저 힌트를 사용해 통계 정보에 담을 수 없는 데이터나 업무 특성 등을 활용해 더 효율적인 액세스 경로를 선택할 수 있다.
소프트 파싱과 하드 파싱
라이브러리 캐시 : 옵티마이저가 최적화를 통해 생성한 내부 프로시저를 캐싱해두는 공간 SGA(System Global Area) : 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간
- SQL 파싱
- 파싱된 SQL이 라이브러리 캐시에 존재하는지 확인
- 존재하는 경우(cache-hit) 해당 로우 소스 재사용, 존재하지 않는 경우(cache-miss) 로우 소스 생성 과정 수행
cache-hit의 경우를 소프트 파싱, cache-miss의 경우를 하드 파싱이라고 한다.
하드 파싱의 경우 옵티마이저가 쿼리를 최적화 하기 위해 아래와 같은 정보들을 사용해 다량의 CPU 연산을 처리해야 한다.
- 테이블, 컬럼, 인덱스 구조에 관한 정보
- 오브젝트, 시스템 통계
- 옵티마이저 관련 파라미터
I/O 작업에 비하면 큰 작업은 아니지만, 적은 연산은 아니기에 캐싱 전략을 사용해 효율적으로 처리한다.
이름 없는 SQL
사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖고 컴파일된 상태로 딕셔너링 저장되어 사용자가 삭제하기 전까지 영구적으로 보관된다. 또한 라이브러리 캐시에 적재되어 여러 사용자가 공유해서 재사용할 수 있다.
하지만 SQL은 별도로 이름이 없고, 전체 SQL 텍스트가 이름 역할을 한다. 따라서 별도로 딕셔너리에 저장되지도 않는다. 하지만 똑같이 처음 실행 시에는 최적화 과정을 거쳐 내부 프로시저를 생성하고, 이후에는 라이브러리 캐시에 적재된걸 공유해서 재사용한다. 캐시 공간이 부족한 경우에는 사라졌다가 다시 최적화 과정을 거쳐 캐싱된다.
SQL ID가 존재하지만 이거도 마찬가지로 SQL과 1:1 대응이라 의미가 없다.
모든 SQL을 캐싱하려면 많은 공간이 필요하고, SQL을 찾는 속도도 느려지기 때문에 오라클과 SQL Server는 SQL을 영구 저장하지 않는다.
공유 가능 SQL
1
Map<SQL, Procedure> LibraryCache;
라이브러리 캐시에서는 SQL문 자체를 키로 프로시저를 조회한다. 같은 SQL문이라도 대소문자나 문법 등이 다르면 키가 다르기 때문에 다른 SQL이다.
예를 들어 동시에 다양한 조건으로 쿼리가 날라오면 같은 쿼리라도 조건이 다르기 때문에 조건 별로 하드 파싱이 진행된다. 하지만 바인드 변수를 사용하면 하드 파싱을 한 번만 진행하여 캐싱된 SQL을 공유해서 재사용할 수 있다.
SQL이 느린 이유
대부분의 원인은 I/O 때문이다. I/O를 처리하는 동안 프로세스는 잠에 든다.
생성 -> (준비 <-> 대기 <-> 실행) -> 종료
실행 중이던 프로세스가 디스크에서 데이터를 읽으려면 OS에 CPU를 반납하고 수면 상태(Wait Queue)에서 I/O가 처리되길 기다려야한다. 즉 I/O가 많으면 쉬고 있는 프로세스들이 많다는 것
I/O Call로 인해 디스크 경합이 심해지고 대기 시간이 늘어남 + I/O 처리 시간
테이블 스페이스(세그먼트(익스텐트(블록)))
테이블 스페이스 = 세그먼트들을 담는 컨테이너
여러 개의 데이터 파일(물리적)로 구성
세그먼트 = 테이블, 인덱스, 파티션, LOB 처럼 데이터 저장 공간이 필요한 오브젝트
익스텐트 = 공간을 확장하는 단위로 연속된 블록들의 집합이다.
테이블이나 인덱스에 데이터를 넣다 공간이 부족해지면 테이블스페이스로부터 익스텐트를 추가로 할당 받는다
블록(페이지) = 실제 레코드가 저장되는 공간
블록과 익스텐트 모두 하나의 테이블이 독점한다. (SQL Server는 한 익스텐트를 여러 오브젝트가 사용 가능)
세그먼트에 할당된 익스텐트들은 항상 같은 데이터 파일에 위치하지 않는다.
모든 익스텐트가 같은 데이터 파일에 할당되면 해당 데이터 파일에 I/O 부하가 집중된다.
그래서 DBMS는 파일 경합을 줄이기 위해 가능한 한 여러 개의 데이터 파일로 분산해서 저장한다.
즉, 익스텐트 내 블록은 연속된 공간이지만, 세그먼트 내 익스텐트는 연속된 공간이 아니다.
Data Block Address : 데이터 블록이 디스크 상에서 몇 번 데이터 파일의 몇 번째 블록인지를 나타내는 고유 주소
인덱스 ROWID = DBA + 로우 번호
블록 단위 I/O
블록 = DB의 데이터 I/O 단위
단위가 블록이기에 하나의 로우, 하나의 컬럼만 읽고 싶어도 블록을 통째로 읽는다.
오라클은 8KB 블록을 사용한다. (2, 4, 16, 32KB도 사용 가능하다) 1byte만 읽고 싶어도 8KB를 읽어야 하는 것
블록 액세스 방식
- 시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 순차적으로 블록을 읽는 방식
- 인덱스 리프 블록 : 앞뒤를 가리키는 주소값을 통해 앞 또는 뒤로 순차적으로 스캔
- Full Table Scan : 세그먼트 헤더의 익스텐트 맵에서 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순차적으로 스캔
- 랜덤 액세스 : 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식
논리적 I/O와 물리적 I/O
DB Buffer Cache : 같은 블록에 대한 반복적인 I/O Call을 줄이기 위해 데이터 블록을 캐싱하는 공간, 공유 메모리 영역
위치 : 서버 프로세스 - 버퍼 캐시 - 데이터 파일
논리적 I/O : SQL 처리 과정에서 발생한 총 블록 I/O, 버퍼 캐시에 있는 경우, 전기적 신호, 메모리 I/O
물리적 I/O : 디스크에서 발생한 총 블록 I/O, 버퍼 캐시에 없는 경우, 물리적 신호, 액세스 Arm
평균적으로 물리적 I/O가 10000배 느리다.
Buffer Cache Hit Ratio(BCHR) : 읽은 블록 중에서 물리적 I/O 없이 메모리에서 찾은 비율
SQL 성능을 향상 시키기 위해서는 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다.
물리적 I/O는 BCHR에 의해 결정되고, BCHR은 상황에 따라 달라지기 때문에 통제 불가능한 값이다.
데이터의 변경이 없는한 항상 일정한 통제할 수 있는 논리적 I/O를 줄여야 한다.
논리적 I/O를 줄이는 방법 = SQL 튜닝을 통해 읽는 총 블록 개수를 줄이는 것
하지만 BCHR이 높다고 무조건 좋은 것은 아니다. 같은 블록을 비효율적으로 반복해서 읽어도 올라가기 때문이다.
Single Block I/O와 Multiblock I/O
Single Block I/O : I/O Call 시 한 번에 하나의 블록만 요청해서 메모리에 적재하는 방식
Multiblock I/O : 여러 블록을 요청해서 메모리에 적재하는 방식
인덱스 이용 시에는 인덱스와 테이블 블록 모두 Single Block I/O 방식 사용 (인덱스는 주로 소량의 데이터를 읽을 때 사용하기에)
인덱스 이용 없이 테이블 전체를 스캔하는 것처럼 많은 데이터를 읽을 때는 Multiblock I/O 방식이 효율적
테이블이 클 수록 Multiblock I/O 단위가 커야 프로세스가 잠 자는 횟수를 줄일 수 있다.
Multiblock I/O 방식은 찾고자 하는 블록을 포함한 인접한 블록까지인데, 인접한 블록은 같은 익스텐트에 속한 블록을 말한다.
Table Full Scan과 Index Range Scan
Table Full Scan : 테이블에 속한 모든 블록을 읽어서 데이터를 찾는 방식
시퀀셜 액세스와 Multiblock I/O 방식을 사용 = 잠자는 횟수가 적고, 한 번에 많은 데이터를 가져오기 때문에 대량의 데이터 처리가 좋음, 블록을 한 번만 스캔함함
Index Range Scan : 인덱스에서 일정량을 스캔해서 얻은 ROWID로 테이블 레코드를 찾아가는 방식
랜덤 액세스와 Single Block I/O 방식을 사용 = 레코드 하나를 읽기 위해 잠을 자기 때문에 많은 데이터를 읽기에는 좋지 않음, 같은 블록을 여러번 읽음
인덱스는 큰 테이블에서 아주 적은 데이터를 빨리 찾을 때 좋지, 읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan이 유리하다.
인덱스를 맹신하지말자! 예상 카디널리티가 일정량을 넘어서는 경우는 인덱스가 별로다
캐시 탐색 메커니즘
버퍼 캐시는 해시 구조로 관리되고 있고, 특정 블록 번호들은 특정 해시 체인에만 존재하기 때문에
해당 해시 체인에 블록이 없다면 불필요하게 다른 해시 체인을 탐색할 필요가 없다.
액세스 직렬화
버퍼 캐시는 공유 자원이기 때문에 캐싱된 버퍼 블록들에 동시에 여러 프로세스가 접근할 수가 있고
이런 경우에 블록 정합성 문제가 생길 수 있다.
이를 방지하기 위해 한 프로세스씩 순차적으로 접근하게 하는 메커니즘이 직렬화다.캐시버퍼 체인 래치(Latch)
해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경하는 일을 방지하기 위해
각 체인을 잠구는 자물쇠 같은 메커니즘으로 키를 획득한 프로세스만 체인으로 진입할 수 있다.
SGA의 서브 캐시마다 별도의 래치가 존재하고, 버퍼 캐시에는 체인 래치와 LRU 래치 등이 존재한다.
버퍼 캐시 히트율을 높이는게 좋지만, 래치에 의한 경합으로 캐시 I/O도 생각만큼 빠르지 않을 수 있다.
직렬화로 인한 경합을 줄이려면 결국 SQL 튜닝을 통해 논리적 I/O 자체를 줄이는게 좋다.
- 버퍼 Lock
체인 래치를 해제하기 전에 버퍼 헤더에 락을 걸어 후행 프로세스가 현재 I/O 작업 중인 블록에 접근해서
I/O 작업을 하는 경우를 방지한다.