실행계획 분석

실행계획(Execution Plan)은 옵티마이저가 선택한 SQL 처리 절차를 트리 구조로 표현한 것이다. 어떤 순서로 테이블에 접근하고, 어떤 인덱스를 쓰며, 어떤 방식으로 조인하는지 보여 준다. 튜닝은 실행계획을 정확히 읽는 데서 시작한다.

예상 실행계획과 실제 실행계획

예상 실행계획은 바인드 변수 타입, 바인드 Peeking, 실행 시점의 통계 차이 때문에 실제 수행된 계획과 다를 수 있다. 실제 수행 통계를 보는 방법은 SQL 트레이스와 실행 통계에서 다룬다.

EXPLAIN PLAN과 DBMS_XPLAN.DISPLAY

EXPLAIN PLAN 명령

-- 1. PLAN_TABLE 생성 (10g 이후 기본 제공, 구버전은 아래 스크립트)
@?/rdbms/admin/utlxplan.sql

-- 2. 실행계획을 PLAN_TABLE에 저장
EXPLAIN PLAN SET STATEMENT_ID = 'query1' FOR
SELECT *
FROM   EMP E, DEPT D
WHERE  D.DEPTNO = E.DEPTNO
AND    E.SAL >= 2000;

-- 3. 저장된 실행계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'query1', 'TYPICAL'));

• EXPLAIN PLAN은 SQL을 실행하지 않고, 실행계획을 PLAN_TABLE에 저장만 한다.
• Oracle 10g부터는 SYS.PLAN_TABLE$ 테이블과 PLAN_TABLE 퍼블릭 시노님이 기본 제공되어 별도로 만들 필요가 없다.

DBMS_XPLAN.DISPLAY 출력 예

Plan hash value: 615168685

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     9 |   540 |     4  (25)| 00:00:01 |
|*  1 |  HASH JOIN                   |         |     9 |   540 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL          | EMP     |     9 |   360 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("D"."DEPTNO"="E"."DEPTNO")
   3 - filter("E"."SAL">=2000)

포맷 옵션

Predicate Information

실행계획 아래의 Predicate Information은 각 오퍼레이션에서 조건이 어떻게 사용되었는지 보여 준다. 튜닝에서 가장 중요하게 봐야 하는 정보다.

-- 인덱스 EMP_X01 = (DEPTNO, JOB, SAL), 조건: DEPTNO = 20 AND SAL >= 2000
|*  2 |   INDEX RANGE SCAN          | EMP_X01 |    10 |       |     1   (0)|

Predicate Information (identified by operation id):
   2 - access("DEPTNO"=20 AND "SAL">=2000)
       filter("SAL">=2000)

중간 컬럼 JOB에 조건이 없으므로 스캔 범위는 DEPTNO = 20으로만 정해지고, SAL 조건은 리프 블록을 읽으면서 걸러내는 필터 역할을 한다. 이처럼 access와 filter에 같은 조건이 함께 나타나면 사실상 필터로만 동작한다고 해석한다.

시험 포인트: 인덱스에서 access로 쓰인 조건만 스캔 범위를 줄인다. filter로만 쓰인 조건은 이미 읽은 데이터를 버리는 것이므로 스캔량을 줄이지 못한다. 인덱스 액세스 조건과 필터 조건의 구분은 인덱스 스캔 방식과 인덱스 설계에서 자세히 다룬다.

실행계획 읽는 순서

실행계획은 트리 구조이며 다음 규칙으로 읽는다.

---------------------------------------------------------------
| Id  | Operation                        | Name               |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                    |
|   1 |  NESTED LOOPS                    |                    |
|   2 |   NESTED LOOPS                   |                    |
|   3 |    TABLE ACCESS BY INDEX ROWID   | DEPT               |
|   4 |     INDEX RANGE SCAN             | DEPT_LOC_IDX       |
|   5 |    INDEX RANGE SCAN              | EMP_DEPTNO_IDX     |
|   6 |   TABLE ACCESS BY INDEX ROWID    | EMP                |
---------------------------------------------------------------

