진단 도구 선택과 교차 검증: AUTOTRACE·DISPLAY_CURSOR·SQL Monitor·V$SQL

핵심 요약

성능 진단 도구는 서로 대체하는 단일 정답이 아니라 서로 다른 시간·집계 단위의 질문에 답합니다.

실행 전 후보 계획
  → EXPLAIN PLAN + DBMS_XPLAN.DISPLAY

간단한 SQL의 결과·예상 계획·문장 전체 Session 통계
  → SQL*Plus AUTOTRACE

현재 Cursor Cache의 실제 Child Plan
  → DBMS_XPLAN.DISPLAY_CURSOR

Operation별 마지막 실행 Row Source 통계
  → DISPLAY_CURSOR(..., 'ALLSTATS LAST')

현재 Shared Pool의 Child별 누적 부하
  → V$SQL

SQL_ID별 빠른 Top SQL 탐색·더 긴 통계 보존
  → V$SQLSTATS

한 번의 장시간·병렬 실행을 거의 실시간으로 분석
  → Real-Time SQL Monitoring

특정 업무의 Parse·Execute·Fetch·Wait·호출 순서
  → SQL Trace·TKPROF·Raw Trace

과거 시간 구간의 Database 전체 부하
  → AWR·ASH 또는 Statspack·Custom Snapshot

도구를 선택하기 전에 다음 질문을 먼저 고정합니다.

1. SQL을 실행하지 않을 것인가, 실제 실행을 측정할 것인가?
2. 한 실행인가, Cursor가 Load된 이후의 누적인가?
3. 실제 Child Cursor Plan인가, 설명 시점의 예상 Plan인가?
4. Operation별 Row·I/O인가, Session·Statement 전체 통계인가?
5. 현재 실행인가, 이미 끝난 실행인가, 과거 시간 구간인가?
6. SQL 한 문장인가, 한 화면·Batch의 여러 Call인가?
7. Pack 라이선스와 접근 권한이 허용되는가?

도구 결과가 다름
  → 하나가 틀렸다고 단정하지 않음
  → 시간 범위·실행 범위·Child·Plan·Bind·Fetch 조건을 먼저 맞춤

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → SQL 분석 도구 → 응답 시간 분석 범위에서 진단 도구 선택과 교차 검증을 다룹니다. 각 도구의 내부 저장 구조와 Raw Trace Record 전체 형식은 후속 이론에서 다룹니다.

학습 목표

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

• AUTOTRACE의 SQL 실행 여부, 예상 계획과 문장 전체 통계를 구분한다.
• DISPLAY_CURSOR가 현재 Cursor Cache의 실제 Child Plan을 표시한다는 점을 설명한다.
• ALLSTATS LAST에 A-Rows·Buffers가 나타나기 위한 선행 조건을 설명한다.
• V$SQL의 Child Cursor 누적값과 SQL Monitor의 단일 실행 통계를 구분한다.
• V$SQL의 ELAPSED_TIME이 Parallel SQL에서 벽시계 시간보다 클 수 있음을 설명한다.
• END_OF_FETCH_COUNT로 전체 Fetch 완료 실행과 일부 Fetch 실행을 구분한다.
• V$SQLSTATS의 SQL_ID별 집계와 더 긴 보존 특성을 설명한다.
• SQL Monitor의 자동 대상 조건, 실행 식별키와 병렬 Process 행을 설명한다.
• V$SQL_MONITOR·V$SQL_PLAN_MONITOR의 상태·갱신·보존 특성을 설명한다.
• SQL Monitor와 SQL Trace의 Operation 단위·Call 순서 단위를 구분한다.
• 과거 Plan과 부하 분석에서 AWR·ASH·Statspack을 선택한다.
• 같은 성능 문제를 서로 다른 도구의 단위로 교차 검증한다.
• Diagnostics·Tuning Pack, CONTROL_MANAGEMENT_PACK_ACCESS와 계약상 라이선스를 구분한다.

1. 질문을 먼저 정한다

2. AUTOTRACE: 예상 계획과 문장 전체 통계

