SQL) 옵티마이저 원리 Ⅰ

jinsung·2026년 1월 2일

SQLP oracle sql 데이터 자격증

SQL

목록 보기

34/46

1. 옵티마이저

옵티마이저는 사용자가 요청한 SQL을 가장 효율적이고 빠르게 수행할 수 있는 최적의 처리결로를 선택해 주는 DBMS의 핵심엔진이다.

SQL로 사용자가 원하는 결과집합을 정의하면 옵티마이저가 이를 얻는 데 필요한 프리절차를 자동으로 생성해준다.

1. RBO(Rule Based Optimizer)

RBO(규칙 기반 옵티마이저)란 다른 말로 '휴리스틱 옵티마이저' 라고 불리며, 미리 정해둔 우선순위에 따라 액세스 경로를 평가하고 실행계획을 선택한다.

위 표는 RBO가 사용하는 규칙인데 인덱스 구조, 연산자, 조건절 형태가 순위를 짓는 주 요인임을 알 수 있다.

OLTP 환경의 중소형 데이터베이스 시스템이라면 RBO는 어느정도의 타당성을 갖는다.
하지만 RBO는 데이터량, Distinct Value의 수 , 컬럼 값 분포, 인덱스 높이, CF 같은 데이터 특성을 고려하지 못 하기 때문에 대용량 데이터를 처리하는 데 있어 합리적이지 않다.
ex) 인덱스가 있으면 무조건 인덱스를 사용함

2. CBO(Cost Based Optimizer)

CBO는 비용을 기반으로 최적화를 수행한다.
'비용'이란 쿼리를 수행하는데 소요되는 일량 또는 시간을 말한다.

전통적인 I/O 비용 모델에서는 I/O 요청 횟수로만 비용을 평가했지만,
최근 CPU 비용 모델에서는 CPU 연산 비용 + 수행 일량을 상대적인 시간 개념으로 환산해 비용을 평가한다.

CBO가 실행계획을 수립할 때 판단 기준이 되는 비용은 어디까지나 예상치다.
미리 구해놓은 테이블과 인덱스에 대한 여러 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고, 이를 합산한 총비용이 가장 낮은 실행계획 하나를 수립한다.

비용을 산정할 때 사용되는 오브젝트 통계항목에는 레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, CF 등이 있다.

옵티마이저의 최적화 수행 단계

사용자가 던진 쿼리수행을 위해, 후보군이 될만한 실행계획을 찾는다.
데이터 딕셔너리에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 비용을 산정한다.
각 실행계획을 비용을 비교해 최저비용을 갖는 하나를 선택한다.

동적 샘플링 (Dynamic Sampling)

쿼리를 최적화할 때 통계정보가 없거나 너무 오래돼 신뢰할 수 없을 때 옵티마이저가 동적으로 샘플링을 수행할 수 있다.

optimizer_dynamic_sampling 파라미터로 동적 샘플링 레벨을 조정한다.
(default 값으로 9i는 1, 10g는 2, 0이면 동적샘플링 실행 안 함, 최대 레벨은 10까지)

10g의 default 값이 2이기 때문에 10g부터는 쿼리 최적화 시 통계정보가 없으면 무조건 동적 샘플링을 수행한다.

9i의 default 값인 1로 설정했을 때 아래 조건을 만족해야 동적 샘플링이 일어난다.

1. 통계정보가 수집되지 않은 테이블이 적어도 하나 이상 있다

2. 그 테이블이 다른 테이블과 조인되거나 서브쿼리 또는 Non-mergeable View에 포함된다.

3. 그 테이블에 인덱스가 없다.

4. 그 테이블에 할당된 블록 수가 32개 보다 많다.

최대 레벨은 10이며, 높을수록 옵티마이저는 더 적극적인 동적 샘플링을 수행하며 샘플링에 사용되는 표본 블록 개수도 증가한다.

동적 샘플링으로 얻은 통계정보는 데이터 딕셔너리에 영구 저장되지 않는다.

스스로 학습하는 옵티마이저 (Self-Learning Optimizer)

