실행계획 읽기

예상 계획과 실행 후 계획의 차이, ALLSTATS LAST의 E-Rows, A-Rows 및 Buffers 읽기

실행계획EXPLAIN PLANDBMS_XPLANDISPLAY_CURSORALLSTATS LASTE-RowsA-RowsBuffersgather_plan_statistics

#핵심 요약

EXPLAIN PLAN은 옵티마이저가 쓸 것으로 예상한 계획이고, 실행 환경 차이 때문에 실제 실행 때의 계획과 다를 수 있음
바인드 변수가 있으면 특히 그러함

실제 실행된 계획과 실행 통계는 커서 캐시에서 DBMS_XPLAN.DISPLAY_CURSOR로 봄
실습에서는 DISPLAY_CURSOR(NULL, NULL, 'allstats last')로 버퍼 수를 비교했음

E-Rows는 옵티마이저의 예상 건수, A-Rows는 실제 처리 건수임
둘의 차이가 크면 카디널리티 추정이 틀어진 것임

버퍼(Buffers) 수는 SQL을 실행하려고 읽은 블록 수이며 작을수록 좋음

#개념과 동작 원리

함수	용도
`DBMS_XPLAN.DISPLAY`	`EXPLAIN PLAN`으로 `PLAN_TABLE`에 저장된 계획 표시
`DBMS_XPLAN.DISPLAY_CURSOR`	커서 캐시에 올라온 커서의 계획 표시 계획에 더해 I/O, 메모리, 시간 같은 실행 통계(`V$SQL_PLAN_STATISTICS_ALL` 기반)를 보여 줄 수 있음
`DBMS_XPLAN.DISPLAY_AWR`	AWR에 저장된 계획 표시

계획의 주요 컬럼:

컬럼	뜻
`Starts`	그 단계가 실행된 횟수
`E-Rows`	예상 행 수
`A-Rows`	실제 행 수
`A-Time`	실제 경과 시간
`Buffers`	읽은 버퍼 수

계획 예에서는 예상 행이 1인데 실제 9135행이 나온 MERGE JOIN CARTESIAN 단계처럼 예상과 실제 행 수 차이가 큰 단계를 확인함
계획 아래의 Predicate Information에는 단계 번호별로 access(...), filter(...) 조건이 표시됨
access와 filter의 차이는 스캔 범위와 필터 조건에서 다룸

Starts는 해당 연산이 시작된 횟수이며 LAST 형식에서는 마지막 실행의 횟수를 읽음
A-Time은 실제 경과 시간이며 기반 통계 LAST_ELAPSED_TIME의 단위는 마이크로초임

#주요 용어와 구성 요소

구문	의미
Row Source 트리	부모가 자식으로부터 행을 받아 처리하는 계획의 구조임
ID와 들여쓰기	단계 번호와 부모, 자식 관계를 구별하는 표시임
SQL_ID, child number	조회할 실제 커서를 식별하는 값임
LAST	해당 커서의 마지막 실행 통계를 표시하는 형식임

#필수 명령어 또는 SQL

항목	실행 조건
작업 대상	설명할 SQL의 예상 계획과 실제 커서의 마지막 실행 통계
필요 권한	대상 SELECT 권한, 계획 테이블 접근 권한, DISPLAY_CURSOR 관련 동적 뷰의 SELECT 또는 READ 권한
바꿀 입력값	EMP와 조건 값, 실제 커서의 SQL_ID, child number, 표시 형식
결과 해석	E-Rows와 A-Rows는 Starts와 연산 범위를 함께 비교함 추가 실행 시간과 행 수는 기재 없음

#예상 실행계획

SQL*Plus 또는 SQL Developer객체 또는 설정을 변경함

EXPLAIN PLAN FOR
SELECT ename, sal
  FROM emp
 WHERE sal BETWEEN 1000 AND 3000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

결과 해석: 개별 실행 출력은 기재 없음

#실제 실행계획과 버퍼 수

실습에서는 실행 통계를 모으려고 SYS에서 ALTER SYSTEM SET statistics_level = all을 실행한 뒤(인스턴스 전체 설정 변경) SQL을 실행하고 바로 다음을 조회했음
실습의 다른 예에서는 /*+ gather_plan_statistics */ 힌트를 붙였음(집합 연산)

SQL*Plus 또는 SQL Developer조회만 함

SELECT /*+ gather_plan_statistics */ ename, sal
  FROM emp
 WHERE sal = 3000;

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last'));

결과 해석: 같은 세션에서 방금 실행한 SQL의 실제 계획과 마지막 실행의 통계(A-Rows, Buffers 등)가 나옴
실습에서는 이 방법으로 같은 결과를 내는 두 SQL의 버퍼 수를 비교해 더 나은 쪽을 골랐음(문자 함수, 인덱스 정의)

실습 "SQL 튜닝 수업" 메모에는 EXPLAIN PLAN FOR 뒤에 바로 DISPLAY_CURSOR 조회가 이어져 적혀 있음
두 방법은 각각 예상 계획(DISPLAY)과 실제 계획(DISPLAY_CURSOR)을 보는 것이라 함께 쓰는 형태는 맞지 않음

#느린 SQL 진단 순서

느린 SQL을 확인함
예상 건수(E-Rows)와 실제 건수(A-Rows)를 비교함
비용 계산 근거(통계정보, 선택도)를 확인함

실습 예: 예상 1건인데 실제 100만 건이 처리되면 카디널리티 추정이 틀어진 것임
이때 힌트보다 먼저 통계정보가 최신인지 확인함(통계정보와 실행계획 변화)

#구문과 옵션 설명

계획은 숫자가 큰 단계부터 일괄 실행하는 목록이 아님
첫 번째 자식 없는 연산이 행을 부모로 넘기고 같은 부모의 다음 자식을 따라 결과를 결합하는 트리로 읽음
조인, 정렬 등의 부모 연산과 자식 입력을 함께 확인함

ALLSTATS LAST가 보여 줄 실제 통계는 먼저 수집되어 있어야 함
GATHER_PLAN_STATISTICS 힌트 또는 적절한 통계 수집 설정을 사용한 커서를 대상으로 함
대상 조회를 끝까지 fetch하지 않으면 전체 결과 처리 통계가 아님

#사용 조건과 주의사항

DISPLAY_CURSOR는 V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL, V$SQL의 SELECT 또는 READ 권한을 필요로 함
중간에 도구가 다른 SQL을 수행하면 NULL 지정이 의도한 SQL을 가리키지 않을 수 있어 SQL_ID와 child number를 명시해 구분함

EXPLAIN PLAN에는 설명할 SQL의 실행 권한과 계획 테이블 쓰기 권한이 필요함
현재 SQL의 실제 행 수, 시간을 생성하는 명령은 아님
DISPLAY_AWR로 저장된 AWR 계획을 사용하는 경우 Diagnostics Pack 적용 조건도 확인함