SQL 처리 과정

SQL은 "무엇을(What)" 원하는지만 기술하는 선언적 언어다. "어떻게(How)" 데이터를 찾을지는 DBMS 내부의 옵티마이저가 결정한다. 이 문서는 SQL이 실행 가능한 프로시저로 바뀌는 과정과, 그 결과물을 재사용하는 라이브러리 캐시를 정리한다.

SQL 최적화 과정

사용자가 SQL을 던지면 DBMS는 다음 세 단계를 거쳐 실행 가능한 형태로 만든다.

1. SQL 파싱 (Parser)

2. SQL 최적화 (Optimizer)

옵티마이저는 미리 수집한 시스템·오브젝트 통계정보를 바탕으로 여러 실행경로를 생성해 비교하고, 가장 효율적인 하나를 선택한다. 데이터베이스 성능을 결정하는 가장 핵심 엔진이다. 세부 동작은 옵티마이저와 쿼리 변환에서 다룬다.

3. 로우 소스 생성 (Row-Source Generator)

옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계다.

flowchart LR
  SQL["사용자 SQL"] --> P["Parser<br/>파싱 트리 · Syntax/Semantic 체크"]
  P --> O["Optimizer<br/>Query Transformer → Plan Generator ↔ Estimator"]
  O --> R["Row-Source Generator"]
  R --> E["SQL Engine<br/>실행"]
  D[("Data Dictionary<br/>통계정보")] -.-> O
  classDef hl fill:#fef3c7,stroke:#f59e0b,color:#451a03
  class O hl

시험 포인트: SQL 처리 단계는 파싱 → 최적화 → 로우 소스 생성 순서다. 문법 오류는 Syntax 체크, 존재하지 않는 테이블·권한 오류는 Semantic 체크에서 걸러진다.

옵티마이저와 실행계획

옵티마이저의 최적화 과정은 다음과 같다.

• 실행계획(Execution Plan): 옵티마이저가 생성한 처리 절차를 트리 구조로 표현한 것. 자세한 내용은 실행계획 분석을 참고한다.
• 비용(Cost): 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간. 실측값이 아니라 추정치이므로 실제 수행 성능과 다를 수 있다.
• 옵티마이저 힌트: 옵티마이저의 판단을 사용자가 직접 지정할 때 쓴다. 자세한 내용은 힌트를 참고한다.

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

라이브러리 캐시와 SQL 공유

SQL 최적화 과정은 하드웨어적으로 CPU를 많이 소비하는 무거운 작업이다. 그래서 한 번 만든 실행계획과 SQL 정보를 SGA의 공유 풀(Shared Pool) 안 라이브러리 캐시(Library Cache)에 저장해 두고 재사용한다.

flowchart LR
  A["SQL 수신"] --> B["SQL 파싱<br/>Syntax·Semantic"]
  B --> C{"라이브러리 캐시에<br/>있는가?"}
  C -- "있음 (소프트 파싱)" --> X["실행"]
  C -- "없음 (하드 파싱)" --> D["최적화"] --> E["로우 소스 생성"] --> X
  classDef hl fill:#fef3c7,stroke:#f59e0b,color:#451a03
  class D,E hl

하드 파싱이 왜 무거운가

옵티마이저는 SQL 하나를 최적화할 때 다음과 같은 수많은 경우의 수를 따진다.

• 테이블·컬럼·인덱스 구조에 관한 기본 정보
• 오브젝트 통계(테이블·인덱스·컬럼 통계)와 시스템 통계(CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등)
• 옵티마이저 관련 파라미터
• 조인 순서, 조인 방식, 액세스 경로(Full Scan, 인덱스 종류별 스캔)의 조합

다섯 개 테이블을 조인하는 것만으로도 조인 순서 경우의 수가 5! = 120가지다. 여기에 조인 방식과 인덱스 선택까지 곱해지므로, 그 과정에서 딕셔너리 정보를 읽고 비용을 계산하느라 CPU와 공유 풀 래치를 많이 소모한다.

SQL 공유 기준과 바인드 변수

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; -- 힌트

위 SQL은 실행 결과가 같지만 모두 각각 하드 파싱되어 라이브러리 캐시에 별도 공간을 차지한다.

