옵티마이저의 한계와 역할 분담: 좋은 판단 조건 만들기

핵심 요약

옵티마이저는 SQL이 요구한 결과 의미를 지키면서, 현재 제공된 통계·제약조건·설정과 실제로 사용 가능한 접근 경로 안에서 실행계획을 선택합니다. 화면이 몇 행만 필요로 하는지, 문서에만 있는 업무 규칙이 무엇인지, 미래의 동시 실행량이 얼마인지까지 스스로 추측해 SQL의 의미를 바꾸지는 않습니다.

좋은 실행계획을 만들기 위한 조건
= 필요한 결과와 처리 범위를 정확히 표현한 SQL
+ 현재 분포를 합리적으로 나타내는 통계
+ 검증 가능한 제약조건과 일관된 데이터 타입
+ 업무 패턴에 맞는 인덱스·파티션 등 물리 구조
+ 대표 Bind 값과 명확한 성능 목표
+ 실제 실행계획·실행 통계를 이용한 검증

따라서 SQL 튜닝은 옵티마이저와 경쟁하거나 계획 모양을 무조건 고정하는 작업이 아닙니다. 옵티마이저가 올바른 후보를 만들고 합리적으로 비교할 수 있는 조건을 제공한 뒤, 실제 실행 결과로 판단을 검증하는 작업입니다.

이 이론의 범위

이 이론은 SQL 옵티마이저 → SQL 옵티마이징 원리에서 옵티마이저의 판단 한계와 SQL 작성자·DBA·튜너의 책임 범위를 설명합니다. Histogram, Adaptive Cursor Sharing, 인덱스 설계, Call 최소화, Lock, SQL Plan Management의 상세 동작과 사용법은 후속 이론에서 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음 내용을 설명할 수 있어야 합니다.

• 옵티마이저가 활용할 수 있는 정보와 추측할 수 없는 업무 정보를 구분한다.
• SQL이 업무 요구를 정확히 표현해야 하는 이유를 설명한다.
• 존재하지 않는 인덱스나 파티션 경로를 일반 최적화 과정에서 사용할 수 없는 이유를 설명한다.
• SQL 작성자, 데이터 모델러·DBA, 튜너의 역할을 구분한다.
• SQL·통계·제약조건·인덱스·Bind·Hint를 검토하는 순서를 설명한다.
• Hint가 항상 적용되는 명령이 아니며 실제 실행계획으로 확인해야 하는 이유를 설명한다.
• 실행계획 안정성과 현재 최적성이 서로 다른 목표임을 설명한다.
• 안전한 튜닝 절차와 회귀 검증 범위를 설명한다.

1. 옵티마이저가 판단에 사용할 수 있는 정보

옵티마이저는 Database와 SQL에 표현된 정보를 이용해 후보 실행계획을 만들고 Cost를 비교합니다.

이 정보가 존재한다고 해서 항상 정확한 계획이 나오는 것은 아닙니다. 통계가 오래되었거나 제약조건이 실제 업무 규칙과 다르거나, Bind 값별 분포 차이가 크면 추정이 실제와 달라질 수 있습니다.

1.1 Bind 정보의 범위

옵티마이저는 Hard Parse 시 Bind 값을 확인하여 선택도를 추정할 수 있으며, 실행 이력에 따라 하나의 Bind SQL에 여러 계획을 사용하는 기능도 존재합니다. 그러나 앞으로 입력될 모든 Bind 값과 업무별 중요도를 미리 알고 있는 것은 아닙니다.

따라서 대표값뿐 아니라 인기값·희소값·대량 반환값에서도 계획과 실제 작업량을 검증해야 합니다.

2. 옵티마이저가 스스로 정할 수 없는 것

2.1 SQL에 표현되지 않은 업무 의도

다음 SQL은 고객의 모든 주문을 최신순으로 요청합니다.

SELECT *
FROM orders
WHERE customer_id = :customer_id
ORDER BY order_date DESC;

화면에서 최신 20건만 사용하더라도 SQL에 결과 제한이 없다면 Database가 보장해야 하는 결과는 조건에 맞는 전체 행입니다. 적절한 인덱스가 있다면 명시적인 전체 Sort를 피할 수도 있지만, 옵티마이저가 SQL의 결과 의미를 임의로 20행으로 줄일 수는 없습니다.

