실제 실행 통계 읽기: GATHER_PLAN_STATISTICS와 E-Rows·A-Rows

핵심 요약

실행계획의 Operation 구조만으로는 SQL이 실제로 몇 번 호출되고 몇 행·Block을 처리했는지 알 수 없습니다. Runtime Plan Statistics를 수집하면 각 Row Source의 실제 작업량을 예상치와 비교할 수 있습니다.

예상 계획 정보
  → Optimizer가 예상한 Cardinality·Cost·Access Path

실제 실행 통계
  → Operation별 Starts·실제 행 수·논리/물리 I/O·경과시간·Workarea 사용량

대표적인 수집과 출력 방법은 다음과 같습니다.

SELECT /*+ GATHER_PLAN_STATISTICS */
       ...
FROM   ...;

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

핵심 계산은 다음과 같습니다.

예상 총 출력 행 수 ≈ Starts × E-Rows
실제 1회당 평균 행 수 = A-Rows ÷ Starts

다만 Starts가 0이면 해당 Branch는 이번 실행에서 시작되지 않았으며, Starts가 1보다 큰 반복 Operation에서는 E-Rows와 총 A-Rows를 그대로 비교하지 않습니다.

분석의 핵심은 최상위 숫자만 보는 것이 아니라 다음 순서로 원인을 좁히는 것입니다.

가장 안쪽 Object Access
  → 반복 횟수와 예상·실제 행 수 비교
  → 최초로 크게 어긋난 Operation 찾기
  → Predicate와 데이터 분포 확인
  → 상위 Join·Sort로 오차와 I/O 전파 추적

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → SQL 분석 도구 → 예상 실행계획 범위에서 실제 Row Source 통계 수집과 해석을 다룹니다. SQL Trace·TKPROF의 Call 통계, SQL Monitor, AWR과 각 Join·Index 내부 동작은 후속 이론에서 다룹니다.

학습 목표

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

• 실제 실행 통계가 필요한 이유를 설명한다.
• GATHER_PLAN_STATISTICS와 STATISTICS_LEVEL=ALL의 적용 범위를 구분한다.
• ALLSTATS와 ALLSTATS LAST의 누적·마지막 실행 차이를 설명한다.
• Starts, E-Rows, A-Rows, A-Time, Buffers, Reads를 해석한다.
• Buffers가 Consistent Get과 Current Get의 합계 성격임을 설명한다.
• 반복 Operation의 예상 총 행 수와 실제 1회당 평균 행 수를 계산한다.
• 최초 Cardinality 오차가 생긴 Operation을 찾는다.
• 부모·자식의 A-Time·Buffers를 단순 합산하지 않는다.
• Workarea의 OMem, 1Mem, Used-Mem, Used-Tmp를 구분한다.
• 일부 Fetch가 Runtime Statistics에 미치는 영향을 설명한다.
• Adaptive Plan의 비활성 Branch와 Parallel 실행 통계의 주의점을 설명한다.
• 실제 분석 대상 SQL_ID·Child·Bind·Fetch 범위를 정확히 통제한다.

1. Operation 구조만으로 부족한 이유

같은 SQL과 같은 Plan Hash를 사용해도 실제 작업량은 크게 달라질 수 있습니다.

SELECT order_id,
       order_date
FROM   orders
WHERE  customer_id = :customer_id;

실행 A: 고객 주문 2건
실행 B: 고객 주문 500,000건

두 실행은 같은 Index Range Scan Plan을 재사용할 수 있지만 다음 값은 달라질 수 있습니다.

• 실제 출력 행 수
• Table Access 반복 횟수
• Logical I/O와 Physical Read
• 전체 Fetch 여부
• Cache 상태
• 응답시간

예상 계획만으로는 다음 내용을 알 수 있습니다.

• Access Path와 Join Method
• Operation별 예상 Cardinality
• Optimizer Cost
• Predicate 적용 위치

Runtime Statistics를 수집하면 다음 내용을 추가로 확인할 수 있습니다.

• Operation이 몇 번 시작되었는가
• 실제로 몇 행을 생산했는가
• 어느 Branch에서 Buffer 방문과 Disk Read가 집중되었는가
• Sort·Hash Workarea가 Memory 또는 TEMP를 사용했는가
• Adaptive Plan에서 어느 대안이 실제 사용되었는가

2. Runtime Plan Statistics 수집 방법

통계는 실행 전에 수집이 활성화되어야 합니다. 통계 없이 끝난 과거 실행에 ALLSTATS LAST Format만 추가해도 누락된 정보가 소급 생성되지는 않습니다.

2.1 문장 단위: GATHER_PLAN_STATISTICS

SELECT /*+ GATHER_PLAN_STATISTICS */
       employee_id,
       last_name,
       salary
