[바미] 옵티마이저(Optimizer)란 무엇인가?

옵티마이저(Optimizer)란?

옵티마이저는 데이터 베이스가 SQL 쿼리를 '어떻게' 실행할지를 결정(최적화)하는 엔진입니다.

 

개발자가 작성한 쿼리를 보고 어떤 순서로 테이블에 접근할 지, 어떤 인덱스를 사용할 지, 어떤 조인 방식을 쓸 지, 등등을 가장 빠르고 적은 자원을 사용하는 방법으로 결정해주죠.

 

데이터베비스 내부에서 쿼리가 실제로 실행되기 전에 '이 쿼리를 어떤 플랜(Plan)으로 실행하면 가장 비용(Cost)이 적을까?'를 계산하고, 이것에 따른 실행 계획(Execution Plan)을 생성해주는데 이 과정을 수행하는 것이 오늘 알아 볼 옵티마이저(Optimizer)입니다.

---

옵티마이저(Optimizer)가 SQL을 실행하는 과정

옵티마이저(Optimizer)는 크게 SQL 작성 -> 구문 분석(Parsing) -> 최적화(Optimizer) -> 실행(Execution) -> 결과 반환 형태로 진행됩니다. 

 

SQL 작성

애플리케이션에서 SELECT * FROM 테이블 WHERE 조건 처럼 쿼리를 입력하면 DB 서버로 전송됩니다.

구문 분석(Parsing)

이 단계에서는 DB는 먼저 구문(syntax)적 오류가 있는 지 등을 확인하기 위해 파서가 쿼리를 해석해줍니다.

옵티마이저의 최적화(Optimizer)

이 단계에서는 파서가 정상이라고 판단한 쿼리를 넘겨받아, 어떤 방식으로 실행할 것인지를 결정하는 단계입니다.

 

파싱된 SQL이 옵티마이저로 넘어가게 되는데 옵티마이저는 여러가지 통계정보(Statistics), 시스템 파라미터 등을 토대로 가장 최적의 실행 계획 시뮬레이션 하며 비용을 계산하게 됩니다.

 

가장 낮은 비용을 가지는 플랜(Plan)을 최적 실행 계획으로 선택하게 됩니다.

실행(Execution) 

최종적으로 옵티마이저가 만든 최적화된 실행 계획에 따라 DB엔진이 실제 데이터를 읽고, 필터링하고, 조인하는 과정을 거쳐 결과를 반환 하는 단계입니다.

 

그리고 최종적으로 결과를 사용자에게 반환하게 되죠.

결과 반환

사용자(애플리케이션, SQL 클라이언트)에 결과셋이 전송되어 완료됩니다.

 

정리하면 우리가 SQL을 실행하는 행위는 사실 파서 -> 옵티마이저 -> 실행기를 거치는 일련의 프로세스이며, 그 중심에서 어떤 방식으로 데이터를 조회할 지를 결정해주는 것이 옵티마이저 역할이라는 점을 기억해야 합니다.

---

옵티마이저의 역할

옵티마이저는 크게 계획 수립과 비용(Cost) 계산을 합니다. 자세하게 살펴보겠습니다.

 

계획 수립

데이터를 가져올 때, 가능한 여러 경로(Plan) 중 가장 적은 비용이 드는 방법을 찾습니다. 예를 들어, 인덱스를 쓸 수도 있고, 테이블 풀 스캔(Table Full Scan)을 할 수도 있고, 해시 조인(Hash Join)을 할 수도 있고, 네스티드 루프 조인(Nested Loop Join)을 할 수도 있습니다. 이런 수 많은 옵션 중에서 최적을 찾게 되죠.

 

비용(Cost) 계산

옵티마이저는 내부적으로 '비용'이라는 개념을 계산하는데, 이는 CPU 사용량, 디스크 I/O 횟수, 메모리 사용량 등을 추정하여 점수화한 값입니다.

 

당연히 위에서 언급한 비용이 낮은 실행 계획이 선택되죠.

 

