[DB]옵티마이저(Optimizer)란?

옵티마이저(Optimizer)란?

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

컴퓨터 두뇌가 CPU라면, DBMS의 두뇌는 Optimizer입니다. 개발자가 SQL을 작성하고 실행하면 소프트웨어 실행 파일처럼 즉시 실행되는 것이 아니라 옵티마이저(Optimizer)에서 "이 쿼리문을 어떻게 실행 시키겠다" 라는 여러가지 실행 계획을 세우게 됩니다. 이렇게 실행 계획을 세운 뒤 시스템 통계정보를 활용하여 각 실행 계획의 예상 비용을 산정한 후 각 실행 계획을 비교하여 최고의 효율을 가지고 있는 실행 계획을 판별한 후 그 실행 계획에 따라 수행하게 됩니다.

 

---

옵티마이저의 종류

옵티마이저는 규칙 기반 옵티마이저와 비용 기반 옵티마이저로 나뉩니다.

 

 

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

Oracle 8 이하의 버전에서 기본으로 설정된 옵티마이저가 바로 규칙 기반 옵티마이저입니다. 이 규칙 기반 옵티마이저는 말 그대로 실행 속도가 빠른 순으로 규칙을 먼저 세워두고 우선순위가 앞서는 방법을 채택하는 것입니다. 과거에는 옵티마이저의 비용을 예측하는 능력이 그다지 좋지 않아 이러한 방식을 사용하였습니다. 규칙의 우선순위는 아래와 같습니다.

 

위 우선순위를 참고하면 쿼리문을 효율적으로 작성하는데 큰 도움이 되니 꼭 학습하는 것을 추천드립니다.

이렇게 규칙 기반 옵티마이저는 우선 순위가 있기 때문에 옵티마이저에서 실행 계획이 세워지는 것을 미리 예측할 수 있는 장점이 있고 조금 응용하면 내가 원하는 대로 쿼리문이 실행되도록 유도할 수 있습니다.

 

하지만, 테이블에 데이터가 몇 개 없을 경우 전체 조회를 한다고 하면 FULL SCAN이 더 빠를 수 있는데 INDEX를 확인하고 타버리던지 하는 비효율적인 실행 계획이 도출될 수 있습니다. 또한, 옵티마이저의 실행 계획을 유도할 수 있는 힌트와 HASH JOIN의 경우에는 규칙 기반 이후에 나온 개념들이기 때문에 RBO에선 사용할 수 없습니다.

 

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

최근에 많이 사용되는 옵티마이저 방식입니다. Oracle 10 이후부터 공식적으로 비용 기반 옵티마이저만 사용하고 있습니다. 비용 기반 옵티마이저는 옵티마이저에서 실행 계획을 세운 뒤(최대 2천 개까지) 비용이 최소한으로 나온 실행 계획을 수행합니다.

비용 기반 옵티마이저는 비용을 예측하기 위해서 테이블, 인덱스, 칼럼 등의 다양한 객체 통계 정보와 시스템 통계 정보를 이용합니다. 통계 정보가 없는 경우 비효율적인 실행 계획을 생성할 수 있기 때문에 정확한 통계 정보를 유지하는 것이 중요합니다.

비용 기반 옵티마이저의 여러가지 모드

• CHOOSE: SQL이 실행되는 환경에서 통계 정보를 가져올 수 있으면 비용 기반으로 그렇지 않다면 규칙 기반으로 작동시키는 모드입니다.
• FIRST_ROWS: 옵티마이저가 처리 결과 중 첫 건을 출력하는데 걸리는 시간을 최소화할 수 있는 실행 계획을 세우는 모드입니다.
• FIRST_ROWS_n: SQL의 실행 결과를 출력하는 데까지 걸리는 응답 속도를 최적화하는 모드입니다.
• ALL_ROWS: SQL 실행 결과 전체를 빠르게 처리하는데 최적화 된 실행 계획을 세우는 모드입니다. 마지막으로 출력 될 행까지 최소한의 자원을 사용하여 최대한 빨리 가져오게 하며 Oracle 10g 이후, 이 모드가 기본 값으로 설정되어 있습니다.

