통계정보도 최신이고 인덱스도 그대로인데 어제까지 0.05초였던 화면이 오늘 갑자기 8초가 되는 경우가 있다. 더 이상한 건 잠시 뒤 다시 빨라진다는 점이다. 코드도 데이터도 크게 바뀐 게 없다면 바인드 변수 피킹을 의심할 차례다.
옵티마이저가 첫 값을 엿본다
바인드 변수를 쓴 쿼리는 실행 시점마다 값이 다르다. 그런데 옵티마이저는 매번 계획을 새로 세우지 않는다. 파싱 비용이 크기 때문에 한 번 만든 실행계획을 공유 풀에 올려두고 재사용한다.
문제는 계획을 처음 세울 때다. 옵티마이저는 그 순간 넘어온 바인드 값을 슬쩍 들여다보고(peeking) 그 값에 맞는 계획을 만든다. 그리고 그 계획이 이후 모든 실행에 쓰인다.
값 분포가 고르면 아무 문제가 없다. 하지만 편중돼 있으면 얘기가 달라진다.
select count(*), status from orders group by status;
-- C (완료) 9,800,000
-- P (처리중) 1,200
-- E (오류) 40이런 테이블에서 다음 쿼리를 생각해 보자.
select ord_no, ord_dt, amt
from orders
where status = :b1;첫 실행이 :b1 = ‘E’로 들어오면 옵티마이저는 40건이 나올 것으로 보고 인덱스 스캔 계획을 만든다. 합리적인 선택이다. 그런데 그 계획이 캐시에 남은 상태에서 다음 실행이 :b1 = ‘C’로 들어오면, 980만 건을 인덱스로 하나씩 찾아가게 된다.
반대 순서면 반대 현상이 난다. 첫 실행이 ‘C’였다면 풀 스캔 계획이 만들어지고, 40건짜리 조회도 980만 건을 훑는다.
재시작이나 통계정보 재수집으로 캐시가 날아갈 때마다 어느 값이 먼저 들어오느냐에 따라 성능이 달라진다. “가만히 있었는데 갑자기 느려졌다”의 정체가 이것이다.
확인하는 법
먼저 그 SQL이 계획을 몇 개 가지고 있는지 본다.
select sql_id, child_number, plan_hash_value, executions, buffer_gets,
round(buffer_gets / nullif(executions,0)) gets_per_exec
from v$sql
where sql_id = '&sql_id'
order by child_number;plan_hash_value가 두 개 이상이고 gets_per_exec 차이가 크면 같은 SQL이 서로 다른 계획으로 돌고 있다는 뜻이다.
다음으로 계획이 만들어질 때 어떤 값을 엿봤는지 확인한다.
select * from table(
dbms_xplan.display_cursor('&sql_id', &child_number, 'ADVANCED +PEEKED_BINDS')
);출력 하단의 Peeked Binds 섹션에 계획 수립에 사용된 값이 찍힌다. 이 값이 현재 느린 실행의 값과 다르다면 원인이 확정된 셈이다.
실제로 어떤 값들이 들어오는지는 별도 뷰에서 본다.
select name, datatype_string, value_string, last_captured
from v$sql_bind_capture
where sql_id = '&sql_id'
order by last_captured desc;이 뷰는 모든 실행을 기록하지 않는다. 일정 간격으로 표본만 잡기 때문에 값이 비어 있을 수 있다.
적응형 커서 공유가 이미 돌고 있다
오라클 11g부터는 이 문제를 스스로 완화하려는 기능이 들어 있다. 같은 SQL이 바인드 값에 따라 실제 반환 건수가 크게 달라진다는 것을 감지하면, 값 구간별로 계획을 나눠서 가지도록 커서를 분리한다.
select child_number, is_bind_sensitive, is_bind_aware, plan_hash_value, executions
from v$sql
where sql_id = '&sql_id';is_bind_sensitive가 Y면 오라클이 이 SQL을 감시 대상으로 보고 있다는 뜻이고, is_bind_aware가 Y면 이미 값에 따라 계획을 나눠 쓰고 있다는 뜻이다.
다만 이 기능은 한 번 느리게 돌아 본 다음에야 작동한다. 나쁜 계획으로 몇 번 실행되고 나서 학습하는 구조이기 때문에, 하루에 몇 번만 호출되는 야간 배치에서는 거의 도움이 되지 않는다. 캐시가 비워지면 학습도 초기화된다.
대응
상황에 따라 선택지가 다르다.
- 계획을 고정한다 — 어떤 값이 와도 무난한 계획이 하나 있다면 SQL Plan Baseline으로 그 계획을 고정하는 게 가장 안정적이다. 힌트를 코드에 박는 것보다 운영 중에 되돌리기 쉽다.
- 쿼리를 분리한다 — 조회 성격이 뚜렷하게 다르다면 아예 다른 SQL로 나눈다. 상태 코드처럼 값이 몇 개 안 되고 분포가 극단적인 경우 가장 확실하다.
- 해당 컬럼의 히스토그램을 제거한다 — 히스토그램이 없으면 옵티마이저는 균등 분포로 가정하고 값에 상관없이 같은 계획을 만든다. 편차는 남지만 예측 가능해진다.
- 리터럴을 쓴다 — 값 종류가 극소수이고 호출 빈도가 낮은 배치라면, 바인드 대신 리터럴로 매번 계획을 새로 만들게 하는 편이 나을 수 있다. 다만 OLTP 화면 쿼리에 쓰면 공유 풀이 파편화되므로 쓰지 않는다.
CURSOR_SHARING 파라미터를 건드리는 방법도 알려져 있지만, 인스턴스 전체에 영향을 주기 때문에 특정 쿼리 하나를 잡자고 쓸 설정은 아니다.
정리
바인드 변수 피킹으로 인한 장애는 재현이 어렵다는 점이 특징이다. 개발 환경에서는 데이터가 적어 분포 편중이 없고, 운영에서도 캐시 상태에 따라 나타났다 사라진다. 그래서 “느리다고 해서 봤더니 빠른데요”가 반복되기 쉽다.
판단 기준은 단순하다. 같은 SQL의 plan_hash_value가 여러 개인지, 그리고 실행당 buffer_gets가 계획마다 크게 다른지. 이 두 가지가 맞으면 코드나 인덱스를 손대기 전에 계획이 왜 갈렸는지부터 보는 게 순서다.