v$sql, v$sql_plan_statistics, v$sql_plan_statistics_all, v$sql_workarea 등에 SQL별로 저장된 수많은 런타임 수행 통계를 보면 앞으로 옵티마이저의 발전 방향을 예상할 수 있다.

옵티마이저는 지금까지 오브젝트 통계와 시스템 통계로부터 산정한 '예상' 비용만으로 실행계획을 수립했지만 앞으로는 예상치가 빗나갔을 때 이들 런타임 수행 통계를 보고 실행계획을 조정할 움직임을 보이고 있다.

3. 옵티마이저 모드

옵티마이저 모드로 선택할 수 있는 값으로는 5가지가 있고, 시스템 레벨, 세션 레벨, 쿼리 레벨에서 바꿀 수 있다.

alter system set optimizer_mode = all_rows;        -- 시스템 레벨 변경
alter session set optimizer_mode = all_rows;       -- 세션 레벨 변경
select /*+ all_rows */ * from t where ...;         -- 쿼리 레벨 변경

1. RULE

RBO 모드를 선택할 때 사용한다.

2. ALL_ROWS

쿼리 최종 결과집합을 끝까지 Fetch하는 것을 전제로, 시스템 리소스를 가장 적게 사용하는 실행계획을 선택한다.

DML 문장은 일부 데이터만 가공하고 멈출 수 없으므로 옵티마이저 모드에 상관없이 항상 all_rows 모드로 작동한다.

select 문장도 union, minus 같은 집합 연산자나 for update절을 사용하면 all_rows 모드로 작동한다.

PL/SQL 내에서 수행되는 SQL도 힌트를 사용하거나 기본모드가 RULE인 경우를 제외하면 항상 all_rows 모드로 작동한다.

3. FIRST_ROWS

쿼리 전체 결과집합 중 일부 로우만 Fetch하다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.
만약 사용자가 끝까지 Fetch한다면 오히려 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.

first_rows는 비용(CBO)과 규칙(RBO)을 혼합한 형태의 옵티마이저 모드이다.
얼마만큼 Fetch할지 지정하지 않았으므로 정확한 비용을 예측할 수 없고, 따라서 옵티마이저는 내부적으로 정해진 규칙을 사용한다.

규칙의 영향을 받긴 하지만 first_rows도 CBO모드이기 때문에 통계정보를 활용한다.
first_rows가 RBO보다 낫긴 해도 완벽한 비용에 근거하지 않기 때문에 불합리한 결정을 할 때가 종종 있다.

4. FIRST_ROWS_N

사용자가 처음 n개 로우만 Fetch하는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.
파라미터 사용 시 n으로 지정할 수 있는 값은 1, 10, 100, 1000 네 가지며, 사용자가 지정한 n개 로우 이상을 Fetch한다면 오히려 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.
(힌트를 사용하면 n > 0 이면 모든 정수 사용 가능)

first_rows와 달리 firsr_rows_n은 완전한 CBO 모드로 작동한다.

5. CHOOSE

액세스되는 테이블 중 통계정보가 없다면 RBO, 적어도 하나에 통계정보가 있다면 CBO, 그 중에서도 all_rows를 선택한다.

9i까지 CHOOSE가 기본 설정이었지만 10g부터는 all_rows가 기본 옵티마이저 모드로 설정된다.
10g 부터는 통계정보가 없으면 무조건 동적 샘플링이 일어나 choose가 필요 없어졌기 때문이다.

옵티마이저 모드 선택

DW 시스템이나 배치 프로그램은 all_rows 모드를 써야 된다.
OLTP의 경우 애플리케이션 아키텍처에 따라 다르다고 할 수 있다.
애플리케이션 아키텍처가 2-Tier 환경인 경우 first_rows 모드가 효과적이고, 3-Tier 구조는 페이지 처리 기법을 주로 사용한다.

애플리케이션 특성상 확실히 first_rows가 적합하다는 판단이 서지 않는다면 all_rows를 기본 모드로 선택하고, 필요한 쿼리 또는 세션 레벨에서 first_rows 모드로 전환하는 것이 좋다.

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

