[Oracle] 오라클 SQL 처리 절차 - CBO (Cost-Based Optimizer)

SQL 처리 절차 - CBO (Cost-Based Optimizer)

- SQL Parsing

① Syntax check (문법적 오류)

② Semantic check (의미상 오류)

③ 권한 검사

④ SQL과 실행계획이 Library Cache에 존재하는지 check

⑤ 존재하면 Execution 단계로 점프 (Soft Parsing), 존재하지 않으면 Optimization (Hard Parsing)

˚Soft Parsing : SQL과 실행계획을 Library Cache에서 찾아 바로 실행

˚Hard Parsing : SQL과 실행계획이 캐싱되어 있지 않아 최적화 한 후 실행

▼ Library Cache 저장 구조

* Hash Bucket = Hash Function(SQL Text)

   Library Cache Latch를 획득한 후 LCO (Library Cache Object) Handle 탐색 (같은 SQL 문장이 있는지..)

- Optimization

① Query Transformer : 사용자가 던진 SQL을 그대로 최적화하는 것이 아니라 우선 최적화하기 쉬운 형태로 변환을 시도, 쿼리 변환 전후 결과가 동일함이 보장되어야 함은 필수

② Estimator : Query Operation 각 단계의 선택도, 카디널리티, 비용을 계산하고, 궁극적으로는 실행계획 전체에 대한 총 비용을 계산. 각 단계를 수행하는데 필요한 I/O, CPU, 메모리 사용량 등을 예측하기 위해 database object (테이블, 인덱스 등) 통계정보와 하드웨어적인 시스템 성능 통계정보 (CPU 속도, Single Block Read TIme, Multiblock Read Time 등)을 이용

* Cost (비용) = blevel (leaf 전까지 읽게 될 branch 블록 개수) // 인덱스 수직적 탐색 비용

   + (Leaf Block 수 x 유효 인덱스 선택도) // 인덱스 수평적 탐색 비용

   + (C·F x 유효 테이블 선택도) // 테이블 Random access 비용

* C·F (Clustering Factor) : 데이터가 모여있는 정도

- 인덱스를 순차적으로 읽어 이전 RowID block과 다음 RowID block이 다를 때 +1

→ 좋으면 block 수에 근접, random access 효율↑, Buffer Pinning 효과↑

→ 나쁘면 row 수에 근접, random access 효율↓

* Buffer Pinning : 다음 RowID가 현재와 같은 block을 가리킬 경우 대상 block이 age out 되지 않도록 pin을 걸어두고 해당 DBA를 PGA에 저장하여 바로 찾아가는 기법

③ Plan Generator : 하나의 Query를 수행하는 데 있어, 후보군이 될만한 실행계획들을 생성

- Row-Source Generator

  Optimizer가 생성한 실행계획을 실행가능한 형태로 포맷팅

- Execution

  포맷팅한 실행계획 순으로 SQL 실행