---

옵티마이저 동작 방식

• Parser: SQL 문장을 분석하여 문법 검사와 구성요소를 파악하고 이를 파싱하여 파싱 트리를 만듭니다.
• Query Transformer: 파싱된 SQL을 보고 같은 결과를 도출하되 좀 더 나은 실행 계획을 갖는 SQL로 변환이 가능한지 판단하여 변환 작업을 수행합니다.
• Esimator: 시스템 통계 정보를 Dictionary로부터 수집하여 SQL을 실행할 때 소요되는 총 비용을 계산합니다.
• Plan Generator: Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행 계획을 도출합니다.
• ROW-Source Generator: 옵티마이저가 생성한 실행 계획을 SQL 엔진이 실제 실행할 수 있는 코드나 프로시저 형태로 포맷팅합니다.
• SQL Engine: SQL을 실행합니다.

---

옵티마이저를 최적으로 활용하기 위해 다루어야할 내용들

이와 같이 옵티마이저는 SQL 성능 향상에 있어 매우 중요한 영향을 미칩니다. 이러한 옵티마이저를 좀 더 효율적으로 사용해주기 위해 여러가지 내용들을 고려해야 합니다.

통계 정보

비용 기반 옵티마이저(CBO)에선 실행 계획 도출 시 DBMS에서 제공하는 통계 정보를 사용합니다. 이러한 통계 정보는 꾸준히 갱신되고 있는 것이 좋습니다. 통계 정보의 생성 주기 및 수행 시간을 스케줄러에 일괄적으로 등록하여 일정 주기마다 통계 정보를 수집하고 종료하도록 자동 통계 정보를 생성할 수도 있고 사용자가 직접 수동으로 통계 정보를 생성할 수도 있습니다. DBMS_STATS 패키지를 사용하면 데이터베이스, 스키마 및 계정, 테이블 인덱스 단위로 구분하여 정보를 수집할 수 있습니다.

주요 통계 정보들

 

옵티마이저에 영향을 줄 수 있는 파라미터

옵티마이저가 실행 계획을 결정할 때 파라미터의 값도 매우 중요한 영향을 미칩니다. 예를 들어 OPTIMIZER_MODE는 옵티마이저의 모드를 결정하는 파라미터인데 중간에 옵티마이저가 변경되면 실행 계획이 꼬일 수 있습니다. 이와 같이 직접적으로 영향을 줄 수 있는 파라미터들은 개발하고자 하는 방향으로 설계 단게에서 최적화로 설정하고 바꾸지 않는 것이 좋습니다.

 

옵티마이저는 만능이 아니다

옵티마이저의 성능은 점차 향상되고 있긴 하지만, 결코 만능이 아닙니다.

칼럼의 통계 정보만 가지고 조건절에서 사용된 조건을 만족하는 데이터의 양이 어느 정도인지 알 수 없기 때문에 비용 계산 결과가 정확하지 않습니다. 그리고 비용 산정 시 쿼리문 단독으로 실행된다고 가정하기에 운영 서버와 같이 쿼리가 동시 실행된다면 실제 비용은 달라질 수 있기도 하고 히스토그램 버킷이 최대 254까지만 사용할 수 있어 254개 이상의 값을 갖는 컬럼의 경우 비용 예측 결과가 정확하지 않는 등의 옵티마이저에 여러가지 빈틈들이 많습니다.

절대 옵티마이저를 맹신하면 안됩니다. 쿼리 튜닝을 할 때에는 쿼리 문의 실행계획을 꼭 보시고 옵티마이저가 비효율적으로 작동하고 있다면 오라클의 힌트 같은 부가적인 장치를 통해 올바르게 작동될 수 있도록 유도하는 방법도 필요합니다. 예를 들어 SQL의 바인드 변수의 경우 바인드 변수에 입력될 값의 예측이 가능하다면 입력받을 값에 따라 적절한 힌트를 사용할 수 있겠습니다.