안녕하세요. 오늘은 SQL 파싱과 최적화 그리고SQL 옵티마이저를 사용할 때 주의할 점에 대해 알아보겠습니다.
SQL 최적화
1. SQL 파싱
사용자가 SQL을 작성하면 SQL 파서(SQL Parser)가 가장 먼저 파싱을 진행한다. SQL 파싱 요약을 아래와 같다.
- 파싱 트리 생성: SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
- Syntax 체크: 문법적 오류가 없는지 확인. 예를 들어, 사용할 수 없는 키워드를 사용했거나 순서가 바르지 않거나 누락된 키워드가 있는지 확인.
- Semantic 체크: 의미상 오류가 없는지 확인. 예를 들어, 존재하지 않는 테이블 또는 컬럼을 사용했는지, 사용한 오브젝트에 대한 권한이 있는지 확인.
2. SQL 최적화
SQL 파싱 다음 단계가 SQL 최적화이며, SQL 옵티마이저가 그 역할을 맡는다. SQL 옵티마니저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행 경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다. 데이터베이스 성능을 결정하는 가장 핵심적인 엔진이다
SQL 실행 시 위에서 언급한 ‘SQL 파싱’과 ‘SQL 최적화’에 대한 과정을 거치지만, 세부적으로 SQL 처리 과정을 설명할 목적이 아니라면 굳이 이 둘을 구별할 필요는 없다.
3. 로우 소스 생성
SQL 옵티마이저가 선택한 실행 경로를 실행 가능한 코드 또는 프로시저 형태로 변환 단계로써, 그 역할은 로우 소스 생성기(Row-Source Generator)가 맡는다.
SQL 옵티마이저란?
우리가 사용하는 각각의 DBMS의 핵심 엔진이다. SQL 옵티마이저는 사용자가 작업 수행 시 가장 효율적으로 수행할 수 있도록 최적의 데이터 액세스 경로를 선택하는 것에 도움을 준다. 아래는 SQL 옵티마이저의 최적화 단계를 요약한 것이다.
- 사용자에게 전달받은 쿼리로부터 수행 시 실행계획의 후보군들을 찾아낸다.
- 데이터 딕셔너리(Data Dictionary)에 미리 저장해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
- 가장 적은 비용, 즉 가장 효율적으로 실행된 수 있는 실행계획을 선택한다.
실행 계획과 비용
SQL 옵티마이저는 자동차 내비게이션과 여러모로 흡사하다. 예를들어, 경로 요약 혹은 모의 주행 같은 기능과 비슷하다. 경로를 미리 검색하고 이동 경로를 확인하는 기능이며, 내비게이션이 선택한 경로가 마음에 들지 않으면 검색모드를 변경하거나 혹은 경유지를 추가해서 사용자가 원하는 방향으로 변경할 수 있다. 각각의 DBMS에는 ‘실행계획(Execution Plan)’이 존재하는데, 이 것이 위에서 언급한 자동차 내비게이션, 경로 요약 혹은 모의 주행 같은 역할을 이행한다. SQL 옵티마이저가 생성한 처리절차를 확인할 수 있게 트리 구조로 표현한 것이 실행 계획이다. 또한, SQL 실행계획에 표시되는 Cost도 어디까지나 예상치이며, 실측치가 아닐므로 실제 수행할 때 발생하는 I/O 또는 시간과 많은 차이가 발생한다.
옵티마이저 힌트(Optimizer Hint)
SQL 옵티마이저는 대부분 최적의 비용을 계산하여 경로를 선택하지만, 완벽하진 않다. 마치 길을 잘 알고 있는 구역에서는 운전자가 네비게이션보다 더 빠른, 더 적은 비용을 지불하는 경로를 알수 있듯이 옵티마이저도 그러하다. 또한 복잡할수록 실수한 가능성도 크다. 통계정보에 담기지 않는 데이터 혹은 업무 경험 등을 활용하여 개발자가 직접 더 최적의 액세스 경로를 찾아낼 수도 있다. 이 때 사용하는 것이 바로 ‘옵티마이저 힌트’이다. 또한 SQL 옵티마이저는 인덱스만 힌트로 지정하고, 조인 방식, 순서, 테이블 액세스 방식 등은 옵티마이저가 알아서 판단하도록 진행할 수 있다. 만약, 개발자가 힌트를 빈틈없이 사용할거면, 하나도 빠짐없이 엄격하게 기술해야 한다.
옵티마이저 힌트 사용 시 주의사항
- 힌트 안에 인자를 나열할 땐 ‘,'(콤마)를 사용할 수 있지만, 힌트와 힌트 사이에 사용하면 안된다.
- 테이블을 지정할 때 스키마명까지 명시하면 안된다.
- FROM 절에 테이블명 옆에 ALIAS를 지정했다면, 힌트에도 ‘반드시’ ALIAS를 사용해야 한다.
FROM 절에 ALIAS를 지정했는데 힌트에는 테이블명만 사용하면, 그 힌트는 무시된다.
자주 사용하는 힌트 목록
| 분류 | 힌트 | 설명 |
| 최적화 목표 | 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. LEADIN (a1, a2)) | |
| SWAP_JOIN_INPUTS | 해시 조인 시, BUILD INPUT을 명시적으로 선택 (ex. SWAP_JOIN_INPUTS(a1)) | |
| 조인방식 | USE_NL | NL 조인으로 유도 |
| USE_MERGE | 소트 머지 조인으로 유도 | |
| USE_HASH | 해시 조인으로 유도 | |
| USE_SJ | NL 세미조인으로 유도 | |
| MERGE_SJ | 소트 머지 세미조인으로 유도 | |
| HASH_SJ | 해시 세미조인으로 유도 | |
| 서브쿼리 팩토링 | MATERIALIZE | WITH 문으로 정의한 집합을 물리적으로 생성하도록 유도 ex) WITH /*+ MATERIALIZE */ a1 AS (SELECT …) |
| INLINE | WITH 문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE 처리하도록 유도 ex) WITH /*+ INLINE */ a1 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 방지 | |
| 병렬 처리 | PARALLEL | 테이블 스캔, DML 병렬 방식으로 처리하도록 할 때 사용. 단일 대형 테이블 접근 시 많이 사용됨 ex) /+ PARALLEL(a1 4)/ |
| PARALLEL_INDEX | 인덱스 스캔을 병렬 방식으로 처리하도록 유도 | |
| PQ_DISTRIBUTE | 병렬 수행 시 데이터 분배 방식 결정 ex) PQ_DISTRIBUTE(a1 HASH(–BUILD INPUT) HASH(–PROBE TABLE)) | |
| 기타 | APPEND | DIRECT PATH INSERT 유도로 INSERT 문에 주로 많이 사용됨 |
| DRIVING_SITE | DB LINK REMOTE 쿼리에 대한 최적화 및 실행 주체 지정 (LOCAL 또는 REMOTE) | |
| PUSH_SUBQ | 서브쿼리를 가급적 빨리 필터링하도록 유도 | |
| NO_PUSH_SUBQ | 서브쿼리를 가급적 늦게 필터링하도록 유도 |
결론
SQL 최적화는 성능을 극대화하기 위한 핵심적인 과정으로, SQL 파싱과 옵티마이저의 역할을 이해하는 것이 중요합니다. SQL 파싱은 문법적 및 의미적 오류를 잡아내고, 최적화 단계에서 SQL 옵티마이저는 통계 정보를 바탕으로 가장 효율적인 실행 경로를 선택합니다. 하지만 SQL 옵티마이저가 항상 최선의 경로를 선택하는 것은 아니므로, 옵티마이저 힌트를 적절히 활용해 더 나은 성능을 이끌어낼 수 있습니다. 힌트를 사용할 때는 주의사항을 숙지하고, 올바르게 기술하는 것이 필수입니다. SQL 최적화는 다양한 옵션과 도구를 적절히 활용하여 성능을 개선하는 과정이므로, 옵티마이저 힌트와 실행 계획에 대한 이해를 통해 보다 효율적인 쿼리를 작성할 수 있습니다.