힌트와 옵티마이저

힌트는 최후의 수단, 과연 필요한게 맞을까

최정환·2026년 7월 25일·4분 읽기1 views

힌트와 옵티마이저 이야기

힌트 문법을 정리하려고 시작한 글인데, 쓰다 보니 결국 옵티마이저 이야기가 됐다. 힌트를 쓰면서 생긴 의문들을 따라가다 보면 자연스럽게 그렇게 된다. 그 과정을 그대로 담았다.

힌트가 뭔데

옵티마이저에게 "이렇게 실행해"라고 지시하는 주석이다.

sql

SELECT /*+ 힌트 */ 컬럼 FROM 테이블;

/*+ 처럼 +가 붙어야 힌트로 인식된다. 문법이 틀려도 에러 없이 그냥 무시되기 때문에, 힌트가 안 먹는 것 같으면 오타부터 의심해야 한다.

실무에서 자주 쓰는 것들:

sql

-- 인덱스 강제 사용
SELECT /*+ INDEX(e emp_idx01) */ * FROM emp e WHERE ename = 'KIM';

-- 풀스캔 강제
SELECT /*+ FULL(e) */ * FROM emp e;

-- 조인 순서 지정 (FROM 절 순서대로)
SELECT /*+ ORDERED */ * FROM a, b WHERE a.id = b.id;

-- 조인 방식 지정
SELECT /*+ USE_NL(b) */ ...    -- Nested Loop (소량 데이터)
SELECT /*+ USE_HASH(b) */ ...  -- Hash Join (대량 데이터)

-- 병렬 처리
SELECT /*+ PARALLEL(e 4) */ * FROM emp e;

주의할 점 하나. 테이블에 별칭(alias)을 줬으면 힌트에도 반드시 별칭을 써야 한다. INDEX(emp ...)가 아니라 INDEX(e ...)다.

여기까지는 어느 블로그에나 있는 내용이다. 진짜 이야기는 힌트를 직접 써보면서 시작됐다.

의문 1. 힌트를 썼는데 코스트가 올라간다?

인덱스 힌트를 걸었더니 실행계획의 코스트(cost)가 오히려 올라갔다. 힌트가 성능을 좋게 하는 거 아니었나?

알고 보니 정상이다. 코스트는 옵티마이저의 "예상치"일 뿐이다.

옵티마이저는 원래 자기가 계산한 코스트가 가장 낮은 계획을 고른다. 힌트로 다른 계획을 강제하면, 옵티마이저 입장에선 "내 계산으로는 더 비싼 길"로 가는 것이니 코스트 숫자는 당연히 올라간다.

중요한 건 코스트 숫자가 아니라 실제 수행 시간이다. 애초에 옵티마이저의 예상이 틀렸기 때문에 힌트를 쓰는 건데, 그 틀린 계산 기준으로 나온 코스트를 보고 판단하면 의미가 없다.

실제로 확인하려면:

sql

SELECT /*+ GATHER_PLAN_STATISTICS */ ...
-- 실행 후
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

여기서 E-Rows(예상 건수)와 A-Rows(실제 건수)가 크게 차이 나면, 옵티마이저가 잘못 예측하고 있다는 증거다.

그런데 잠깐. 옵티마이저는 왜 잘못 예측하는 걸까?

의문 2. 옵티마이저는 뭘 보고 판단하나 — 통계정보

옵티마이저는 쿼리를 실행할 때마다 테이블을 직접 뒤져보지 않는다. 미리 수집해둔 통계정보를 보고 실행계획을 짠다.

테이블 행 수, 블록 수
컬럼별 값 분포
인덱스 상태

문제는 이게 실시간이 아니라는 것. 어제 100건이던 테이블에 오늘 1,000만 건을 넣었는데 통계는 여전히 "100건짜리 테이블"이라고 알고 있으면, 옵티마이저는 "100건이면 풀스캔이 낫지"라고 엉뚱한 계획을 짠다.

비유하면 옵티마이저는 내비게이션이고, 통계는 지도다. 지도가 낡으면 아무리 좋은 내비도 길을 잘못 든다.

통계는 알아서 갱신된다 (대부분은)

그럼 통계를 사람이 계속 갱신해줘야 하나? 다행히 아니다. 오라클 10g부터 자동 통계 수집이 기본으로 켜져 있다. 보통 밤 시간대 유지보수 윈도우에 돌면서, 데이터가 10% 이상 변경된 테이블의 통계를 알아서 갱신한다.

수동 갱신이 필요한 경우는 이 타이밍이 어긋날 때다:

배치로 대량 데이터를 넣은 직후, 밤에 자동 수집이 돌기 전에 낮에 그 테이블을 조회해야 할 때
테이블을 새로 만들고 대량 적재한 직후

sql

EXEC DBMS_STATS.GATHER_TABLE_STATS('스키마명', '테이블명');

여기서 "대량"의 기준은 건수가 아니라 비율이다. 100만 건 테이블에 5만 건이 추가되면 5%라 별 영향이 없지만, 10만 건 테이블에 5만 건이면 50%라 통계가 크게 어긋난다.

