쿼리 실행 계획 회귀(갑자기 느려짐)

어제 빨랐던 쿼리가 오늘 갑자기 느리다. 대개 옵티마이저가 더 나쁜 실행 계획으로 바꾼 것이다. 오래된 통계, 파라미터 민감도, 데이터 양 증가, 갑작스러운 실행 계획 변경 중 근본 원인을 찾아 실행 계획을 안정화한다.

실행 계획이 정말 바뀌었는가, 언제인가?

같은 쿼리에 여러 실행 계획(plan_hash/plan_generation)이 있는가, 느려지기 시작한 시점은 언제인가? 최근 배포, 업그레이드, 파라미터 변경, 대량 데이터 적재와 일치하는가?

SELECT sql_id, plan_hash_value, executions, ROUND(elapsed_time/GREATEST(executions,1)/1e6,3) AS sec_per_exec, first_load_time
FROM v$sql WHERE sql_id='&sqlid' ORDER BY first_load_time;

• 통계가 오래되었을 수 있음
• 같은 쿼리가 파라미터에 따라 다르게 동작함(sniffing/peeking)
• 데이터 양이 크게 증가함
• 업그레이드/flush 후 실행 계획이 갑자기 바뀜

통계가 최신인가?

옵티마이저는 통계를 기반으로 결정한다. 마지막 analyze 날짜가 오래되었거나 변경 카운터가 높다면 추정치가 실제로 어긋났을 수 있으며, 이는 나쁜 실행 계획으로 이어진다.

SELECT table_name, num_rows, last_analyzed, stale_stats
FROM dba_tab_statistics WHERE table_name='T';

• 통계가 오래되었거나 없음
• 통계는 최신이지만 실행 계획이 여전히 파라미터에 민감함

파라미터 민감도가 있는가?

쿼리는 첫 실행 시의 파라미터 값을 기준으로 실행 계획을 선택하고 캐싱한다(MSSQL parameter sniffing / Oracle bind peeking). 그 실행 계획은 분포가 치우친 다른 값에 대해 나쁠 수 있다.

SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware
FROM v$sql WHERE sql_id='&sqlid';

• 예, 파라미터에 따라 실행 계획이 좋고 나쁨을 오감
• 올바른 실행 계획을 알고 있으며 영구적으로 고정하고 싶음

근본 원인: 오래된 / 누락된 통계

실제와 맞지 않는 오래된 통계를 바탕으로 옵티마이저가 행 수를 잘못 추정해 나쁜 실행 계획을 선택했다. 실행 계획 회귀의 가장 흔한 원인이다.

근본 원인: 파라미터 민감도(sniffing / bind peeking)

캐시된 실행 계획은 처음 들어온 파라미터 값에 맞춰 선택되었으며 분포가 치우친 다른 값에는 나쁘다. 목표는 값에 맞는 실행 계획이 선택되도록 하는 것이다.

해결: 올바른 실행 계획 고정(baseline / force plan)

검증된 좋은 실행 계획이 있다면 옵티마이저가 벗어나지 않도록 고정해 회귀를 영구적으로 막는다.

근본 원인: 데이터 양이 증가해 실행 계획이 더 이상 맞지 않음

테이블이 커지면서 예전에 이상적이던 실행 계획(예: nested loop)이 이제는 잘 확장되지 않는다. 옵티마이저에는 증가를 정확히 반영하는 통계와 접근 경로가 필요하다.

근본 원인: 갑작스러운 실행 계획 변경(업그레이드 / 캐시 flush)

버전 업그레이드, 파라미터 변경, plan cache 비우기 후 실행 계획이 갑자기 바뀌었다. 옵티마이저 동작이나 환경 조건이 달라졌다.