SQL 파싱과 최적화

SQL을 실행하기 전 최적화 과정을 세분화해보자

 

1. SQL 파싱

사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서(Parser)가 파싱을 진행한다. SQL 파싱을 요약하면 다음과 같다.

• 파싱 트리 생성 : SQL문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
• Syntax 체크 : 문법적 오류가 없는지 확인. 예를 들어, 사용할 수 없는 키워드를 사용했거나 순서가 바르지 않거나 누락된 키워드가 있는지 확인한다.
• Semantic 체크 : 의미상 오류가 없는지 확인. 예를 들어, 존재하지 않는 테이블 또는 컬럼을 사용했는지, 사용한 오브젝트에 대한 권한이 있는지 확인한다.

 

2. SQL 최적화

그다음 단계가 SQL 최적화이고, 옵티마이저(Optimizer)가 그 역할을 맡는다. SQL 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다. 데이터베이스 성능을 결정하는 가장 핵심적인 엔진이다.

 

 

3. 로우 소스 생성

SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅 하는 단계다. 로우 소스 생성시(Row-Source Generator)가 그 역할을 맡는다.

 

 

 

SQL 옵티마이저

SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해 주는 DBMS의 핵심 엔진이다. 옵티마이저의 최적화 단계를 요약하면 다음과 같다.

1. 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아낸다.
2. 데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
3. 최저 비용을 나타내는 실행계획을 선택한다.

 

 

 

 

실행계획과 비용

SQL 옵티마이저는 자동차 네비게이션과 여러모로 흡사하다. 경로 요약이나 모의 주행 같은 기능이 그렇다. 

DBMS에도 SQL 실행경로 미리보기 기능이 있다. 실행계획(Execution Plan)이 바로 그것이다. SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 아래와 같이 트리 구조로 표현한 것이 실행계획이다.

실행계획

 

미리보기 기능을 통해 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지를  확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행경로를 변경할 수 있다.

 

옵티마이저가 특정 실행계획을 선택하는 근거는 무엇일까?

 

비용을 통해 선택을 하게 된다. 비용(Cost)은 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간을 표현한 값이다.

 

하지만 SQL 실행계획에 표시되는 Cost는 어디까지나 예상치다. 실행경로를 선택하기 위해 옵티마이저가 여러 통계정보를 활용해서 계산해 낸 값이다. 실측치가 아니므로 실제 수행할 때 발생하는 I/O 또는 시간과 많은 차이가 난다.

 

 

 

옵티마이저 힌트

힌트 사용법은 아래와 같다. 주석 기호에 '+'를 붙이면 된다.

SELECT /*+ INDEX(A 고객_PK) */
  고객명, 연락처, 주소, 가입일시
 FROM 고객 A
 WHERE 고객ID = :cust_id;

 

주의사항

/*+ INDEX(A A_X01) INDEX(B, B_X03) */ 	-> 모두 유효
/*+ INDEX(C), FULL(D) */ 				-> 첫 번째 힌트만 유효

SELECT /*+ FULL(SCOTT.EMP) */ 			-> 스키마명까지 명시하면 무효가 된다.
FROM EMP;


SELECT /*+ FULL(EMP) */					-> FROM 절 테이블명 옆에 ALIAS를 지정했다면, 힌트에도 반드시 ALIAS를 사용해야 한다.
FROM EMP E

 

자주 사용하는 힌트 목록