SQL 처리 단계 (Stages of SQL Statement Processing)

오라클 데이터베이스에서 SQL 문은 여러 단계를 거쳐 처리됩니다. 각 단계는 SQL 문을 실행하고 결과를 반환하는 데 필수적이며, 성능에 큰 영향을 미칩니다. SQL 처리 단계를 이해하면 쿼리 성능을 진단하고 개선하는 데 도움이 됩니다. 이 글에서는 각 단계를 자세히 살펴보고, 실무에 적용할 수 있는 최적화 기법을 제공합니다.

1. SQL 파싱 (SQL Parsing)

SQL 파싱은 SQL 문을 데이터베이스가 이해할 수 있는 구조로 분해하는 첫 번째 단계입니다. 이 단계에서는 다음과 같은 작업을 수행합니다.

  • 구문 검사 (Syntax Check): SQL 문이 올바른 구문으로 작성되었는지 확인합니다.
  • 의미론적 검사 (Semantic Check): SQL 문에 사용된 객체(테이블, 뷰, 열 등)가 존재하는지, 접근 권한이 있는지 등을 확인합니다.
  • 공유 풀 검사 (Shared Pool Check): 동일한 SQL 문이 이전에 실행되었는지 확인하고, 재사용 가능한 실행 계획이 있는지 검색합니다.

예시:

SQL> SELECT * FROM employees WHERE employee_id = 100;

이 쿼리는 먼저 구문 및 의미론적 검사를 거쳐 employees 테이블이 존재하는지, SELECT 권한이 있는지 등을 확인합니다. 이후 공유 풀에서 동일한 쿼리의 실행 계획이 있는지 확인합니다.

1.1. 하드 파싱 (Hard Parsing) vs 소프트 파싱 (Soft Parsing)

  • 하드 파싱: 공유 풀에 실행 계획이 없거나, 환경 변수가 변경된 경우 발생하는 전체 파싱 과정입니다. 리소스 소모가 큽니다.
  • 소프트 파싱: 공유 풀에 실행 계획이 존재하고 재사용 가능한 경우 발생하는 간소화된 파싱 과정입니다. 성능에 유리합니다.

하드 파싱을 최소화하고 소프트 파싱을 최대화하는 것이 성능 최적화의 핵심입니다.

2. SQL 최적화 (SQL Optimization)

SQL 최적화 단계에서는 쿼리 옵티마이저가 SQL 문을 가장 효율적으로 실행할 수 있는 실행 계획을 생성합니다. 옵티마이저는 다양한 실행 계획을 고려하고, 비용 기반 접근 방식 (Cost-Based Optimization, CBO)을 사용하여 예상 비용이 가장 낮은 계획을 선택합니다.

2.1. 비용 기반 최적화 (Cost-Based Optimization)

비용 기반 최적화는 다음과 같은 요소들을 고려하여 실행 계획의 비용을 추정합니다.

  • 테이블 통계 (Table Statistics): 테이블의 행 수, 블록 수, 평균 행 길이 등
  • 인덱스 통계 (Index Statistics): 인덱스의 레벨, 리프 블록 수, 클러스터링 팩터 등
  • 시스템 통계 (System Statistics): I/O 성능, CPU 성능 등 하드웨어 자원 사용량

옵티마이저는 이러한 통계 정보를 바탕으로 다양한 접근 경로(Access Path), 조인 방법(Join Method), 조인 순서(Join Order) 등을 결정합니다.

예시:

다음 쿼리를 생각해 봅시다.

SQL> SELECT e.employee_id, e.last_name, d.department_name
  FROM employees e, departments d
 WHERE e.department_id = d.department_id
   AND d.location_id = 1700;

옵티마이저는 다음과 같은 실행 계획을 고려할 수 있습니다.

  • 계획 1: employees 테이블 전체 스캔 후 departments 테이블과 Nested Loops Join
  • 계획 2: departments 테이블 전체 스캔 후 employees 테이블과 Nested Loops Join
  • 계획 3: departments 테이블의 인덱스 사용 후 employees 테이블과 Nested Loops Join
  • 계획 4: Hash Join 사용

옵티마이저는 테이블 통계, 인덱스 유무, 데이터 분포 등을 고려하여 가장 효율적인 계획을 선택합니다. 예를 들어, departments 테이블의 location_id에 인덱스가 있고, 선택도가 높다면 계획 3이 가장 효율적일 수 있습니다.

3. SQL 실행 (SQL Execution)

SQL 실행 단계에서는 쿼리 옵티마이저가 생성한 실행 계획에 따라 SQL 문이 실제로 실행됩니다. 데이터베이스는 행 소스 생성기 (Row Source Generator)를 사용하여 실행 계획의 각 단계를 순차적으로 처리하고, 결과를 반환합니다.

3.1. 행 소스 (Row Source)

행 소스는 실행 계획의 각 단계에서 처리되는 데이터의 흐름을 나타냅니다. 각 행 소스는 테이블, 뷰, 조인 결과 등이 될 수 있습니다.

3.2. 접근 경로 (Access Path)

접근 경로는 데이터베이스가 데이터를 검색하는 방법을 나타냅니다. 테이블 전체 스캔 (Full Table Scan), 인덱스 스캔 (Index Scan), 로우아이디 접근 (Rowid Access) 등이 있습니다.

3.3. 조인 방법 (Join Method)

조인 방법은 두 개 이상의 테이블을 조인하는 방법을 나타냅니다. Nested Loops Join, Hash Join, Sort Merge Join 등이 있습니다.

예시:

앞선 쿼리에서 옵티마이저가 계획 3을 선택했다면, SQL 실행 엔진은 다음과 같은 단계를 거쳐 쿼리를 실행합니다.

  1. departments 테이블의 location_id 인덱스를 사용하여 location_id = 1700인 행을 찾습니다.
  2. 찾은 행의 department_id를 사용하여 employees 테이블과 Nested Loops Join을 수행합니다.
  3. 결과를 반환합니다.

4. 튜닝 팁 (Tuning Tips)

  • 통계 정보 최신 유지: 테이블 및 인덱스 통계 정보를 최신으로 유지하여 옵티마이저가 정확한 실행 계획을 생성하도록 합니다.
  • 인덱스 활용: 쿼리에 필요한 열에 인덱스를 생성하여 테이블 전체 스캔을 최소화합니다.
  • 조인 방법 최적화: 조인 조건, 데이터 크기 등을 고려하여 적절한 조인 방법을 선택합니다.
  • 힌트 사용: 옵티마이저의 판단을 변경해야 할 경우 힌트를 사용하여 실행 계획을 유도합니다.
  • SQL 프로파일링: SQL 튜닝 어드바이저를 사용하여 SQL 프로파일을 생성하고 적용하여 성능을 개선합니다.
  • SQL 계획 관리: SQL 계획 기준선(Baseline)을 사용하여 실행 계획의 변경을 방지하고, 성능 회귀를 예방합니다.

SQL 처리 단계를 이해하고, 각 단계에 따른 튜닝 기법을 적용하면 오라클 데이터베이스의 성능을 획기적으로 개선할 수 있습니다.

위로 스크롤