FROM   employees
WHERE  department_id = 50;

• 특정 SQL 한 문장에만 적용합니다.
• 실습과 변경 전후 비교에 적합합니다.
• Hint가 SQL Text에 추가되므로 기존 운영 SQL과 다른 SQL_ID·Cursor가 될 수 있습니다.
• 실제 운영 분석에서는 SQL Text 변경의 영향을 고려합니다.

2.2 Session 단위: STATISTICS_LEVEL=ALL

ALTER SESSION SET statistics_level = ALL;

테스트가 끝나면 원래 값으로 복원합니다.

ALTER SESSION SET statistics_level = TYPICAL;

ALL은 해당 Session의 여러 SQL에 Row Source 계측을 추가할 수 있으므로 제한된 테스트 Session에서 사용합니다. System 전체를 성급하게 변경하지 않습니다.

2.3 두 방법 비교

3. 정확한 Cursor의 실행 통계 출력

운영 분석에서는 대상 SQL과 Child Cursor를 명시합니다.

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

다음 항목을 먼저 확정합니다.

SQL_ID
CHILD_NUMBER
실행 Bind 값과 Bind Type
실행 Session의 Optimizer 환경
실제 Fetch 범위

3.1 NULL 인자 사용 시 주의

DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ...)

현재 Session의 직전 Cursor를 편리하게 조회할 수 있지만 중간에 다른 SQL이 실행되면 대상이 달라질 수 있습니다. 같은 SQL_ID에 여러 Child가 있다면 다른 Plan의 통계를 읽을 수도 있습니다.

3.2 Shared Pool 제한

DISPLAY_CURSOR는 현재 Cursor Cache에 Load된 Plan을 표시합니다. Cursor가 Aging Out되었거나 Purged되었다면 해당 함수로 조회하지 못할 수 있습니다. 과거 Plan은 AWR·SQL Tuning Set 등 별도 저장소가 필요할 수 있습니다.

4. ALLSTATS와 LAST

Oracle의 Runtime View에는 과거 실행 누적 Column과 마지막 실행 LAST_* Column이 함께 존재합니다.

ALLSTATS
  → 여러 실행에 누적된 Starts·Rows·I/O 통계가 표시될 수 있음

ALLSTATS LAST
  → 마지막 실행의 LAST_STARTS·LAST_OUTPUT_ROWS·LAST Buffer·Disk Read·Elapsed 통계

특정 Bind 값의 한 번 실행을 분석할 때는 일반적으로 LAST를 사용합니다.

다음 조건을 함께 확인합니다.

1. 분석할 Bind 값으로 실제 SQL을 실행했는가
2. 통계 수집을 실행 전에 활성화했는가
3. 올바른 Child Cursor를 선택했는가
4. Cursor가 Shared Pool에 남아 있는가
5. 결과를 같은 범위까지 Fetch했는가

5. 핵심 Column의 의미

E-Rows는 계획 수립 시점의 예상치이고, A-Rows는 실제 수행 결과입니다. 두 값의 차이는 원인이 아니라 추정 오류가 있다는 신호입니다.

6. 반복 Operation에서 단위를 맞춘다

다음 통계를 보겠습니다.

Starts = 5
E-Rows = 10
A-Rows = 5,000

E-Rows=10은 한 번 시작할 때의 예상치이고 A-Rows=5,000은 5번 Start의 실제 총합입니다.

예상 총 행 수
  = 5 × 10
  = 50행

실제 1회당 평균
  = 5,000 ÷ 5
  = 1,000행

따라서 다음 두 비교가 모두 가능합니다.

예상 총 50행 vs 실제 총 5,000행

예상 1회당 10행 vs 실제 1회당 평균 1,000행

Starts=0이면 실제 1회당 평균을 계산하지 않습니다. Adaptive Plan의 비활성 대안이나 실행 조건상 방문하지 않은 Branch일 수 있습니다.

7. A-Time과 I/O 통계의 누적 성격

다음 Plan을 가정합니다.

NESTED LOOPS                 Buffers 16,200
  TABLE ACCESS DEPARTMENTS  Buffers     20
  TABLE ACCESS ORDERS       Buffers 16,180
    INDEX RANGE SCAN        Buffers  1,200

최상위 NESTED LOOPS의 16,200에 모든 자식 값을 다시 더하면 같은 작업을 중복 계산할 수 있습니다.

전체 SQL 작업량
  → 최상위 값과 Branch별 분포를 비교

잘못된 계산
  → 모든 Plan 행의 Buffers·A-Time 단순 합산

7.1 Buffers의 의미

Buffers는 다음 두 논리 요청의 합계 성격을 가집니다.

Consistent Get
  → Read Consistency 기준 Block 요청

Current Get
  → 최신 Current Mode Block 요청