1. SQL과 연산자 형태

결과가 같더라도 SQL을 어떤 형태로 작성헀는지 또는 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 궁극적으로 쿼리에 영향을 미친다.

2. 인덱스, IOT, CF, 파티셔닝, MV 등 옵티마이징 팩터

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

3. 제약 설정

DBMS가 제공하는 PK, FK, Check, Not Null 같은 제약 설정 기능을 이용해야 완벽한 데이터 무결성을 확보할 수 있다.
제약 설정은 데이터 무결성을 보장해 줄뿐만 아니라 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공한다.

4. 옵티마이저 힌트

옵티마이저 힌트는 옵티마이저에게 매우 강한 영향력을 끼친다.

✅ 옵티마이저가 힌트를 따르지 않는 경우

문법적으로 맞지 않게 힌트를 기술
잘못된 참조 사용 (없는 테이블이나 별칭 사용, 없는 인덱스명 사용)
의미적으로 맞지 않게 힌트를 기술
논리적으로 불가능한 액세스 경로 (조인조건에 '=' 조건이 하나도 없는데 해시조인으로 유도 등)
버그

위 경우가 아니면 옵티마이저는 무조건 적으로 힌트를 따르고 남은 부분만 자신의 판단에 따라 최적화 한다.

가급적 힌트 선택을 자제하고, 옵티마이저가 스스로 좋은 선택을 할 수 있도록 돕는다.
옵티마이저가 잘못된 선택을 할 때만 힌트를 사용한다.

5. 통계정보

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

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

7. DBMS 버전과 종류

오라클 7 버전의 최댓값 구할 때 index_desc 힌트 + rownum 조건
=> 오라클 8 버전 max() -> 인덱스 (MIN/MAX) 스캔

다만, 해당 오브젝트에 대한 통계정보가 수집돼 있어야 한다.
10g 부터는 동적 샘플링이 자동 진행돼서 통계정보가 없더라도 잘 작동한다.
MS-SQL 에서 작동해보면 다른 방식으로 동작한다.

3. 옵티마이저의 한계

자동 튜닝 옵티마이저 (Automatic Tuning Optimizer)

자동 튜닝 옵티마이저를 '오프라인 옵티마이저' 라고도 한다.
우리가 쓰는 옵티마이저는 '온라인 옵티마이저' 로 '런타임 옵티마이저'라고도 부른다.
통계를 분석하고, SQL을 프로파일링을 실시하며, 액세스 경로 및 SQL 구조 분석을 통해 SQL 튜닝을 실시한다.

튜닝 모드에서 작동하는 이 옵티마이저에게는 한 문장을 튜닝하는 데에 런타임 옵티마이저보다 훨씬 긴 시간이 주어진다.
긴 시간 동안 더 많은 정보를 수집, 활용함으로써 데이터 액세스 비용과 카디널리티를 보다 정확하게 계산할 수 있다.

동적 샘플링을 통해 부가적인 정보를 수집하고, 부분적인 실행을 통해 예측치를 검증함으로써 잘못된 정보를 조정하는 테크닉을 사용한다.
그렇게 각 SQL 단위로 수집된 프로파일을 데이터 딕셔너리에 영구 저장해 런타임 옵티마이저가 참조할 수 있도록 하는 기능도 제공한다.

자동 튜닝 옵티마이저 기능을 활용하려면 'SQL Tuning Advisor'라 불리는 서버 유틸리티를 이용하면 된다.
SQL Tuning Advisor에 SQL문을 입력하면 내부적으로 자동 튜닝 옵티마이저를 호출해 SQL 분석을 실시한다.
분석이 끝나면 SQL 성능을 높이기 위해 사용자가 취해야 할 조치사항들을 보고서 형태로 출력한다.

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

옵티마이저는 주어진 환경에서 최선을 다할 뿐 옵티마이징 팩터를 제공하는 것은 사람의 역할이기 때문에 옵티마이저가 고성능 실행계획을 수립해 주기를 기대해선 안 된다.