AUTOTRACE는 SQL*Plus의 SET 기능입니다.

SET AUTOTRACE ON
SET AUTOTRACE ON EXPLAIN
SET AUTOTRACE ON STATISTICS
SET AUTOTRACE TRACEONLY
SET AUTOTRACE TRACEONLY EXPLAIN
SET AUTOTRACE TRACEONLY STATISTICS
SET AUTOTRACE OFF

ON이나 TRACEONLY에 세부 Option을 명시하지 않으면 기본적으로 EXPLAIN STATISTICS를 요청합니다. TRACEONLY는 결과 인쇄를 억제할 뿐이며, STATISTICS가 포함된 Query는 Server에서 실제 실행·Fetch됩니다.

2.1 DML 주의

SET AUTOTRACE TRACEONLY

UPDATE orders
SET    status = 'CLOSED'
WHERE  order_date < ADD_MONTHS(SYSDATE, -12);

일반 TRACEONLY에서 DML은 실제로 수행됩니다.

• Data 변경
• Row Lock
• Undo·Redo
• Trigger
• Transaction 유지

계획만 확인하려면 TRACEONLY EXPLAIN 또는 EXPLAIN PLAN FOR를 사용합니다.

2.2 통계의 관찰 단위

AUTOTRACE Statistics는 문장 실행 전후 Session 통계 차이입니다.

• Recursive Calls
• DB Block Gets
• Consistent Gets
• Physical Reads
• Redo Size
• SQL*Net Byte·Round Trip
• Sorts
• Rows Processed

AUTOTRACE Statistics
  → SQL 문장 전체 요약

ALLSTATS LAST
  → 실행계획 Operation별 통계

2.3 계획 부분의 한계

AUTOTRACE의 Plan은 EXPLAIN PLAN 기반 예상 계획입니다. 실제 Child Plan과 다음 이유로 달라질 수 있습니다.

• 실제 Bind 값·Type과 Bind Peeking
• Child Cursor의 Optimizer Environment
• Parsing Schema·권한
• Object Statistics·Histogram
• Adaptive Cursor Sharing·Final Plan
• SQL Profile·Patch·Baseline

3. DISPLAY_CURSOR: 실제 Child Cursor Plan

DBMS_XPLAN.DISPLAY_CURSOR는 현재 Cursor Cache에 Load된 Cursor의 실행계획을 표시합니다.

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

SQL_ID=NULL은 현재 Session의 마지막 Cursor를 대상으로 하지만 진단 SQL 실행 과정에서 대상이 바뀔 수 있습니다. 운영 분석에서는 SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE와 Bind·Session 환경을 기록합니다.

Cursor가 Shared Pool에서 Aging Out되면 DISPLAY_CURSOR로 조회하지 못할 수 있습니다.

3.1 ALLSTATS LAST의 선행 조건

실행 전에 다음 중 하나로 Row Source Statistics를 수집합니다.

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

ALTER SESSION SET STATISTICS_LEVEL = ALL;

실행 후 확인합니다.

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

Format만 나중에 추가해도 과거 실행에 수집되지 않은 A-Rows·Buffers가 소급 생성되지는 않습니다.

3.2 확인 항목

• Starts
• E-Rows
• A-Rows
• A-Time
• Buffers
• Reads
• OMem, 1Mem, Used-Mem, Used-Tmp
• Predicate Information
• Note·Adaptive 정보

ALLSTATS LAST는 마지막 실행 기준이며 Partial Fetch 여부를 함께 확인합니다.

4. V$SQL: Child Cursor의 누적 통계

V$SQL은 Child Cursor마다 한 행을 제공합니다. 통계는 해당 Cursor가 Library Cache에 Load된 이후의 누적값입니다.

SELECT sql_id,
       child_number,
       child_address,
       plan_hash_value,
       executions,
       end_of_fetch_count,
       parse_calls,
       fetches,
       rows_processed,
       buffer_gets,
       disk_reads,
       cpu_time,
       elapsed_time,
       last_load_time,
       last_active_time,
       module,
       action
FROM   v$sql
WHERE  sql_id = :sql_id
ORDER BY child_number;

