업무에 바로 쓰는 SQL 튜닝 : 데이터베이스 구조 및 옵티마이저 이해

---

1. 데이터베이스 처리 단계: Parse와 Bind

✅ Parse 단계 (구문 분석 단계)

Parse는 SQL 문을 처음 데이터베이스에 제출했을 때 실행 전에 반드시 거치는 해석 및 검증 단계입니다.

🔍 주요 작업

• 문법 검사: SQL 문이 문법적으로 맞는지 검사
• 객체 이름 해석: 테이블, 뷰, 컬럼 등이 존재하는지 확인
• 권한 확인: 사용자가 객체에 접근할 수 있는 권한이 있는지 확인
• 파싱 트리 생성: SQL 문장을 트리 형태로 분석
• Shared Pool 검색: SGA의 Shared Pool에 같은 SQL이 이미 파싱되어 있는지 검사 (Soft Parse를 위해)

✅ Hard Parse vs Soft Parse

• Hard Parse: 처음 제출되는 SQL → 모든 파싱 과정, 권한 검사, 실행 계획 생성 포함
• Soft Parse: 이전에 파싱된 SQL이 Shared Pool에 존재 → 재사용하여 빠르게 처리

---

✅ Bind 단계 (값 바인딩 단계)

Bind는 SQL 문에 포함된 변수나 리터럴 값들을 실제 값으로 바꾸는 과정입니다.

예시

SELECT * FROM employees WHERE employee_id = :1;

• :1이 바인드 변수입니다.
• 사용자는 :1에 100 같은 값을 전달하면, 이 시점에 SQL 문이 변수와 함께 실행됩니다.

장점

• SQL 재사용 가능 (같은 문장에 다른 값만 바꿔서 실행 가능 → Soft Parse 유도)
• 성능 최적화: Hard Parse 줄여 Shared Pool 효율성 증가
• 보안 강화: SQL Injection 위험 감소

---

2. SGA와 Instance

✅ SGA (System Global Area)

• Oracle DB에서 인스턴스 메모리 영역을 의미
• 서버 프로세스와 백그라운드 프로세스들이 공유
• 데이터베이스 작동 시 반드시 메모리에 올라오는 구조

주요 구성 요소

구성 요소 설명

Shared Pool	SQL 파싱 결과, 커서, 라이브러리 캐시 등 저장
Database Buffer Cache	디스크에서 읽어온 블록 저장 (읽기/쓰기 캐시)
Redo Log Buffer	변경 사항 기록 (장애 발생 시 복구용)
Large Pool	백업, 복구, 병렬 처리 등에서 사용되는 대용량 메모리
Java Pool	Java 기반 객체 저장

---

✅ Instance (인스턴스)

• SGA + 백그라운드 프로세스의 조합
• 하나의 DB 인스턴스는 한 번에 하나의 DB만 관리 가능 (단, RAC는 예외)
• 인스턴스는 DB를 오픈하고 관리하는 역할을 함

주요 백그라운드 프로세스

프로세스 설명

DBWR	Dirty Buffer를 디스크에 기록
LGWR	Redo Log Buffer를 Redo Log File로 기록
SMON	시스템 모니터링, 복구 처리
PMON	프로세스 모니터링, 세션 정리
CKPT	체크포인트 발생 시 관련 정보 기록

---

3. 전체 흐름 요약: SQL 실행과정

SQL 문장 → Parse → Bind → Execute → Fetch

1. Parse
   • 문법 검사, 객체 존재 검사, 권한 확인
   • 실행계획 생성 (Hard Parse)
   • Shared Pool 검색 (Soft Parse)
2. Bind
   • SQL 문에 변수값 적용
3. Execute
   • 실제 SQL 실행
4. Fetch
   • 결과 데이터 가져오기 (SELECT 문의 경우)

---

📌 결론 및 정리

• Parse는 SQL 문장을 해석하고 실행 계획을 생성하는 단계
• Bind는 변수에 실제 값을 넣어 SQL을 완성하는 단계
• SGA는 인스턴스가 사용하는 공유 메모리 공간으로 SQL 처리에 필요한 캐시와 버퍼를 포함
• Instance는 SGA와 백그라운드 프로세스의 집합으로 데이터베이스를 운영하는 단위

---

 

 

 

 