그리고 진짜 함정은 매일 꾸준히 쌓이는 테이블이 아니다. truncate하고 다시 채우는 집계 테이블 같은 게 위험하다. 0건일 때 통계가 잡히면 그 뒤로 계속 "빈 테이블" 취급을 받아서, 옵티마이저가 계속 이상한 계획을 짠다.

통계가 낡았는지 확인하는 법:

sql

SELECT table_name, num_rows, last_analyzed
FROM user_tables WHERE table_name = '테이블명';

num_rows(통계상 건수)와 실제 COUNT(*)가 크게 다르면 통계가 낡은 것이다.

평소엔 잊고 살아도 된다. 다만 "어제까지 잘 되던 쿼리가 갑자기 느려졌다" 싶으면 그때 통계를 떠올리면 된다.

의문 3. 옵티마이저는 어디에 있나

사소해 보이지만 의외로 헷갈리는 부분. 옵티마이저는 DBeaver나 Toad 같은 툴에 있는 게 아니라 DB 서버(엔진) 안에 있다.

툴은 SQL을 서버에 전달하고 결과를 받아서 보여주는 클라이언트일 뿐이다. 실행계획을 짜고, 힌트를 해석하고, 통계를 참조하는 건 전부 서버 쪽 일이다. 그래서 어떤 툴로 실행하든 같은 SQL이면 같은 옵티마이저가 처리한다. 툴마다 실행계획 화면이 달라 보여도 포장만 다를 뿐, 내용물은 서버가 짠 동일한 계획이다.

참고로 이 구조는 오라클만의 것이 아니다. MySQL, PostgreSQL, SQL Server 전부 "통계 기반 옵티마이저"라는 같은 구조를 쓴다. 힌트 문법만 DB마다 다를 뿐이다. 재밌는 건 PostgreSQL인데, "옵티마이저를 믿어라, 틀리면 통계를 고쳐라"는 철학으로 공식 힌트를 아예 만들지 않았다.

의문 4. 통계가 정확하면 힌트는 필요 없는 거 아닌가?

여기까지 오면 자연스럽게 이 생각이 든다. 옵티마이저가 틀리는 이유가 낡은 통계 때문이라면, 통계만 잘 관리하면 힌트는 필요 없지 않나?

대부분 맞다. 그게 힌트의 정석적인 위치다. PostgreSQL이 힌트를 안 만든 것도 같은 논리다.

하지만 통계가 완벽해도 힌트가 필요한 경우가 남는다:

컬럼 간 상관관계. 통계는 요약본이라 한계가 있다. 각 컬럼의 통계는 정확해도, "지역이 서울이면서 등급이 VIP인 고객"처럼 컬럼을 조합하면 예측이 틀어질 수 있다.

바인드 변수 문제. WHERE status = :v 같은 쿼리는 처음 들어온 값 기준으로 실행계획이 캐시된다. status='오류'(100건)로 처음 실행되면 인덱스 계획이 굳고, 그 계획으로 status='정상'(990만 건) 조회가 돌아버린다. 통계는 멀쩡한데 계획이 어긋나는 대표 사례다.

계획 고정. 지금 계획이 최적이어도 나중에 통계가 갱신되면서 계획이 바뀌어 갑자기 느려질 수 있다. 중요한 배치 쿼리는 힌트로 계획을 못 박아두기도 한다.

정리: 힌트는 마지막 도구다

힌트에 대해 오해하기 쉬운 것 하나. 힌트는 인덱스를 만들어주는 게 아니다. 이미 존재하는 인덱스 중 뭘 쓸지 지목하는 것뿐이다. 인덱스가 없으면 INDEX 힌트를 써도 아무 일도 일어나지 않는다.

그리고 옵티마이저 자체는 우리가 못 건드린다. 오라클이 만든 블랙박스다. 대신 건드릴 수 있는 건 옵티마이저의 입력과 출력이다:

SQL 작성 — 개발자가 가장 크게 영향을 주는 부분. WHERE SUBSTR(col,1,4) = '2026'은 인덱스를 못 타지만 WHERE col LIKE '2026%'는 탈 수 있다
인덱스 설계 — 옵티마이저가 고를 수 있는 선택지를 만들어주는 것
통계 — 옵티마이저가 보는 지도를 최신으로 유지하는 것
힌트 — 그래도 안 될 때, 판단을 무시하고 강제하는 마지막 수단

내비게이션 비유로 마무리하면: 내비 알고리즘은 못 고치지만, 지도를 최신으로 유지하고(통계), 도로를 놓아주고(인덱스), 목적지를 제대로 입력하고(SQL), 정 안 되면 경로를 수동 지정(힌트)하는 건 우리 몫이다.

그래서 순서는 이렇다.

실행계획 확인 → SQL과 인덱스 점검 → 통계 확인 → 그래도 안 되면 힌트.

힌트 문법을 검색해서 들어왔다면, 문법은 잊어도 이 순서만 기억하면 된다. 문법은 다시 검색하면 되지만, 순서를 모르면 힌트를 첫 번째 도구로 쓰게 되니까.

실행계획 읽는 법과 히스토그램 이야기는 다음 글에서 다룰 예정.