옵티마이저가 확인하는 통계 정보(Statistics)는 무엇인가?

위 '옵티마이저(Optimizer)가 SQL을 실행하는 과정'의 최적화 단계에서 언급된 통계 정보에 대해 살펴보려 합니다.

옵티마이저는 데이터 분포나 테이블의 크기 등을 알수록 더 정확한 비용 계산이 가능하게 됩니다. 

 

대표적으로 테이블 통계, 인덱스 통계, 칼럼 통계, 시스템 통계, 카디널리티(Cardinality), Rows(예상 건수), 등이 있는데 각각 살펴보겠습니다.

테이블 통계

테이블 통계에는 전체 레코드(Row)수, 블록(Block) 수, 평균 로우 길이(Row Length)같은 기본적인 정보와 함께 특정 컬럼 값이 어떤 범위에 얼마나 분포되어 있는지를 파악할 수 있는 빈도(히스토그램)정보가 포함됩니다.

인덱스 통계

인텍스 통계는 인덱스의 구조와 효율성을 보여주는 B-Tree 레벨, 리프(Leaf) 블록 수, 인덱스를 통해 조건을 얼마나 잘 걸러낼 수 있는 지를 나타내는 선택도(Selectivity)등을 담고 있습니다.

칼럼 통계

칼럼 통계에는 특정 칼럼의 최대/최소/평균 값, 고유 값(Distinct) 개수, NULL 값의 개수, 히스토그램 정보가 포함되어 보다 세밀한 데이터 분포 파악이 가능합니다.

시스템 통계

시스템 통계는 CPU 속도, I/O 처리량과 같은 DB 인스턴스의 전반적 리소스 상태를 측정하여, 실제 쿼리 실행 비용 산정에 기여 합니다.

카디널리티(Cardinality)

옵티마이저가 연산에서 예상되는 결과 행의 수를 추정한 값입니다. 예를 들어 특정 컬럼의 값 분포(히스토그램)나 전체 로우 수, 필터 조건 등을 종합해 '필터 후 몇 건이 남을 것이다'를 계산합니다.

 

실제 실행 계획(Execution Plan)에서도 ROWS(또는 EST.ROWS)라는 형태로 표시되는 것이 이 카디널리티의 추정치가 됩니다.

Rows(예상 건수)

계획 단계별로 '얼마나 많은 건(row)이 나올 것인가?'를 나타내는 숫자를 의미하는데 옵티마이저가 추정한 카디널리티를 그대로 표시하는 항목을 의미합니다.

 

이러한 예상 행의 수(카디널리티, Rows)가 정확하지 않으면 옵티마이저는 조인 순서나 인덱스 사용 여부 등에서 잘못된 비용 계산을 할 수 있어 비효율적인 플랜을 선택할 수 있습니다.

 

그래서 정확한 테이블/컬럼 통계를 주기적으로 갱신해 주어야 옵티마이저가 정확한 카디널리티를 추정하고, 올바른 실행 계획을 세울 확률이 높아집니다.

CBO(Cost-Based Optimizer)와 RBO(Rule-Based Optimizer)

CBO(Cost-Based Optimizer)

비용 기반 옵티마이저 라고 부르며 현재 대부분의 Oracle DB에서 사용하는 표준 옵티마이저 입니다.

 

테이블, 인덱스, 컬럼 등의 통계 정보를 바탕으로 하여 비용(Cost)이 가장 낮은 실행 계획을 선택합니다.

 

데이터 분포가 달라지면 통계를 업데이트 한 후, 자동으로 그 분포에 맞는 최적의 플랜을 다시 찾을 수 있기 때문에 DB 관리자 (또는 DBA)는 정기적으로 이 통계를 갱신하여 CBO가 정확한 결정을 내릴 수 있도록 해야 합니다.

 

통계정보가 오래 되었을 때 기대를 하기 어렵다는 점을 주의해야 합니다.

RBO(Rule-Based Optimizer)