업무 요구가 최신 20건이라면 필요한 컬럼, 결정적인 정렬 기준, 결과 제한을 SQL에 표현해야 합니다.

SELECT order_id,
       order_date,
       order_status,
       total_amount
FROM orders
WHERE customer_id = :customer_id
ORDER BY order_date DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;

order_id가 고유한 키라면 같은 order_date를 가진 행 사이에서도 결과 순서를 결정할 수 있습니다.

이 SQL은 옵티마이저에 다음 정보를 제공합니다.

• 실제로 필요한 컬럼
• 결과의 정렬 기준
• 필요한 최대 행 수
• 동률 행의 순서를 결정하는 키

2.2 Database에 선언되지 않은 관계

업무상 ORDERS.CUSTOMER_ID가 항상 CUSTOMERS.CUSTOMER_ID를 참조하더라도 FK가 정의되지 않았다면, 옵티마이저는 그 관계를 Database가 검증하는 제약조건 정보로 사용할 수 없습니다.

업무 문서에만 있는 규칙
  ≠ Database가 검증하고 최적화에 활용할 수 있는 Constraint

실제 업무 규칙과 일치하는 PK·UK·FK·NOT NULL은 데이터 무결성뿐 아니라 Cardinality 추정과 일부 Query Transformation에 도움을 줄 수 있습니다. 다만 제약조건이 비활성·미검증 상태라면 활용 가능 범위를 별도로 확인해야 합니다.

2.3 미래의 부하와 모든 실행 환경

옵티마이저는 최적화 시점의 통계와 설정을 기준으로 판단합니다. 다음 상황을 완벽히 예측할 수는 없습니다.

• 다음 달에 특정 상태값이 급증하는 데이터 변화
• 배치 시간의 동시 실행량
• 다른 세션이 유발할 Lock·CPU·I/O 경합
• Cache 상태에 따른 실제 응답시간 차이
• 향후 객체와 설정 변경

적응형 실행계획은 실행 중 수집한 정보로 미리 준비된 대안 중 하나를 선택할 수 있지만, 업무 요구를 새로 정의하거나 모든 미래 경합을 해결하는 기능은 아닙니다.

2.4 존재하지 않는 실행 경로

조건에 맞는 인덱스가 없으면 일반적인 Hard Parse 과정에서 그 인덱스를 사용하는 계획을 만들 수 없습니다. 파티션이 없는 테이블에서 Partition Pruning을 사용할 수도 없습니다.

옵티마이저는 현재 사용 가능한 후보 중에서 선택한다.
필요한 물리 구조를 매 SQL 최적화 과정에서 새로 설계하는 것은 아니다.

일부 Oracle 환경에는 Automatic Indexing 기능이 있지만, 이는 별도의 관리·검증 기능입니다. 옵티마이저가 한 SQL을 최적화하면서 존재하지 않는 인덱스를 즉석에서 만들어 사용하는 것과는 구분해야 합니다.

3. 옵티마이저만으로 해결하기 어려운 대표 문제

Row-by-Row Call과 Lock의 세부 해결 방법은 각각 Database Call 최소화, Lock과 트랜잭션 동시성 제어 이론에서 다룹니다.

4. SQL 작성자의 역할

4.1 업무 결과와 처리 범위를 정확히 표현한다

SQL 작성자는 Database가 추측하지 않아도 되도록 업무 요구를 명확하게 표현해야 합니다.

• 필요한 행을 선별하는 조건을 작성한다.
• 필요한 컬럼만 선택한다.
• Join 조건을 빠뜨리지 않는다.
• 필요하지 않은 DISTINCT, ORDER BY, 중복 Subquery를 줄인다.
• 일부 행만 필요하면 Top-N이나 Pagination을 명시한다.
• 정렬 결과가 반복 실행마다 일관되어야 하면 고유한 Tie-breaker를 포함한다.
• 행마다 같은 SQL을 호출하기보다 집합 처리 가능성을 검토한다.

좋은 SQL은 단순히 짧은 SQL이 아니라, 정확한 업무 결과를 만들면서 필요 이상의 작업을 요구하지 않는 SQL입니다.

4.2 일관된 SQL Text와 Bind 사용을 설계한다

반복 실행되는 SQL은 불필요한 Hard Parse를 줄이고 Cursor를 공유할 수 있도록 설계합니다.

-- Literal 값마다 SQL Text가 달라지는 형태
SELECT order_id
FROM orders
WHERE customer_id = 1001;

