카테고리 없음
업무에 바로 쓰는 SQL 튜닝 : 데이터베이스 구조 및 옵티마이저 이해
aonee
2025. 5. 12. 14:31
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
- Parse
- 문법 검사, 객체 존재 검사, 권한 확인
- 실행계획 생성 (Hard Parse)
- Shared Pool 검색 (Soft Parse)
- Bind
- SQL 문에 변수값 적용
- Execute
- 실제 SQL 실행
- 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을 최적화합니다:
- SQL 문 분석 (Parse 단계에서 수행)
- 가능한 실행 계획들 생성
- 테이블 Full Scan
- Index Scan
- Nested Loop Join, Hash Join 등 다양한 조인 전략
- 각 계획의 Cost 계산
- CPU, I/O, 메모리 자원 사용량 예측
- 가장 낮은 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 등 |
반응형