Oracle - 실행 계획 분석

2025-01-06

2026-04-25

Oracle 실행 계획 분석

오라클 쿼리의 실행 계획을 확인하고 분석하는 다양한 방법을 다룹니다.

실행 계획은 오라클 옵티마이저가 SQL 문을 실행하기 위해 선택한 작업 순서와 방법입니다. 효과적인 쿼리 튜닝을 위해서는 실행 계획을 정확히 읽고 분석할 수 있어야 합니다.

실행 계획을 본다는 것은 단순히 INDEX RANGE SCAN 이 나왔는지 확인하는 수준이 아니다.
실제로는 다음 질문에 답하는 과정에 가깝다.

어떤 테이블을 먼저 읽었는가
예상 건수와 실제 건수가 얼마나 차이 나는가
불필요한 Full Scan, Sort, Temp 사용이 발생하는가
조인 순서와 조인 방식이 데이터 특성에 맞는가
인덱스, 통계, SQL 구조 중 무엇이 병목의 원인인가

실행 계획을 볼 때 가장 먼저 볼 것

실행 계획을 처음 볼 때는 아래 항목부터 확인하는 것이 좋다.

Operation: 어떤 작업을 수행하는가
Rows: 옵티마이저가 예상한 결과 건수
Cost: 옵티마이저가 계산한 상대적 비용
Predicate Information: 어떤 조건이 접근 조건이고 어떤 조건이 필터 조건인가
Plan Hash Value: 실행 계획이 바뀌었는지 비교할 수 있는 값

특히 Rows 는 매우 중요하다.
예상 건수가 실제와 크게 다르면, 그 뒤의 조인 방식이나 액세스 경로도 잘못 선택될 가능성이 높다.

실행 계획 읽는 순서

실행 계획은 보통 위에서 아래로 읽는 것처럼 보이지만, 실제 수행 흐름은 그렇지 않다.
대체로 가장 안쪽의 자식 노드부터 시작해서 부모 노드로 올라간다고 이해하면 된다.

예를 들어 아래처럼 나왔다면:

SELECT STATEMENT
  HASH JOIN
    TABLE ACCESS FULL DEPARTMENTS
    TABLE ACCESS FULL EMPLOYEES

의미는 다음과 같다.

DEPARTMENTS 를 읽는다.
EMPLOYEES 를 읽는다.
두 결과를 HASH JOIN 한다.
최종적으로 SELECT STATEMENT 가 결과를 반환한다.

즉, 실행 계획의 들여쓰기 구조를 보면서 부모-자식 관계를 읽는 것이 핵심이다.

자주 나오는 실행 계획 연산

TABLE ACCESS FULL

테이블 전체를 읽는 방식이다.
무조건 나쁜 것은 아니며, 아래 경우에는 오히려 정상적인 선택일 수 있다.

전체 데이터 중 많은 비율을 읽을 때
적절한 인덱스가 없을 때
인덱스를 타는 것보다 Full Scan 이 더 저렴할 때

INDEX RANGE SCAN

인덱스 범위 검색이다.
등호 조건, 범위 조건, 선두 컬럼 조건이 적절히 맞을 때 자주 사용된다.

INDEX UNIQUE SCAN

유니크 인덱스를 통해 정확히 한 건을 찾는 경우다.
일반적으로 매우 효율적인 액세스 방식이다.

TABLE ACCESS BY INDEX ROWID

인덱스로 ROWID 를 찾은 뒤, 실제 테이블 블록을 다시 읽는 방식이다.
보통 INDEX RANGE SCAN 과 함께 나타난다.

SORT ORDER BY / SORT GROUP BY

정렬이나 그룹핑을 위해 별도 Sort 작업이 발생한 경우다.
데이터 양이 많으면 Temp 사용량이 늘 수 있으므로 주의해서 봐야 한다.

NESTED LOOPS / HASH JOIN / MERGE JOIN