룰 기반 옵티마이저 라고 부르는데 과거 Oracle 7 이전(또는 Oracle 8까지도 호환) 시절에 주로 사용되던 방법으로 쿼리를 최적화하는 규칙이 고정된 우선순위에 따라 적용되는 방식입니다.

 

예를 들어 '인덱스 스캔이 데이터 풀 스캔보다 좋다'던지, '이 테이블이 무조건 크기가 작으니 드라이빙 테이블로 쓰고 이 테이블하고 이 키로 조인해야지' 등으로 강제 시킬 수 있습니다.

 

주로 테스트 서버에서 (혹은 가끔 라이브 서버에서) 내가 최신 통계정보를 정확히 알고 있고, 통계 정보 갱신이 안되서 옵티마이저가 계속 오판하는 경우 또는 갱신은 오래 걸려 급하게 처리해보기 위해 사용 합니다.

 

하지만 내가 정확히 알고 있다고 생각하는 테이블이 정확히 아는 게 아니라면 이는 독이 됩니다.

 

CBO와 RBO는 추후 자세히 다뤄보도록 하겠습니다.

---

추가로 알면 좋은 부분들

통계 정보 갱신

CBO가 제대로 작동하기 위해서는 최신 통계 정보가 매우 중요합니다. 운영 중인 데이터의 양이 급증하거나, 특정 칼럼 분포가 크게 변했는데 통계가 오래되었을 때 옵티마이저가 잘못된 비용 계산을 할 수 있습니다.

 

따라서 이 때는 ANALYZE나 DBMS_STATS 패키지 등을  사용해 주기적으로 통계를 업데이트 해야 합니다.

실행 계획 확인

옵티마이저가 만든 실행 계획(Execution Plan)을 확인할 줄 알아야, 내가 작성한 쿼리가 '인덱스를 쓰고 있는 지, 풀 스캔을 하는지, 조인은 어떤 방식으로 하는지' 등을 알 수 있습니다.

 

Oracle의 경우 EXPLAIN PLAN FOR ... 문 또는 AUTOTRACE 기능, SQL Delveloper의 실행 계획 보기 기능을 사용하면 확인 할 수 있습니다.

힌트(Hint)의 사용

옵티마이저가 항상 완벽하지는 않으므로, 때로는 개발자가 직접 '이 인덱스를 사용해라' 등과 같은 힌트를써서 유도하기도 하는데 이 힌트는 유지보수나 환경 변화에 따라 부작용이 생길 수 있기 때문에 필요할 때만 최소한으로 사용하는 것을 권장합니다.

RBO는 이제 대부분 사용하지 않는다.

Oracle 10g 이후부터는 RBO를 공식적으로 지원하지 않습니다. 오래된 시스템이나 구 버전에서는 RBO 모드가 남을 수 있지만 일반적으로는 CBO만 사용한다고 보면 됩니다.

 

마치며..

정리하면 옵티마이저(Optimizer)는 데이터베이스에서 SQL 쿼리가 들어왔을 때 가장 효율적인 방법으로 데이터를 읽어오는 실행 계획을 세워주는 두뇌 같은 역할을 합니다.

 

이 실행 계획을 세우기 위해 필요한 것이 테이블과 인덱스, 시스템 환경 등에 대한 통계 정보이며, 이를 바탕으로 비용(Cost)을 계산해 최적의 방안을 찾게 됩니다.

 

우리는 쿼리를 작성 할 때 바로 데이터가 실행되어 조회되는 것처럼 보이지만, 내부에서는 옵티마이저를 거쳐서 실제 실행이 이루어지게 되는 것이죠.

 

과거에는 RBO 방식도 있었지만, 현재는 CBO가 사실상 표준이며, 통계를 기반으로 훨씬 정교한 최적화가 가능하게 되죠.

 

이 과정을 잘 이해하면 쿼리 튜닝을 할 때 왜 통계 정보가 중요한 지, 왜 실행 계획을 살펴봐야 하는 지가 명확해지겠죠? 옵티마이져에 대해 공부하실 때 해당 글이 도움되셨길 바랍니다.