SELECT order_id
FROM orders
WHERE customer_id = 1002;

-- 같은 SQL Text를 재사용하는 형태
SELECT order_id
FROM orders
WHERE customer_id = :customer_id;

Bind 사용은 SQL 공유와 Parse 비용 측면에서 중요합니다. 그러나 데이터 편중이 심하면 모든 Bind 값에 같은 계획이 최선은 아닐 수 있으므로 대표값별 검증이 필요합니다.

4.3 실행계획에서 질문할 수 있어야 한다

SQL 작성자는 최소한 다음 사항을 확인할 수 있어야 합니다.

• 어떤 Row Source부터 읽는가?
• Table Full Scan과 Index Access 중 무엇을 선택했는가?
• Join Order와 Join Method는 무엇인가?
• 조건이 가능한 한 이른 단계에서 행을 줄이는가?
• 예상 Rows와 실제 행 수가 크게 다른 Operation은 어디인가?
• 특정 Operation이 예상보다 많이 반복되는가?

실행계획을 상세히 읽는 방법은 후속 예상 실행계획과 SQL 트레이스 이론에서 다룹니다.

5. 데이터 모델러와 DBA의 역할

5.1 정확한 메타데이터 제공

• PK·UK·FK·NOT NULL을 실제 업무 규칙과 일치하도록 정의한다.
• Join 컬럼의 데이터 타입과 길이를 일관되게 설계한다.
• 애플리케이션 Bind 타입을 컬럼 타입과 맞춰 불필요한 암묵적 형 변환을 방지한다.
• Query Rewrite나 Partition Pruning이 필요한 경우 관련 객체와 설정을 검증한다.

5.2 적절한 물리 구조 제공

• 핵심 조회 조건과 Join 패턴에 맞는 인덱스를 설계한다.
• 대용량 테이블의 파티셔닝 필요성을 검토한다.
• 반복되는 집계가 있다면 Materialized View 등 대체 구조를 검토한다.
• 인덱스와 파티션 추가가 DML, 저장 공간, 배치 시간, 유지보수에 미치는 비용도 함께 평가한다.

5.3 통계정보 관리

• 데이터 변화량과 배치 주기를 고려해 통계를 수집한다.
• 파티션 통계와 글로벌 통계의 일관성을 관리한다.
• 편중이 중요한 컬럼은 Histogram 필요성을 검토한다.
• 상관관계가 강한 컬럼은 Extended Statistics 필요성을 검토한다.

통계 관리의 목표는 통계를 많이 만드는 것이 아니라, 옵티마이저가 실제 데이터 분포를 합리적으로 추정하도록 필요한 정보를 제공하는 것입니다.

6. 튜너의 역할과 안전한 튜닝 절차

튜너는 실행계획을 특정 모양으로 만드는 사람이 아니라, 성능 문제의 원인을 측정하고 변경의 효과와 부작용을 검증하는 사람입니다.

1. 업무 요구, 정상 결과, 성능 목표를 확정한다.
2. 실제 SQL Text, Child Cursor, Bind 값, 실행 빈도, 반환 행 수를 확인한다.
3. 실제 Cursor의 실행계획과 실행 통계를 기준선으로 수집한다.
4. 불필요한 작업이나 Cardinality 추정 오류가 시작되는 지점을 찾는다.
5. SQL·통계·제약조건·인덱스·데이터 모델 중 근본 원인을 우선 수정한다.
6. Hint는 가설 검증 또는 제한적 제어가 필요할 때 사용하고 실제 적용 여부를 확인한다.
7. 대표값·인기값·희소값·대량 반환값에서 결과와 성능을 다시 측정한다.
8. DML, 동시성, 저장 공간, 배치, 다른 SQL에 미치는 영향을 회귀 검증한다.

6.1 결과 정합성 검증

성능이 좋아져도 결과가 달라지면 올바른 튜닝이 아닙니다. 다음 항목을 함께 확인합니다.

• NULL 처리
• 중복 행
• Outer Join에서 보존되어야 하는 행
• 정렬 순서와 동률 행 처리
• Top-N 경계값
• 날짜·문자·숫자 형 변환
• DML 대상 행과 트랜잭션 결과

7. Hint의 역할과 한계

Hint는 SQL 주석 안에 작성하여 Optimizer Mode, Query Transformation, Access Path, Join Order, Join Method 등에 영향을 주는 지시입니다.