같은 Block을 여러 번 방문하면 여러 번 반영될 수 있으므로 고유 Block 개수와 같지 않습니다.

7.2 A-Time 주의

A-Time은 Operation과 하위 작업의 시간이 중첩되어 보일 수 있으며 짧은 SQL에서는 계측 정밀도 때문에 값의 합계가 직관적으로 맞지 않을 수 있습니다. 병렬 실행에서는 여러 Process의 시간 성격까지 고려해야 합니다.

따라서 A-Time은 어느 Branch에 시간이 집중되었는지 파악하는 보조 지표로 사용하고 모든 행을 합산하지 않습니다.

8. Workarea Memory 통계

Sort, Hash Join, Hash Group By 같은 Operation은 Workarea를 사용합니다.

OPTIMAL
  → Workarea가 Memory 안에서 완료

ONE PASS
  → TEMP에 일부 기록하고 한 번의 추가 Pass

MULTI-PASS
  → Memory가 더 부족해 여러 Pass 발생

Used-Tmp가 존재하거나 One-pass·Multi-pass라면 TEMP I/O와 Workarea Size를 후속 분석 대상으로 삼습니다. 단, 현재 이론에서는 Memory 통계의 읽는 방법까지만 다룹니다.

9. 실행 통계 예제

SELECT /*+ GATHER_PLAN_STATISTICS */
       d.department_name,
       o.order_id,
       o.order_date
FROM   departments d
JOIN   orders o
  ON   o.department_id = d.department_id
WHERE  d.location_id = 1700
AND    o.order_status = 'OPEN';

가상의 실행 결과입니다.

--------------------------------------------------------------------------------------------------
| Id | Operation                     | Name        | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |             |      1 |        |  5,000 | 00:00.12 | 16,200 |
|  1 |  NESTED LOOPS                 |             |      1 |     50 |  5,000 | 00:00.12 | 16,200 |
|* 2 |   TABLE ACCESS FULL           | DEPARTMENTS |      1 |      5 |      5 | 00:00.01 |     20 |
|* 3 |   TABLE ACCESS BY INDEX ROWID | ORDERS      |      5 |     10 |  5,000 | 00:00.11 | 16,180 |
|* 4 |    INDEX RANGE SCAN           | ORD_DEPT_IX |      5 |     10 |  5,000 | 00:00.02 |  1,200 |
--------------------------------------------------------------------------------------------------

