데이터베이스 및 애플리케이션 유지 관리

SQL 튜닝 개요

SQL 튜닝은 데이터베이스 성능을 최적화하는 데 필수적인 과정입니다. 특히 OLTP(Online Transaction Processing) 환경에서 SQL 튜닝은 시스템 전체의 응답 시간을 단축하고 자원 활용도를 높이는 데 중요한 역할을 합니다.

SQL 튜닝의 목표

  • 사용자 응답 시간(User Response Time) 단축
  • 처리량(Throughput) 향상
  • 자원 소비 최소화

SQL 튜닝 단계

  1. 성능 분석:
    • 대량의 I/O 발생 SQL
    • CPU 사용률이 높은 SQL
    • 실행 빈도가 높은 SQL
  2. 튜닝 대상 선정:
    • 전체 성능에 미치는 영향이 큰 SQL을 우선적으로 선정
  3. 튜닝 수행:
    • 인덱스 추가/변경
    • SQL 쿼리 재작성
    • SQL 프로필 적용
    • SQL Plan Baseline 적용

SQL 처리 과정

SQL 튜닝을 효과적으로 수행하려면 SQL 처리 과정에 대한 이해가 필수적입니다. SQL 문장은 다음 단계를 거쳐 처리됩니다.

  1. Parsing: SQL 문장 분석
    • Syntax Check: SQL 문법 오류 검사
    • Semantic Check: 테이블, 컬럼 존재 여부 등 의미론적 오류 검사
    • Shared Pool Check: Shared Pool에 동일 SQL 문장 존재 여부 확인
  2. Optimization: 쿼리 실행 계획 생성
    • Query Transformer: 쿼리 재작성 여부 결정
    • Estimator: 실행 계획 비용 예측
    • Plan Generator: 최적 실행 계획 선택
  3. Row Source Generation: 실행 계획 기반으로 데이터 추출 루틴 생성
  4. Execution: 데이터 추출 및 결과 반환

쿼리 옵티마이저 (Query Optimizer)

쿼리 옵티마이저는 SQL 문장의 최적 실행 계획을 생성하는 핵심 구성 요소입니다. Cost-Based Optimization (CBO) 방식으로 작동하며, 다양한 실행 계획의 비용을 추정하여 가장 낮은 비용의 계획을 선택합니다.

옵티마이저 주요 구성 요소

  • Query Transformer: SQL 문장을 의미적으로 동일하지만 비용이 더 낮은 SQL 문장으로 재작성합니다.
  • Estimator: 통계 정보를 기반으로 각 실행 계획의 비용을 추정합니다.
  • Plan Generator: 다양한 접근 경로(Access Path), 조인 방법(Join Method), 조인 순서(Join Order)를 고려하여 실행 계획을 생성합니다.

실행 계획 (Execution Plan)

실행 계획은 SQL 문장을 실행하기 위한 단계별 지침을 나타냅니다. 각 단계는 데이터베이스에서 데이터를 물리적으로 검색하거나 사용자에게 결과를 준비하는 작업을 수행합니다.

실행 계획 확인 방법

  • EXPLAIN PLAN 문 사용
  • DBMS_XPLAN 패키지 사용

실행 계획 예시

다음은 EXPLAIN PLAN을 사용하여 얻은 실행 계획의 예입니다.

EXPLAIN PLAN FOR
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary > 5000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  
Plan hash value: 3753977611

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows | Bytes| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |   62 |  496 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN                      |               |   62 |  496 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL               | EMPLOYEES     |  107 |  321 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL               | DEPARTMENTS   |   27 |   162 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   2 - filter("E"."SALARY">5000)

위 실행 계획은 HASH JOIN을 사용하여 employees 테이블과 departments 테이블을 조인하고, employees 테이블에서 salary가 5000보다 큰 레코드를 필터링하는 것을 보여줍니다.

접근 경로 (Access Paths)

접근 경로는 데이터베이스가 데이터를 검색하는 데 사용하는 방법입니다. 주요 접근 경로는 다음과 같습니다.

  • Full Table Scan (전체 테이블 스캔): 테이블의 모든 행을 순차적으로 읽습니다.
  • Index Unique Scan (인덱스 고유 스캔): 고유 인덱스를 사용하여 단일 행을 검색합니다.
  • Index Range Scan (인덱스 범위 스캔): 인덱스를 사용하여 특정 범위 내의 행을 검색합니다.
  • Bitmap Index Scan (비트맵 인덱스 스캔): 비트맵 인덱스를 사용하여 여러 조건을 결합하여 행을 검색합니다.

조인 (Joins)

