583. 프로시저 파라미터 스니핑 (Parameter Sniffing)과 캐시 오염

⚠️ 이 문서는 데이터베이스(MS-SQL, Oracle 등)에서 성능을 높이겠다고 개발자가 짜놓은 '저장 프로시저(Stored Procedure)'가, 평소에는 0.1초 만에 돌다가 어느 날 갑자기 10분이 걸려도 멈춰버리는 끔찍한 병목 현상의 주범인 '파라미터 스니핑(Parameter Sniffing)'이라는, DB의 멍청한 첫 기억(실행 계획 캐시)에 의한 성능 왜곡 참사를 다룹니다.

핵심 인사이트 (3줄 요약)

  1. 본질: DB 엔진은 쿼리가 처음 들어올 때 딱 1번만 고민(실행 계획을 짬)하고, 그 정답을 램(캐시)에 영구 저장한다. 문제는 '처음 들어온 변수(파라미터)'의 데이터 양에 따라 짠 1번 작전이, 나중에 들어올 2번, 3번 변수에게는 최악의 독약이 될 수 있다는 점이다.
  2. 가치: "어제까지 잘 되던 쿼리가 오늘 갑자기 타임아웃이 나요!"라는 백엔드 개발자의 절규를 들었을 때, 원인이 하드웨어 고장이 아니라 'DB의 캐시 오염'임을 1초 만에 짚어내고 플랜을 강제로 초기화할 수 있는 DBA의 핵심 진단 능력이다.
  3. 기술 체계: 이를 막기 위해 프로시저 안에 변수를 한 번 더 옮겨 담는 꼼수(Local Variable 재할당), 쿼리에 RECOMPILE 힌트를 달아 매번 뇌를 쓰게 만드는 방법, 또는 OPTIMIZE FOR UNKNOWN 옵션으로 맹목적인 첫사랑(첫 파라미터)을 잊게 만드는 조치를 취한다.

Ⅰ. 똑똑함이 부른 재앙: 실행 계획 캐싱 (Plan Caching)

한 번 짠 작전은 버리지 않는다. 그런데 그 작전이 암살 계획이라면?

  1. 저장 프로시저의 뇌 구조 (컴파일과 캐싱):
    • 프로시저 Get_Orders_By_Status(@상태값)를 만들었다.
    • 아침 9시. 개발자가 Get_Orders_By_Status('배송완료')를 처음 찔렀다.
    • DB 엔진(옵티마이저)이 뇌를 굴린다(파싱). "음, '배송완료' 데이터는 1억 건이나 되네? 인덱스를 타면 오히려 느리니까, 무식하게 하드디스크 전체를 긁는 [풀 테이블 스캔(Full Scan)] 작전이 낫겠다!" $\rightarrow$ 이 작전을 램(캐시)에 박제한다.
  2. 파라미터 스니핑(Sniffing)의 발동:
    • 9시 5분. 다른 직원이 Get_Orders_By_Status('환불대기')를 찔렀다. 환불 대기 데이터는 딱 10건밖에 없다. 당연히 **[인덱스 스캔(Index Scan)]**을 하면 0.001초 만에 찾을 수 있다.
    • 하지만 DB는 뇌를 쓰지 않는다(Sniffing). "아까 9시에 만들어둔 훌륭한 작전(캐시)이 있네? 이걸로 돌리자!"
    • 결과는 끔찍하다. 10건의 데이터를 찾기 위해 DB는 1억 건의 하드디스크 전체를 미친 듯이 긁기(풀 스캔) 시작하며, 이 직원의 화면은 10분 동안 하얗게 멈춰버린다. (서버 마비)

📢 섹션 요약 비유: 파라미터 스니핑은 '첫사랑의 저주'입니다. 식당 주방장(DB 엔진)이 아침에 첫 손님으로 온 씨름부 10명(대용량 파라미터)을 보고 "오늘은 밥을 100인분씩 거대한 솥에 끓여야겠다(풀 스캔 작전)"라고 하루 계획을 세워버렸습니다. 5분 뒤 혼자 온 초등학생 손님(소용량 파라미터)이 공깃밥 하나를 시켰는데, 멍청한 주방장은 아까 세운 계획대로 100인분짜리 가마솥에 불을 지피며 1시간을 낭비하는 끔찍한 고정관념(캐시 오염)에 빠진 상태입니다.

Ⅱ. 파라미터 스니핑의 해결책 (우회 기법)

