예상 실행계획 확인: EXPLAIN PLAN·PLAN_TABLE·AUTOTRACE

핵심 요약

Oracle의 실행계획 확인 도구는 SQL 실행 여부, 계획의 출처, 통계의 관찰 단위가 서로 다릅니다.

SQL을 실행하지 않고 예상 계획 저장
  → EXPLAIN PLAN

PLAN_TABLE의 예상 계획 출력
  → DBMS_XPLAN.DISPLAY

SQL*Plus에서 결과·예상 계획·문장 전체 통계 조합
  → AUTOTRACE

Shared Pool에 Load된 실제 Child Cursor 계획
  → DBMS_XPLAN.DISPLAY_CURSOR

도구를 선택하기 전에 다음 세 질문에 답해야 합니다.

1. 대상 SQL이 실제로 실행되는가?
2. 표시되는 계획은 예상 계획인가 실제 Cursor 계획인가?
3. 통계는 문장 전체 요약인가 Operation별 통계인가?

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → SQL 분석 도구 → 예상 실행계획 범위에서 EXPLAIN PLAN, PLAN_TABLE, DBMS_XPLAN.DISPLAY, SQL*Plus AUTOTRACE를 다룹니다. DISPLAY_CURSOR ... ALLSTATS LAST의 Row Source별 상세 해석은 앞선 실행계획 기초 및 후속 SQL 트레이스 이론과 연결합니다.

학습 목표

이 이론을 학습한 뒤에는 다음 내용을 설명할 수 있어야 합니다.

• PLAN_TABLE과 SYS.PLAN_TABLE$의 관계를 설명한다.
• 공용 Global Temporary PLAN_TABLE과 사용자 Local PLAN_TABLE을 구분한다.
• EXPLAIN PLAN이 대상 SQL을 실행하지 않고 예상 계획을 저장한다는 점을 설명한다.
• EXPLAIN PLAN 자체는 DML이며 자동 Commit을 발생시키지 않는다는 점을 설명한다.
• STATEMENT_ID, PLAN_ID, TIMESTAMP의 역할을 구분한다.
• DBMS_XPLAN.DISPLAY의 네 인자를 설명한다.
• filter_preds의 용도와 보안 주의점을 설명한다.
• Bind SQL에서 예상 계획이 실제 Cursor 계획과 다를 수 있는 이유를 설명한다.
• AUTOTRACE 옵션별 대상 SQL 실행·결과 출력·예상 계획·통계 표시 여부를 구분한다.
• TRACEONLY가 무조건 SQL 실행을 막는 옵션이 아님을 설명한다.
• AUTOTRACE 계획과 통계가 서로 다른 출처에서 만들어지는 점을 설명한다.
• DML에 AUTOTRACE를 사용할 때 Transaction·Lock·Redo를 고려한다.
• 실제 운영 SQL의 계획을 확인할 때 DISPLAY_CURSOR를 우선하는 이유를 설명한다.

1. 목적에 따라 도구를 선택한다

AUTOTRACE의 ON 또는 TRACEONLY에 EXPLAIN·STATISTICS를 명시하지 않으면 기본적으로 두 항목을 모두 표시합니다.

2. PLAN_TABLE의 역할

PLAN_TABLE은 EXPLAIN PLAN이 생성한 예상 실행계획의 각 Operation을 행 단위로 저장합니다.

EXPLAIN PLAN
  → Optimizer가 예상 계획 선택
  → Operation별 행을 PLAN_TABLE에 Insert
  → DBMS_XPLAN.DISPLAY가 표 형태로 Format

대표 저장 정보는 다음과 같습니다.

• STATEMENT_ID
• PLAN_ID
• TIMESTAMP
• ID, PARENT_ID
• OPERATION, OPTIONS
• OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS
• CARDINALITY, BYTES, COST
• Access·Filter Predicate
• Query Block·Projection 관련 정보

2.1 현대 Oracle의 기본 PLAN_TABLE

Oracle Database는 SYS Schema에 Global Temporary Table인 PLAN_TABLE$을 자동 생성하고 PLAN_TABLE Synonym을 제공합니다. 필요한 권한은 일반적으로 PUBLIC에 제공되므로 각 Session은 자신의 Temporary 영역에서 PLAN_TABLE 데이터를 사용합니다.

SYS.PLAN_TABLE$
  → Global Temporary Table

PLAN_TABLE
  → 공용 Synonym

각 Session
  → 자신의 임시 계획 행만 확인

2.2 PLAN_TABLE 확인

SELECT owner,
       object_name,
       object_type
