[SQLP] 1-2. SQL 파싱과 최적화

🧷 SQL 파싱과 최적화

🖇️ SQL 이란?

SQL : Structured Query Language

 

SQL은 데이터베이스에서 데이터를 질의, 조작, 정의, 제어하기 위한 구조적(Structured)이고 집합적(Set-Based)이며 선언적(Declarative)인 언어이다. 오라클 PL/SQL, SQL Server T-SQL처럼 절차적(Procedural) 프로그래밍 기능을 제공하는 확장 언어도 있지만, SQL은 집합 기반의 결과를 얻기 위한 선언적 언어이다.

 

사용자는 SQL을 통해 원하는 데이터 결과를 선언하지만, 그 결과 집합을 만드는 과정은 절차적일 수밖에 없다. 이러한 과정을 처리하는 DBMS 내부 엔진이 바로 SQL 옵티마이저(Optimizer)이며, 사용자가 작성한 SQL을 효율적으로 처리할 실행 계획을 수립한다. 옵티마이저가 프로그래밍을 대신하는 셈이다.

 

사용자 -[SQL]→ 옵티마이저 -[실행계획]→ 프로시저

 

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행할 수 있는 상태로 만드는 전 과정을 SQL 최적화라고 한다.

 

🖇️ SQL 처리 과정

 

SQL 처리 과정은 위의 사진처럼 3단계로 나누어진다. 사용자가 SQL 문을 실행하면 SQL 파싱을 거친 후, 해당 SQL이 이미 메모리에 캐싱 되어 있는지 확인한다. 캐싱 되어 있으면 소프트 파싱(Soft Parsing)으로 곧바로 실행단계로 넘어가며, 그렇지 않은 경우 하드 파싱(Hard Parsing)으로 최적화 및 로수 소스 생성 단계를 진행한다.

 

1.  SQL 파싱

(1) 파싱 트리 생성

사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서(Parser)가 SQL 문을 분석하여 파싱 트리 생성한다. 파싱 트리는 SQL문 을 구성하는 개별 요소들을 트리 형태로 구조화한 것이다.

 

(2) 오류 체크

파싱 트리가 만들어지는 과정에서 Syntax 검사를 진행하고, 그 후에는 Semantic 검사를 진행한다.

• Syntax 체크 : 문법적 오류가 없는지 확인
• Semantic 체크 : 의미상 오류가 없는지 확인 ex, 권한 부족, 공간 부족

(3) 캐싱 여부 확인

오류가 없다면, 해싱 알고리즘을 이용해 Shared Pool에 해당 SQL 커서가 이미 캐싱 되어있는지 확인한다.

• SQL 문과 100% 일치
• 파싱을 요청한 사용자와 일치
• 옵티마이저 관련 파라미터 설정 일치

위의 경우를 만족한다면 실행계획을 재사용하고, 조건을 만족하지 않거나 캐싱 된 커서가 없는 경우 최적화 과정을 수행한다.

2. SQL 최적화

SQL 최적화는 SQL 옵티마이저가 처리하며, 효율적인 실행계획을 수립하는 단계이다. SQL 옵티마이저는 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교하고, 가장 효율적인 실행경로를 선택하는 데이터베이스 성능을 결정하는 가장 핵심적인 엔진이다. 옵티마이저는 최적화 과정에서 세부적으로 3개의 서브 엔진을 수행한다.

• Query Transformer : 최적화하기 쉬운 형태로 쿼리 변환. 변환 후에도 결과가 동일함을 보장될 경우에만 진행한다.
• Plan Generator : 실행계획을 생성
• Estimator : 데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 오브젝트(테이블, 인덱스 등) 통계와 시스템 통계정보를 이용해 총비용을 계산 (예상치)

실행계획(Execution Plan)
옵티마이저가 생성한 처리 절차를 사용자가 확인할 수 있게 트리구조로 표현한 것이다. SQL이 테이블 스캔, 인덱스 스캔 방식 등을 확인할 수 있으며, 예상과 다른 방식으로 처리된다면 실행경로를 변경할 수 있다.

비용(Cost)
옵티마이저가 실행계획을 선택하는 근거로, 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요 시간을 표현한 값이다. 여러 통계정보를 활용해서 계산한 예상값이기 때문에 실제값과는 차이가 발생할 수 있다.

3. 로우 소스 생성

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

 

🖇️ 옵티마이저 힌트

대부분의 경우 SQL 옵티마이저는 최적의 선택을 하지만, 항상 최선의 선택을 하는 것이 아니다. SQL이 복잡할수록 실수할 가능성도 크기 때문이다. 이때, 힌트를 이용해 액세스 경로를 바꿀 수 있다. 힌트 사용법은 /*+ hint */ 와 --+ hint가 있다. 후자의 경우, 동적 쿼리 사용 시 줄 바꿈 오류가 발생할 수 있기 때문에 가급적 사용을 자제하는 게 좋다.

• 통계정보나 실행 환경 변화에도 문제가 없는 경우 : 힌트를 최소화하여 옵티마이저의 자율성을 보장
• 작은 실수에도 큰 손실을 초래하는 시스템 : 힌트를 세밀하게 지정하여 옵티마이저의 결정에 제한

주의사항

• 힌트와 힌트 사이에 , 을 사용하면 안 된다.
• 테이블 지정 시 스키마 명을 명시하지 않는다.
• 테이블에 ALLAS를 지정했다면, 힌트에도 반드시 동일한 ALLAS를 사용해야 한다.

자주 사용하는 Hint

분류	힌트	설명
최적화 목표	ALL_ROWS	전체 처리 속도 최적화
	FIRST_ROWS(N)	최초 N 건 응답속도 최적화
액세스 방식	FULL	Tabl Full Scan으로 유도
	INDEX	Index Scan으로 유도
	INDEX_DESC	Index를 역순으로 스캔하도록 유도
	INDEX_FFS	Index Fast Full Scan으로 유도
	INDEX_SS	Index Skip Scan으로 유도
조인순서	ORDERED	FROM 절에 나열된 순서대로 조인
	LEADING	LEADING 힌트 괄호에 순서대로 조인
	SWAP_JOIN_INPUTS	해시 조인 시, BUILD INPUT을 명시적으로 선택
조인방식	USE_NL	NL 조인으로 유도
	USE_MERGE	소트 머지 조인으로 유도
	USE_HASH	해시 조인으로 유도
	NL_SJ	NL 세미조인으로 유도
	MERGE_SJ	소트 머지 세미조인으로 유도
	HASH_SJ	해시 세미조인으로 유도
서브쿼리 팩토링	MATERIALIZE	WITH 문으로 정의한 집합을 물리적으로 생성하도록 유도
	INLINE	WITH 문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE 처리하도록 유도
쿼리 변환	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 방지
병렬 처리	PARALLEL	테이블 스캔 또는 DML을 병렬방식으로 처리하도록 유도
	PARALLEL_INDEX	인덱스 스캔을 병렬방식으로 처리하도록 유도
	PQ_DISTRIBUTE	병렬 수행 시 데이터 분배 방식 결정
기타	APPEND	Direct-Path Insert로 유도
	DRIVING_SITE	DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지정(Local 또는 Remote)
	PUSH_SUBQ	서브쿼리를 가급적 빨리 필터링하도록 유도
	NO_PUSH_SUBQ	서브쿼리를 가급적 늦게 필터링하도록 유도

 

---

Reference

• 친절한SQL 튜닝
• 오라클 성능고도화 원리와 해법 1