조인 방식이다.
실행 계획 분석에서는 조인 종류 자체보다, 그 조인 방식이 현재 건수와 인덱스 구조에 맞는지가 더 중요하다.

Access Predicate 와 Filter Predicate

실행 계획에서 Predicate Information 은 매우 중요하다.
같은 인덱스를 써도 조건이 access 로 들어가는지, filter 로만 처리되는지에 따라 성능 차이가 크다.

access: 인덱스나 테이블에 접근할 때 바로 활용되는 조건
filter: 읽어온 뒤 추가로 걸러내는 조건

예를 들어 복합 인덱스 (dept_id, emp_no) 가 있는데 emp_no 만 조건에 쓰면, 기대만큼 효율적인 접근이 안 될 수 있다.
실행 계획에서 이 차이는 Predicate Information 에 그대로 드러난다.

EXPLAIN PLAN 사용

EXPLAIN PLAN 은 SQL 을 실제로 실행하지 않고, 옵티마이저가 예상한 실행 계획을 미리 보는 방법이다.

장점은 가볍고 빠르다는 점이다.
하지만 한계도 분명하다.

실제 실행 결과가 아니다.
바인드 변수, 세션 환경, Adaptive 동작이 실제와 다를 수 있다.
실제 Rows, 실제 Buffer Gets 같은 실행 통계는 알 수 없다.

따라서 EXPLAIN PLAN 은 초벌 확인용으로는 좋지만, 운영 문제를 분석할 때는 DBMS_XPLAN.DISPLAY_CURSOR 로 실제 실행 계획을 보는 편이 더 중요하다.

-- 실행 계획 생성
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

-- 실행 계획 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Predicate 정보까지 함께 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'TYPICAL +PREDICATE'));

실무에서는 EXPLAIN PLAN 결과만 보고 튜닝 방향을 확정하지 않는 것이 좋다.

AUTOTRACE 활용

AUTOTRACE 는 SQL*Plus 나 SQLcl 환경에서 빠르게 실행 계획과 통계를 같이 볼 때 유용하다.

EXPLAIN: 예상 실행 계획 중심
STATISTICS: 실제 실행 후 통계 중심
TRACEONLY: 결과 행은 출력하지 않고 통계만 확인

간단한 테스트 SQL 을 반복 확인할 때 편리하지만, 운영 중인 복잡한 SQL 의 상세 분석은 여전히 DISPLAY_CURSOR 쪽이 더 강력하다.

-- AUTOTRACE 활성화
SET AUTOTRACE ON EXPLAIN;
SET AUTOTRACE ON STATISTICS;
SET AUTOTRACE TRACEONLY;

-- 쿼리 실행
SELECT * FROM employees WHERE salary > 10000;

-- AUTOTRACE 비활성화
SET AUTOTRACE OFF;

SQL 모니터링

실행 시간이 길거나 리소스를 많이 쓰는 SQL 은 SQL Monitor 로 보는 것이 훨씬 유용하다.
특히 병렬 처리, 장시간 Full Scan, 대용량 조인, Temp 사용량 등을 시각적으로 파악하기 좋다.

다음 상황에서 특히 유용하다.

지금 실행 중인 SQL 이 왜 느린지 봐야 할 때
어느 단계에서 시간이 많이 쓰이는지 확인할 때
병렬 SQL 의 분산 상태를 확인할 때

-- SQL 모니터링 활성화 (11g 이상)
SELECT /*+ MONITOR */ * FROM large_table WHERE condition = 'value';

-- SQL 모니터 레포트 확인
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id => 'sql_id_here',
  type => 'TEXT'
) FROM DUAL;

운영 환경에서 실행된 쿼리의 실행 계획 조회

운영 중인 환경에서는 여러 쿼리가 동시에 실행되므로, 특정 쿼리의 실행 계획을 정확히 추출하는 것이 중요합니다.

운영 환경에서는 보통 아래 우선순위로 접근하면 된다.