멍청한 주방장의 기억(캐시)을 강제로 지우거나 눈을 가려라.

  1. Option (RECOMPILE) - "매번 뇌를 새로 갈아 끼워라":
    • 쿼리 맨 끝에 OPTION (RECOMPILE) (MS-SQL 기준)을 적어준다.
    • 효과: DB 엔진에게 "내가 이 프로시저를 부를 때마다, 제발 옛날 기억(캐시)은 무시하고 지금 들어온 변수에 맞춰 새로 작전(실행 계획)을 짜라!"라고 강제한다.
    • 단점: 1초에 1만 번 호출되는 프로시저에 이걸 달아놓으면, DB가 매번 작전을 짜느라(CPU 파싱 과부하) 서버 전체 CPU가 타버린다. 트래픽이 적고 가끔 도는 무거운 배치 쿼리에만 써야 한다.
  2. OPTIMIZE FOR UNKNOWN - "모든 손님을 공평하게 대하라":
    • 쿼리 끝에 OPTION (OPTIMIZE FOR UNKNOWN)을 적는다.
    • 효과: 주방장이 첫 손님('배송완료' 1억 건)의 데이터 크기를 보고 솥을 고르는 행위 자체를 막는다. "지금 들어온 변수값 쳐다보지 마! 그냥 전체 데이터의 평균 통계(중간값)를 짐작해서 가장 무난한 범용 작전을 짜!"라고 눈을 가려버린다. 대박(0.001초)도 안 터지지만, 쪽박(10분 대기)도 막아주는 방어적 튜닝이다.

📢 섹션 요약 비유: RECOMPILE은 주방장에게 "손님이 올 때마다 메뉴얼을 찢어버리고 새로 요리법을 연구해라!"라는 지시입니다. 완벽한 요리가 나오지만 주방장이 과로사(CPU 과부하)합니다. OPTIMIZE FOR UNKNOWN은 주방장에게 "첫 손님이 누군지 보지 말고, 그냥 하루 종일 모든 손님에게 똑같이 나갈 가장 무난한 '백반 정식(평균 실행 계획)'을 하나 통일해서 만들어 둬!"라는 지시입니다. 극강의 스피드는 포기하지만 식당이 멈추는 최악의 병목은 안전하게 방어합니다.

Ⅲ. 최후의 꼼수: 로컬 변수 (Local Variable) 덮어치기

가장 원시적이지만 개발자들이 10초 만에 에러를 틀어막는 마법의 꼼수.

  1. 로컬 변수 우회 기법의 원리:
    • 쿼리에 옵션을 달기 귀찮을 때 백엔드 개발자들이 쓰는 편법이다.
    • 프로시저 안에서 밖에서 날아온 변수 @상태값을 그대로 쓰지 않는다.
    • 프로시저 맨 윗줄에 껍데기 변수(DECLARE @Local_상태값)를 새로 하나 파놓고, 바깥에서 들어온 값을 거기에 복사(SET @Local_상태값 = @상태값)해서 쿼리에 집어넣는다.
  2. 왜 이 짓이 통하는가? (DB 엔진의 멍청함):
    • 옵티마이저(DB 뇌)는 프로시저가 컴파일될 때 외부 변수 @상태값 안에는 '배송완료(1억 건)'가 들어있다는 걸 냄새 맡고(Sniffing) 풀 스캔 작전을 짠다.
    • 하지만 변수를 내부 Local 변수로 옮겨 담으면, 옵티마이저는 "어라? 이 내부 변수 안에는 런타임에 무슨 글자가 들어올지 미리 알 수가 없네?" 하고 스니핑을 아예 포기해 버린다.
    • 결국 옵티마이저는 첫 손님의 데이터에 속지 않고, 어쩔 수 없이 위에서 말한 '무난한 범용 작전(통계 기반)'을 짜게 되어 끔찍한 병목이 해결된다.

📢 섹션 요약 비유: 주방장(DB 엔진)이 첫 손님 얼굴(외부 파라미터)을 보면 자꾸 편견을 가지고 요리법을 고정(스니핑)해 버립니다. 그래서 홀서빙 직원이 손님 얼굴을 보지 못하게 중간에 가림막(Local Variable)을 치고, "손님 주문은 내가 대신 받아서 메모지에 적어(변수 복사) 주방으로 넘겨줄게!"라고 시스템을 바꿉니다. 주방장은 손님이 뚱뚱한지(1억 건) 마른지(10건) 알 수 없으므로, 멍청한 편견(캐시 오염)을 버리고 무난한 표준 레시피대로만 요리하게 만드는 고도의 심리전 꼼수입니다.