[SQL 튜닝] SQL 옵티마이저

선택도와 카디널리티

1. 1. 선택도 : 조건절에 의해 선택되는 레코드 비율
   • 선택도 = 1 / NDV(컬럼 값 종류 개수)
2. 카디널리티 : 전체 레코드 중 조건절에 의해 선택되는 레코드 수
   • 카디널리티 = 총 로우 수 * 선택도  = 총 로우 수 / NDV

• 옵티마이저는 카디널리티를 구하고, 데이터를 액세스하는데 드는 비용을 계산해서 데이블 액세스 방식, 조인순서, 조인방식 등을 결정
• 선택도를 제대로 계산하는 것이 중요

통계정보

1. 테이블 통계
2. 인덱스 통계
3. 컬럼 통계 : 테이블 통계 수집할 때 함께 수집.
   • '=' 조건에 대한 선택도는 데이터 분포가 균일하지 않은 컬럼에서는 잘못 산출될 수 있기 때문에 옵티마이저는 일반적인 컬럼통계 외에 히스토그램을 추가로 활용
4. 시스템 통계 : 애플리케이션 및 하드웨어 성능 특성 측정
   • CPU 속도
   • 평균적인 Single Block I/O 속도
   • 평균적인 MultiBlock I/O 속도
   • 평균적인 MultiBlock I/O rotn
   • I/O 서브시스템의 최대 처리량
   • 병렬 Slave의 평균적인 처리량

비용 계산 원리

• 단일 테이블을 인덱스로 액세스 할때의 비용 계산 원리
• 인덱스 키 값을 모드 '='조건으로 검색할 때는 아래와 같이 인덱스 통계만으로도 쉽게 비용 계산 가능
  • 비용 = 인덱스 수직적 탐색비용(BLEVEL) + 인덱스 수평적 탐색 비용(AVG_LEAF_BLOCKS PER_KEY) + 테이블 랜덤 액세스 비용(AVG_DATA_BLOCKS PER_KEY)
• 인덱스 키값이 모두 '=' 조건이 아닐 때
  • 비용 = 인덱스 수직적 탐색 비용(BLEVEL) + 인덱스 수평적 탐색 비용(LEAF_BLOCKS * 유효 인덱스 선택도) + 테이블 랜덤 액세스 비용(CLUSTERING_FACTOR * 유효 테이블 선택도)
  • BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR 는 인덱스 통계에서, 유효 인덱스 선택도와, 유효 테이블 선택도는 컬럼 통계 및 히스토그램 이용
  • 유효 인덱스 선택도 : 전체 인덱스 레코드 중 액세스 조거에 의해 선택될 것으로 예상되는 레코드 비중
  • 유효 테이블 선택도 : 전체 인덱스 레코드 중 인덱스 컬럼에 대한 모든 조건절에 의해 선택될 것으로 예상되는 레코드 비중

옵티마이저 종류

1. 비용 기반 옵티마이저(CBO)

• 사용자 쿼리를 위해 후보군이 될만한 실행계획을 도출하고 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용 산정하고, 그 중 가장 낮은 비용의 실행계획 하나를 선택
• 데이터량, 컬럼값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터

2. 규칙기반 옵티마이저(RBO)

• 각 액세스 경로에 대한 우선순위 규칙에 따라 실행계획을 만드는 옵티마이저
• 데이터 특성을 나타내는 통계정보를 전혀 활용하지 않고 단순 규칙에만 의존하기 때문에 대량 데이터 처리에 부적합
• 인덱스로 전체 레코드를 액세스하기 때문

옵티마이저 모드

최적화 목표 설정 기능

• ALL_ROWS : 전체 처리속도 최적화
• FIRST_ROWS : 최초 응답속도 최적화 BUT 앞으로 사라질 모드
• FIRST_ROWS_N : 최초 N건 응답속도 최적화 (앞쪽 일부만 읽다가 멈춤) 

옵티마이저에 영향을 주는 요소

1. SQL과 연산자 형태
   • 결과가 같더라도 SQL을 어떤 형태로 작성했는지, 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택할 수 있음
2. 옵티마이징 팩터
   • 인덱스, IOT, 클러스터, 파티션, MV 등을 구성했는지에 따라 실행계획과 성능이 달라짐
3. 제약설정
   • PK, FK, Check, NOT NULL 같은 제약들은 데이터 무결성을 보장해 줄 뿐만 아니라 옵티마이저가 쿼리 성능을 최적화하는데 매우 중요한 메타 정보로 활용
4. 통계정보
5. 옵티마이저 힌트
   • 힌트를 명령어로 인식하고 그대로 따름
6. 옵티마이저 관련 파라미터

옵티마이저의 한계

• 통계정보를 필요한 만큼 충분히 확보하는 것이 불가능
• 통계정보를 완벽히 수집해도 바인드 변수를 사용한 SQL에 컬럼 히스토그램을 활용할 수 없다는 단점도 있음
• 기본적으로 비용을 기반으로 작동하지만 내부적으로 여러 가정과 규칙을 이용해 기계적인 선택을 한다는 것도 한계

개발자의 역할

1. 필요한 최소블록만 읽도록 쿼리 작성
2. 최적의 옵티마이징 팩터를 제공
   • 옵티마이저 모드를 포함해 각종 파라미터를 적절한 값으로 설정하고 통계정보를 잘 수집하는 것이 중요
   • 전략적인 인덱스 구성이 필수
   • DBMS가 제공하는 기능(파티션, 클러스터, IOT, MV, RESULT CACHE 등)을 적극적으로 활용해 옵티마이저가 최적의 선택을 할 수 있도록 다양한 수단 제공
3. 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도