옵티마이저

✨ 옵티마이저?

옵티마이저(Optimizer)는 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진이다. 사용자가 구조화된 질의언어(SQL)로 결과집합을 요구하면, 이를 생성하는데 필요한 처리경로는 DBMS에 내장된 옵티마이저가 자동 생성해준다. 이렇게 생성된 경로를 실행계획(Execution Plan)이라고 부르며, 최적화 과정은 다음과 같다.

 

1) 사용자가 던진 쿼리 수행을 위해, 후보군이 될만한 수행계획을 찾는다

2) 데이터 딕셔너리(Data Dictionary)에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.

3) 각 실행계획을 비교해서 최저비용을 갖는 하나를 선택한다.

 

기본적인 옵티마이저에 대한 정리는 여기에 있음

2024.04.04 - [DBMS] - 옵티마이저와 실행계획

옵티마이저와 실행계획

 

 

---

 

 

🪡 옵티마이저 행동에 영향을 미치는 요소

 

1) SQL과 연산자 형태

결과가 같더라도 SQL을 어떤 형태로 작성했는지, 또는 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 이는 쿼리 성능에 영향을 미친다.

2) 옵티마이징 팩터

쿼리를 똑같이 작성하더라도 인덱스, IOT, 클러스터링, 파티셔닝, MV 등을 어떻게 구성했는지에 따라 실행계획과 성능이 크게 달라진다.

3) DBMS 제약 설정

개체 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS가 제공하는 PK, FK, Check, Not null 같은 제약 설정 기능을 이용할 수 있고, 이들 제약 설정은 옵티마이저가 쿼리 성능을 최적화하는데에 매우 중요한 정보를 제공한다.

4) 옵티마이저 힌트

옵티마이저의 판단보다 사용자가 지정한 옵티마이저 힌트가 우선한다.

5) 통계정보

통계정보가 옵티마이저에게 미치는 영향력은 절대적이며, CBO의 모든 판단 기준은 통계정보에서 나온다.

6) 옵티마이저 관련 파라미터

SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일하더라도 DBMS 버전을 업그레이드하면 옵티마이저가 다르게 작동할 수 있다. 이는 옵티마이저 관련 파라미터가 추가 또는 변경되면서 나타나는 현상이다.

7) DBMS 버전과 종류

옵티마이저 관련 파라미터가 같더라도 버전에 따라 실행계획이 다를 수 있다. 또한, 같은 SQL이더라도 DBMS 종류에 따라 내부적으로 처리하는 방식이 다를 수 있다.

 

 

 

🧶 옵티마이저의 한계

 

옵티마이저는 사람이 만든 소프트웨어 엔진이기 때문에 완벽할 수 없다. 현재의 기술 수준으로 해결하기 어렵거나, 기술적으로는 가능하나 현실적인 제약때문에 아직 적용하지 못하는 것들도 존재한다. 옵티마이저의 한계에는 다음과 같은 것들이 있다.

 

1) 옵티마이징 팩터의 부족

옵티마이저는 주어진 환경에서 가장 최적의 실행계획을 수립하기 위해 정해진 기능을 수행하는 것 뿐이다. 옵티마이저가 아무리 정교하고 기술적 발전을 하더라도 사용자가 적절한 옵티마이징 팩터를 제공하지 않는다면 결코 좋은 실행계획을 수립할 수 없다.

2) 통계정보의 부정확성

최적화에 필요한 모든 정보를 수집해서 보관할 수 있다면 좋겠지만, 그렇게 하기는 현실적으로 불가능하다. 

3) 바인드 변수 사용 시 균등분포 가정

아무리 정확한 칼럼 히스토그램을 보유하더라도 바인드 변수를 사용할 경우에는 균등분포를 가정하고 비용을 계산한다.

4) 비현실적 가정

옵티마이저는 쿼리 수행 비용을 평가할 때 여러 가정을 사용하는데, 그 중 일부는 상당히 비현실적이라 이해할 수 없는 실행계획을 수립하곤 한다.

5) 규칙 의존 CBO

아무리 비용 기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다. 

6) 하드웨어 성능

옵티마이저는 기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰져 있다. 따라서, 실제 운영 시스템의 사양이 다를 때 옵티마이저가 잘못된 실행계획을 수립할 가능성이 높아진다.

 

 

 

🏹 통계정보를 이용한 비용계산 원리

 

실행계획을 수립할 때 CBO는 SQL 문장에서 엑세스할 데이터 특성을 고려하기 위해 통계정보를 이용한다. 최적의 실행계획을 위해 통계정보는 항상 데이터 상태를 정확하게 반영하고 있어야 하는 이유이기도 하다. 옵티마이저가 참조하는 통계정보 종류로는 테이블 통계, 인덱스 통계, 칼럼 통계, 시스템 통계 네가지가 있다. 이와 같은 통계정보는 다음과 같이 사용된다.

 

1) 선택도

선택도(Selectivity)는 전체 대상 레코드 중 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율을 말한다.

선택도를 가지고 카디널리티를 구하고, 다시 비용을 구해 인덱스 사용 여부, 조인 순서와 방법 등을 결정하므로 최적의 실행계획을 수립하는데 있어 가장 중요한 요인이라고 할 수 있다.

2) 카디널리티

카디널리티(Cardinality)는 특정 엑세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수를 말하며, 총 로우 수에 선택도를 곱하는 방식으로 구한다.

3) 히스토그램

미리 저장된 히스토그램 정보가 있으면, 옵티마이저는 그것을 사용해 더 정확하게 카디널리티를 구할 수 있다. 특히, 분포가 균일하지 않은 컬럼으로 조회할 때 효과를 발휘한다. 히스토그램은 도수분포 히스토그램, 높이균형 히스토그램 두가지로 나뉜다.

4) 비용

CBO는 비용 기반으로 최적화를 수행, 실행계획 생성을 진행하며 이것은 정확한 비용이 아닌 예상치이다. 여기에서 사용하는 비용 모델로는 예상되는 I/O 요청(Call) 횟수만을 쿼리 수행 비용으로 간주해 실행계획을 평가하는 I/O 비용 모델과, I/O 비용 모델에서 시간 개념을 더해 비용을 산정하는 CPU 비용 모델이 있다.