[SQL] 튜닝 - 옵티마이저🔥

통계 정보와 비용 계산 원리

선택도와 카디널리티

선택도 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율

 

선택도 = 1 / NDV         📍NDV (Number of Distinct Values) = 컬럼 값 종류 개수

 

카디털리티 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수

 

카디널리티 = 총 로우 수 X 선택도 = 총 로우 수 / NDV

 

통계정보

• 오브젝트 통계
  • 테이블 통계
  • 인덱스 통계
  • 컬럼 통계
• 시스템 통계

시스템 통계는 애플리케이션 및 하드웨어 성능 특성을 측정한 것이며, 아래 항목들을 포함한다.

• CPU 속도
• 평균적인 Single Block I/O 속도
• 평균적인 Multiblock I/O 속도
• 평균적인 Multiblock I/O 개수
• I/O 서브시스템의 최대 처리량
• 병렬 Slave의 평균적인 처리량

---

옵티마이저에 대한 이해

규칙 기반(Rule-Based) 옵티마이저 (RBO)

데이터 특성을 나타내는 통계정보를 활용하지 않고 단순 규칙에만 의존. 따라서 대량 데이터를 처리하는데 부적합하다.  (잘 안쓰임)

비용 기반(Cost-Based) 옵티마이저 (CBO)

데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 예상 비용을 산정하고, 그 중 가장 낮은 비용의 실행 계획 하나를 선택한다.

 

옵티마이저 모드

비용 기반 옵티마이저에는 최적화 목표를 설정하는 기능이 있다.

• ALL_ROWS : 전체 처리속도 최적화
• FISRT_ROWS : 최초 응답속도 최적화
• FIRST_ROWS_N : 최초 N건 응답속도 최적화

옵티마이저의 한계

같은 SQL인데도 DBMS에 따라, 버전에 따라 옵티마이저가 다른 실행계획을 생성한다.

그 선택이 항상 최선이 아님을 짐작할 수 있다.

기본적으로 비용 기반으로 작동하지만, 어느쪽이 나은지 판단하기 애매할 때 사용하는 가정과 규칙들이 현실과 맞지 않을 수 있다.

SQL최적화 기법이 많이 좋아졌고 발전하는 중이지만 옵티마이저는 불완전할 수밖에 없다.

개발자의 역할

• 필요한 최소 블록만 읽도록 쿼리를 작성한다.
• 최적의 옵티마이징 팩터를 제공한다.
• 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도한다.