FROM   all_objects
WHERE  object_name IN ('PLAN_TABLE', 'PLAN_TABLE$')
ORDER BY owner, object_name, object_type;

Synonym은 다음처럼 확인할 수 있습니다.

SELECT owner,
       synonym_name,
       table_owner,
       table_name
FROM   all_synonyms
WHERE  synonym_name = 'PLAN_TABLE';

2.3 catplan.sql과 utlxplan.sql

환경에 PLAN_TABLE Global Temporary Table과 Synonym이 없다면 DBA는 다음 스크립트를 사용할 수 있습니다.

@$ORACLE_HOME/rdbms/admin/catplan.sql

전통적인 Sample Definition과 같은 Local PLAN_TABLE이 필요하면 다음 스크립트를 사용할 수 있습니다.

@?/rdbms/admin/utlxplan.sql

catplan.sql
  → SYS.PLAN_TABLE$ Global Temporary Table과 PLAN_TABLE Synonym 구성

utlxplan.sql
  → 현재 Schema에 Sample 구조의 Local PLAN_TABLE 생성

실제 사용은 Database 버전과 DBA 정책을 따릅니다.

3. EXPLAIN PLAN으로 예상 계획 생성하기

기본 문법은 다음과 같습니다.

EXPLAIN PLAN FOR
SELECT employee_id,
       last_name
FROM   employees
WHERE  department_id = 50;

EXPLAIN PLAN은 대상 SELECT의 결과 행을 실제로 조회하지 않습니다. Optimizer가 설명 시점의 환경을 기준으로 예상 계획을 만들고 PLAN_TABLE에 행을 Insert합니다.

3.1 EXPLAIN PLAN 자체는 DML이다

EXPLAIN PLAN은 PLAN_TABLE에 행을 Insert하는 DML Statement입니다. DDL이 아니므로 자동 Commit을 발생시키지 않습니다.

대상 SELECT·UPDATE·DELETE
  → 실제로 실행하지 않음

PLAN_TABLE
  → 예상 계획 행을 Insert

Implicit Commit
  → 발생하지 않음

따라서 Local Permanent PLAN_TABLE을 사용하는 환경에서는 Transaction 정책에 맞게 계획 행의 Commit·Rollback을 관리합니다.

3.2 STATEMENT_ID 지정

EXPLAIN PLAN
SET STATEMENT_ID = 'EMP_DEPT_V1'
FOR
SELECT employee_id,
       last_name
FROM   employees
WHERE  department_id = 50;

STATEMENT_ID는 여러 계획을 논리적으로 구분하는 사용자 지정 값입니다. 같은 ID를 반복 사용하면 여러 Plan이 존재할 수 있으므로 가능하면 비교 목적에 맞는 고유한 이름을 사용합니다.

EXPLAIN PLAN
SET STATEMENT_ID = 'EMP_DEPT_V2'
FOR
SELECT employee_id,
       last_name
FROM   employees
WHERE  department_id = 50
AND    salary >= 5000;

3.3 PLAN_ID와 TIMESTAMP

같은 STATEMENT_ID로 여러 계획이 저장될 수 있으므로 정확한 계획을 선택할 때 PLAN_ID나 TIMESTAMP를 함께 확인합니다.

4. DBMS_XPLAN.DISPLAY로 예상 계획 출력하기

기본 사용 예시는 다음과 같습니다.

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY(
    table_name   => 'PLAN_TABLE',
    statement_id => 'EMP_DEPT_V1',
    format        => 'TYPICAL +PREDICATE +ALIAS +NOTE'
  )
);

4.1 DISPLAY의 네 인자

DBMS_XPLAN.DISPLAY는 다음 네 인자를 가집니다.

대부분의 기본 사용에서는 앞의 세 인자만 작성합니다.

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY(
    NULL,
    NULL,
    'TYPICAL +PREDICATE'
  )
);

statement_id를 지정하지 않으면 기본적으로 가장 최근에 Explain된 계획을 표시합니다.

4.2 같은 STATEMENT_ID에서 PLAN_ID 선택

같은 STATEMENT_ID로 여러 Plan이 있을 때는 filter_preds를 이용해 PLAN_ID를 제한할 수 있습니다.

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY(
    table_name   => 'PLAN_TABLE',
    statement_id => 'EMP_DEPT_V1',
    format        => 'TYPICAL +PREDICATE',
    filter_preds  => 'plan_id = 42'
  )
);

filter_preds는 PLAN_TABLE에 적용되는 SQL Predicate 문자열이므로 Application이 사용자 입력을 그대로 전달하면 SQL Injection 위험이 있습니다. 미리 정한 안전한 조건만 사용합니다.