조인에서는 같은 레벨 두 자식 중 위쪽이 먼저(Outer, Build), 아래쪽이 나중(Inner, Probe)이다. 위 예에서 DEPT가 드라이빙 테이블, EMP가 Inner 테이블이다. 조인 방식별 읽기는 NL 조인, 소트 머지 조인, 해시 조인에서 다룬다.

시험 포인트: 실행계획 순서 문제는 "가장 안쪽 들여쓰기 → 같은 레벨은 위부터 → 부모"로 푼다. Id 번호 순서가 곧 실행 순서는 아니다.

주요 오퍼레이션

테이블·인덱스 액세스

조인

정렬·집계·기타

정렬 오퍼레이션은 소트 튜닝, 서브쿼리 관련 오퍼레이션은 서브쿼리 조인, 파티션 관련 오퍼레이션은 파티셔닝에서 다룬다.

Cost와 Cardinality

선택도(Selectivity)  = 1 / NDV           (= 조건, 히스토그램 없을 때)
카디널리티(Cardinality) = 총 로우 수 × 선택도

시험 포인트: 예상 실행계획의 Rows는 실제 건수가 아니라 Cardinality 추정치다. Cost가 낮다고 실제로 빠른 것은 아니며, 정확한 판단은 SQL 트레이스나 DISPLAY_CURSOR의 실측값으로 한다. 비용 계산 원리는 옵티마이저를 참고한다.

AutoTrace로 실행계획 보기

SQL*Plus에서 AutoTrace를 사용하면 실행계획과 실행 통계를 함께 볼 수 있다.

TRACEONLY EXPLAIN만 SQL을 실제로 실행하지 않는다. 실행 통계 항목의 의미는 SQL 트레이스와 실행 통계에서 다룬다.

SQL Server 실행계획

SQL Server는 다음 방식으로 예상 실행계획을 확인한다.

SET SHOWPLAN_TEXT ON;   -- 예상 실행계획 (텍스트, SQL 실행 안 함)
GO
SELECT * FROM dbo.EMP WHERE EMPNO = 7900;
GO

SET SHOWPLAN_ALL ON;    -- 예상 실행계획 + 예상 비용·건수 등 상세 정보
SET STATISTICS PROFILE ON; -- 실제 실행 후 실행계획과 실제 처리 건수
SET STATISTICS IO ON;   -- 실행 후 논리적·물리적 읽기 수
SET STATISTICS TIME ON; -- 파싱·컴파일·실행 시간

StmtText
-------------------------------------------------------------
  |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DEPTNO]))
       |--Clustered Index Scan(OBJECT:([EMP].[PK_EMP] AS [e]))
       |--Clustered Index Seek(OBJECT:([DEPT].[PK_DEPT] AS [d]),
                               SEEK:([d].[DEPTNO]=[e].[DEPTNO]))

• 읽는 순서는 Oracle과 같이 안쪽 들여쓰기부터, 같은 레벨은 위부터다.
• Management Studio의 그래픽 실행계획은 오른쪽에서 왼쪽으로, 위에서 아래로 읽는다.

핵심 요약

• 예상 실행계획(EXPLAIN PLAN, DISPLAY, TRACEONLY EXPLAIN)은 SQL을 실행하지 않으며 실제 계획과 다를 수 있다.
• EXPLAIN PLAN은 PLAN_TABLE에 저장하고 DBMS_XPLAN.DISPLAY로 조회한다. 기본 포맷은 TYPICAL이다.
• Predicate Information의 access는 스캔 범위를 정하는 조건, filter는 읽은 뒤 걸러내는 조건이다.
• 실행 순서는 안쪽 들여쓰기부터, 같은 레벨이면 위부터, 그 다음 부모 순이다. Id 순서가 실행 순서는 아니다.
• 조인에서 같은 레벨 위쪽이 Outer(Build), 아래쪽이 Inner(Probe)다.
• Rows는 Cardinality 추정치이며 Cardinality = 총 로우 수 × 선택도, 선택도 = 1/NDV(= 조건)다.
• Cost는 예상치이므로 실제 성능 비교는 실측 통계(블록 수, 시간)로 한다.
• SQL Server는 SET SHOWPLAN_TEXT/ALL로 예상 계획, SET STATISTICS PROFILE/IO/TIME으로 실측 정보를 본다.