2. 부정확한 통계

많은 정보를 수집, 보관한다면 좋을 결과를 낼 수 있겠지만 현질적으로 100% 정확한 통계를 유지하는 것은 어렵다.
이런 현실적인 제약 때문에 샘플링 방식으로 통계를 수집하다 보니 실제 데이터와 불일치가 발생한다.

샘플링 방식으로 통계를 수집할 때는 정확성 문제와 더불어 안정성도 생각해야 한다.
옵티마이저의 올바른 선택을 위해 통계 수집 주기도 매우 중요하다.

3. 히스토그램의 한계

히스터그램 버킷 개수로 254개까지만 허용된다는 점도 옵티마이저에겐 중요한 제약사항이다.
컬럼의 Distinct Value 개수가 그 이상이면 모든 값 별로 빈도수를 기록해 둘 수 없고, 이때는 높이균형 히스토그램을 사용하게 되므로 발생 빈도가 낮은 값들에 대한 정확한 분포를 반영할 수 없다.

버킷 개수를 무한정 많이 가져갈 수 있으면 더 정확한 카디널리티를 구하겠지만, 정보를 수집하는 데 소요되는 시간과 저장 공간 때문에 생기는 어쩔 수 없는 제약사항이다.

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

컬럼 히스토그램이 있으면 옵티마이저가 그것을 가지고 조건절에 대한 선택도를 구한다.
만약 조건절에 바인드 변수를 사용한다면 옵티마이저가 균등분포를 가정하고 비용을 계산하기 때문에 히스토그램이 무용지물이 된다.

5. 결합 선택도 산정의 어려움

카디널리티가 잘못 계산되면 다른 집합과 여러 번 조인을 거치는 동안 카디널리티는 점점 부정확해지고 궁극적으로 옵티마이저가 잘못된 실행계획을 수립하는 결과를 낳는다.
테이블 컬럼이 많을수록 컬럼 조합의 수는 기하급수적으로 늘기 때문에, 모든 컬럼 간 상관관계와 결합 선택도를 미리 저장해 두는 것은 불가능하다.

오라클 9i 부터 동적 샘플링 레벨 4 이상일 때, 데이터 샘플링을 통해 where 조건절에 사용된 두 개 이상 컬럼의 결합 분포를 구할 수 있다.
11g 부터는 사용자가 지정한 컬럼들에 대해 결합 선택도를 미리 수집해 두는 기능을 제공한다.

6. 비현실적인 가정

CBO는 쿼리 수행 비용을 평가할 때 여러 가정을 사용하는데, 그 중 일부는 매우 비현실적이다.
예를 들면, Single Block I/O와 Multiblock I/O 비용을 같게 평가하거나 캐싱 효과를 고려하지 않는다는 점이다.

7. 규칙에 의존하는 CBO

비용 기반 옵티마이저라도 부분적으로는 규칙에 의존한다.
물론 10g에서 대부분 쿼리 변환이 비용기반으로 개선되었다.

✅ 알파벳순 인덱스 선택 규칙

CBO는 두 대안의 인덱스의 예상 비용이 같을 때 알파벳 순에서 앞선 것을 선택한다.
CBO가 이런 단순한 규칙을 사용함으로 인해 가끔 실행계획에 문제가 생기는 것을 보게 된다.
이런 일이 발생하지 않게 하려면 인덱스 명명 규칙을 정하는 것이 좋다.

8. 하드웨어 성능 특성

옵티마이저는 기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰져 있다.
따라서 실제 운영 시스템의 하드웨어 사양이 그것과 다를 때 옵티마이저가 잘못된 실행계획을 수립할 가능성이 높아진다.
하지만 오라클 9i부터 시스템 통계를 수집하는 기능이 도입돼서 이 한계점은 어느 정도 해결방법을 찾은 상태이다.

jinsung

Data Engineer

이전 포스트

SQL) 조인 원리와 활용 Ⅱ

다음 포스트