Oracle 19c DBMS_XPLAN: E-Rows와 A-Rows 차이로 느린 SQL 원인 찾기

Oracle 19c에서 인덱스가 있는데도 조회가 느리다면, 먼저 EXPLAIN PLAN의 예상 비용만 보지 말고 실제 실행 커서의 E-Rows와 A-Rows를 나란히 확인해야 해요. 두 값이 처음 크게 벌어지는 실행계획 단계가 옵티마이저의 판단이 어긋나기 시작한 지점이며, 그 아래의 통계정보·조건식·바인드 값부터 점검하는 편이 안전합니다.

A-Rows는 SQL을 실제로 실행해 행 원본 통계를 수집했을 때만 보입니다. 운영에서 무거운 SQL을 진단 목적으로 다시 실행하기 전에 부하와 결과 건수를 확인하고, 가능하면 동일한 바인드 값과 이미 실행된 커서를 사용하세요.

DBMS_XPLAN 진단이 맞는 상황과 적용 조건

이 방법은 “인덱스가 존재하는가”보다 “옵티마이저가 각 단계에서 몇 행이 나올 것으로 예상했고 실제로 몇 행이 나왔는가”를 비교할 때 유용해요. 실행 시간이 긴 원인이 예상 행수 오차가 아닐 수도 있으므로 시간, 버퍼 읽기, 메모리 사용도 함께 봐야 합니다.

적용 조건·영향 범위

E-Rows와 A-Rows는 무엇이 다른가

E-Rows는 옵티마이저가 통계정보와 조건식을 바탕으로 예상한 출력 행수예요. A-Rows는 해당 실행계획 단계가 실제로 내보낸 행수입니다. Oracle 공식 SQL Tuning Guide는 V$SQL_PLAN_STATISTICS_ALL이 예상값과 실제 실행 통계를 나란히 비교하도록 정보를 결합한다고 설명합니다.

두 값이 같아야만 좋은 실행계획이라는 뜻은 아니에요. 작은 테이블에서 2행과 10행의 차이는 영향이 미미할 수 있지만, 중간 단계에서 10행을 예상하고 실제로 100만 행이 흘렀다면 조인 방식과 조인 순서, 메모리 사용량을 고른 전제가 달라질 수 있습니다. 반대로 행수 추정은 대체로 맞는데 시간이 길다면 물리 I/O, 경합, TEMP spill, 네트워크나 함수 비용을 따로 봐야 해요.

작은 예제로 실제 행수 통계를 수집하는 방법

아래 예제는 테스트 스키마에서만 실행하세요. 고객 등급 분포를 일부러 치우치게 만들어 히스토그램을 수집하지 않은 통계에서 예상 행수와 실제 행수가 벌어질 수 있는 상황을 살펴봅니다.

CREATE TABLE plan_customer_demo (
    customer_id NUMBER       NOT NULL,
    grade_code  VARCHAR2(10) NOT NULL,
    created_at  DATE         NOT NULL,
    CONSTRAINT plan_customer_demo_pk PRIMARY KEY (customer_id)
);

INSERT INTO plan_customer_demo (customer_id, grade_code, created_at)
SELECT level,
       CASE WHEN level <= 10 THEN 'VIP' ELSE 'NORMAL' END,
       DATE '2026-01-01' + MOD(level, 180)
FROM dual
CONNECT BY level <= 10000;

COMMIT;

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
      ownname    => USER,
      tabname    => 'PLAN_CUSTOMER_DEMO',
      method_opt => 'FOR ALL COLUMNS SIZE 1');
END;
/

실행 통계를 한 SQL에만 수집하려면 다음처럼 힌트를 사용합니다. 필요한 컬럼만 조회하고, 실제 장애에서 사용된 바인드 값과 같은 조건을 써야 비교 의미가 생겨요.

SELECT /*+ gather_plan_statistics */
       customer_id,
       grade_code,
       created_at
FROM plan_customer_demo
WHERE grade_code = :grade_code;

방금 실행한 커서의 마지막 실행 통계를 확인합니다. ALLSTATS는 I/O와 메모리 통계 형식을 포함하고, LAST는 누적값 대신 마지막 실행을 보여줘요.

SELECT plan_table_output
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
      sql_id          => NULL,
      cursor_child_no => 0,
      format          => 'ALLSTATS LAST +PREDICATE'
  )
);

실행계획은 아래에서 위로, 오차가 처음 생긴 줄부터 읽는다

실행계획의 맨 위에서 “SELECT STATEMENT가 느리다”고 보는 것은 원인을 좁히기 어려워요. 들여쓰기상 자식인 아래쪽 행 원본부터 위로 올라가며, 실제 행수가 예상보다 처음 크게 늘거나 줄어드는 지점을 찾습니다. 상위 단계의 오차는 하위 단계에서 들어온 오차가 누적된 결과일 수 있기 때문입니다.

실행 전후 검증표

실제 장애 SQL에서는 SQL ID와 child cursor를 맞춘다

