실행계획과 히스토그램

옵티마이저는 왜 틀린 예측을 하는가

최정환·2026년 7월 26일·5분 읽기0 views

실행계획과 히스토그램

옵티마이저는 왜 틀린 예측을 하는가, 직접 만들어서 확인해봤다

고백부터 하고 시작

전편에서 힌트 이야기를 길게 했지만, 솔직히 말하면 나는 힌트로 문제를 풀어본 적이 없다. 힌트를 걸면 코스트가 오히려 올라가기만 했다. 그때는 "코스트는 예상치일 뿐이니까"로 넘어갔는데, 곱씹어보니 찜찜하다.

코스트를 믿으면 안 된다면, 그럼 뭘 봐야 하나? 그리고 옵티마이저의 예상은 대체 왜 틀리나?

이번 글은 그 두 질문을 따라간다. 앞부분이 실행계획 읽는 법이고, 뒷부분이 히스토그램이다. 따로 노는 주제 같지만 끝까지 가보면 한 이야기다.

실행계획에는 '예보'와 '실황'이 있다

먼저 헷갈렸던 것 하나. 실행계획을 보는 방법이 두 가지인데, 성격이 완전히 다르다.

sql

-- 방법 1: 예상 계획 (쿼리를 실행하지 않음)
EXPLAIN PLAN FOR SELECT * FROM t_order WHERE status = '취소';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 방법 2: 실제 계획 (쿼리를 실행하고, 실제 수치까지 보여줌)
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM t_order WHERE status = '취소';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

방법 1은 일기예보다. 옵티마이저가 "이렇게 실행할 계획이고, 아마 이 정도 걸릴 것"이라고 말하는 것. 방법 2는 실황이다. 실제로 실행해보고 "몇 건이 나왔고, 얼마나 읽었는지" 결과를 보여준다.

DB 툴에서 버튼 한 번으로 확인하는 실행계획은 대부분 방법 1, 즉 예보다. 편해서 자주 쓰지만 어디까지나 예상치다. 튜닝할 땐 방법 2를 봐야 한다. 우리가 궁금한 건 "예보가 얼마나 빗나갔나"니까, 예보만 봐서는 알 수가 없다.

읽는 순서: 가장 안쪽부터

실행계획을 처음 보면 어디부터 읽어야 할지 막막하다. 규칙은 하나다. 들여쓰기가 가장 깊은 줄부터 읽는다. 같은 깊이가 여럿이면 위에서부터.

왜 이런 순서냐면, 실행계획은 회사 조직도 같은 구조라서다. 실무자(가장 안쪽)가 먼저 일해서 결과를 올리면, 상사(바깥쪽)가 그걸 받아서 조립한다.

처음엔 다 못 읽어도 된다. "안쪽이 먼저다" 하나만 알아도 절반은 읽힌다.

진짜 봐야 할 두 컬럼: 예상과 실제

방법 2로 실행계획을 보면 이런 컬럼들이 나온다.

text

-----------------------------------------------------------------
| Id | Operation         | Name    | E-Rows | A-Rows | Buffers  |
-----------------------------------------------------------------
|* 1 |  TABLE ACCESS FULL| T_ORDER |    500K|    100 |    45231 |
-----------------------------------------------------------------

E-Rows = Estimated Rows. 옵티마이저가 예상한 건수 (예보)
A-Rows = Actual Rows. 실제로 나온 건수 (실황)
Buffers = 읽은 블록 수. 쉽게 말해 "이 작업을 하느라 책을 몇 페이지 넘겼나"

컬럼이 많아 보여도 튜닝 포인트를 찾는 방법은 단순하다. E-Rows와 A-Rows가 크게 벌어진 줄을 찾는 것. 예보가 크게 빗나간 지점이 거기고, 옵티마이저의 모든 판단(인덱스냐 풀스캔이냐, 어떤 조인이냐)은 그 예보 위에 세워지기 때문에, 예보가 틀리면 계획 전체가 무너진다.

