옵티마이저(Optimizer)의 정의와 종류 각각의 장단점에 대해 설명하세요

옵티마이저(Optimizer)란?

옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진입니다.

https://coding-factory.tistory.com/743

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=364

+) 옵티마이저의 한계

옵티마이저가 사람이 만든 소프트웨어 엔진에 불과하며 결코 완벽할 수 없음을 이해하는 것은 중요하다. 현재의 기술 수준으로 해결하기 어려운 문제가 있는가 하면, 기술적으론 가능한데 현실적인 제약 때문에 아직 적용하지 못하는 것들도 있다. 따라서 옵티마이저를 맹신하지 않아야 하며 옵티마이저가 비효율적으로 동작하고 있다면 오라클의 힌트와 같은 부가적인 장치로 올바르게 작동하도록 유도해야 한다. 옵티마이저가 완벽하지 못하게 만드는 요인들은 다음과 같다. 1. 옵티마이징 팩터의 부족 옵티마이저는 주어진 환경에서 가장 최적의 실행계획을 수립하기 위해 정해진 기능을 수행할 뿐이다. 옵티마이저가 아무리 정교하고 기술적으로 발전하더라도 사용자가 적절한 옵티마이징 팩터(효과적으로 구성된 인덱스, IOT, 클러스터링, 파티셔닝 등)를 제공하지 않는다면 좋은 실행계획을 수립할 수 없다. 2. 통계정보의 부정확성 최적화에 필요한 모든 정보를 수집해서 보관할 수 있다면 옵티마이저도 그만큼 고성능 실행계획을 수립하겠지만, 100% 정확한 통계정보를 유지하기는 현실적으로 불가능하다. 특히, 칼럼 분포가 고르지 않을 때 칼럼 히스토그램이 반드시 필요한데, 이를 수집하고 유지하는 비용이 만만치 않다. 칼럼을 결합했을 때의 모든 결합 분포를 미리 구해두기 어려운 것도 큰 제약 중 하나다. 이는 상관관계에 있는 두 칼럼이 조건절에 사용될 때 옵티마이저가 잘못된 실행계획을 수립하게 만드는 주요인이다. 3. 바인드 변수 사용 시 균등 분포 가정 아무리 정확한 칼럼 히스토그램을 보유하더라도 바인드 변수를 사용한 SQL에는 무용지물이다. 조건절에 바인드 변수를 사용하면 옵티마이저가 균등 분포를 가정하고 비용을 계산하기 때문이다. 4. 비현실적인 가정 옵티마이저는 쿼리 수행 비용을 평가할 때 여러 가정을 사용하는데, 그중 일부는 상당히 비현실적이어서 종종 이해할 수 없는 실행계획을 수립하곤 한다. 예전 Oracle 버전에선 Single Block I/O와 Multiblock I/O의 비용을 같게 평가하고 데이터 블록의 캐싱 효과도 고려하지 않았는데, 그런 것들이 비현실적인 가정의 좋은 예다. DBMS 버전이 올라가면서 이런 비현실적인 가정들이 계속 보완되고 있지만 완벽하지 않고, 모두 해결되리라고 기대하는 것도 무리다. 5. 규칙에 의존하는 CBO 아무리 비용 기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다. 예를 들어, 최적화 목표를 최초 응답속도에 맞추면 order by 소트를 대체할 인덱스가 있을 때 무조건 그 인덱스를 사용한다. 6. 하드웨어 성능 옵티마이저는 기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰져 있다. 따라서 실제 운영 시스템의 하드웨어 사양이 그것과 다를 때 옵티마이저가 잘못된 실행계획을 수립할 가능성이 높아진다. 또한 애플리케이션 특성(I/O 패턴, 부하 정도 등)에 의해서도 하드웨어 성능은 달라진다.

jinsusong / CS-Study

옵티마이저(Optimizer)의 정의와 종류 각각의 장단점에 대해 설명하세요 #82