4.3 자주 사용하는 Format

초기 학습에서는 TYPICAL +PREDICATE +ALIAS +NOTE를 기본으로 사용하면 구조와 조건 적용 위치를 함께 확인하기 쉽습니다.

5. EXPLAIN PLAN의 범위와 한계

EXPLAIN PLAN은 실제 수행된 Cursor가 아니라 설명 시점의 예상 계획입니다.

다음 요소가 실제 실행과 다를 수 있습니다.

• 실제 Bind 값과 Bind 데이터 타입
• Parsing Schema와 객체 해석
• Session·System Optimizer Parameter
• 통계정보·Histogram과 Object 상태
• Library Cache에 이미 존재하는 Child Cursor
• SQL Profile·Patch·Plan Baseline
• Adaptive Plan의 Runtime 최종 선택

5.1 Bind SQL 제한

Oracle 공식 문서는 Bind Variable이 있는 SQL의 EXPLAIN PLAN이 실제 계획을 나타내지 않을 수 있다고 설명합니다.

EXPLAIN PLAN FOR
SELECT *
FROM   orders
WHERE  status = :status;

이 계획은 실제 Bind 값이 'COMPLETE'인지 'ERROR'인지와 기존 Child Cursor의 환경을 정확히 재현하지 못할 수 있습니다.

또한 Date Bind의 암묵적 형변환을 수행하는 Statement에는 EXPLAIN PLAN 제한이 존재할 수 있으므로 실제 Data Type을 명확히 맞춥니다.

5.2 EXPLAIN PLAN의 적절한 용도

• SQL 실행 전 후보 Access Path·Join 구조 확인
• 개발 중 변경 전후 예상 계획 비교
• Hint가 계획 후보를 만드는지 1차 확인
• 교육·설계 단계에서 예상 Row Source 구조 확인

5.3 실제 Cursor 확인이 필요한 상황

• 운영에서 특정 SQL_ID가 느렸던 원인 분석
• Bind 값에 따라 여러 Child Cursor가 존재
• 예상 계획과 실제 수행이 다름
• Operation별 Starts, A-Rows, Buffers 분석
• Adaptive Plan의 Final Plan 확인

이 경우 DBMS_XPLAN.DISPLAY_CURSOR를 우선합니다.

6. PLAN_TABLE 데이터 관리

6.1 Global Temporary PLAN_TABLE

공용 Global Temporary Table을 사용하는 경우 각 Session은 자신의 계획 데이터만 봅니다. Session 종료 시 임시 행이 정리됩니다.

6.2 Local Permanent PLAN_TABLE

사용자 Schema에 영구 PLAN_TABLE을 만든 경우 과거 계획 행이 남을 수 있습니다.

DELETE FROM plan_table
WHERE  statement_id = 'EMP_DEPT_V1';

정확한 비교를 위해 생성 전에 같은 식별자의 기존 행을 삭제하거나 고유한 STATEMENT_ID를 사용합니다.

DELETE FROM plan_table
WHERE  statement_id = 'EMP_DEPT_V1';

EXPLAIN PLAN
SET STATEMENT_ID = 'EMP_DEPT_V1'
FOR
SELECT employee_id,
       last_name
FROM   employees
WHERE  department_id = 50;

PLAN_TABLE을 무조건 매번 전체 삭제할 필요는 없습니다. DBMS_XPLAN.DISPLAY는 기본적으로 조건에 맞는 가장 최근 Explain Plan을 표시할 수 있지만, 오래된 행이 과도하면 Display 성능과 관리성이 나빠질 수 있으므로 주기적으로 정리합니다.

7. AUTOTRACE란 무엇인가

AUTOTRACE는 Oracle SQL 문법이 아니라 SQL*Plus의 SET System Variable입니다.

SET AUTOTRACE ON

정상적으로 완료된 SELECT·INSERT·UPDATE·DELETE 등의 Statement에 대해 다음 내용을 조합하여 출력할 수 있습니다.

• Query 결과 행
• Optimizer 예상 실행 경로
• Statement 실행 통계

AUTOTRACE의 실행계획은 EXPLAIN PLAN과 DBMS_XPLAN을 이용해 생성합니다. 실제 Cursor의 Runtime Plan이 아닙니다.

8. AUTOTRACE 옵션별 동작

8.1 ON과 TRACEONLY의 기본값

ON 또는 TRACEONLY만 작성하면 EXPLAIN STATISTICS를 모두 요청하는 것이 기본입니다.