위 예시가 딱 그렇다. 예상 50만 건, 실제 100건. 5,000배 차이다. 옵티마이저는 "50만 건이나 가져올 거면 인덱스로 한 건씩 찾는 것보다 처음부터 다 읽는 게 낫지"라고 판단해서 풀스캔을 골랐다. 100건일 줄 알았으면 당연히 인덱스를 골랐을 것이다. 판단 로직은 멀쩡한데 입력된 예상치가 틀린 것.

그런데 잠깐. 전편에서 예측이 틀리는 원인은 낡은 통계라고 했다. 그럼 통계만 갱신하면 해결 아닌가? 나도 그렇게 생각했다. 직접 확인해보기 전까지는.

만들어서 확인해봤다

통계가 방금 수집한 최신 상태인데도 옵티마이저가 틀리는지 실험해봤다. 실무에서 흔한 상황을 흉내 낸 테이블이다. 주문 100만 건 중 99.99%는 '정상'이고, '취소'는 딱 100건.

sql

-- 100만 건짜리 주문 테이블 생성
CREATE TABLE t_order AS
SELECT level AS order_no,
       CASE WHEN level <= 100 THEN '취소' ELSE '정상' END AS status,
       SYSDATE - dbms_random.value(0, 365) AS order_date
FROM dual CONNECT BY level <= 1000000;

CREATE INDEX ix_status ON t_order(status);

-- 통계 수집 (일단 히스토그램 없이)
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORDER', method_opt => 'FOR ALL COLUMNS SIZE 1');

통계는 방금 수집했으니 완벽하게 최신이다. 이제 '취소' 100건을 조회하면?

text

-----------------------------------------------------------------
| Id | Operation         | Name    | E-Rows | A-Rows |
-----------------------------------------------------------------
|* 1 |  TABLE ACCESS FULL| T_ORDER |    500K|    100 |
-----------------------------------------------------------------

예상 50만 건. 인덱스가 버젓이 있는데 풀스캔을 골랐다. 통계가 최신인데 왜?

옵티마이저는 세상이 공평하다고 믿는다

50만이라는 숫자가 어디서 나왔는지 보면 답이 나온다. 기본 통계가 status 컬럼에 대해 아는 건 딱 두 가지다.

전체 100만 건
값의 종류는 2가지 ('정상', '취소')

이게 전부다. 각 값이 몇 건씩인지는 모른다. 그래서 옵티마이저는 어쩔 수 없이 "값들이 골고루 나눠져 있겠지"라고 가정하고, 100만 ÷ 2 = 50만 건이라고 답한다. '정상'을 조회해도 50만, '취소'를 조회해도 50만. 뭘 물어봐도 같은 답이다.

문제는 현실 데이터가 전혀 공평하지 않다는 것. 상태 코드, 삭제 여부, 회원 등급 — 실무 컬럼은 대부분 한쪽으로 심하게 쏠려 있다. 통계가 아무리 따끈따끈해도, "골고루 퍼져 있다"는 가정 자체가 틀렸으면 예측은 틀린다.

전편의 내비 비유로 말하면 이렇다. 지도는 최신인데, 내비가 모든 도로를 같은 속도로 계산하는 상황. 8차선 대로와 골목길을 똑같이 취급하니 경로가 이상해진다. 낡은 지도가 문제가 아니라, 지도에 '도로 폭' 정보가 아예 없는 게 문제다.

히스토그램: 지도에 도로 폭을 그려 넣기

그 빠진 정보 — 값별로 몇 건씩인지 — 를 채워주는 게 히스토그램이다. 이름은 거창하지만 실체는 단순하다. "'정상'은 999,900건, '취소'는 100건"이라는 분포표를 통계에 추가하는 것.

sql

-- status 컬럼에 히스토그램 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORDER',
     method_opt => 'FOR COLUMNS status SIZE 254');

같은 쿼리를 다시 실행하면:

text