시험 포인트: 대소문자, 공백, 주석, 스키마 명 유무가 달라도 다른 SQL로 인식한다. "결과가 같으면 같은 SQL로 공유된다"는 보기는 틀린 설명이다.

리터럴 SQL의 문제

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'oraking';
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'javaking';
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'tommy';
...

로그인 ID마다 SQL 텍스트가 달라지므로 사용자가 로그인할 때마다 하드 파싱이 일어난다. 동시 사용자가 많으면 라이브러리 캐시와 공유 풀 경합이 심해지고, 캐시에 재사용되지 않을 SQL이 가득 차 정말 필요한 SQL이 밀려난다.

바인드 변수 사용

SELECT * FROM CUSTOMER WHERE LOGIN_ID = :LOGIN_ID;

값만 바꿔 실행하므로 SQL 텍스트가 하나로 고정된다. 최초 한 번만 하드 파싱하고 이후에는 캐시된 실행계획을 재사용한다.

// 리터럴 방식: 매번 다른 SQL
stmt.executeQuery("SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + id + "'");

// 바인드 변수 방식: 하나의 SQL을 공유
PreparedStatement ps = conn.prepareStatement(
    "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?");
ps.setString(1, id);

SQL 공유 여부 확인

SELECT SQL_ID, SQL_TEXT, PARSE_CALLS, LOADS, EXECUTIONS
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%CUSTOMER%';

리터럴 SQL은 같은 패턴의 SQL이 여러 행으로 나오고, 바인드 변수 SQL은 한 행에 EXECUTIONS만 증가한다.

바인드 변수 사용 시 주의점

바인드 변수가 항상 좋은 것은 아니다. 하드 파싱 시점에 변수에 어떤 값이 들어올지 모르는 상태로 실행계획을 만들어야 하기 때문이다.

바인드 변수 Peeking

Oracle 9i부터는 하드 파싱 시점에 처음 들어온 바인드 변수 값을 살짝 엿보고(Peeking) 그 값의 분포를 고려해 실행계획을 만든다. 그러나 이후 다른 값이 들어와도 처음 만든 실행계획을 그대로 재사용하므로, 첫 실행 값이 무엇이냐에 따라 성능이 들쭉날쭉해지는 문제가 생긴다.

Oracle 11g부터는 적응적 커서 공유(Adaptive Cursor Sharing)로 입력 값 분포에 따라 다른 실행계획을 사용할 수 있게 했지만, 한 번은 비효율적으로 실행되어야 이를 감지한다는 한계가 있다.

바인드 변수와 리터럴 상수의 선택

시험 포인트: OLTP처럼 짧고 빈번한 SQL은 바인드 변수가 원칙이다. 반대로 수행 빈도가 낮고 오래 걸리는 배치·DW 쿼리, 값 종류가 적고 분포가 치우친 컬럼은 리터럴 상수가 유리할 수 있다.

정적 SQL과 동적 SQL

라이브러리 캐시 효율과 직접 관련된 것은 정적이냐 동적이냐가 아니라 바인드 변수를 쓰느냐다. 동적 SQL이라도 바인드 변수를 쓰면 SQL이 공유된다. 다만 조건절 조합에 따라 SQL을 동적으로 조립하면 조합의 수만큼 서로 다른 SQL이 생긴다는 점은 유의한다.

핵심 요약

• SQL 처리는 파싱(Syntax·Semantic 체크) → 최적화 → 로우 소스 생성 순서다.
• 옵티마이저는 통계정보로 후보 실행계획의 비용을 추정해 최저 비용 계획을 고른다. Cost는 예상치다.
• 실행계획은 SGA 공유 풀의 라이브러리 캐시에 저장되어 재사용된다.
• 캐시에서 찾으면 소프트 파싱, 못 찾아 최적화·로우 소스 생성까지 하면 하드 파싱이다.
• SQL은 텍스트 자체가 식별자이므로 대소문자·공백·주석·스키마 명만 달라도 다른 SQL이다.
• 리터럴 SQL은 매번 하드 파싱을 유발하므로 OLTP에서는 바인드 변수를 사용한다.
• 바인드 변수는 히스토그램을 활용하지 못하고, Peeking은 첫 값 기준 계획을 고정한다는 단점이 있다.
• 배치·DW 쿼리나 분포가 치우친 소수 값 컬럼은 리터럴 상수가 나을 수 있다.