[데이터베이스] SQL 성능의 나침반, 오라클 옵티마이저(Optimizer)와 통계 정보의 중요성

Optimizer

오라클 데이터베이스의 옵티마이저 통계(Optimizer Statistics)는 SQL 실행 계획을 수립하는 데 있어 내비게이션의 지도와 같습니다. 현실 세계에서 자동차를 운전하는 상황을 떠올려 보면, 바로 앞에 있는 마트까지 10분이면 갈 수 있는 최적의 경로가 존재하더라도 내비게이션이 없고 길을 모른다면 어떨까요? 어디로 가야 할지 판단하기 어려울 뿐만 아니라, 가까운 거리임에도 불구하고 길을 헤매다 30분 이상이 걸려 도착할 수도 있습니다. 또한 내비게이션이 틀렸거나 오래되었다면  엉뚱한 길을 선택하게 됩니다.

옵티마이저는 가장 효율적인 데이터 액세스 경로(Index Scan vs Full Scan 등)를 결정을 진행합니다.

데이터 딕셔너리(Data Dictionary)에 저장되며, 매번 실시간으로 통계를 내면 DB 부하가 너무 크기 때문에 정기적으로 수집합니다. 

 

 

Rule-Based vs Cost-Based

옵티마이저의 두 가지 방식이 존재합니다. (8버전은 거의 없다고 봐도 무방하니 CBO를 사용한다고 보면 됩니다.)

👉 규칙 기반(RBO, Rule-Based)
오라클 8 이하 버전에서는 규칙 기반 옵티마이저(RBO)가 기본이었으며, 이는 미리 정해진 규칙과 우선순위에 따라 가장 빠를 것이라 판단되는 실행 계획을 선택하는 방식입니다. 당시에는 비용 기반 옵티마이저의 예측 정확도가 낮았기 때문에 이러한 규칙 기반 방식이 사용되었습니다.


👉 비용 기반(CBO, Cost-Based)
오라클 10 이후 버전에서는 비용 기반 옵티마이저(CBO)만 사용되며, 여러 실행 계획(최대 2,000개)을 비교해 비용이 가장 낮은 계획을 선택합니다. CBO는 테이블/인덱스/컬럼 통계와 시스템 통계 등 다양한 통계 정보를 기반으로 비용을 예측하므로, 통계가 부정확하거나 없을 경우 비효율적인 실행 계획이 생성될 수 있습니다. 따라서 정확한 통계 정보를 유지하는 것이 매우 중요합니다.

 

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

수집되는 핵심 통계 데이터 입니다.

테이블/인덱스 크기: 얼마나 많은 블록을 차지하고 있는가?
행(Row) 수: 전체 데이터가 몇 건인가?
데이터의 밀도: 평균 행 크기는 얼마고, 데이터가 얼마나 골고루 퍼져 있는가?
시스템 통계: CPU 속도는 어떤지, I/O 전송률은 얼마인지 같은 서버의 물리적 특성.

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |    45 |  3600 |   158 (2)  |
|* 1 |  FILTER                              |                   |       |       |            |
|   2 |   HASH GROUP BY                      |                   |    45 |  3600 |   158 (2)  |
|* 3 |    HASH JOIN                         |                   |   120 |  9600 |   156 (1)  |
|* 4 |     TABLE ACCESS FULL                | CUSTOMERS         |   200 |  4000 |    12 (0)  |
|* 5 |     HASH JOIN                        |                   |  1500 | 90000 |   143 (1)  |
|   6 |      NESTED LOOPS                    |                   |   300 |  6000 |    45 (0)  |
|   7 |       NESTED LOOPS                   |                   |   300 |  6000 |    45 (0)  |
|   8 |        VIEW                          | VW_NSO_1          |    10 |   150 |     5 (0)  |
|   9 |         HASH UNIQUE                  |                   |    10 |   150 |     5 (0)  |
|  10 |          TABLE ACCESS FULL           | PREMIUM_CATS      |    10 |   150 |     3 (0)  |
|* 11 |        INDEX RANGE SCAN              | IX_PROD_CAT       |    30 |       |     2 (0)  |
|  12 |       TABLE ACCESS BY INDEX ROWID    | PRODUCTS          |    30 |   450 |     4 (0)  |
|* 13 |      TABLE ACCESS FULL               | ORDERS            | 50000 |  1.4M |    97 (1)  |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUM("O"."AMOUNT")>1000000)
   3 - access("C"."CUST_ID"="O"."CUST_ID")
   4 - filter("C"."REGION"='SEOUL')
   5 - access("O"."PROD_ID"="P"."PROD_ID")
  11 - access("P"."CATEGORY"="VW_NSO_1"."CAT_NAME")
  13 - filter("O"."ORDER_DATE">=TRUNC(SYSDATE, 'YYYY'))

 

