옵티마이저의 최적화와 목표 그리고 한계

전체 처리 속도 최적화 #

쿼리 최종 결과집합을 끝까지 읽는 것을 전제로, 시스템 리소스를 가장 적게 사용하는 실행 계획을 선택한. 대부분 DBMS의 기본 옵티마이저 모드는 전체 처리속도 최적화에 맞춰져있다.

전체 결과집합 중 일부만 읽다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다. 만약 이 모드에서 생성한 실행계획으로 데이터를 끝까지 읽는다면 전체 처리속도 최적화 실행계획보다 더 많은 리소스를 사용하고 수행 속도도 더 느려질 수 있다.

SQL과 연산자 형태 결과가 같더라도 SQL을 어떤 행태로 작성했는지 또는 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있다.
옵티마이징 팩터 쿼리를 똑같이 작성하더라도, 인덱스, IOT, 클러스터링, 파티셔닝 등을 어떻게 구성했는지에 따라 실행 계획성과 성능이 크게 달라진다.
DBMS 제약 설정 개체는 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS가 제공하는 PK,FK,Check,NotNull 같은 제약 설정 기능을 이용할 수 있고, 이들 제약 설정은 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공한다. 예를 들어 인덱스 컬럼에 NotNull 제약이 설정돼 있으면 옵티마이저는 전체 개수를 구하는 Count 쿼리에 이 인덱스를 활용할 수 있다.
옵티마이저 힌트 옵티마이저의 판단보다 사용자가 지정한 옵티마이저 힌트가 우선한다.
통계 정보 통계 정보가 옵티마이저에게 미치는 영향력은 절대적이며, CBO의 모든 판단 기준은 통계 정보에서 나온다.
주요 통계 정보들에는 다음과 같은 것들이 있다.

테이블
- 테이블 전체 행의 갯수
- 테이블이 차지하고 있는 전체 블록 갯수
- 테이블의 행들이 가지고 있는 평균 길이
컬럼
- 컬럼 값의 종류
- 컬럼 내부 NULL값의 분포도
- 컬럼 값의 평균 길이
- 컬럼 내부 데이터 분포의 추정치
인덱스
- LEAF BLOCK 수: 데이터를 보관하는 블록 수
- LEVELS: 인덱스 트리의 LEVEL 정보
- CLUSTERING FACTOR: 접근하고자 하는 데이터가 모여 있는 밀집도
시스템 통계
- I/O 성능 및 사용률
- CPU 성능 및 사용률

옵티마이저 관련 파라미터 SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일하더라도 DBMS 버전을 업그레이드함녀 옵티마이저가 다르게 작동할 수 있다. 이는 옵티마이저 관련 파라미터가 추가 또는 변경되면서 나타나는 현상이다.
DBMS 버전과 종류 옵티마이저 관련 파라미터가 같더라도 버전에 따라 실행계획이 다를 수 있다. 또한, 같은 SQL이더라도 DBMS 종류에 따라 내부적으로 처리하는 방식이 다를 수 있다.

옵티마이저가 사람이 만든 소프트웨어 엔진에 불과하기 때문에 결코 완벽할 수 없다.

현재의 기술 수준으로 해결하기 어려운 문제가 있는가 하면, 기술적으론 가능한데 현실적인 제약 때문에 아직 적용하지 못하는 것들도 있다.

따라서 옵티마이저를 맹신하지 않아야 하며 옵티마이저가 비효율적으로 동작하고 있다면 오라클의 힌트와 같은 부가적인 장치로 올바르게 작동하도록 유도해야 한다.

옵티마이저가 완벽하지 못하게 만드는 요인들은 다음과 같다.