가능하면 SQL_ID 확보
DBMS_XPLAN.DISPLAY_CURSOR 로 실제 계획 확인
필요하면 ALLSTATS LAST 로 실제 실행 통계 확인
장시간 SQL 이면 SQL Monitor 확인
과거 이슈면 AWR 에서 계획 이력 확인

방법 1: SQL_ID를 이용한 조회 (가장 추천)

-- 1단계: 실행할 쿼리에 고유 식별자 추가
SELECT /* MY_UNIQUE_QUERY_20260106 */ *
FROM employees
WHERE department_id = 10;

-- 2단계: SQL_ID 찾기
SELECT sql_id, sql_text, executions, last_active_time
FROM v$sql
WHERE sql_text LIKE '%MY_UNIQUE_QUERY_20260106%'
  AND sql_text NOT LIKE '%v$sql%';  -- 이 조회문 자체 제외

-- 3단계: SQL_ID로 실제 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id_here', NULL, 'ALLSTATS LAST'));

-- 실행 통계까지 포함된 상세 계획
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id_here', NULL, 'ADVANCED ALLSTATS LAST'));

DISPLAY_CURSOR 는 실제 Shared Pool 에 올라온 커서를 대상으로 하므로, 가장 실전적인 방식이다.

ALLSTATS LAST 를 볼 때 체크할 것

ALLSTATS LAST 는 마지막 실행 기준 실제 수행 통계를 보여준다.
이 값을 보면 단순 예상 계획이 아니라, 실제로 어느 단계가 병목이었는지 더 정확하게 볼 수 있다.

특히 아래를 확인한다.

E-Rows 와 A-Rows 차이
특정 단계의 Starts 가 과도하게 큰지
Buffer Gets 가 특정 단계에 집중되는지
예상보다 많은 행이 상위 조인 단계로 전달되는지

예를 들어 E-Rows = 10, A-Rows = 500000 이면 통계 정보나 SQL 구조 문제를 먼저 의심해야 한다.

방법 2: GATHER_PLAN_STATISTICS 힌트 사용

-- 실행 계획 수집 힌트 추가하여 쿼리 실행
SELECT /*+ GATHER_PLAN_STATISTICS MY_QUERY_001 */ *
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;

-- SQL_ID 찾기
SELECT sql_id, child_number, sql_text
FROM v$sql
WHERE sql_text LIKE '%MY_QUERY_001%'
  AND sql_text NOT LIKE '%v$sql%';

-- 실제 실행 통계가 포함된 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('발견된_sql_id', NULL, 'ALLSTATS LAST'));

튜닝 테스트 시에는 이 방법이 가장 간단하다.
다만 모든 SQL 에 상시로 붙이는 것은 좋지 않고, 분석이 필요한 SQL 에 한정해서 사용하는 것이 좋다.

방법 3: 세션 레벨 추적

-- 현재 세션에서만 통계 수집 활성화
ALTER SESSION SET STATISTICS_LEVEL = ALL;

-- 쿼리 실행
SELECT * FROM employees WHERE department_id = 10;

-- 마지막 실행된 쿼리의 SQL_ID 조회
SELECT prev_sql_id, prev_child_number
FROM v$session
WHERE sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1);

-- 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- 통계 수집 원복
ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

특정 세션에서만 재현 테스트를 하는 경우 유용하다.
운영 전체 설정을 건드리지 않고도 실제 통계를 볼 수 있다는 점이 장점이다.

방법 4: SQL 해시값으로 추적