운영 세션에서 다른 SQL을 하나 더 실행하면 “직전 SQL”이 바뀔 수 있어요. 그래서 실제 진단에서는 SQL ID와 child number를 명시하는 편이 안전합니다. 바인드 값이나 옵티마이저 환경이 달라 child cursor가 여러 개라면, 실행 횟수와 마지막 활성 시각을 함께 확인해 문제 실행과 같은 커서를 골라야 합니다.

SELECT sql_id,
       child_number,
       plan_hash_value,
       executions,
       last_active_time
FROM v$sql
WHERE sql_id = :sql_id
ORDER BY child_number;

SELECT plan_table_output
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
      sql_id          => :sql_id,
      cursor_child_no => :child_number,
      format          => 'ALLSTATS LAST +PREDICATE'
  )
);

오차를 발견한 뒤 통계부터 무조건 수집하면 안 되는 이유

큰 행수 오차는 오래된 테이블 통계 때문일 수 있지만 원인은 하나가 아니에요. 값이 한쪽으로 몰린 컬럼에 히스토그램이 없거나, 두 컬럼의 값이 서로 연관되어 있거나, 조건식이 컬럼에 함수를 적용하거나, 실제 장애 바인드와 테스트 바인드가 다를 때도 추정이 흔들립니다. 통계를 다시 모아도 조건식과 데이터 모델의 문제가 그대로라면 계획은 달라지지 않거나 다른 SQL의 계획이 바뀔 수 있어요.

먼저 현재 통계 상태를 읽기 전용으로 확인하세요. NUM_ROWS는 정확한 실시간 행수가 아니라 마지막 통계 수집 시점의 추정치라는 점도 함께 봐야 합니다.

SELECT owner,
       table_name,
       num_rows,
       blocks,
       last_analyzed,
       stale_stats
FROM dba_tab_statistics
WHERE owner = :owner
  AND table_name = :table_name
  AND object_type = 'TABLE';

변경과 롤백의 경계를 먼저 정한다

DISPLAY_CURSOR 조회 자체는 데이터를 바꾸지 않지만, 진단 뒤의 통계 수집·인덱스 생성·SQL 수정은 실행계획을 바꿀 수 있습니다. 변경 전에는 현재 통계를 백업하거나 복원 가능성을 확인하고, 문제 SQL뿐 아니라 같은 객체를 사용하는 중요 SQL의 계획과 응답 시간도 비교해야 해요.

행수 오차를 확인한 뒤 새 인덱스가 필요하다는 결론에 이르렀다면 Oracle 19c ONLINE 인덱스 생성 전 잠금·TEMP 점검 순서를 먼저 확인하세요. 기존 인덱스 상태가 UNUSABLE이라면 새 인덱스를 겹쳐 만들기 전에 ORA-01502와 UNUSABLE 인덱스의 영향 범위를 분리해 진단하는 편이 안전합니다.

자주 놓치는 해석 실수

• EXPLAIN PLAN의 예상 계획을 실제 실행 커서와 같은 것으로 단정하지 않습니다. 실제 커서는 컴파일 환경과 바인드 조건이 다를 수 있어요.
• LAST를 빼고 누적 통계를 보면서 한 번의 실행값으로 오해하지 않습니다.
• A-Rows가 크다는 이유만으로 그 단계가 느리다고 단정하지 않습니다. Starts, A-Time, Buffers, 메모리와 TEMP 사용을 함께 봅니다.
• 루프 안쪽 행 원본은 Starts가 여러 번일 수 있어요. Starts가 0보다 크면 A-Rows를 Starts로 나눈 실행당 행 수와 E-Rows를 비교합니다. 결과를 일부만 가져온 실행은 전체 반환 결과와 다를 수 있으므로 fetch 완료 여부도 확인하세요.
• 테스트에서 리터럴로 실행한 계획을 운영의 바인드 SQL에 그대로 대입하지 않습니다.

인덱스보다 먼저 틀린 예상이 시작된 위치를 찾는다

“인덱스가 있는데 느리다”는 말만으로는 인덱스 사용 여부도, 원인도 정해지지 않아요. Oracle 19c에서는 실제 문제 커서의 ALLSTATS LAST를 확보하고, 아래쪽 행 원본부터 E-Rows와 A-Rows가 처음 갈라지는 지점을 찾은 다음 Predicate·통계·데이터 분포·바인드 값을 확인하는 순서가 가장 재현 가능해요. 변경은 그 증거가 모인 뒤에 결정해야 다른 SQL의 계획까지 흔드는 일을 줄일 수 있습니다.

공식 출처

• Oracle Database 19c PL/SQL Packages and Types Reference — DBMS_XPLAN
• Oracle Database 19c SQL Tuning Guide — 실행계획 생성과 표시
• Oracle Database 19c SQL Tuning Guide — Optimizer Statistics Concepts
• Oracle Database 19c Reference — STATISTICS_LEVEL