I. 최적 SQL 수행 위한, 옵티마이저(Optimzer)의 정의
가. 옵티마이저 정의
- 사용자 SQL질의의 처리 가능 실행계획 탐색, 비용 산정, 최적의 실행 계획 수립하는 DBMS 핵심 엔진
나. 옵티마이저 주요 기능
| 기능 | 설명 |
| 실행 계획 탐색 (Search Space Enumeration) |
- 주어진 SQL 질의를 처리할 수 있는 실행 계획들을 나열(P1, .... Pn) |
| 비용 산정 (Cost Estimation) |
- 각 실행 계획의 예상 비용을 계산하여 가장 비용이 적게 드는 실행 계획 Pi를 선택해서 SQL을 실행하고 결과를 사용자에게 제공 |
다. 옵티마이저 처리 절차
| 질의 처리 단계 | 주요 기능 | 설명 |
| 1). Query Rewrite | 질의 변환기 | - 옵티마이저가 더욱 효과적인 QEP를 찾기 위하여 더 효과적인 플랜이 있는지 그 가능성을 확인하는 과정 |
| 2). Query Optmization | 비용 산정기 | - 질의에 대한 엑세스 경로를 결정(규칙 기반 / 비용 기반 옵티마이저 사용) |
| 3). QEP Generation | 실행 계획 생성기 | - 질의 실행 계획(Query Execution Plan) : 질의를 실행하는데 필요한 상세한 정보를 생성. |
II. 규칙 기반 옵티마이저, RBO(Rule Based Optimzer)
| 항목 | 설명 |
| 개념 | - 인덱스 구조나 비교연산자에 따른 순위여부를 기준으로 최적의 경로를 설정하는 규칙 기반 옵티마이저 |
| 특징 | - 판단이 규칙적이고 분명하여 사용자가 정확히 예측 통계정보라는 현실요소를 무시하여 판단의 오차가 크게 날 수도 있음. |
| 우선순위 | Path 1: Single Row by ROWID Path 2: Single Row by Cluster Join Path 3: Single Row by hash Cluster Key with Unique or Primary Key Path 4: Single Row by Unique or Primary Key Path 5: Clustered Join Path 6: Hash Cluster Key Path 7: Indexed Cluster Key Path 8: Composite Index Path 9: Single-Column Indexes Path 10: Bounded Range Search on Indexed Columns Path 11: Unbounded Range Search on Indexed Columns Path 12: Sort_Merge Join Path 13: MAX or MIN of Indexed Column Path 14: ORDER BY on Indexed Column Path 15: Full Table Scan |
| 예 시 | Emp 테이블에 ‘A’ 인덱스가 “deptno”로 구성, ‘B’ 인덱스가 “deptno+empno”로 구성시 다음의 SQL은 Bounded Range Search(Between)이므로 ‘A” 인덱스를 사용. 즉 ’A’인덱스는 Rank 9이고 ‘B’인덱스는 Rank 10 조건이 됨 SELECT /*+ Rule */ from emp where deptno = 10 and empno between 7888 and 8888; |
III. 비용 기반 옵티마이저, CBO(Cost Based Optimzer) 개념, 처리과정
가. 비용 기반 옵티마이저 개념
| 항목 | 설명 |
| 개념 | - 처리 방법들에 대한 비용을 산정해 보고 그 중, 가장 적은 비용이 들어가는 처리방법을 선택하는 옵티마이저 |
| 특징 | - 현실을 감안한 판단, 통계 정보의 관리를 통해 최적화 제어, 옵티마이저를 깊이 이해하고 있지 않더라도 최소 성능을 보장하는 실행 계획을 미리 예측 |
| 원리 | 시스템 통계정보(CPU, 디스크 엑세스 타임을 이용하여 처리시간으로 환산한 방식)을 사용 SQL문장에 대한 여러가지 경우의 수 별로 처리시간에 비례한 비용을 산출해 내고 이들 비용에서 가장 작은 비용을 갖는 플랜을 결정 |
| 예 시 | SELECT * FROM dept WHERE deptno= 10의 SQL 에서 dept테이블은 deptno에 대한 인덱스가 있 고 테이블은 전체 10블록으로 구성되어 있으며 풀 테이블 스캔일 경우 I/O 단위를 결정하는 파 라미터가 db_file_multiblock_read_count=8 이라면 RBO는 무조건 인덱스를 타는 플랜을 결정하지만 CBO는 deptno의 인덱스를 이용해 실행하면 3회의 I/O가 발생한다고 가정하고 풀 테이블 스 캔의 경우 2회(8블고+2블고)의 I/O가 발생한다면 인덱스가 있음에도 풀테이블 스캔을 선택함 |
나. 비용 기반 옵티마이저 처리 과정
- CBO는 옵티마이저의 통계 정보를 활용한 비용(Cost) 계산을 통해 최적 실행계획 도출
다. CBO 기반 SQL 최적화 수행 과정 구성요소
| 수행 과정 | 구성 요소 | 설명 |
| 1). SQL 파싱 과정 | SQL Parser | - Parsing Tree 생성, SQL 문장 개별 구성요소 분석, - 문번적 오류(Syntax), 의미상 오류(Semantic) 확인 |
| 2). Optimization 과정 | 2-1). Query Transformer(쿼리 변환) | - Parsed SQL 표준(Canonical) 형태 변환 ex) 서브쿼리, 뷰 -> Join 형태로 변경 |
| 2-2). Estimator(비용산출) | - Dictionary 통계활용, 비용(Cost) 산출 - 선택도(Selectivity), 카디널리티(Cardinality) 계산 |
|
| 2.3). Plan Generator(실행 계획 생성) | - 실행 계획 후보군 산출 - Query + Estimates 기반 최종 실행 계획 선정 |
|
| 3). Row-Source Generation과정 | Row-Source Generation | - 옵티마이저 실행계획 포맷팅 - SQL 엔진 실행가능 코드(or 프로시저) 변환 |
| 4). Execution 과정 | SQL Engine | - Row-Source 형태의 실행계획 실행 - 쿼리 실행 결과 제공 |
- CBO 참조 통계정보는 테이블 통계, 인덱스 통계, 컬럼 통계, 시스템 통계 존재함
'메가노트 > 토픽과제(정리)' 카테고리의 다른 글
| 샤딩(문경숙 수석님) (0) | 2022.10.22 |
|---|---|
| 마스터 데이터(황선환 이사님) (0) | 2022.10.22 |
| 데이터베이스 인덱스(이강욱 선임님) (0) | 2022.10.22 |
| 정규화(이상희 부장님) (0) | 2022.10.22 |
| 동시성 제어(안혜진 선임님) (0) | 2022.10.22 |
