SQL은 기본적으로 구조적(Structed)이고 집합적(Set-Based)이고 선언적(Declarative)인 질의 언어이다.
원하는 결과집합을 구조적, 집합적으로 선언하지만, 그 결과집합을 만드는 과정은 절차적일 수 밖에 없다.
즉, 프로시저가 필요한데, 그런 프로시저를 만들어 내는 DBMS 내부 엔진이 SQL 옵티마이저다.
SQL 최적화
SQL 파싱
사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서(Parser)가 파싱을 진행한다.
- 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
- Syntax 체크 : 문법적 오류가 없는지 확인한다. 사용할 수 없는 키워드를 사용했거나 순서가 바르지 않거나 누락된 키워드가 있는지 확인한다.
- Semantic 체크 : 의미상 오류가 없는지 확인한다. 존재하지 않는 테이블 또는 컬럼을 사용했는지, 사용한 오브젝트에 대한 권한이 있는지 확인한다.
SQL 최적화
SQL 최적화는 옵티마이저(Optimizer)가 맡는다.
SQL 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다.
로우 소스 생성
SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅 하는 단계이다. 로우 소스 생성기가 그 역할을 맡는다.
SQL 옵티마이저
SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해 주는 DBMS의 핵심 엔진이다.
옵티마이저의 최적화 단계 요약
- 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아낸다.
- 데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
- 최저 비용을 나타내는 실행계획을 선택한다.
실행계획과 비용
실행계획(Execution Plan)이 SQL 실행경로 미리보기 기능과 같다.
SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리 구조로 표현한 것이 실행계획이다.
미리보기 기능을 통해 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지를 확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행경로를 변경할 수 있다.
미리보기 기능을 통해 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지를 확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행경로를 변경할 수 있다.
옵티마이저가 인덱스를 선택하는 근거는 "Cost"이다.
Cost는 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간을 표현한 값이다.
SQL 실행계획에 표시되는 Cost도 어디까지나 예상치다. 실행경로를 선택하기 위해 옵티마이저가 여러 통계정보를 활용해서 계산해 낸 값이다. 실측치가 아니므로 실제 수행할 때 발생하는 I/O 또는 시간과 많은 차이가 난다.
옵티마이저 힌트
SQL 옵티마이저는 대부분 좋은 선택을 하는 것이지, 완벽하지는 않다. SQL이 복잡할수록 실수할 가능성도 크다.
그렇기 떄문에 옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀 수 있다.
힌트에는 인덱스 명을 입력하면 된다.
아래와 같이 주석 기호를 '+'를 붙이면 된다.
SELECT /*+ INDEX(A A_X01) INDEX(B B_X03)*/
A.CustomerNm, A.Phone, A.Address, B.OrderId
FROM Customer AS A
LEFT OUTER JOIN Order AS B
WHERE A.CustomerID = 'Y20251982'FROM 절 테이블명 옆에 ALIAS를 지정했다면, 힌트에도 반드시 ALIAS를 사용해야 된다.
SQL 공유 및 재사용
SQL의 내부 최적화 과정의 복잡성을 알고 나면, 동시성이 높은 온라인 트랜잭션 처리 시스템에서 바인드 변수가 왜 중요한지 자연스럽게 이해하게 될 것이다.
소프트 파싱 VS 하드파싱
SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간을 "라이브버리 캐시(Library Cache)"라고 한다. 라이브러리 캐시는 SGA 구성요소다. SGA(System Global Area)는 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다.
사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지부터 확인한다.
캐시에 존재하면 곧바로 실행 단계로 넘어가지만, 찾지 못하면 최적화 단계를 거치게 된다. SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 "소프트 파싱(Soft Parsing)"이라 하고, 찾는 데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 "하드 파싱(Hard Parsing)"이라고 한다.
옵티마이저가 SQL을 최적화할 때 많은 일을 수행한다.
예를 들어, 5개의 테이블을 JOIN하는 쿼리문 하나를 최적화하는 데도 무수히 많은 경우의 수가 존재한다. 조인 순서만 고려해도 120(=5!)가지다. 여기에 NL Join, Soft Merge Join, Hash Join 등 다양한 Join 방식이 있다. Full Scan할지, 인덱스를 이용할지를 결정해야 되고, 인덱스 스캔에도 여러 방식이 제공된다.
이렇게 SQL 옵티마이저는 순식간에 엄청나게 많은 연산을 한다. 그 과정에서 옵티마이저가 사용하는 정보는 다음과 같다.
- 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
- 오브젝트 통계 : 테이블 통계, 인덱스 통계, 히스토그램을 포함한 컬럼 통계
- 시스템 통계 - CPU 속도, Single Block I/O 속도, MultiBlock I/O 속도 등
- 옵티마이저 관련 파라미터
하나의 쿼리를 수행하는 데 있어 후보군이 될만한 무수히 많은 실행경로를 도출하고, 짧은 순간에 딕셔너리와 통계정보를 읽어 각각에 대한 효율성을 판단하는 과정은 결코 가벼울 수 없다.
데이터베이스에서 이루어지는 처리 과정은 대부분 I/O 작업에 집중되는 반면, 하드 파싱은 CPU를 많이 소비하는 몇 안 되는 작업 중 하나다.
이렇게 여러운(=hard) 작업을 거쳐 생성한 내부 프로시저를 한 번만 사용하고 버린다면 이만저만한 비효율이 아니다. 라이브러리 캐시가 필요한 이유가 바로 여기에 있다.
이름없는 SQL 문제
사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖고, 컴파일된 상태로 딕셔너리에 저장되며, 사용자가 삭제하지 않는 한 영구적으로 보관된다.
반면, SQL은 이름이 따로 없다. 전체 SQL 텍스트가 이름 역할을한다.
오라클, SQL Server 같은 DBMS는 이름없는 SQL을 영구 저장하지 않는다.
그 이유는, 일회성 또는 무효화된 SQL까지 모두 저장하려면 많은 공간이 필요하고, 그만큼 SQL을 찾는 속도도 느려진다.
데이터 저장 구조 및 I/O 매커니즘
I/O 튜닝이 곧 SQL 튜닝이라고 해도 과언이 아니다.
SQL이 느린 이유
SQL이 느린 이유는 십중팔구 디스크 I/O 때문이다.
"I/O = 잠(Sleep)"이라고 설명한다. OS 또는 I/O 서브시스템이 I/O를 처리하는동안 프로세스는 잠을 자기 때문이다. 프로세스가 일하지 않고 잠을 자는 이유는 여러 가지가 있지만, I/O가 가장 대표적이고 절대 비중을 차지한다.
대부분 대학교 3학년 떄 죽어라 하는 운영체제 PTSD가 올 것이다.
프로세스는 실행 중인 프로그램이며, 다음과 같은 생명주기를 가진다.
생성 이후 종료 전까지 준비와 실행과 대기 상태를 반복한다. 프로세스는 Interrupt에 의해 수시로 실행 준비 상태로 전환했다가 다시 실행 상태로 전환한다. 여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 떄문에 이런 매커니즘이 필요하다.
프로세스가 디스크에서 데이터를 읽어야 할 때는 CPU를 OS에 반환하고 잠시 수면 상태에서 I/O가 완료되기를 기다린다. 정해진 OS 함수를 호출하고 CPU를 반환한 채 알람을 설정하고 대기 큐에서 잠을 자는 것이다. 이러한 이유로 I/O가 많으면 성능이 느린 것이다.
Table Full Scan VS Index Range Scan
테이블에 저장된 데이터를 읽는 방식은 두 가지다. 테이블 전체를 스캔해서 읽는 방식과 인덱스를 이용해서 읽는 방식이다.
인덱스를 이용한 테이블 액세스는 인덱스에서 "일정량"을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식이다. ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치 정보다.
한 번에 많은 데이터를 처리하는 집계용 SQL과 배치 프로그램은 인덱스를 사용할 경우 SQL 성능을 떨어뜨린다.
그래서 이들 프로그램에서 사용하는 SQL은 온라인 트랜잭션 처리 시스템에서 사용하는 SQL보다 튜닝하기가 비교적 쉽다. 상당수가 Table Full Scan으로 유도하면 성능이 빨라진다. 조인을 포함한 SQL이면, 조인 메소드로 해서 조인을 선택해주면 된다.
인덱스를 이용하는데 왜 성능이 더 느릴까?
Table Full Scan은 시퀀셜 액세스와 MultiBlock I/O 방식으로 디스크 블록을 읽는다. 한 블록에 속한 모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 "한 번의 수면을 통해 인접한 수십 수백 개 블록을 한꺼번에 I/O하는 매커니즘"이다. 이 방식을 사용하는 SQL은 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다.
시퀀셜 액세스와 MultiBlock I/O가 아무리 좋아도 수십 수백 건의 소량 데이터를 찾기 위해 수백만 수천만 건 데이터를 스캔하는 건 비효율적이다. 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 이용해야 된다.
Index Range Scan을 통한 테이블 액세스는 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는다. 캐시에서 블록을 못 찾으면 레코드 하나를 읽기 위해 매번 잠을 자는 I/O 매커니즘이다. 따라서 많은 데이터를 읽을 때는 Table Full Scan보다 불리하다.
인덱스는 큰 테이블에서 아주 작은 일부 데이터를 빨리 찾기 위한 도구일 뿐이므로 모든 성능 문제를 인덱스로 해결하려 해서는 안된다. 읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan이 유리하다.