✅ 1. 옵티마이저란?

옵티마이저(Optimizer)는 SQL 문을 어떻게 실행할지 결정하는 데이터베이스 내부 엔진입니다. Oracle에는 크게 두 가지 옵티마이저가 있습니다:

옵티마이저 유형설명

Rule-Based Optimizer (RBO)	오래된 방식. 정해진 규칙에 따라 실행 계획 결정 (Oracle 10g부터 공식적으로 제거됨)
Cost-Based Optimizer (CBO)	현재 표준 방식. 실행 비용을 계산해서 최적의 계획 선택

 

---

✅ 2. Cost-Based Optimizer(CBO)의 핵심 개념

CBO는 다음 요소들을 종합해 각 실행 계획의 비용을 추정하고, 가장 적은 비용을 가지는 계획을 선택합니다.

📌 주요 입력 요소

요소설명

통계 정보 (Statistics)	테이블, 인덱스, 컬럼, 데이터 분포, 카디널리티 등
시스템 리소스 정보	CPU 속도, 디스크 I/O 속도 등
쿼리 구조	조인 조건, WHERE 절, 서브쿼리 등
옵티마이저 파라미터	OPTIMIZER_MODE 등 초기화 파라미터

 

---

✅ 3. 실행 계획 선택 과정

CBO는 다음과 같은 절차를 통해 SQL을 최적화합니다:

1. SQL 문 분석 (Parse 단계에서 수행)
2. 가능한 실행 계획들 생성
   • 테이블 Full Scan
   • Index Scan
   • Nested Loop Join, Hash Join 등 다양한 조인 전략
3. 각 계획의 Cost 계산
   • CPU, I/O, 메모리 자원 사용량 예측
4. 가장 낮은 Cost를 가진 계획 선택

---

✅ 4. 비용(Cost) 계산 방식

Oracle의 비용은 실제 "시간"이 아니라 상대적인 리소스 소비량을 나타내는 추상적 수치입니다.

text

복사편집

Cost = CPU Cost + I/O Cost + Network Cost (예: 병렬 처리 시)

예를 들어, 인덱스 스캔이 50, 테이블 Full Scan이 80이라면, CBO는 인덱스 스캔을 선택합니다. 단, 통계 정보가 정확해야 제대로 판단합니다.

---

✅ 5. 통계 정보의 중요성

CBO는 정확한 결정을 위해 DB 객체의 통계 정보를 사용합니다. 이 정보는 다음 명령어로 수집합니다:

sql

복사편집

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP');

통계 정보 예시:

• 테이블의 행 수
• 컬럼의 최대/최소 값
• NULL 비율
• 데이터 분포 (히스토그램)
• 인덱스 정보 (Clustering Factor 등)

---

✅ 6. CBO의 실행 계획 확인 방법

실제 Oracle에서 SQL 문장이 어떤 계획으로 실행될지를 보고 싶다면:

sql

복사편집

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

또는

sql

복사편집

SELECT * FROM V$SQL_PLAN WHERE SQL_ID = 'xyz...';

---

✅ 7. 옵티마이저 관련 파라미터

파라미터설명

OPTIMIZER_MODE	FIRST_ROWS, ALL_ROWS, CHOOSE 등
OPTIMIZER_FEATURES_ENABLE	Oracle 버전에 따른 최적화 동작 선택
OPTIMIZER_INDEX_COST_ADJ	인덱스 사용 유도 정도 조절

 

---

✅ 8. 실전 예시

예:

sql

복사편집

SELECT * FROM employees WHERE last_name = 'Smith';

• 테이블에 수백만 건의 데이터가 있고
• last_name 컬럼에 인덱스가 있을 경우
• CBO는 인덱스를 사용할지, Full Scan을 할지 비용을 계산해서 결정합니다

만약 통계 정보가 없다면, CBO는 부정확한 판단을 내릴 수 있습니다.

---

✅ 요약 정리

항목설명

CBO	비용 기반의 실행 계획 선택 시스템
기반 정보	테이블/인덱스 통계, 시스템 리소스, SQL 구조
목표	최소 비용(Max Performance)의 실행 계획 선택
필수 조건	정확한 통계 수집 (DBMS_STATS)
확인 방법	EXPLAIN PLAN, V$SQL_PLAN, AUTOTRACE 등