조인은 여러 테이블의 행을 결합하는 연산입니다. 주요 조인 방법은 다음과 같습니다.

  • Nested Loops Join (중첩 루프 조인): 외부 테이블의 각 행에 대해 내부 테이블을 반복적으로 검색합니다.
  • Hash Join (해시 조인): 작은 테이블을 사용하여 해시 테이블을 구축하고, 큰 테이블의 각 행을 해시 테이블에서 검색합니다.
  • Sort Merge Join (정렬 병합 조인): 두 테이블을 정렬한 후 병합합니다.

옵티마이저 통계 (Optimizer Statistics)

옵티마이저 통계는 쿼리 옵티마이저가 실행 계획을 생성하는 데 사용하는 데이터베이스 객체에 대한 메타데이터입니다. 정확한 통계는 최적의 실행 계획을 선택하는 데 매우 중요합니다.

주요 통계 유형

  • Table Statistics: 테이블의 행 수, 블록 수, 평균 행 길이 등
  • Column Statistics: 컬럼의 고유 값 수, Null 값 수, 데이터 분포(히스토그램) 등
  • Index Statistics: 인덱스 레벨 수, 리프 블록 수, 클러스터링 팩터 등

통계 수집 방법

  • DBMS_STATS 패키지 사용
  • 자동 통계 수집 (Automatic Optimizer Statistics Collection) 기능 활성화

SQL 프로필 (SQL Profiles)

SQL 튜닝 어드바이저가 생성하는 보조 정보로, 특정 SQL 문장에 대한 최적 실행 계획을 생성하는 데 사용됩니다. SQL 프로필은 Cardinality Estimates (카디널리티 추정)을 개선하고, 새로운 접근 경로를 탐색하는 데 도움을 줍니다.

SQL Plan Baseline

SQL Plan Baseline은 SQL 문장에 대해 허용된 실행 계획 집합입니다. 쿼리 옵티마이저는 SQL Plan Baseline에 있는 계획 중에서만 선택하여 성능 저하를 방지합니다.

SQL Plan Baseline 관리

  • SQL Plan Capture 기능 활성화
  • DBMS_SPM 패키지를 사용하여 SQL Plan Baseline 생성 및 관리
  • SQL Plan Evolution 기능을 사용하여 성능 검증 및 계획 추가

힌트 (Hints)

힌트는 쿼리 옵티마이저에게 특정 실행 계획을 사용하도록 지시하는 주석입니다. 힌트는 일반적으로 부적절한 옵티마이저 판단을 극복하거나 특정 접근 경로를 강제하는 데 사용됩니다.

힌트 종류

  • 접근 경로 힌트 (예: INDEX, FULL)
  • 조인 순서 힌트 (예: ORDERED)
  • 조인 방법 힌트 (예: USE_HASH, USE_NL)

힌트 사용 예시

SELECT /*+ INDEX(e emp_department_ix) */ employee_id, last_name
FROM employees e
WHERE department_id = 50;

위 쿼리는 employees 테이블에서 department_id 컬럼에 생성된 인덱스 emp_department_ix를 사용하도록 힌트를 지정합니다.

커서 공유 (Cursor Sharing)

커서 공유는 동일한 SQL 문장을 여러 번 실행할 때 파싱(Parsing) 오버헤드를 줄여 성능을 향상시키는 기술입니다. Shared Pool에 SQL 문장의 파싱 트리 및 실행 계획을 저장하여 재사용합니다.

바인드 변수 (Bind Variables) 사용

리터럴(Literal) 대신 바인드 변수를 사용하면 커서 공유 효율을 높일 수 있습니다. 동일한 SQL 문장을 재사용하여 파싱 오버헤드를 줄이고 SQL Injection 공격을 방지할 수 있습니다.

-- 리터럴 사용 예
SELECT * FROM employees WHERE employee_id = 100;

-- 바인드 변수 사용 예
SELECT * FROM employees WHERE employee_id = :employee_id;

SQL Test Case Builder

SQL Test Case Builder는 SQL 문장과 관련된 정보를 자동으로 수집하여 다른 데이터베이스 환경에서 문제를 재현하는 데 사용되는 도구입니다. 이 도구를 사용하면 개발자는 특정 SQL 문장의 실행 계획을 검증하고 성능 문제를 진단할 수 있습니다.

결론

이 가이드는 오라클 데이터베이스 및 애플리케이션 유지 관리, 특히 SQL 튜닝에 대한 기본적인 이해를 제공합니다. 이러한 기술들을 숙지하고 실제 환경에 적용함으로써 데이터베이스 성능을 최적화하고 시스템 안정성을 확보할 수 있습니다.

위로 스크롤