-- 쿼리 텍스트의 해시값 미리 계산
SELECT DBMS_UTILITY.GET_SQL_HASH('
SELECT * FROM employees WHERE department_id = 10
', NULL, 1) as sql_hash FROM DUAL;

-- 쿼리 실행 후 해시값으로 찾기
SELECT sql_id, sql_text, executions, last_active_time
FROM v$sql
WHERE hash_value = '위에서_계산한_해시값';

-- 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', NULL, 'ALLSTATS LAST'));

다만 실무에서는 보통 SQL_ID 기반 추적이 더 일반적이고 안정적이다.
해시값 방식은 보조 수단 정도로 이해하면 된다.

방법 5: AWR에서 과거 실행 계획 조회

-- AWR에 저장된 SQL 찾기
SELECT sql_id, snap_id, plan_hash_value, executions_delta
FROM dba_hist_sqlstat
WHERE sql_id = 'your_sql_id'
ORDER BY snap_id DESC;

-- AWR에서 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id', 'plan_hash_value'));

어제는 빨랐는데 오늘 갑자기 느려진 SQL 은 AWR 이 특히 유용하다.
plan_hash_value 를 비교하면 실행 계획이 바뀌었는지 빠르게 확인할 수 있다.

Child Cursor 확인

같은 SQL_ID 라도 Child Cursor 가 여러 개 생길 수 있다.
바인드 값, 세션 환경, 옵티마이저 환경 차이로 인해 같은 SQL 텍스트라도 다른 실행 계획이 붙을 수 있기 때문이다.

SELECT sql_id,
       child_number,
       plan_hash_value,
       executions,
       parsing_schema_name
FROM v$sql
WHERE sql_id = 'your_sql_id'
ORDER BY child_number;

실행 계획을 볼 때는 sql_id 만 보지 말고, 어떤 child_number 의 계획인지도 함께 보는 것이 좋다.

실전 팁

-- 실시간 실행 중인 쿼리 모니터링
SELECT s.sid, s.serial#, s.username, s.sql_id,
       q.sql_text, s.last_call_et, s.status
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.username IS NOT NULL
  AND s.status = 'ACTIVE'
ORDER BY s.last_call_et DESC;

-- 특정 세션의 현재 실행 중인 SQL 계획
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  (SELECT sql_id FROM v$session WHERE sid = '특정_sid'),
  NULL,
  'ALLSTATS LAST'
));

-- SQL_ID로 모든 child cursor의 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', NULL, 'ALLSTATS LAST'));

-- SQL Baseline이 있는 경우 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
  sql_handle => 'sql_handle',
  plan_name => NULL,
  format => 'TYPICAL'
));

실행 계획 분석 순서

실제 튜닝에서는 아래 순서로 보면 훨씬 안정적이다.

SQL 텍스트와 바인드 조건을 확인한다.
실제 실행 계획을 DISPLAY_CURSOR 로 본다.
E-Rows 와 A-Rows 차이를 본다.
조인 순서와 조인 방식을 확인한다.
Predicate Information 에서 access / filter 를 확인한다.
Full Scan, Sort, Temp 사용이 필요한 이유가 합리적인지 본다.
인덱스 문제인지, 통계 문제인지, SQL 구조 문제인지 분리한다.

실행 계획에서 자주 놓치는 포인트

Cost 가 낮다고 항상 빠른 SQL 은 아니다.
EXPLAIN PLAN 과 실제 실행 계획은 다를 수 있다.
인덱스를 사용했다고 해서 무조건 좋은 계획은 아니다.
Full Scan 이 나와도 읽는 건수가 많다면 정상일 수 있다.
예상 Rows 와 실제 Rows 차이가 큰 계획은 다시 검토해야 한다.
SELECT * 로 인해 테이블 접근이 추가되는 경우가 많다.
Buffers 가 작아도 Reads 나 Temp 가 크면 느릴 수 있다.

예를 들어 인덱스를 사용했더라도 TABLE ACCESS BY INDEX ROWID 로 수십만 건을 다시 읽는다면, Full Scan 보다 더 느릴 수 있다.

실행 계획 예제 해석

실행 계획은 실제 예제를 놓고 보는 것이 가장 이해가 빠르다.
아래는 부서 테이블과 사원 테이블을 조인하는 간단한 예제다.

SELECT /*+ GATHER_PLAN_STATISTICS */
       e.employee_id,
       e.last_name,
       d.department_name
FROM employees e
JOIN departments d
  ON e.department_id = d.department_id