4.1 V$SQL이 답하는 질문

• 어느 Child가 몇 번 실행됐는가?
• Child별 Plan Hash와 누적 작업량은 얼마인가?
• 실행당 Buffer Gets·Elapsed는 얼마인가?
• Parse Calls·Loads·Invalidations가 많은가?
• 특정 Child가 현재 실행 중인가?

V$SQL
  → Child Cursor 누적

SQL Monitor
  → Monitored SQL의 한 실행

4.2 END_OF_FETCH_COUNT

EXECUTIONS는 Cursor 실행 시작을 포함하고, END_OF_FETCH_COUNT는 전체 Fetch가 완료된 실행 수를 나타냅니다.

END_OF_FETCH_COUNT < EXECUTIONS
  → 일부 실행이 오류·일부 Fetch·재실행·Cursor Close로 끝났을 수 있음

SELECT의 실행당 평균을 해석할 때 전체 Fetch 완료 실행인지 확인합니다.

4.3 ELAPSED_TIME 단위와 Parallel SQL

CPU_TIME과 ELAPSED_TIME은 Microsecond입니다.

Parallel SQL에서 ELAPSED_TIME은 Query Coordinator와 Parallel Worker Process의 누적 Database Time일 수 있으므로 실제 벽시계 실행시간보다 클 수 있습니다.

V$SQL ELAPSED_TIME / EXECUTIONS
  ≠ Parallel SQL의 정확한 Wall Clock Time

개별 Parallel 실행은 SQL Monitor의 SQL_EXEC_ID로 확인합니다.

4.4 MODULE·ACTION 주의

V$SQL의 MODULE, ACTION은 해당 SQL이 처음 Parse될 때의 Context입니다. 같은 Parent·Child가 다른 업무에서 재사용되면 현재 실행 업무를 완전하게 대표하지 않을 수 있습니다.

5. V$SQLSTATS: SQL_ID별 빠른 Top SQL 탐색

V$SQLSTATS는 SQL_ID마다 한 행을 제공하며 V$SQL·V$SQLAREA보다 빠르고 확장성이 높고 통계 보존이 더 길 수 있습니다. Cursor가 Shared Pool에서 Age Out된 뒤에도 통계가 남아 있을 수 있습니다.

SELECT sql_id,
       plan_hash_value,
       executions,
       buffer_gets,
       disk_reads,
       cpu_time,
       elapsed_time,
       last_active_time
FROM   v$sqlstats
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

5.1 적합한 용도

• 현재·최근 Top SQL 후보 선정
• SQL_ID별 총량·평균 비교
• Shared Pool Age Out 직후의 기본 통계 확인
• V$SQL보다 가벼운 정기 Snapshot

5.2 한계

• SQL_ID 단위로 합쳐져 Child별 차이가 제한됨
• V$SQL보다 Column이 적음
• 정확한 Child 공유 실패·Bind·Plan별 분석에는 부족함

V$SQLSTATS로 후보 찾기
  → V$SQL에서 Child·Plan 분리
  → DISPLAY_CURSOR에서 실제 Plan

6. Real-Time SQL Monitoring: 단일 실행 분석

SQL Monitoring은 STATISTICS_LEVEL=TYPICAL 또는 ALL 환경에서 기본 기능이 활성화될 수 있습니다. Simple SQL·PL/SQL Operation은 다음 조건 중 하나를 만족하면 자동 Monitoring 대상이 됩니다.

1. 한 번의 실행에서 CPU 또는 I/O 시간을 5초 이상 소비
2. Parallel로 실행
3. /*+ MONITOR */ Hint 사용
4. 설정된 SQL Monitor Event로 강제 지정

MONITOR Hint는 CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING'일 때 기능적으로 유효합니다.

SELECT /*+ MONITOR */
       ...
FROM   ...;

6.1 실행 식별키

Simple SQL 실행은 다음 조합으로 구분합니다.

SQL_ID
SQL_EXEC_START
SQL_EXEC_ID

같은 SQL이 두 번 Monitoring되면 별도 실행 Entry가 만들어집니다. V$SQL처럼 여러 실행이 누적된 한 행이 아닙니다.

