[DB] 데이터베이스 옵티마이저(Optimizer)란?

데이터베이스 옵티마이저란?

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

 

옵티마이저의 SQL 최적화 과정은 다음과 같다.

 

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

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

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

 

옵티마이저의 종류는 규칙 기반 옵티마이저와 비용 기반 옵티마이저가 있다.

 

 

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

규칙 기반 옵티마이저는 실행 속도가 빠른순으로 규칙을 세워두고 우선순위가 높은 방법을 채택하는 옵티마이저이다. 여기서 규칙이란 액세스 경로별 우선순위로서, 인덱스 구조, 연산, 조건절 형태가 순위를 결정짓는 주요인이다. 규칙의 우선순위는 다음과 같다. 

 

우선순위	설명
1	ROWID를 사용한 단일 행인 경우
2	클러스터 조인에 의한 단일 행인 경우
3	유일하거나 기본키(Primary key)를 가진 해시 클러스터 키에 의한 단일 행인 경우
4	유일하거나 기본키(Primary key)에 의한 단일 행인 경우
5	클러스터 조인인 경우
6	해시 클러스터 조인인 경우
7	인덱스 클러스터 키인 경우
8	복합 칼럼 인덱스인 경우
9	단일 칼럼 인덱스인 경우
10	인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
11	인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
12	정렬-병합(Sort-Merge) 조인인 경우
13	인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
14	인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
15	전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

 

 

 

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

비용 기반 옵티마이저는 말 그대로 비용을 기반으로 최적화를 수행한다. 여기서 '비용'이란 쿼리를 수행하는데 소요되는 일 량 또는 시간을 뜻한다. CBO는 실행 계획을 최대 2천 개까지 세운 뒤 비용이 최소한으로 나온 실행 계획을 수행하게 된다. 이때 실행계획을 수립할 때 판단 기준이 되는 비용은 예상치다. 따라서 CBO는 비용을 예측하기 위해 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보 및 시스템 통계정보(CPU 속도, 디스크 I/O 속도 등)를 이용한다. 

 

역사가 오래된 Oracle은 RBO로 시작했으나 다른 상용 RDBMS는 탄생 초기부터 CBO를 채택하였다. Oracle 또한 10g 버전부터는 RBO에 대한 지원을 중단하고 CBO를 채택하였다. 

 

 

 

옵티마이저의 최적화 목표

1. 전체 처리속도 최적화

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

 

2. 최초 응답속도 최적화

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

 

 

 

 

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

1. SQL과 연산자 형태

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

 

2. 옵티마이징 팩터

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

 

3. DBMS 제약 설정

개체 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS가 제공하는 PK, FK, Check, Not Null 같은 제약 설정 기능을 이용할 수 있고, 이들 제약 설정은 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공한다. 예를 들어, 인덱스 칼럼에 Not Null 제약이 설정돼 있으면 옵티마이저는 전체 개수를 구하는 Count 쿼리에 이 인덱스를 활용할 수 있다.

 

4. 옵티마이저 힌트

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

 

5. 통계 정보

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

주요 통계 정보들에는 다음과 같은 것들이 있다. 

 

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

 

 

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

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

 

7. DBMS 버전과 종류

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

 

 

 

옵티마이저의 한계

옵티마이저가 사람이 만든 소프트웨어 엔진에 불과하며 결코 완벽할 수 없음을 이해하는 것은 중요하다. 현재의 기술 수준으로 해결하기 어려운 문제가 있는가 하면, 기술적으론 가능한데 현실적인 제약 때문에 아직 적용하지 못하는 것들도 있다. 따라서 옵티마이저를 맹신하지 않아야 하며 옵티마이저가 비효율적으로 동작하고 있다면 오라클의 힌트와 같은 부가적인 장치로 올바르게 작동하도록 유도해야 한다.

 

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

 

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

옵티마이저는 주어진 환경에서 가장 최적의 실행계획을 수립하기 위해 정해진 기능을 수행할 뿐이다. 옵티마이저가 아무리 정교하고 기술적으로 발전하더라도 사용자가 적절한 옵티마이징 팩터(효과적으로 구성된 인덱스, IOT, 클러스터링, 파티셔닝 등)를 제공하지 않는다면 좋은 실행계획을 수립할 수 없다.

2. 통계정보의 부정확성

최적화에 필요한 모든 정보를 수집해서 보관할 수 있다면 옵티마이저도 그만큼 고성능 실행계획을 수립하겠지만, 100% 정확한 통계정보를 유지하기는 현실적으로 불가능하다. 특히, 칼럼 분포가 고르지 않을 때 칼럼 히스토그램이 반드시 필요한데, 이를 수집하고 유지하는 비용이 만만치 않다. 칼럼을 결합했을 때의 모든 결합 분포를 미리 구해두기 어려운 것도 큰 제약 중 하나다. 이는 상관관계에 있는 두 칼럼이 조건절에 사용될 때 옵티마이저가 잘못된 실행계획을 수립하게 만드는 주요인이다. 

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

아무리 정확한 칼럼 히스토그램을 보유하더라도 바인드 변수를 사용한 SQL에는 무용지물이다. 조건절에 바인드 변수를 사용하면 옵티마이저가 균등 분포를 가정하고 비용을 계산하기 때문이다.

4. 비현실적인 가정

옵티마이저는 쿼리 수행 비용을 평가할 때 여러 가정을 사용하는데, 그중 일부는 상당히 비현실적이어서 종종 이해할 수 없는 실행계획을 수립하곤 한다. 예전 Oracle 버전에선 Single Block I/O와 Multiblock I/O의 비용을 같게 평가하고 데이터 블록의 캐싱 효과도 고려하지 않았는데, 그런 것들이 비현실적인 가정의 좋은 예다. DBMS 버전이 올라가면서 이런 비현실적인 가정들이 계속 보완되고 있지만 완벽하지 않고, 모두 해결되리라고 기대하는 것도 무리다.

5. 규칙에 의존하는 CBO

아무리 비용 기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다. 예를 들어, 최적화 목표를 최초 응답속도에 맞추면 order by 소트를 대체할 인덱스가 있을 때 무조건 그 인덱스를 사용한다.

 

6. 하드웨어 성능

옵티마이저는 기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰져 있다. 따라서 실제 운영 시스템의 하드웨어 사양이 그것과 다를 때 옵티마이저가 잘못된 실행계획을 수립할 가능성이 높아진다. 또한 애플리케이션 특성(I/O 패턴, 부하 정도 등)에 의해서도 하드웨어 성능은 달라진다.

 

 

---

Reference

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

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