WHERE d.location_id = 1700
  AND e.salary >= 5000;

실행 후 다음과 같이 조회할 수 있다.

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +PREDICATE'));

예를 들어 실행 계획이 아래처럼 나왔다고 가정해보자.

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | E-Rows | A-Rows | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |        |     12 |      18 |
|   1 |  NESTED LOOPS                 |                   |     10 |     12 |      18 |
|   2 |   TABLE ACCESS FULL           | DEPARTMENTS       |      1 |      1 |       3 |
|   3 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |     10 |     12 |      15 |
|   4 |    INDEX RANGE SCAN           | IDX_EMP_DEPT_SAL  |     10 |     12 |       4 |
----------------------------------------------------------------------------------------

Predicate Information:
2 - filter("D"."LOCATION_ID"=1700)
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "E"."SALARY">=5000)

이 계획은 다음 순서로 해석하면 된다.

DEPARTMENTS 를 TABLE ACCESS FULL 로 읽는다.
LOCATION_ID = 1700 조건으로 부서를 걸러낸다.
걸러진 부서의 DEPARTMENT_ID 값을 이용해 EMPLOYEES 인덱스를 탐색한다.
IDX_EMP_DEPT_SAL 인덱스에서 department_id 와 salary >= 5000 조건으로 범위를 찾는다.
인덱스로 찾은 ROWID 를 이용해 EMPLOYEES 테이블 본문을 읽는다.
두 테이블을 NESTED LOOPS 로 조인한 뒤 최종 결과를 반환한다.

이 실행 계획에서 읽어야 할 핵심은 다음과 같다.

DEPARTMENTS 는 건수가 매우 작아서 TABLE ACCESS FULL 이 자연스럽다.
EMPLOYEES 는 인덱스를 통해 필요한 범위만 접근하고 있다.
조인 방식이 NESTED LOOPS 인 이유는 선행 집합이 매우 작기 때문이다.
E-Rows 와 A-Rows 가 크게 차이 나지 않으므로 통계도 크게 어긋나지 않은 것으로 볼 수 있다.

즉, 이 경우는 비교적 건강한 실행 계획이라고 볼 수 있다.

좋지 않은 실행 계획 예제

같은 SQL 이더라도 인덱스가 없거나 통계가 부정확하면 아래처럼 바뀔 수 있다.

---------------------------------------------------------------------------------
| Id  | Operation             | Name        | E-Rows | A-Rows | Buffers | Temp |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |        |     12 |    1200 |      |
|   1 |  HASH JOIN            |             |   1000 |     12 |    1200 | 1024 |
|   2 |   TABLE ACCESS FULL   | DEPARTMENTS |      1 |      1 |       3 |      |
|   3 |   TABLE ACCESS FULL   | EMPLOYEES   |   1000 |  50000 |    1197 |      |
---------------------------------------------------------------------------------

이 계획에서 눈여겨볼 포인트는 다음과 같다.

EMPLOYEES 를 전체 스캔하고 있다.
예상 건수 E-Rows=1000 인데 실제는 A-Rows=50000 으로 차이가 크다.
조인 결과는 12건인데, 그 전에 너무 많은 행을 읽고 있다.
HASH JOIN 자체가 문제라기보다, 조인 전에 데이터를 줄이지 못한 것이 핵심 문제일 수 있다.

이 경우는 아래 방향을 먼저 검토한다.

EMPLOYEES(department_id, salary) 또는 적절한 복합 인덱스가 필요한지
DEPARTMENTS.location_id 조건의 선택도가 충분히 반영되는지
통계 정보가 오래되었는지
바인드 변수나 히스토그램 때문에 예상 건수가 틀어졌는지

HASH JOIN 중심 실행 계획 예제

대량 데이터 조인에서는 HASH JOIN 이 더 자연스러운 계획이 될 수 있다.

SELECT /*+ GATHER_PLAN_STATISTICS */
       o.order_id,
       o.customer_id,
       oi.product_id,
       oi.quantity