SELECT /*+ INDEX(o orders_customer_ix) */
       o.order_id,
       o.order_date
FROM orders o
WHERE o.customer_id = :customer_id;

Hint는 다음 상황에서 유용할 수 있습니다.

• 원인 분석 중 대안 계획의 실제 작업량을 비교할 때
• 통계나 구조를 즉시 변경하기 어려운 긴급 상황에서 제한적으로 제어할 때
• 업무 특성을 비용 모델이 충분히 반영하지 못하는 원인을 검증할 때

7.1 Hint가 항상 적용되는 것은 아니다

Hint는 다음과 같은 이유로 적용되지 않거나 기대와 다른 결과를 만들 수 있습니다.

• Hint 문법이나 객체 별칭이 잘못됨
• 서로 충돌하는 Hint를 함께 사용함
• Transformation 이후 Hint 대상 Query Block이나 객체가 달라짐
• 지정한 경로를 사용할 수 없는 구조적 조건이 존재함
• 다른 Hint나 Optimizer 기능과 상호작용함

따라서 Hint를 작성했다는 사실만으로 적용되었다고 판단해서는 안 됩니다. 실제 Cursor의 실행계획과 Outline·Hint Report 등 확인 가능한 정보로 적용 결과를 검증해야 합니다.

Oracle은 데이터와 환경 변화로 Hint가 오래되거나 부정적인 결과를 낼 수 있으므로, 테스트에는 활용하되 장기적인 계획 관리에는 SQL Plan Management 등 다른 수단도 검토하도록 안내합니다.

8. 실행계획 안정성과 최적성

8.1 실행계획 안정성

실행계획 안정성은 SQL이 운영 중 예측 가능한 범위의 검증된 계획을 사용하여 갑작스러운 성능 퇴행 위험을 줄이는 특성입니다.

안정성
  → 무조건 하나의 계획을 영구 고정한다는 뜻은 아님
  → 알려지고 검증된 계획을 예측 가능하게 사용하는 것이 핵심

SQL Plan Management는 알려진 계획을 관리하고 새로운 계획을 검증하여 사용할 수 있게 하는 대표적인 기능입니다. 하나의 Fixed Plan을 강제하는 것과 여러 Accepted Plan을 관리하는 것은 구분해야 합니다.

8.2 실행계획 최적성

실행계획 최적성은 현재 데이터량, 분포, Bind 값과 업무 목표에서 실제 작업량과 응답 특성이 가장 적절한 상태입니다.

두 목표는 항상 같지 않습니다.

과거에 안정적이던 계획
  → 데이터 증가와 분포 변화 후 비효율적일 수 있음

새로운 후보 계획
  → 현재 데이터에는 더 효율적일 수 있으나
     충분한 검증 없이 사용하면 회귀 위험이 있을 수 있음

따라서 계획을 단순히 고정하거나 무조건 새 계획을 허용하기보다, 현재 성능과 변경 위험을 함께 평가해야 합니다.

9. 모든 성능 문제를 옵티마이저 문제로 보면 안 된다

이 표는 각 영역의 상세 튜닝 방법을 설명하기 위한 것이 아니라, 실제 병목이 옵티마이저의 계획 선택 밖에 있을 수 있음을 구분하기 위한 경계입니다.

10. 자주 혼동하는 판단과 정확한 기준

11. 핵심 정리

옵티마이저
  → SQL 의미를 지키면서 주어진 정보와 사용 가능한 경로 안에서 계획을 선택한다.

SQL 작성자
  → 필요한 결과, 처리 범위, 정렬, 결과 제한을 정확히 표현한다.

DBA·데이터 모델러
  → 검증 가능한 제약조건, 일관된 타입, 통계와 물리 구조를 제공한다.

튜너
  → 실제 SQL·Bind·실행계획·실행 통계로 원인을 찾고 변경 효과를 검증한다.

Hint
  → 계획에 영향을 줄 수 있지만 적용 여부와 장기 위험을 반드시 검증한다.

계획 안정성
  → 검증된 계획을 예측 가능하게 관리하는 특성이다.

계획 최적성
  → 현재 데이터와 업무 목표에서 실제 작업량이 적절한 특성이다.

좋은 튜닝은 옵티마이저가 올바른 후보를 만들 수 있도록 입력 조건을 개선하고, 예상과 실제의 차이를 근거로 해결책을 검증하는 과정입니다.