실행 계획(Execution Plan)은 한마디로 SQL이라는 "주문서"를 받은 데이터베이스가 "실제로 데이터를 어떻게 찾아가겠다"라고 작성한 상세 설계도입니다.

사용자가 "서울에 사는 고객의 총 구매액을 알려줘"라고 요청하면, 옵티마이저는 CBO 과정을 거쳐 가장 효율적이라고 판단되는 경로를 선택하고 이를 실행 계획으로 출력합니다.

통계 정보는 데이터가 바뀔 때마다 실시간으로 갱신되지 않습니다. 그래서 실제 데이터와 통계 정보 사이에 괴리가 생기면 옵티마이저가 "비효율" 판단을 내리기도 합니다.

옵티마이저는 테이블의 전체 로우 수, 데이터의 분포도, 컬럼 등을 바탕으로 실행 계획을 세웁니다.

 

실제 데이터는 100만 건인데, 통계 정보는 1,000건으로 기록되어 있다면?
옵티마이저는 데이터가 적다고 판단하여 Full Table Scan이 유리하다고 착각하거나, 잘못된 인덱스를 선택하게 됩니다.

이러한 상황을 옵티마이저의 오판 (Bad Decision)이라고 부르며, 실제 운영 환경에서 매우 빈번하게 발생됩니다.

작은 테이블인 줄 알고 먼저 선행 테이블로 조인 했는데 알고 보니 대용량 테이블이라던가, 데이터가 거의 없다고 판단하여 인덱스를 타지 않고 전체 테이블을 읽거나 메모리에 다 올라갈 줄 알고 Hash Join을 선택했으나 데이터가 너무 많다거나

 

고급 DBA와 초급 DBA의 가장 큰 차이는 옵티마이저를 얼마나 "의도한 상태"로 동작시키고 있는가에 있습니다.

초급 DBA는 옵티마이저가 자동으로 실행 계획을 선택한다고 생각합니다.

고급 DBA는 옵티마이저가 주어진 통계와 환경에 의해 "유도된다"는 사실을 명확히 인지합니다.
즉, 실행 계획은 우연의 결과가 아니라 통제의 결과인 셈 입니다.

 

 

옵티마이저 의도한 상태로 실행하기

👉 통계 정보 수동 갱신

배치 작업으로 대량의 데이터가 입력/수정된 직후에는 즉시 통계 정보를 업데이트해야 합니다.

 

👉 SQL 힌트(Hint) 사용

통계 정보가 부정확하더라도 개발자가 데이터의 특성을 더 잘 안다면, 옵티마이저에게 직접 명령을 내립니다.
/*+ INDEX(A IX_EMP_01) */ 와 같이 특정 인덱스 사용을 강제하거나 조인 순서를 고정합니다.

👉 동적 샘플링 (Dynamic Sampling)

통계 정보가 없거나 믿을 수 없을 때, 런타임 시점에 데이터의 일부를 살짝 읽어서 통계를 즉석에서 계산하는 방식입니다.

 

 

👉 통계 정보 고정 (Statistics Locking)
운영 환경에서 실행 계획이 갑자기 바뀌어 성능이 튀는 것을 방지하기 위해, 가장 최적의 상태일 때 통계 정보를 고정(Lock)하기도 합니다.

 

 

옵티마이저를 얼마나 "의도한 상태"로 동작시키고 있는가? 이는 DB 튜닝 영역에서도 거의 정점에 가까운 수준의 개념으로, 실행 계획과 통계를 완전히 이해하고 통제할 수 있어야 가능한 매우 고난도의 작업입니다.



인프라 엔지니어라면 이 개념은 “반드시 직접 컨트롤해야 할 대상”이라기보다는, 옵티마이저에 대한 개념 정도만 알고 있어도 매우 훌륭한 수준입니다.