1장 SQL 처리 과정 - 내가 날리는 쿼리가 이렇게 실행된다고?

1. SQL 파싱과 최적화

1.1. 구조적, 집합적, 선언적 질의 언어

SQL은 ‘Structured Query Language’의 줄임말이다. 말 그대로 구조적 질의 언어이다.

SQL은 기본적으로 구조적(structured)이고 집합적(set-based)이고 선언적(declarative)인 질의 언어이다. 원하는 결과집합을 구조적, 집합적으로 선언하지만, 그 결과를 만드는 과정에서 절차적일 수 밖에 없다.

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

SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME, D.LOC 
FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
ORDER BY E.ENAME

1.2. SQL 실행전 최적화 과정

1) SQL 파싱

구분	설명
파싱 트리 생성	SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
Syntax 체크	문법적 오류가 없는지 확인 (ex. 사용할 수 없는 키워드를 사용했거나 순서가 바르지 않거나 누락된 키워드가 있는지 확인)
Semantic 체크	의미상 오류가 없는지 확인 (ex. 존재하지 않는 테이블 또는 컬럼을 사용했는지, 사용한 오브젝트에 대한 권한이 있는지 확인)

2) SQL 최적화

옵티마이저는 데이터베이스 성능을 결정하는 결정하는 가장 핵심적인 엔진이다.

SQL 옵티마이저(Optimizer)가 미리 수집한 시스템 및 오브젝트 통게정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다.

3) 로우 소스 생성

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

1.3. SQL 옵티마이저

SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진이다.(자동자 내비게이션과 흡사)

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

1.4. 실행계획과 비용

실행계획(Execution Plan)이란 SQL 실행경로를 미리보는 기능이다. SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리구조로 표현한다.

• 실행계획 확인하는 방법 ⇒ 부록

미리보기 기능을 통해 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지를 확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행경로를 변경할 수 있다.

비용(Cost)는 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요시간을 표현한 값이다.

1.4. 옵티마이저 힌트

자동차 내비게이션이 보편적으로 좋은 선택을 하지만, 그 선택이 항상 최선은 아니다. SQL 옵티마이저도 대부분 좋은 선택을 하지만, 완벽하진 않다. 이는 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	서브쿼리를 가급적 늦게 필터링하도록 유도

2. SQL 공유 및 재사용

SQL의 내부 최적화 과정의 복잡성을 알고 나면, 동시성이 높은 온라인 트랜잭션 처리 시스템에서 바인드 변수가 왜 중요한지 자연스럽게 이해하게 된다.

2.1. 소프트 파싱 vs 하드 파싱

‘라이브러리 캐시(Library Cache)’란 SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간이다. SGA의 구성요소이기도 한데, ‘SGA(System Global Area)’는 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다.

사용자가 SQL 문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지부터 확인한다. 캐시에서 찾으면 곧바로 실행 단계로 넘어가는데 이를 ‘소프트 파싱(Soft Parsing)’이라고 한다. 캐시를 찾는데 실패하여 최적화 및 로우 소스 생성 단계까지 모두 거치는 것은 ‘하드 파싱(Hard Parsing)’이라고 한다.

알고리즘과 하드웨어가 발전하면서 워낙 빠르게 처리하다 보니 잘 느끼지 못하겠지만, SQL 오비마이저는 순식간에 엄청나게 많은 연산을 한다. 하나의 쿼리를 수행하는 데 있어서 후보군이 될만한 무수히 많은 실행경로를 도출하고, 짧은 순간에 딕셔너리와 통계정보를 읽어 각각에 대한 효율성을 판단하는 과정은 결코 가벼울(soft) 없다. 하드 파싱은 CPU를 많이 소비하는 몇 안 되는 작업 중 하나인데, 이렇게 어려운(hard) 작업을 거쳐 생성한 내부 프로시저를 한 번만 사용하고 버리는 것은 정말 비효율적이다. 따라서 라이브러리 캐시가 필요한 이유가 바로 여기에 있다.

2.2. 바인드 변수의 중요성

이름없는 SQL 문제
사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖으며, 컴파일한 상태로 딕셔너리에 저장되어, 사용자가 삭제하지 않는 한 영구적으로 보관된다. 실행할 때 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다. 사용자 정의 함수/프로시저는 내용을 수정해도 변하지 않으므로 같은 프로그램이 무한 생성되지 않는다.

반면, SQL은 이름이 따로 없다. SQL 자체가 이름이기 때문에 텍스트 중 작은 부분이라도 수정되면 그 순간 다른 객체가 새로 탄생하는 구조다. DBMS에서 수행되는 SQL이 일회성 또는 무효화된 SQL까지 모두 저장하려면 많은 공간이 필요하고, 그만큼 SQL을 찾는 속도도 느려지기 때문에 DBMS가 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 scott.emp where empno = 7900  ;
    select /* comment */ * from emp where empno = 7900;
    select /*+first_rows */ * from emp where empno = 7900;

DBMS에 발생하는 부하는 대개 과도한 I/O가 원인인데, 어떤 날은 I/O가 거의 발생하지 않음에도 불구하고 CPU 사용률이 급격히 올라가고, 라이브러리 캐시에 발생하는 여러 종류의 경합 때문에 로그인이 제대로 처리되지 않았을 것이다. 이는 동시다발적으로 발생하는 SQL 하드파싱 때문이다.

따라서 반복적인 쿼리에 바인드 변수를 사용하면 SQL에 대한 하드파싱은 최초 한 번만 일어나고, 캐싱된 SQL을 100만 고객이 공유하면서 재사용할 것이다.