SET AUTOTRACE ON
  = 결과 인쇄 + SQL 실행 + 예상 계획 + 통계

SET AUTOTRACE TRACEONLY
  = 결과 인쇄 생략 + SQL 실행 + 예상 계획 + 통계

8.2 TRACEONLY의 정확한 의미

TRACEONLY는 Query 결과의 화면 인쇄를 생략합니다. SQL 실행 자체를 무조건 막지 않습니다.

STATISTICS가 포함되면 SQL*Plus는 Query 데이터를 Server에서 Fetch하지만 화면에 출력하지 않습니다.

TRACEONLY STATISTICS
  → Query 실제 실행
  → 결과 Fetch
  → 화면에 행 미출력
  → SQL 전체 통계 출력

대량 Query에 TRACEONLY를 사용해도 Database I/O·CPU·Fetch 작업량은 발생합니다.

8.3 TRACEONLY EXPLAIN

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *
FROM orders
WHERE customer_id = 100;

이 조합은 대상 Query를 실행하지 않고 EXPLAIN PLAN을 이용한 예상 계획만 표시합니다.

단, EXPLAIN PLAN 자체는 PLAN_TABLE에 계획 행을 Insert하는 DML입니다.

9. DML에서 AUTOTRACE 사용 시 주의

다음 설정은 UPDATE를 실제로 수행합니다.

SET AUTOTRACE TRACEONLY

UPDATE employees
SET    salary = salary * 1.05
WHERE  department_id = 50;

TRACEONLY는 화면 출력을 억제할 뿐 다음 작업을 막지 않습니다.

• 실제 데이터 변경
• Row Lock
• Undo 생성
• Redo 생성
• Trigger 실행
• Transaction 유지

자동 Rollback도 수행하지 않습니다.

안전한 절차는 다음과 같습니다.

1. 실습 전 대상 행 수 확인
2. 별도 테스트 Schema·Transaction 사용
3. AUTOCOMMIT 상태 확인
4. Lock과 Trigger 영향 확인
5. COMMIT 또는 ROLLBACK 계획 확정

대상 DML을 실행하지 않고 예상 계획만 보려면 TRACEONLY EXPLAIN 또는 EXPLAIN PLAN FOR를 사용합니다.

10. AUTOTRACE 통계

STATISTICS가 포함되면 Statement 실행 전후 Session 통계의 차이를 문장 전체 요약으로 표시합니다.

10.1 Logical·Physical I/O

consistent gets
  → Read Consistency 기준 Logical Block 요청

db block gets
  → Current Mode Block 요청

physical reads
  → Storage에서 실제 읽은 Block

한 Block을 Disk에서 한 번 읽고 Buffer Cache에서 여러 번 접근할 수 있으므로 Logical I/O와 Physical I/O는 같은 값이 아닙니다.

10.2 문장 전체 요약의 한계

AUTOTRACE 통계는 SQL 전체의 Session 통계 차이입니다.

다음 내용은 직접 알려 주지 않습니다.

• 어느 Operation에서 I/O가 발생했는가
• 각 Operation의 Starts
• E-Rows와 A-Rows 차이
• Row Source별 Buffers와 시간

Operation별 분석에는 Runtime Statistics를 수집한 뒤 DISPLAY_CURSOR ... ALLSTATS LAST를 사용합니다.

10.3 통계용 보조 Connection

SQLPlus가 STATISTICS Report를 생성할 때 통계를 읽기 위한 두 번째 Database Connection을 자동 생성할 수 있습니다. 이 Connection은 STATISTICS를 OFF로 바꾸거나 SQLPlus를 종료할 때 닫힙니다.

Multitenant 환경에서 ALTER SESSION SET CONTAINER로 Container를 전환하며 AUTOTRACE를 사용하면 통계가 일관되지 않을 수 있다는 공식 주의사항이 있으므로 PDB Context를 명확히 확인합니다.

11. AUTOTRACE 계획과 통계의 출처

AUTOTRACE에서 계획과 통계가 동시에 보이더라도 두 정보의 출처는 다릅니다.

Execution Plan
  → EXPLAIN PLAN + DBMS_XPLAN
  → 예상 계획

Statistics
  → 대상 SQL 실제 실행
  → 문장 전체 Session 통계 차이

따라서 다음 상황이 가능할 수 있습니다.

AUTOTRACE 예상 Plan
  ≠ 실제 실행에 사용된 Child Cursor Plan

AUTOTRACE 통계
  = 실제 Statement 수행의 전체 작업량

