1. SQL 파싱
SQL은 기본적으로 구조적(structured)이고 집합적(set-based)이고 선언적(declarative)인 질의 언어이다. ‘SQL 최적화’란 DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정을 말한다.
- Syntax Check : 먼저 문법적 오류가 없는지 확인한다. 오류가 있다면 에러를 반환하고 마무리한다.
SQL> SELECT * FORM employees;
SELECT * FORM employees
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected- Semantic Check : 필요로 하는 오브젝트가 실제로 존재하는지, 접근 권한이 충분한지, 제대로 된 컬럼명을 사용했는지 등을 확인한다.
SQL> SELECT * FROM nonexistent_table;
SELECT * FROM nonexistent_table
*
ERROR at line 1:
ORA-00942: table or view does not exist-
실행계획(Shared Pool Caching) Check : 오라클은 SQL 실행 전 옵티마이저(Optimizer)라는 최적화 도구에 의해 어떻게 데이터를 가장 효율적으로 처리할지, 즉 I/O를 최소화 하기위한 여러 방법을 검토하고 최적의 실행계획을 생성한다.
실행계획을 생성하는 과정은 많은 자원을 소모하는 작업이기 때문에 실행계획을 재사용할 수 있도록 공유(캐싱)해두는데, Shared Pool의 Library Cache라는 영역에 공유되어 있다.
2. SQL 옵티마이저(최적화)
SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진이다.
옵티마이저의 최적화 단계
- 실행계획 후보군 생성
- 쿼리를 수행하는데 사용할만한 실행계획 후보군을 찾는다.
- 실행계획의 비용 계산
- 미리 수집해 둔 오브젝트 통계 정보, 시스템 통계 정보를 기반으로 각 실행계획의 예상 비용을 계산한다.
- 최적의 실행계획 선택
- 실행계획 후보군 중에서 예상비용이 가장 적은 실행계획을 선택한다.
3. 실행계획과 비용
실행계획(Execution Plan)이란 SQL 실행경로를 미리보는 기능이다. SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리구조로 표현한다.
미리보기 기능을 통해 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지 볼 수 있는데, 인덱스를 스캔한다면 어떤 인덱스인지를 확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행경로를 변경할 수 있다.
비용(Cost)은 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요시간을 표현한 값이다.
옵티마이저 힌트
SQL이 복잡할수록 액세스 경로를 찾을 때 실수할 가능성도 크다. 이때 옵티마이저 힌트를 이용해 효율적인 액세스 경로를 찾아낼 수 있다.
- 힌트 사용법
- 힌트 사용법은 주석 기호에 ‘+’를 붙이면 된다.
SELECT /*+ INDEX(A 고객_PK) */
고객명, 연락처, 주소, 가입일시
FROM 고객 A
WHERE 고객ID = '00000000008'- 자주 사용하는 힌트 목록
분류 힌트 설명 최적화 목표 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으로 유도 조인 방식 USE_NL NL 조인으로 유도 조인 방식 USE_MERGE 소트 머지 조인으로 유도 조인 방식 USE_HASH 해시 조인으로 유도 조인 방식 NL_SJ NL 세미조인으로 유도 조인 방식 MERGE_SJ 소트 머지 세미조인으로 유도 조인 방식 HASH_SJ 해시 세미조인으로 유도 서브쿼리 팩토링 MATERIALIZE WITH 문으로 정의한 집합을 물리적으로 생성하도록 유도(ex. WITH /+ INLINE / T AS ( SELECT ... )) 서브쿼리 팩토링 INLINE WITH 문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE 처리하도록 유도 (ex. WITH /+ INLINE / 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(T1 2) PARALLEL (T2 2)) 병렬 처리 PARALLEL_INDEX 인덱스 스캔을 병렬방식으로 처리하도록 유도 병렬 처리 PQ_DISTRIBUTE 병렬 수행 시 데이터 분배 방식 결정 (ex. PQ_DISTRIBUTE(T1 HASH HASH)) 기타 APPEND Direct-Path Insert로 유도 기타 DRIVING_SITE DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지정(Local 또는 Remote) 기타 PUSH_SUBQ 서브쿼리를 가급적 빨리 필터링하도록 유도 기타 NO_PUSH_SUBQ 서브쿼리를 가급적 늦게 필터링하도록 유도
SQL 공유 및 재사용
하드 파싱 / 소프트 파싱
-
하드 파싱
오라클이 기존 코드를 재사용할 수 없는 경우 코드를 새로 빌드해야 한다. 하드 파싱을 하는 동안 Database는 Library Cache와 Dictionary Cache에 여러 번 액세스하여 Data Dictionary를 확인한다.
DDL문은 항상 하드 파싱을 수행한다
-
소프트 파싱
명령문이 재사용 가능한 SQL문과 동일한 경우 오라클은 기존 코드를 재사용한다. -> Library Cache Hit
소프트 파싱은 Optimization, Row Source Generation 단계를 건너뛰고 바로 Execution 단계로 진행하기 때문에 하드 파싱보다 선호된다.
바인드 변수
- SQL문을 키 값으로 사용해 라이브러리 캐시에 SQL이 저장되어 있는지 확인인다.
- 같은 데이터를 조회하는 SQL이라도 문법이 조금만 틀리면, 각자 별도의 SQL 최적화 과정을 거쳐서 별도의 라이브러리 캐시 공간에 저장된다.
- 아래는 모두 다른 SQL로 취급된다.
SELECT * FROM emp WHERE empno = 7900;
select * from emp where empno = 7900;
select * from emp where empno = 7900 ;
select * from emp where empno = 7900 ;
select * from com.emp where empno = 7900;
select /* comment */ * from emp where empno = 7900;
select /*+ first_rows */ * from emp where empno = 7900;- 하드 파싱을 줄이기 위해서는 값을 SQL에 하드 코딩하지 않고 되도록이면 바인드 변수를 사용해서 나타내야 한다.
// SQL에 하드코딩 x
await queryRunner.query('SELECT * FROM emp WHERE empno =' + no + ';');
// 바인드 변수 사용 o
await queryRunner.query('SELECT * FROM emp WHERE empno = ?;', [no]);