옵티마이저란?

DBMS 옵티마이저란?

DBMS에는 개발자가 작성한 SQL을 어떻게 실행할 것인지 실행 계획을 수립하고 SQL을 실행하게 됩니다. 옵티마이저는 이러한 실행 계획을 세워주는 역할을 수행합니다. 옵티마이저를 한 문장으로 정의하자면, SQL을 가장 빠르고 효율적으로 수행할 최적의 경로를 생성해주는 DBMS 내부 엔진입니다.

 

 

옵티마이저의 SQL 최적화 과정은 다음과 같은 순서로 이루어집니다.

 

- 사용자가 작성한 쿼리 수행을 위해 후보군이 될만한 실행계획을 찾는다

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

- 각 실행계획을 비교하여 최저 비용을 갖는 하나를 선택한다.

 

 

---

옵티마이저의 종류

 

옵티마이저의 종류에는 크게 2가지가 있습니다. 규칙기반 옵티마이저(RBO)와 비용 기반 옵티마이저(CBO)입니다. 과거에는 규칙 기반 옵티마이저를 사용하기도 했지만 현재 대부분의 데이터베이스는 비용 기반 옵티마이저를 사용합니다. 단순히 몇 개의 규칙만으로 복잡한 현실의 모든 사항을 정확히 예측할 수 없기 때문입니다. 

 

먼저 규칙 기반 옵티마이저 부터 살펴보도록 하겠습니다.

 

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

규칙기반 옵티마이저는 규칙을 가지고 실행계획을 생성합니다. 따라서 실행 계획을 생성하는 규칙을 이해한다면, 누구나 실행 계획을 비교적 쉽게 예측할 수 있습니다.

 

옵티마이저가 실행계획을 생성할 때 참조하는 정보는 인덱스 유무와 종류, SQL에서 사용하는 연산자, 그리고 참조하는 테이블의 종류등이 있습니다. 이러한 정보에 따라 규칙이 정해져 있고 규칙의 우선순위를 기반으로 실행계획을 생성합니다. 과거 Oracle db는 15가지의 옵티마이저 규칙으로 작동하였습니다.

몇 가지 규칙에 대해서 살펴보자면,

 

규칙 1. Single row by row id 

해당 규칙은 Row id를 통해서 테이블에서 하나의 행을 액세스 하는 방식입니다. 일반적으로 데이터베이스 테이블에는 테이블 스캔이나 인덱스 스캔으로 전체 테이블을 스캔하는 것보다 특정 Row Id를 활용하여 접근하는 방식이 훨씬 효율적입니다. 

 

규칙 15. Full Table Scan

이름에서도 알 수 있듯이 전체 테이블을 액세스 하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출하는 방식입니다.

 

저는 이 지점에서 궁금했던 점이 전체 테이블을 조회하는 경우에도 'Single row by row Id' 1번 규칙이 적용된다면 너무 비효율적인게 아닌가? 라는 의구심이 들었습니다. 하지만 그렇게 작동하진 않고 각 규칙에는 특정한 패턴 또는 조건을 지니고 있어서 해당 패턴 또는 조건에 만족하면 해당 규칙이 실행되는 원리라고 합니다.

 

 

 

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

비용기반 옵티마이저는 말 그대로 비용을 기반으로 최적화를 수행합니다. 즉 SQL문을 처리하는데 피룡한 비용이 가장 적인 실행계획을 실행하는 방식인 것이죠. 여기서 비용은 쿼리를 수행하는데 소요되는 시간과 자원의 양을 뜻합니다. 이러한 비용은 여러 통계 정보들을 바탕으로 산정됩니다. 통계 정보는 크게 두가지로 나뉘어집니다.

 

- 객체 통계 정보

객체 통계 정보는 규칙 기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 칼럼 등 다양한 통계 정보를 말합니다. 또한 행 수, 칼람 값의 분포, 인덱스 카디널리티 등도 포함됩니다. 객체 통계 정보는 주기적으로 업데이터 되어야 정확한 실행 계획을 선택할 수 있습니다.

 

- 시스템 통계 정보

시스템 통계 정보는 데이터베이스 서버의 성능과 리소스에 대한 통계 정보입니다. 시스템 통계 정보에는 CPU속도, I/O 성능, 메모리 용량과 같은 서버의 물리적인 특성이 포함됩니다. 시스템 통계 정보는 주기적으로 수집되어야 하며 데이터 베이스 서버의 성능이 변경될 때는 업데이트 되어야 정확한 비용 추정이 가능합니다.

 

비용기반 옵티마이저는 다양한 실행 계획을 세우고 위의 통계 정보를 바탕으로 하여 각각의 실행계획의 비용을 산정한다음 비용이 낮은 실행 계획을 최종적으로 선택하여 실행하게 됩니다. 

 

 

 

이상으로 데이터베이스 옵티마이저에 대한 정리를 마치겠습니다.

 

글 읽어주셔서 감사합니다!

 

---

참고자료

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