SQL 파싱과 최적화

친절한 SQL 튜닝 Study - 조시형 저(DBian)

SQL을 실행 전, 아래와 같은 최적화 과정을 수행한다.

1) SQL 파싱

SQL Parser가 파싱을 진행하는 단계로, SQL문을 이루는 개별 구성요소를 분석해서 파싱 트리를 생성하고, 문법적 오류가 없는지 확인한다. 그 다음 의미상 오류가 없는지 확인한다.

2) SQL 최적화

옵티마이저가 활약하는 단계다. 미리 수집한 통계정보를 바탕으로 다양한 실행경로를 생성 및 비교한 후 가장 효율적인 경로를 선택한다.

3) 로우 소스 생성

옵티마이저가 선택한 실행경로를 실행 가능한 코드 형태로 포맷팅 하는 단계로, 로우 소스 생성기가 그 역할을 맡는다.

옵티마이저의 최적화 단계

1) 사용자로부터 전달 받은 쿼리를 실제로 실행할 실행계획들을 찾아낸다.

2) Data Dictionary 에 미리 수집해 둔 오브젝트 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.

3) 최저 비용을 나타내는 실행 계획을 선택한다.

옵티마이저 힌트

■ 힌트의 사용은 주석 기호에 '+'를 붙이면 된다.

■ 테이블을 지정할 때, 스키마명까지 명시하면 안된다.

■ 테이블 Alias 를 지정했다면, 힌트에서도 반드시 Alias를 사용해야 한다.

SELECT /*+ INDEX(A 고객_PK) */ 
고객명, 연락처, 주소
FROM 고객 A
WHERE 고객ID = '1234'

자주 사용하는 힌트 목록

1) INDEX Access Operation 관련 HINT

HINT	내용	사용법
INDEX	Index Scan으로 유도	INDEX(TABLE_name, INDEX_name)
INDEX_ASC	INDEX를 내림차순으로 스캔.
INDEX_DESC	INDEX를 오름차순으로 스캔.	INDEX_DESC(TABLE_name, INDEX_name)
INDEX_FFS	INDEX FAST FULL SCAN	INDEX_FFS(TABLE_name, INDEX_name)
PARALLEL_INDEX	INDEX PARALLEL SCAN	PARALLEL_INDEX(TABLE_name, INDEX_name)
NOPARALLEL_INDEX	INDEX PARALLEL SCAN 제한	NOPARALLEL_INDEX(TABLE_name, INDEX_name)
AND_EQUALS	여러개의 INDEX MARGE 수행	AND_EQUALS(INDEX_name, INDEX_name)
FULL	Table full scan으로 유도	FULL(TABLE_name)

2) JOIN Access Operator 관련 HINT

HINT	내용	사용법
USE_NL	NESTED LOOP JOIN 옵티마이저가 NESTED LOOP JOIN을 사용하도록 한다.	USE_NL(TABLE1, TABLE2)
USE_NL_WITH_INDEX	INDEX를 사용해서 NESTED LOOP JOIN을 사용하도록 한다.	USE_NL_WITH_INDEX(TABLE INDEX)
USE_MERGE	SORT MERGE JOIN 옵티마이저가 SORT MERGE JOIN을 사용하도록 한다. 먼저 각각의 TABLE의 처리 범위를 스캔하여 SORT한 후, 서로 MERGE하면서 JOIN하는 방식.	USE_MERGE(TABLE1, TABLE2)
USE_HASH	HASH JOIN 옵티마이저가 HASH JOIN을 사용하도록 한다.	USE_HASH(TABLE1, TABLE2)
HASH_AJ	HASH ANTIJOIN	HASH_AJ(TABLE1, TABLE2)
HASH_SJ	HASH SEMIJOIN	HASH_SJ(TABLE1, TABLE2)
NL_AJ	NESTED LOOP ANTIJOIN	NL_AJ(TABLE1, TABLE2)
NL_SJ	NESTED LOOP SEMIJOIN	NL_SJ(TABLE1, TABLE2)
MERGE_AJ	SORT MERGE ANTIJOIN	MERGE_AJ(TABLE1, TABLE2)
MERGE_SJ	SORT MERGE SEMIJOIN	MERGE_SJ(TABLE1, TABLE2)

3) JOIN 순서

4) 기타 HINT

HINT	내용	사용법
APPEND	INSERT시 DIRECT LOADING
PARALLEL	SELECT, INSERT시 여러개의 프로세스로 수행	PARALLEL(TABLE, 개수)
CACHE	데이터를 메모리에 CACHING
NOCACHE	데이터를 메모리에 CACHING하지 않음
PUSH_SUBQ	SUBQUERY를 먼저 수행
REWRITE	QUERY REWRITE 수행
NOREWIRTE	QUERY REWRITE를 수행 못함
USE_CONCAT	IN절을 CONCATENATION ACCESS OPERATION으로 수행
USE_EXPAND	IN절을 CONCATENATION ACCESS OPERATION으로 수행못하게 함
MERGE	VIEW MERGING 수행
NO_MERGE	VIEW MERGING 수행못하게 함

Augustine