6.2 Parallel 실행

Parallel SQL은 Query Coordinator와 각 PX Server별 행이 존재할 수 있습니다. 같은 실행에 속한 행은 동일한 실행 식별키를 공유합니다.

전체 Parallel 실행 통계
  → SQL_ID + SQL_EXEC_START + SQL_EXEC_ID로 집계

Process 행을 실행 횟수로 세면 안 됩니다.

6.3 V$SQL_MONITOR

SELECT sql_id,
       sql_exec_start,
       sql_exec_id,
       status,
       sid,
       process_name,
       module,
       action,
       elapsed_time,
       cpu_time,
       buffer_gets,
       physical_read_bytes,
       physical_write_bytes
FROM   v$sql_monitor
WHERE  sql_id = :sql_id;

대표 상태는 다음과 같습니다.

• EXECUTING
• DONE (ERROR)
• DONE (FIRST N ROWS)
• DONE (ALL ROWS)
• DONE — Parallel 실행

DONE (FIRST N ROWS)는 Application이 전체 Fetch 전에 실행을 종료했음을 의미합니다.

실행 중 통계는 일반적으로 약 1초마다 갱신됩니다. 실행 종료 후에도 최소 약 1분간 남아 있을 수 있으나 새로운 Monitoring Data를 위해 재사용됩니다.

6.4 V$SQL_PLAN_MONITOR

V$SQL_PLAN_MONITOR는 Monitoring된 실행계획의 Operation별 행을 제공합니다.

• Plan Line·Operation
• Starts
• Output Rows
• Physical I/O
• Memory·Temp
• Parallel Process별 통계
• First·Last Change Time

Operation별 직접 Timing Overhead를 줄이기 위해 각 Plan 행이 모든 CPU·Elapsed·I/O Time을 독립적으로 기록하는 것은 아닙니다. Activity Time은 ASH와 실행 식별키·Plan Line을 연결해 추정할 수 있습니다.

6.5 SQL Monitor Report

Database 버전에 따라 DBMS_SQL_MONITOR 또는 DBMS_SQLTUNE의 Report Function을 사용할 수 있습니다.

SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
         sql_id       => :sql_id,
         sql_exec_id  => :sql_exec_id,
         type         => 'TEXT',
         report_level => 'ALL'
       )
FROM dual;

6.6 특히 유리한 상황

• 아직 실행 중인 SQL
• 수십 초·수분의 단일 실행
• Parallel Query의 QC·PX 활동
• Hash·Sort·TEMP·I/O가 어느 Plan Line에 집중되는지 확인
• 같은 SQL의 느린 개별 실행만 분리
• Batch의 Composite Database Operation

짧고 초고빈도로 실행되는 SQL의 시스템 총량은 V$SQLSTATS·V$SQL·AWR가 더 적합할 수 있습니다.

7. Composite Database Operation

하나의 Batch나 ETL이 여러 SQL·PL/SQL로 구성되면 DBMS_SQL_MONITOR.BEGIN_OPERATION과 END_OPERATION으로 논리적 Operation을 정의할 수 있습니다.

Batch 시작
  → BEGIN_OPERATION
  → 여러 SQL·PL/SQL
  → END_OPERATION

Composite Operation은 이름과 실행 ID로 식별하며, 같은 Session에서 Operation 시작·종료 범위를 정확히 관리합니다. SQL Trace가 Call 순서와 Bind·Wait 분석에 강하다면 Composite Monitoring은 장시간 Batch의 전체 Resource와 하위 SQL 활동을 하나의 논리 단위로 보는 데 유리합니다.

8. SQL Trace·TKPROF: Call과 업무 흐름

SQL Monitor는 한 실행의 Operation별 활동에 강하고, SQL Trace는 한 Session·업무에서 발생한 Call 순서에 강합니다.

N+1 Query, 작은 Fetch Size, 반복 Commit, Recursive SQL처럼 Application Call 구조가 핵심이면 SQL Trace가 더 직접적입니다.

9. 과거 구간: AWR·ASH·Statspack

