[1] SQL과 옵티마이저

(1) Optimizer

• 최소비용, 최적의 경로를 선택해서 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 프로시저를 자동으로 생성해내는 DBMS의 핵심 기능이다.
• 가장 효율적인 Execution Plan을 찾아내고자 한다.
• 사용자가 SQL을 실행하면 옵티마이저가 결과를 얻기 위해 실행계획 및 프로시저(프로그래밍)를 생성하고 사용자가 원하는 결과를 조회한다.

  ✅실행계획(Execution Plan)이란 ?

  옵티마이저에 의해 생성된 처리절차를 사용자가 확인할 수 있도록 트리구조로 표현한 것이다.

1. 옵티마이저의 한계

• Computing power의 부족
• Optimizing algorithm의 한계
• 제한된 시간 내에 방법을 결정해야 한다.
• User data에 대한 이해 부족
• 실행방법을 결정하는데 있어서 오판이 많다.

2. 옵티마이저의 SQL 최적화 수행 단계

1) 실행계획 후보군을 선별한다.
2) 실행계획의 예상비용 산정한다.
이 때 ⭐️ data dictionary에 미리 수집해 놓은 object 통계 및 시스템 통계정보를 이용한다.⭐️
3) 최소비용의 실행계획을 선정한다.

[2] SQL 처리과정

(1) SQL 파싱

1. SQL Parser의 역할(순서)

1. SQL 문장을 분석하고 parsing(tokenize)해서 parsing tree를 생성한다.
2. SQL 문장 검사
   • 먼저 SQL 문장에 문법적 오류가 없는지 검사(Syntax 검사)한다.
   • 문법적으로 오류가 없으면 존재하지 않거나 권한 없는 object 사용 혹은 존재하지 않은 컬럼 참조 등 확인하며 의미상 오류가 없는지를 검사(Semantic 검사)한다.
3. 해싱 알고리즘을 사용해 SQL cursor가 shared pool에 caching 되어 있는지 여부를 확인한다.
   (이 때 ,SQL ASCII 텍스트에 대한 숫자 값을 계산 하고, 해쉬 값으로 변환하는 방식을 사용한다.)
   • parsing 요청한 사용자, optimizer 관련 파라미터 설정 등을 확인하여 하나라도 다르면 새로운 SQL cursor을 생성한다.
   • shared pool에서 정확히 일치하는 SQL cursor를 찾은 경우 실행 단계로 넘어가며 이를 '소프트 파싱'이라 한다.
   • shared pool에서 매칭되는 SQL문을 못 찾았거나 새로운 SQL cursor 생성한 경우 SQL 최적화(SQL Optimization) 단계로 넘어가며 이를 '하드 파싱'이라한다.

(2) SQL 최적화

1. Query Transformation (쿼리 변환기)

   • 사용자가 던진 SQL문을 최적화하기 쉬운 형태로 변환한다.
   • ⭐️ 쿼리 변환 전후 결과가 동일할 경우에만 수행한다.

2. Plan Generator (실행계획 생성기)

   • 후보군이 될만한 실행계획들을 생성한다.

3. Estimator (비용계산기)

   • 실행계획 전체에 대한 총 비용을 계산한다.
   • 비용 측정 기준 : 선택도(selectivity), cardinality, 비용(cost)
     - 선택도(selectivity) : 처리할 집합에서 해당 조건이 만족되는 row가 차지하는 비율
     - cardinality : 판정대상이 가진 결과건수 혹은 다음단계로 들어가는 중간 결과건수
     - 비용(cost) : 실행계획상의 연산을 수행할 때 소요되는 시간비용을 상대적으로 계산한 예측치

     ✅ 선택도 계산

      select * from where empno='0001';

     테이블의 전체 row수는 200, 그 중에서 empno='0001'인 row수는 10건인 경우
     👉 선택도는 5%이고 cardinality는 10rows 이다.

4. 예상치 : 각 단계를 수행하는데 필요한 I/O, CPU, 메모리 사용량 등을 예측한다.

   • 통계 정보를 이용한다.
     • object 통계정보
     • 하드웨어적인 시스템 성능 통계정보
       ex ) CPU 속도, Single blcok read time, multiblock read time 등
   • object 혹은 시스템 통계정보는 오라클이 자동 수집 혹은 정책에 따라 주기적으로 수집된다.

5. 최적화 소요 시간 단축 전략

   • Adaptive search strategy : 예상 쿼리 수행 시간과 쿼리 최적화에 소요되는 시간이 일정비율을 넘지 않도록 적응적 탐색 전략을 사용한다.
   • Multiple Initial orderings heuristic
     • 조인 순서는 최적의 실행계획을 발견할 가능성이 높은 순서대로 비용을 산정해서 평가한다.
     • 조인 순서를 무순위로 평가하는게 아니라 최적의 실행계획을 발결한 가능이 높은 순서대로 비용을 평가하는 것이다.

(3) Row-Source Generation

• 생성된 실행계획을 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 작업이다.
• Row-Source는 사용자가 요구한 최종 결과집합을 실제적으로 생성하는데 사용되는 제어 구조이다.