--------------------------------------------------------------------
| Id | Operation                   | Name      | E-Rows | A-Rows |
--------------------------------------------------------------------
|  1 |  TABLE ACCESS BY INDEX ROWID| T_ORDER   |    100 |    100 |
|* 2 |   INDEX RANGE SCAN          | IX_STATUS |    100 |    100 |
--------------------------------------------------------------------

예상 100건, 실제 100건. 예측이 정확해지자 옵티마이저가 알아서 인덱스를 골랐다. 힌트 없이. 쿼리는 한 글자도 안 고쳤다.

다행인 건, 이걸 매번 사람이 만들 필요는 없다는 것. 전편에서 말한 밤마다 도는 자동 통계 수집이 히스토그램도 챙긴다. 그 컬럼이 WHERE 조건에 쓰인 적이 있고 + 분포가 쏠려 있으면 오라클이 알아서 만들어준다. 그러니 히스토그램도 평소엔 잊고 살면 된다. 다만 "통계도 최신인데 E-Rows가 이상하다" 싶을 때 확인할 곳이 하나 늘어난 것이다.

sql

-- 이 컬럼에 히스토그램이 있는지 확인
SELECT column_name, histogram
FROM user_tab_col_statistics
WHERE table_name = 'T_ORDER';
-- histogram 컬럼이 NONE이면 없는 것

그런데 부작용이 있다: 전편 떡밥 회수

전편에서 바인드 변수 문제를 잠깐 언급했다. WHERE status = :v처럼 값을 변수로 받는 쿼리는 처음 들어온 값 기준으로 실행계획이 저장(캐시)되고, 이후엔 값이 바뀌어도 그 계획을 재사용한다는 것. 사실 그 문제는 히스토그램이 있을 때 생기는 부작용이다.

히스토그램이 없을 땐 문제가 없었다. 무슨 값이든 예측이 50만으로 같으니 계획도 늘 같고, 재사용해도 상관없다. 그런데 히스토그램이 생기면 '취소'는 인덱스가 최적, '정상'은 풀스캔이 최적 — 값에 따라 정답이 달라진다. 계획은 하나만 저장되는데.

분포를 알게 된 대가로, "하나의 쿼리에 정답이 두 개"인 상황이 생긴 것이다.

그래서 실무 절충안: 쏠린 컬럼 중에서 조회하는 값이 몇 가지로 정해져 있는 조건(상태 코드 같은)은 바인드 변수 대신 값을 직접 쓰는 걸 고려한다. status = :v 대신 status = '취소'로. 그러면 값마다 계획이 따로 만들어진다. 오라클도 11g부터 값에 따라 계획을 여러 개 저장하는 기능을 넣었지만, 처음 몇 번은 어긋난 계획으로 돌 수 있어서 만능은 아니다.

정리

이번에 얻은 것들.

실행계획은 예보(EXPLAIN PLAN)가 아니라 실황(DISPLAY_CURSOR)으로 본다
튜닝 포인트는 E-Rows(예상)와 A-Rows(실제)가 벌어진 줄이다
통계가 최신이어도 틀릴 수 있다. 기본 통계는 "값이 골고루 있다"고 가정하니까
쏠린 컬럼에는 히스토그램. 대부분은 자동 수집이 알아서 만들어준다
히스토그램 + 바인드 변수 조합은 계획이 어긋날 수 있다

전편의 순서에 한 단계가 추가된다. 실행계획 확인 → E-Rows/A-Rows 비교 → SQL과 인덱스 점검 → 통계 확인 → 히스토그램 확인 → 그래도 안 되면 힌트.

돌아보면, 내가 힌트를 걸었을 때 코스트만 올라가고 해결이 안 됐던 건 당연했다. 옵티마이저가 왜 틀리는지도 모르면서 결과만 뒤집으려 했으니까. 이번 실험에서는 원인(분포를 모른다)을 알려주자 힌트 없이도 옵티마이저가 스스로 맞는 길을 골랐다. 내비를 수동 조작하기 전에 지도부터 고쳐라 — 전편의 결론이 실험으로 증명된 셈이다.