Bind SQL이나 여러 Child Cursor가 있는 SQL에서 예상 Plan의 특정 Operation이 통계를 발생시켰다고 단정하면 안 됩니다. 실제 Plan과 Operation별 통계는 DISPLAY_CURSOR로 확인합니다.

12. AUTOTRACE와 DBMS_XPLAN 권한

12.1 AUTOTRACE 준비

AUTOTRACE에는 환경에 따라 다음이 필요합니다.

• 사용할 수 있는 PLAN_TABLE
• PLUSTRACE Role 또는 통계 조회에 필요한 권한

DBA는 다음 스크립트를 사용할 수 있습니다.

@$ORACLE_HOME/sqlplus/admin/plustrce.sql

정책에 따라 사용자에게 Role을 부여합니다.

GRANT PLUSTRACE TO study_user;

12.2 DISPLAY_CURSOR 권한

DBMS_XPLAN.DISPLAY_CURSOR 사용자는 다음 Fixed View에 대한 SELECT 또는 READ 권한이 필요합니다.

• V$SQL
• V$SQL_PLAN
• V$SESSION
• V$SQL_PLAN_STATISTICS_ALL

관련 권한은 SELECT_CATALOG_ROLE에 포함될 수 있지만 운영에서는 최소 권한 정책을 따릅니다.

13. 상황별 도구 선택

상황 A: 실행 없이 예상 구조 확인

EXPLAIN PLAN
SET STATEMENT_ID = 'ORDER_Q1'
FOR
SELECT order_id,
       order_date
FROM   orders
WHERE  customer_id = 100;

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY(
    'PLAN_TABLE',
    'ORDER_Q1',
    'TYPICAL +PREDICATE +ALIAS +NOTE'
  )
);

상황 B: SQL*Plus에서 결과 없이 전체 I/O 요약

SET AUTOTRACE TRACEONLY STATISTICS

SELECT order_id,
       order_date
FROM   orders
WHERE  customer_id = 100;

SET AUTOTRACE OFF

Query는 실제 실행·Fetch됩니다.

상황 C: SQL 실행 없이 AUTOTRACE 예상 계획

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT order_id,
       order_date
FROM   orders
WHERE  customer_id = 100;

SET AUTOTRACE OFF

상황 D: 실제 Child Cursor Plan

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :sql_id,
    :child_number,
    'TYPICAL +PREDICATE +ALIAS +NOTE'
  )
);

상황 E: Operation별 Runtime Statistics

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       order_date
FROM   orders
WHERE  customer_id = :customer_id;

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :sql_id,
    :child_number,
    'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
  )
);

14. 안전한 사용 순서

1. 대상 SQL을 실제로 실행해도 되는지 확인합니다.
2. 계획이 예상인지 실제 Cursor 계획인지 구분합니다.
3. AUTOTRACE Option에서 EXPLAIN·STATISTICS 포함 여부를 확인합니다.
4. DML이라면 Lock·Undo·Redo·Trigger·Transaction을 확인합니다.
5. PLAN_TABLE에 여러 계획이 있으면 STATEMENT_ID·PLAN_ID를 식별합니다.
6. Bind SQL은 EXPLAIN PLAN만으로 최종 결론을 내리지 않습니다.
7. AUTOTRACE 통계를 특정 Operation과 직접 연결하지 않습니다.
8. 실제 Plan은 SQL_ID·CHILD_NUMBER로 DISPLAY_CURSOR에서 확인합니다.
9. Operation별 실제 통계가 필요하면 Plan Statistics를 수집합니다.
10. 개발·검증 비교는 SQL Text, Bind, Schema, Parameter, Statistics와 Cache 조건을 통제합니다.

15. 자주 혼동하는 판단

16. 핵심 정리

PLAN_TABLE
  → 예상 실행계획 Operation 저장

EXPLAIN PLAN
  → 대상 SQL 실행 없이 예상 계획 생성
  → PLAN_TABLE Insert
  → Implicit Commit 없음

DBMS_XPLAN.DISPLAY
  → PLAN_TABLE 출력
  → table_name, statement_id, format, filter_preds

AUTOTRACE
  → SQL*Plus 기능
  → Option별 실행·결과·예상 계획·문장 통계 조합

TRACEONLY
  → 결과 인쇄 억제
  → 실행 억제와 같은 뜻이 아님

DISPLAY_CURSOR
  → Shared Pool의 실제 Child Cursor Plan

실행계획 도구를 안전하게 사용하는 핵심은 실행 여부·계획 출처·통계 범위를 먼저 구분하는 것입니다.