FROM orders o
JOIN order_items oi
  ON oi.order_id = o.order_id
WHERE o.order_date >= DATE '2026-01-01'
  AND o.order_date < DATE '2026-02-01';

예를 들어 실행 계획이 아래처럼 나왔다고 보자.

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | E-Rows | A-Rows | Buffers | Reads | Temp |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |        | 120000 |    8500 |   900 |      |
|   1 |  HASH JOIN            |              | 100000 | 120000 |    8500 |   900 |      |
|   2 |   TABLE ACCESS FULL   | ORDERS       |  30000 |  32000 |    1200 |   150 |      |
|   3 |   TABLE ACCESS FULL   | ORDER_ITEMS  | 500000 | 520000 |    7300 |   750 |      |
------------------------------------------------------------------------------------------------

이 계획은 다음처럼 해석할 수 있다.

ORDERS 에서 1월 데이터만 추출한다.
ORDER_ITEMS 는 조인 대상이 많아서 전체 스캔한다.
작은 쪽 집합을 기준으로 해시 구조를 만든다.
두 집합을 HASH JOIN 으로 결합한다.

이 경우는 Nested Loop Join 으로 건건이 접근하는 것보다, 큰 집합을 한 번에 읽고 조인하는 편이 더 유리할 수 있다.
즉 TABLE ACCESS FULL 이 나온다고 해서 바로 나쁜 계획이라고 단정하면 안 된다.

이때는 아래를 같이 봐야 한다.

결과 건수가 대량인지
인덱스로 건건이 접근하면 랜덤 I/O 가 더 커지는지
Buffers, Reads, Temp 사용량이 과도한지
E-Rows 와 A-Rows 차이가 감당 가능한 수준인지

NESTED LOOPS vs HASH JOIN 비교

실행 계획을 보다 보면 가장 자주 비교하게 되는 조인 방식이 NESTED LOOPS 와 HASH JOIN 이다.

항목	NESTED LOOPS	HASH JOIN
유리한 상황	선행 집합이 작고 후행 인덱스가 좋을 때	양쪽 집합이 크고 대량 조인일 때
대표 환경	OLTP, 빠른 단건/소량 조회	배치, DW, 대량 집계
액세스 방식	선행 행마다 후행 테이블 반복 접근	한 번에 읽고 해시 구조로 조인
인덱스 의존도	높음	상대적으로 낮음
I/O 성격	랜덤 I/O 가 늘기 쉬움	순차 읽기와 대량 처리에 유리
메모리 사용	비교적 적은 편	해시 영역 메모리 사용 가능
위험 신호	선행 건수가 너무 크면 급격히 느려짐	Temp 사용과 Full Scan 비용이 커질 수 있음

간단히 정리하면 다음처럼 이해하면 된다.

적은 건수를 빨리 찾는 SQL 은 NESTED LOOPS
큰 집합을 한 번에 결합하는 SQL 은 HASH JOIN
어떤 방식이든 E-Rows 와 A-Rows 차이가 크면 잘못 선택될 수 있음

예를 들어 결과가 10건인데 HASH JOIN 으로 수십만 건을 읽고 있다면 비효율적일 수 있고, 반대로 결과가 수십만 건인데 NESTED LOOPS 로 인덱스를 반복 탐색하면 더 느려질 수 있다.

Buffers 로 수행 시간 가늠하기

실행 계획의 Buffers 값은 SQL 이 발생시킨 논리 읽기(Logical Read) 규모를 파악하는 데 매우 유용하다.
실무에서는 흔히 buffer gets 관점으로 이해하면 된다.

다만 먼저 분명히 해야 할 점이 있다.

Buffers 는 시간을 직접 보여주는 값이 아니다.
Buffers 만으로 정확한 수행시간을 계산할 수는 없다.
그래도 SQL 의 무게를 대략 비교하는 데는 매우 유용하다.

예를 들어 같은 결과를 내는 두 SQL 이 있을 때:

SQL A: Buffers = 1,200
SQL B: Buffers = 180,000

다른 조건이 비슷하다면 일반적으로 SQL B 가 훨씬 무거운 SQL 일 가능성이 높다.

Buffers 와 블록 크기로 읽기량 추정

오라클 블록 크기가 8KB 라면 대략적인 논리 읽기량은 다음처럼 계산해볼 수 있다.

논리 읽기량(바이트) ≈ Buffers × db_block_size

예를 들어:

Buffers = 8,500
db_block_size = 8KB

이면 대략:

8,500 × 8KB ≈ 68,000KB ≈ 약 66MB

즉 이 SQL 은 대략 66MB 수준의 논리 블록 접근을 수행했다고 볼 수 있다.

블록 크기는 다음처럼 확인할 수 있다.

SELECT name, value
FROM v$parameter
WHERE name = 'db_block_size';

Buffers 만으로 시간을 정확히 계산할 수 없는 이유

같은 Buffers 여도 실제 수행시간은 크게 달라질 수 있다.

블록이 버퍼 캐시에 있으면 빠르다.
실제 디스크에서 읽어오면 느리다.
CPU 계산량, 함수 호출, 조인/정렬 비용이 다르다.
병렬 실행 여부가 다를 수 있다.
동시 부하와 세션 상태가 다를 수 있다.

즉 Buffers 는 “얼마나 많은 일을 했는가” 를 보여주는 지표이고, 정확한 초 단위 시간은 Reads, Temp, A-Time, 시스템 상태와 함께 봐야 한다.

대략적인 수행시간 추정 관점

정확한 공식은 없지만 실무에서는 아래처럼 감각적으로 해석한다.

Buffers 가 크면 CPU와 논리 읽기 부담이 큰 SQL 일 가능성이 높다.
Reads 가 크면 물리 I/O 대기시간이 붙을 가능성이 높다.
Temp 가 크면 Sort 또는 Hash 단계에서 시간이 급격히 늘 수 있다.
A-Rows 가 크면 상위 단계 연산도 함께 무거워질 수 있다.

예를 들어:

SQL A: Buffers = 2,000, Reads = 0
SQL B: Buffers = 2,000, Reads = 1,500

이면 논리 읽기량은 비슷해도 SQL B 가 훨씬 느릴 가능성이 높다.
반대로 Reads 는 거의 없고 Buffers 만 큰 SQL 은 디스크보다 CPU 병목 성격에 가까울 수 있다.

수행시간 예측에 더 유용한 지표

실행 계획과 함께 아래 지표를 같이 보면 훨씬 현실적인 추정이 가능하다.

Buffers: 논리 읽기량
Reads: 물리 읽기량
A-Time: 실제 각 단계 소요 시간
A-Rows: 실제 각 단계 처리 건수
TempSpc 또는 SQL Monitor 의 Temp 사용량
SQL Monitor 의 Activity 비중(CPU, I/O wait 등)

특히 A-Time 이 보이는 실행 계획이나 SQL Monitor 가 있으면, 단순 추정보다 훨씬 정확하게 병목 구간을 판단할 수 있다.

실무에서는 이렇게 보면 편하다

빠르게 감을 잡을 때는 보통 다음처럼 해석한다.

Buffers 가 작고 Reads 도 작다: 대체로 가벼운 SQL
Buffers 가 큰데 Reads 는 작다: CPU/논리 읽기 중심 SQL
Reads 가 크다: 디스크 I/O 영향이 큰 SQL
Temp 가 크다: 정렬/해시 작업이 무거운 SQL
E-Rows 와 A-Rows 차이가 크다: 계획 자체가 비효율적일 가능성이 높음

실행 계획 예제를 볼 때 습관화하면 좋은 질문

예제 하나를 볼 때마다 아래 질문을 반복하면 해석 속도가 빨라진다.