Predicate Information
---------------------
  2 - filter("D"."LOCATION_ID"=1700)
  3 - filter("O"."ORDER_STATUS"='OPEN')
  4 - access("O"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

9.1 반복 횟수

Id 4와 Id 3의 Starts=5이므로 DEPARTMENTS에서 생산된 5행마다 ORDERS Branch가 반복 호출되었습니다.

9.2 최초 Cardinality 오차

Id 4 예상 총 행
  = 5 × 10
  = 50행

Id 4 실제 총 행
  = 5,000행

실제 1회당 평균
  = 1,000행

가장 안쪽 Object Access인 Id 4에서 최초로 큰 오차가 나타났습니다. 상위 Nested Loops의 예상 50행과 실제 5,000행 차이는 이 오차가 전파된 결과입니다.

9.3 Predicate와 I/O

• Index는 부서번호 Join 조건으로 범위를 탐색했습니다.
• 테이블 단계에서 상태 Filter를 적용했습니다.
• Index가 만든 5,000개 ROWID가 테이블에서도 거의 그대로 남았습니다.
• Logical I/O의 대부분이 ORDERS Branch에 집중되었습니다.

이 결과만으로 바로 인덱스를 추가하지 않고 데이터 분포, Predicate 상관관계, 반환해야 하는 행 수와 Table Clustering을 확인합니다.

10. 최초로 크게 어긋난 Operation 찾기

분석 순서는 다음과 같습니다.

1. 가장 안쪽 Object Access부터 확인
2. Starts × E-Rows와 A-Rows 비교
3. 차이가 작으면 부모 Operation으로 이동
4. 최초 큰 차이 지점 표시
5. Predicate와 통계 조건 확인
6. 상위 Join·Sort에 미친 영향 추적

최상위 오차는 여러 하위 오차의 결과일 수 있습니다. 따라서 가장 먼저 발생한 Cardinality 오차가 수정 대상을 찾는 데 더 직접적인 근거가 됩니다.

11. 통계 패턴별 조사 방향

이 표는 원인을 확정하는 규칙이 아니라 조사 우선순위를 정하는 출발점입니다.

12. 일부 Fetch와 Client 조건

SELECT는 Client가 Fetch를 요청할 때 실행이 계속될 수 있습니다.

전체 가능 결과: 100,000행
Client Fetch: 10행 후 Cursor Close

이 경우 ALLSTATS LAST는 실제로 수행된 10행 Fetch 범위까지만 반영할 수 있습니다.

변경 전후 비교에서는 다음 조건을 통일합니다.

• Fetch한 행 수
• Array Fetch Size
• Timeout·Cancel 여부
• Client Tool의 Row Limit
• 첫 페이지 성능인지 전체 처리량 성능인지

전체 결과를 강제로 만들기 위해 원본 SQL을 임의로 COUNT(*)로 감싸면 SQL 구조와 Plan이 바뀔 수 있으므로 동일 SQL의 Fetch 조건을 통제합니다.

13. Adaptive·Parallel 실행 주의

13.1 Adaptive Plan

DBMS_XPLAN의 ADAPTIVE Format에서 - 표시가 붙거나 Starts=0인 Row Source는 마지막 실행에서 비활성 대안일 수 있습니다.

Default Plan 후보
  ├─ Nested Loops 대안
  └─ Hash Join 대안

Final Plan
  → 실제 선택된 Branch만 Starts·A-Rows 발생

비활성 Branch의 E-Rows와 Operation을 실제 수행 작업량으로 해석하지 않습니다.

13.2 Parallel Plan

Parallel 실행에서는 Query Coordinator와 여러 PX Server가 Row Source를 수행합니다. Starts·A-Time·Rows가 여러 Process의 수행을 반영할 수 있으므로 직렬 Plan처럼 단순 해석하지 않습니다.

Parallel의 TQ·IN-OUT·PQ Distrib 상세는 후속 이론에서 다룹니다.

14. Cardinality 오차의 대표 원인

• Table·Column·Index 통계가 없거나 오래됨
• 특정 값에 집중된 Data Skew
• Histogram이 없거나 분포를 충분히 표현하지 못함
• 여러 Column 조건의 상관관계
• Bind 값별 선택도 차이
• 함수·암시적 형변환·복잡한 표현식
• Query Transformation 후 추정 난이도
• 임시 Table과 실행 시점 데이터량 변화
• 통계 수집 이후 대량 DML

오차를 발견했다고 무조건 전체 통계를 다시 수집하지 않습니다. 어떤 Predicate와 Operation에서 오차가 시작됐는지 먼저 확인합니다.

15. 실전 분석 순서

1. SQL_ID·Child Number·Bind 값과 Type 확정
2. Plan Statistics 수집 여부 확인
3. LAST 통계인지 누적 통계인지 확인
4. Fetch 범위와 Client 설정 확인
5. Plan Hash·Note·Adaptive·Parallel 여부 확인
6. Leaf Object Access부터 Starts 확인
7. Starts × E-Rows와 A-Rows 비교
8. 최초 큰 Cardinality 오차 표시
9. Access·Filter Predicate 확인
10. Buffers·Reads·A-Time·Memory가 집중된 Branch 확인
11. 부모·자식 통계 단순 합산 금지
12. 데이터 분포·Bind·통계·SQL 구조의 원인으로 연결

좋은 분석 문장은 다음과 같이 수치와 원인을 연결합니다.

ORD_DEPT_IX는 5번 시작되었고 한 번에 10행을 예상했지만 실제 평균은 1,000행이었다. 최초 Cardinality 오차가 Index Access에서 발생해 상위 Table Access와 Nested Loops까지 50행 대 5,000행 차이로 전파되었고, Logical I/O도 ORDERS Branch에 집중되었다.

16. 자주 혼동하는 판단

17. 핵심 정리

수집
  → GATHER_PLAN_STATISTICS 또는 제한적인 STATISTICS_LEVEL=ALL

출력
  → DISPLAY_CURSOR(SQL_ID, CHILD, 'ALLSTATS LAST ...')

행 수 비교
  → 예상 총 행 ≈ Starts × E-Rows
  → 실제 평균 = A-Rows ÷ Starts

I/O
  → Buffers = 논리 Consistent·Current Get 작업량
  → Reads = 물리 Disk Read

Memory
  → OMem·1Mem·Used-Mem·Used-Tmp
  → Optimal·One-pass·Multi-pass 확인

원인 탐색
  → Leaf부터 최초 Cardinality 오차
  → Predicate·Bind·통계·분포 확인

정확한 비교
  → SQL_ID·Child·Bind·Fetch·Cache 조건 통제

실제 실행 통계는 실행계획을 단순한 Operation 목록에서 측정 가능한 Row 흐름과 작업량으로 바꿉니다. SQLP 수준에서는 계획 이름만 설명하는 데서 멈추지 않고, 어느 Operation이 몇 번 시작됐고 예상보다 몇 행을 더 만들었으며, 그 결과 어느 Branch에서 I/O·시간·TEMP 사용이 증가했는지 설명해야 합니다.