문제가 이미 지나갔거나 Cursor가 Age Out된 경우 다음 자료를 사용합니다.

AWR·Statspack
  → Snapshot 구간의 DB Time·Wait·Top SQL·작업량

ASH
  → 문제 초·분의 Active Session·SQL·Plan·Wait·Blocker

DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY
  → AWR에 저장된 과거 실행계획

Oracle AI Database 26ai에서 DISPLAY_WORKLOAD_REPOSITORY는 기존 DISPLAY_AWR을 대체합니다.

Pack을 사용할 수 없는 환경에서는 다음을 조합합니다.

• Statspack
• Custom V$SYSSTAT·V$SYSTEM_EVENT Snapshot
• Custom V$SQLSTATS Snapshot
• Application APM·Log
• SQL Trace
• OS CPU·I/O·Network Monitoring

10. 하나의 문제를 여러 도구로 교차 검증하기

“상품 검색 API가 8초 걸린다”는 상황을 분석합니다.

10.1 APM·Application Log

전체 응답        8.0초
Connection Pool  0.2초
Database Call    7.4초
Network          0.4초

Database Call이 핵심 범위입니다.

10.2 시간 구간 자료·V$SQLSTATS

Executions Delta         100
Elapsed Delta            600초
Buffer Gets Delta     30,000,000
Elapsed / Exec              6초
Gets / Exec             300,000

System 기여와 단건 작업량이 모두 큽니다.

10.3 V$SQL

• 같은 SQL_ID의 Child별 Plan Hash 확인
• END_OF_FETCH_COUNT와 EXECUTIONS 비교
• 특정 Child의 누적 Buffer Gets·Elapsed 확인
• MODULE·ACTION이 최초 Parse Context임을 고려

10.4 DISPLAY_CURSOR

Index E-Rows       100
Index A-Rows   200,000
최종 A-Rows         20
Buffers         300,000

Leaf Index에서 Cardinality 과소 추정과 후보 Row 급증이 확인됩니다.

10.5 SQL Monitor

실행키로 느린 한 번을 분리합니다.

SQL_ID + SQL_EXEC_START + SQL_EXEC_ID

Table Access Operation에 User I/O Activity가 집중되고 DONE (ALL ROWS)인지 DONE (FIRST N ROWS)인지 확인합니다.

10.6 SQL Trace

API 한 요청이 SQL을 두 번 호출하고 특정 Bind의 두 번째 실행만 느리다는 사실을 확인합니다.

결론 후보
  → 특정 Bind의 Cardinality·Plan 문제
  + 후보 ROWID·Table Access 과다
  + Application 중복 호출

11. 도구 결과가 다를 때

12. 라이선스·권한·운영 부하

기능이 활성화됨
  ≠ 계약상 사용 권한이 자동 부여됨

Oracle Tuning Pack에는 Real-Time SQL·PL/SQL Monitoring과 Database Operations Monitoring이 포함되며 Diagnostics Pack을 전제로 합니다. AWR·ASH는 Diagnostics Pack 기능입니다.

13. 도구 선택 순서

1. 사용자 증상 시간과 업무 범위를 정한다.
2. 예상·실제, 단일 실행·누적·과거 구간을 구분한다.
3. 필요한 정보를 얻는 가장 작은 범위의 도구를 고른다.
4. 도구가 SQL을 실제 실행하는지 확인한다.
5. SQL_ID·Child·Plan·Bind·Fetch·Execution Key를 기록한다.
6. 수집 부하·파일·민감정보·Pack 조건을 확인한다.
7. 한 도구의 가설을 다른 시간·집계 단위의 도구로 검증한다.
8. 동일 조건에서 변경 전후를 같은 지표로 비교한다.
9. Bottleneck Migration과 Regression을 재확인한다.

진단 도구를 잘 선택한다는 것은 가장 많은 정보를 수집하는 것이 아닙니다. 현재 질문에 맞는 최소 범위의 신뢰할 수 있는 자료를 수집하고, 다른 단위의 자료로 같은 원인을 확인하는 것입니다.

자주 혼동하는 판단