옵티마이저 실행 계획을 확인하며 정리한 SQL 튜닝 순서

예전에 옵티마이저를 정리한 글에는 비용 기반 옵티마이저, 통계정보, 실행 계획, ALL_ROWS와 FIRST_ROWS, 힌트와 SQL Profile 같은 용어가 한꺼번에 들어 있었다. 개념은 각각 맞지만, 튜닝할 때 무엇부터 확인해야 하는지가 잘 보이지 않았다. 다시 정리하면서 기준을 하나로 좁혔다. SQL을 바꾸기 전에 옵티마이저가 예상한 것과 실제 실행에서 일어난 일을 비교한다.

옵티마이저는 실행 방법을 고른다

Oracle은 SQL을 받으면 문법과 객체를 확인하고, 가능한 변환과 접근 경로를 검토해 실행 계획을 만든다. 비용 기반 옵티마이저는 테이블·컬럼·인덱스 통계와 시스템 환경 등을 사용해 예상 행 수와 작업량을 계산하고 후보 계획을 비교한다.

여기서 실행 계획의 Cost를 실제 실행 시간으로 읽으면 안 된다. 비용은 후보 계획을 비교하기 위한 추정치다. 데이터 분포나 통계가 현실과 다르면 예상 행 수가 틀리고, 그 결과 인덱스 스캔과 전체 스캔, 조인 순서 같은 선택도 기대와 달라질 수 있다.

먼저 예상치와 실제치를 나란히 본다

EXPLAIN PLAN은 SQL을 실행하지 않고 옵티마이저가 선택할 계획을 보여준다. 다만 설명 시점의 환경과 실제 실행 환경이 다르면 실제 커서가 사용한 계획과 다를 수 있다. 실행된 커서의 계획을 확인할 수 있는 경우 DBMS_XPLAN.DISPLAY_CURSOR와 실행 통계를 함께 보는 편이 더 직접적이다.

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

출력에서 예상 행 수(E-Rows)와 실제 행 수(A-Rows)가 크게 어긋나는 단계를 찾는다. 차이가 크다면 조건의 선택도, 통계정보의 신선도, 데이터 편향이나 바인드 값 차이를 살핀다. 실행 통계 수집 설정과 권한은 DB 버전에 따라 확인해야 한다. 운영 SQL을 진단하려고 광범위한 통계 수집 옵션을 무작정 켜기보다 먼저 영향 범위를 확인한다.

테이블 통계가 오래됐다고 판단되면 DBMS_STATS로 수집하는 방법이 있지만, 모든 테이블에 같은 방식으로 즉시 실행하는 만능 처방은 아니다. 수집 시점과 샘플, 유지보수 정책을 확인하고 변경 전후 계획과 응답 시간을 비교한다.

응답 시간 목표와 전체 처리량은 다르다

Oracle의 ALL_ROWS는 전체 결과 처리량을 목표로 하는 기본 최적화 모드다. FIRST_ROWS_n은 지정된 개수의 앞쪽 행을 빨리 반환하는 데 초점을 둔다. 첫 화면에 몇 줄을 먼저 보여 주는 화면과 결과 전체를 집계하는 배치 작업은 목표가 다를 수 있다. 그렇다고 모드를 바꾸면 항상 빨라지는 것은 아니다. 실제 호출이 결과를 어디까지 읽는지, 페이지 크기와 정렬·조인이 어떤지 확인한 다음 판단한다.

바인드 변수도 실행 계획에 영향을 준다. 값의 분포가 치우친 컬럼에서는 하나의 계획이 모든 바인드 값에 적절하지 않을 수 있다. Oracle의 bind peeking과 adaptive cursor sharing 동작, 실제 child cursor와 바인드 환경을 확인해야 한다. SQL 문자열에 값을 직접 붙여 계획을 강제로 분기시키는 방식부터 택하지 않는다.

힌트보다 근거를 먼저 남긴다

힌트는 옵티마이저에 특정 선택을 제안하거나 제한하는 수단이다. 데이터 분포와 인덱스가 바뀐 뒤에도 힌트가 계속 적절한지 확인해야 하므로, 예상 행 수가 틀린 이유와 통계 상태를 먼저 살핀다. SQL Profile은 추정 오류를 보정하는 보조 정보이고, SQL Plan Baseline은 검증된 계획을 관리해 계획 변경에 따른 성능 퇴행을 줄이는 수단이다. 둘의 목적은 같지 않다.

튜닝 순서를 짧게 적으면 이렇다. 재현 가능한 입력과 실행 시간 측정, 실제 커서 계획 확인, 예상·실제 행 수 비교, 통계와 인덱스 검토, 쿼리 구조 조정, 마지막으로 Profile·Baseline·힌트 검토다. 실행 계획 자체를 읽는 기본은 Oracle 공식 가이드의 실행 계획 생성과 표시, 통계는 DBMS_STATS 문서를 참고했다.

옵티마이저를 “SQL을 알아서 빠르게 만들어 주는 기능”으로만 생각하면 결과가 기대와 다를 때 손댈 곳을 찾기 어렵다. 예상이 빗나간 지점을 실행 통계에서 좁히고, 한 번에 한 가지를 바꿔 결과를 비교하는 것이 내가 다시 정리한 튜닝의 출발점이다. 동적 조회를 애플리케이션 코드에서 구성하는 방식은 JPQL과 QueryDSL 선택 기준에 별도로 적었다.

참고 문서: Oracle 실행 계획 표시, Oracle 옵티마이저 통계 개념, SQL Plan Management

당시 기록: Optimizer