가장 먼저 읽는 테이블은 무엇인가
그 테이블을 그렇게 읽는 이유가 타당한가
조인 전에 건수가 충분히 줄었는가
인덱스 조건이 access 로 잘 들어갔는가
예상 건수와 실제 건수가 크게 차이나지 않는가
비싼 작업이 정말 필요한 작업인가

실행 계획은 “인덱스를 썼다 / 안 썼다” 수준으로 보면 놓치는 것이 많다.
반드시 선행 테이블, 조건 적용 위치, 조인 방식, 실제 건수까지 함께 봐야 한다.

주의사항

ALLSTATS LAST는 마지막 실행 통계를 보여줌 (정확한 실행 결과)
GATHER_PLAN_STATISTICS 힌트는 약간의 오버헤드 발생
STATISTICS_LEVEL=ALL은 시스템 전체에 영향을 주므로 세션 레벨에서만 사용
운영 환경에서는 고유한 주석을 사용하여 SQL을 식별하는 것이 가장 안전
Shared Pool에서 aging out 되기 전에 빠르게 조회해야 함

추가로 아래 사항도 자주 문제를 만든다.

통계 정보가 오래되면 실행 계획 자체가 틀어질 수 있음
바인드 변수 값에 따라 최적 계획이 달라질 수 있음
같은 SQL 이라도 Child Cursor 별로 계획이 다를 수 있음
테스트 환경과 운영 환경의 통계/데이터 분포가 다르면 계획도 달라질 수 있음
수행시간 추정은 Buffers 단독보다 Reads, Temp, A-Time 과 같이 봐야 함

정리

실행 계획 분석의 핵심은 “무슨 연산이 보이느냐” 보다 “왜 그 연산이 선택되었고, 실제로도 맞았느냐” 를 보는 것이다.

초벌 확인은 EXPLAIN PLAN
실제 확인은 DBMS_XPLAN.DISPLAY_CURSOR
장시간 SQL 은 SQL Monitor
과거 이력 비교는 AWR
핵심 판단 기준은 E-Rows 와 A-Rows 차이, 조인 순서, Predicate 정보

실무에서는 실행 계획만 따로 떼어 보지 말고, SQL 구조, 인덱스, 통계 정보, 데이터 분포를 함께 봐야 정확한 원인을 찾을 수 있다.

DevelopOracle

Oracle 실행 계획 분석

실행 계획을 볼 때 가장 먼저 볼 것

실행 계획 읽는 순서

자주 나오는 실행 계획 연산

TABLE ACCESS FULL

INDEX RANGE SCAN

INDEX UNIQUE SCAN

TABLE ACCESS BY INDEX ROWID

SORT ORDER BY / SORT GROUP BY

NESTED LOOPS / HASH JOIN / MERGE JOIN

Access Predicate 와 Filter Predicate

EXPLAIN PLAN 사용

AUTOTRACE 활용

SQL 모니터링

운영 환경에서 실행된 쿼리의 실행 계획 조회

방법 1: SQL_ID를 이용한 조회 (가장 추천)

ALLSTATS LAST 를 볼 때 체크할 것

방법 2: GATHER_PLAN_STATISTICS 힌트 사용

방법 3: 세션 레벨 추적

방법 4: SQL 해시값으로 추적

방법 5: AWR에서 과거 실행 계획 조회

Child Cursor 확인

실전 팁

실행 계획 분석 순서

실행 계획에서 자주 놓치는 포인트

실행 계획 예제 해석

좋지 않은 실행 계획 예제

HASH JOIN 중심 실행 계획 예제

NESTED LOOPS vs HASH JOIN 비교

Buffers 로 수행 시간 가늠하기

Buffers 와 블록 크기로 읽기량 추정

Buffers 만으로 시간을 정확히 계산할 수 없는 이유

대략적인 수행시간 추정 관점

수행시간 예측에 더 유용한 지표

실무에서는 이렇게 보면 편하다

실행 계획 예제를 볼 때 습관화하면 좋은 질문

주의사항

정리

관련 문서