1.1 SQL 파싱과 최적화

SQL은 기본적으로 구조적(structured)이고 집합적(set-based)이고 선언적(declarative)인 질의 언어다.

원하는 결과집합을 구조적, 집합적으로 선언하지만, 그 결과집합을 만드는 과정은 절차적일 수 밖에 없다.

즉, 프로시저가 필요한데, 그런 프로시저를 만들어 내는 DBMS 네부 엔진이 바로 SQL 옵티마이저이다.

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상채로 만드는 과정을 'SQL 최적화'라고 한다.

SQL 최적화

① SQL 파싱

사용자로 부터 SQL을 전달 받으면 가장 먼서 SQL 파서가 파싱을 진행한다.

• 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱트리 생성
• Syntax 체크 : 문법적 오류가 없는지 확인 ( 사용할 수 없는 키워드 , 순서, 누락된 키워드 확인 )
• Semantic 체크 : 의미상 오류가 없는지 학인 ( 존재하지 않는 테이블 혹은 컬럼 사용 유무, 사용한 오브젝트에 대한 권한 확인 )

② SQL 최적화

• 옵티마이저가 담당한다.
• SQL옵티마이저는 미리 수집한 시스템 및 오브젝트 통계내용을 바탕으로 다양한 실행 경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다.
• DB성능을 결정하는 가장 핵심적인 엔진이다

③ 로우 소스 생성

• SQL옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 혹은 프로시저 형태로 포맷팅 하는 단계
• 로우 소스 생성기(Row-Source Generator)가 담당

SQL 옵티마이저

| 단계

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

실행계획과 비용

• 실행계획 : SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리구조로 표현한 것
• 해당 기능을 통해서 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캑한다면 어떤 인덱스인지 확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행 계획을 변경할 수 있다.
• 특정 실행계획을 선택하는 근거 → 비용
• 비용 : 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O횟수 또는 예상 소요 시간
• SQL 실행계힉에 표시되는 Cost도 어디까지나 예상치이며, 실행 경로를 선택하기 위해 여러 통계정보를 활용해서 낸 값이다. 실측치가 아니므로 실제 수행할 때 발생하는 I/O또는 시간과 차이가 많이 난다.

옵티마이저 힌트

• 옵티마이저 힌트를 이용해서 데이터 엑세스 경로를 변경 할 수 있다.

SELECT /*+ INDEX(a EMP_IDX02) */  +가 없으면 일반 주석으로 간주하고 아무런 이벤트가 없다
       a.empno 
     , a.ename 
     , a.hiredate 
FROM emp a

주의 사항
1. 힌트 안에 인자를 나열할 땐 ,(콤마)를 사용할 수 있지만, 힌트와 힌트 사이에는 사용하면 안 된다.
2. 테이블을 지정할 때 스키마 명까지 명시하면 안 된다.
3. 테이블 ALIAS를 지정했다면, 힌트에도 반드시 ALIAS를 사용해야 한다.

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

-- 2.
SELECT /*+ FULL(SCOTT.EMP) */        -> 무효
FROM EMP

-- 3.
SELECT /*+ FULL(EMP) */              -> 무효
FROM EMP E

힌트 목록

분류	힌트	설명
최적화 목표	ALL_ROWS	전체 처리속도 최적화
	FIRST_ROWS(N)	최초 N건 응답속도 최적화
엑세스 방식	FULL	Table Full Scan 으로 유도
	INDEX	Index Scan 으로 유도
	INDEX_DESC	Index를 역순으로 스캔하도록 유도
	INDEX_FFS	Index Fast Full Scan 유도
	INDEX_SS	Index Skip Scan 유도
조인 순서	ORDERED	From절에 나열된 순서되로 조인
	LEADING	LEADING 힌트 괄호에 기술한 순서대로 조인 ex)LEADING(T1 T2)
	SWAP_JOIN_INPUTS	해시 조인 시, BUILD INPUT을 명시적으로 선택 ex) SWAP_JOIN_INPUTS(T1)
조인 방식	USE_NL	NL 조인으로 유도
	USE_MERGE	소트 머지 조인으로 유도
	USE_HASH	해시 조인으로 유도
	NL_SJ	NL 세미 조인으로 유도
	MERGE_SJ	소트 머지 세미조인으로 유도
	HASH_SJ	해시 세미조인으로 유도
서브쿼리 팩토링	MATERIALIZE	WITH 문으로 정의한 집합을 물리적으로 생성하도록 유도 ex) WITH /*+ MATERIALIZE*/ T AS ( SELECT ... )
	INLINE	WITH 문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE 처리하도록 유도 ex) WITH /*+ INLINE*/ T AS ( SELECT ... )
쿼리 변환	MERGE	뷰 머징 유도
	NO_MERGE	뷰 머지 방지
	UNNEST	서브쿼리 Unnesting 유도
	NO_UNNEST	서브쿼리 Unnesting 방지
	PUSH_PRED	조인조건 Pushdown 유도
	NO_PUSH_PRED	조인조건 Pushdown 방지
	USE_CONCAT	OR 또는 In-List 조건을 OR-Expansion으로 유도
	NO_EXPAND	OR 또는 In-List 조건에 대한 OR-Expansion 방지
병렬 처리	PARELLEL	테이블 스캔 또는 DML을 병렬방식으로 처리하도록 유도 ex) PARELLEL(T1 2) PARELLEL(T2 2)
	PARALLEL_INDEX	인덱스 스캔을 병력방식으로 처리하도록 유도
	PQ_DISTRIBUTE	병렬 수행 시 데이터 분배 방식 결정 ex) PQ_DISTRIBUTE(T1 HASH HASH)
기타	APPEND	Direct-Parh Insert 로 유도
	DRIVING_SITE	DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지정( Local 또는 Remote)
	PUSH_SUBQ	서브쿼리를 가급적 빨리 필터링 하도록 유도
	NO_PUSH_SUBQ	서브쿼리를 가급적 늦게 필터링하도록 유도