시간 구간 성능 분석: AWR·Statspack·ASH

핵심 요약

실시간으로 문제 Session을 보지 못했더라도 Snapshot과 Active Session 표본을 이용해 문제 시간대의 부하와 원인 후보를 재구성할 수 있습니다.

사용자 증상 시간·Service·Module 확정
  → AWR·Statspack Snapshot 구간 선택
  → DB Time·DB CPU·Foreground Wait Class 분해
  → Load Profile로 처리량과 업무당 작업량 비교
  → Top SQL·Plan Hash·Service·Segment 확인
  → ASH로 짧은 Peak의 실행·Wait·Blocker 시간축 확인
  → DISPLAY_CURSOR·SQL Trace로 실제 Row Source와 Call 검증

AWR·ASH 분석의 핵심은 보고서의 모든 숫자를 읽는 것이 아닙니다.

같은 시간축
+ 같은 Instance·Container·업무 범위
+ 정상 Baseline
+ 실행당·업무당 작업량
→ 의미 있는 성능 비교

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → SQL 분석 도구 → 응답 시간 분석 범위에서 AWR·Statspack·ASH의 시간 구간 분석을 다룹니다. SQL Trace Raw Format, 각 Wait Event·Join·Index의 내부 튜닝과 Oracle Pack 계약 판단 자체는 후속 이론 및 조직 정책에서 다룹니다.

학습 목표

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

• AWR, Statspack, In-memory ASH, Historical ASH의 분석 단위를 구분한다.
• AWR·Statspack 보고서가 Begin·End Snapshot의 Delta라는 점을 설명한다.
• Snapshot Header의 DBID·Instance·Startup·PDB 범위를 확인한다.
• 기본 AWR Snapshot 간격·보존 기간이 설정으로 변경될 수 있음을 설명한다.
• Elapsed Time·DB Time·AAS를 계산한다.
• Load Profile의 Per Second와 Per Transaction을 구분한다.
• AWR의 Database Transaction 분모가 Application 업무 건수와 다를 수 있음을 설명한다.
• Top Foreground Event를 원인이 아닌 시간 소비 위치로 해석한다.
• Top SQL을 총량·실행당·Plan Hash·업무 영향 기준으로 선택한다.
• AWR Top SQL에 없다는 사실이 SQL 미실행을 뜻하지 않는 이유를 설명한다.
• ASH의 Active Session·1초 Sampling과 Historical ASH 축약 저장을 설명한다.
• ASH Sample 수와 SQL 실행 횟수를 구분한다.
• SQL_EXEC_ID·SQL_EXEC_START로 같은 SQL의 실행을 구분한다.
• SESSION_STATE, EVENT, SQL_PLAN_LINE_ID, BLOCKING_SESSION을 연결한다.
• AWR·ASH·Real-Time SQL Monitoring의 Pack 라이선스 경계를 설명한다.
• Pack을 사용할 수 없는 환경의 대체 수집 조합을 설계한다.

1. AWR·Statspack·ASH의 역할

1.1 AWR

Automatic Workload Repository는 Database의 누적 성능 Statistic과 고부하 SQL·Segment·Service·ASH 정보를 주기적으로 수집·저장합니다.

AWR Report는 두 Snapshot 사이의 변화량을 계산합니다.

Begin Snapshot
  → 누적 Counter A

End Snapshot
  → 누적 Counter B

Report Delta
  = B - A

AWR는 모든 SQL을 동일하게 영구 저장하지 않습니다. System Load에 영향을 크게 준 SQL을 중심으로 Snapshot에 포착하므로 AWR Top SQL Section에 없다는 사실만으로 해당 SQL이 실행되지 않았다고 결론 내리지 않습니다.

1.2 Statspack

Statspack은 PERFSTAT Schema와 STATSPACK Package를 이용해 Snapshot을 저장합니다.

CONNECT perfstat/비밀번호
EXECUTE statspack.snap;

@?/rdbms/admin/spreport

spreport.sql은 Begin·End Snapshot ID 사이의 Instance 통계 변화를 보고합니다. Snapshot Level과 SQL Threshold에 따라 수집 범위와 부하가 달라집니다.

Statspack은 AWR의 모든 Section과 ASH 표본을 동일하게 제공하는 도구가 아닙니다. AWR와 Statspack 수치를 비교할 때 수집 Level·Threshold·Snapshot 간격과 Instance 범위를 확인합니다.

1.3 ASH

V$ACTIVE_SESSION_HISTORY는 Active Session을 약 1초마다 표본 수집합니다.

Active Session
  → ON CPU
  또는
  → Non-Idle Event를 WAITING

Idle Session은 일반적으로 표본 대상이 아닙니다.

ASH는 Audit Log가 아닙니다.

• 짧은 SQL은 Sample 사이에 시작·종료되어 누락될 수 있습니다.
• 오래 Active한 SQL은 여러 Sample에 나타납니다.
• Sample 수는 실행 횟수가 아니라 Active Time의 표본입니다.
• 같은 실행이 CPU와 여러 Wait Event 사이를 이동할 수 있습니다.

2. In-memory ASH와 Historical ASH

2.1 In-memory ASH

V$ACTIVE_SESSION_HISTORY는 최근 System Activity의 Rolling Buffer입니다. 새 Sample이 쌓이면 오래된 Sample이 덮어써질 수 있습니다.

대표 Column은 다음과 같습니다.

• SAMPLE_TIME
• SESSION_ID, SESSION_SERIAL#
• SESSION_STATE
• SQL_ID, SQL_CHILD_NUMBER
• SQL_EXEC_ID, SQL_EXEC_START
• SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID
• EVENT, WAIT_CLASS
• BLOCKING_SESSION
• MODULE, ACTION, CLIENT_ID
• CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#

2.2 Historical ASH

DBA_HIST_ACTIVE_SESS_HISTORY는 In-memory ASH의 Historical Snapshot을 AWR에 보존합니다.

Storage 비용을 줄이기 위해 In-memory의 모든 1초 Sample을 그대로 저장하지 않으며, 대표적으로 약 10개 중 1개 Entry가 Historical Repository로 Flush될 수 있습니다.

V$ASH
  → 최근 약 1초 단위 표본

DBA_HIST_ACTIVE_SESS_HISTORY
  → 과거 분석용 축약 표본

Historical ASH의 단순 Row Count를 1초 Active Time과 바로 동일시하지 않습니다. ASH Report 또는 Sample 간격·가중치를 고려한 분석을 사용합니다.

3. Snapshot 범위를 정확히 선택한다

AWR·Statspack Report는 Snapshot 두 개가 정의한 구간을 분석합니다.

Begin Snapshot 100 : 10:00
End Snapshot   101 : 10:15
Report Range       : 10:00~10:15

사용자가 10:07~10:09에만 느렸다면 2시간 Report에서는 2분 Peak가 정상 시간과 평균되어 작게 보일 수 있습니다.

3.1 기본 Snapshot 설정과 변경

Oracle Database의 AWR는 기본적으로 약 1시간 간격으로 Snapshot을 만들고 약 8일간 보존하지만, 간격과 보존 기간은 설정으로 변경할 수 있습니다.

Snapshot 간격 축소
  → 시간 해상도 향상
  → Repository 저장량·관리 부하 증가

보존 기간 증가
  → 장기 비교 가능
  → SYSAUX 사용량 증가

고정 기본값을 전제로 하지 않고 실제 DBA_HIST_WR_CONTROL 또는 AWR Setting을 확인합니다.

3.2 Snapshot Header 확인

Begin·End 비교 전에 다음 항목을 확인합니다.

• DBID·Database Name
• Instance Number·Instance Name
• Host
• Startup Time
• Snapshot Time과 Time Zone
• CDB·PDB·Container 범위
• RAC 전체 Report인지 Instance Report인지

Instance Restart나 Container 범위 변경이 포함되면 구간을 분리하거나 Report Header의 범위를 명확히 기록합니다.

3.3 정상 Baseline

문제 구간과 다음 조건이 유사한 정상 구간을 준비합니다.

• 같은 요일·업무 시간
• 비슷한 Transaction·Request 수
• 같은 Service·Module·Action
• 같은 Instance·PDB
• 비슷한 Batch·Backup 일정
• 같은 Application Version과 주요 Parameter

업무량이 다른 구간은 초당 지표뿐 아니라 Transaction·Request당 지표를 함께 비교합니다.

4. AWR Report를 읽는 큰 순서

1. Snapshot·DBID·Instance·PDB 범위 확인
2. Elapsed Time·DB Time·AAS 확인
3. DB CPU와 Top Foreground Event로 DB Time 분해
4. Load Profile의 Per Second·Per Transaction 비교
5. Host CPU·Run Queue·I/O 확인
6. SQL ordered by Elapsed·CPU·Gets·Reads 확인
7. Plan Hash·Executions·Rows·Service 확인
8. Segment·File·RAC Section으로 Object 연결
9. 정상 Baseline과 문제 구간 비교
10. ASH·Cursor Plan·Trace로 검증

Report Section의 순서는 버전·Report 종류에 따라 달라질 수 있지만 분석 질문은 동일합니다.

5. Elapsed Time·DB Time·AAS

Elapsed Time
  → Snapshot 구간의 벽시계 시간

DB Time
  → 모든 Foreground Session이 Database Call 안에서
     CPU 또는 Non-Idle Wait로 소비한 누적시간

여러 Session이 동시에 Active하면 DB Time은 Elapsed보다 클 수 있습니다.

Elapsed = 900초
DB Time = 4,500초

AAS
  = DB Time / Elapsed
  = 4,500 / 900
  = 5

평균적으로 5개의 Foreground Session이 Active했습니다.

5.1 DB Time 분해

DB Time
  ≈ DB CPU
  + Foreground Non-Idle Wait Time

• DB CPU 비중이 크면 CPU Time·Buffer Gets·Parse·Function·Row 처리량이 큰 SQL을 찾습니다.
• 특정 Wait Class 비중이 크면 Event·SQL·Object·Blocker로 좁힙니다.
• Host CPU 포화는 DB CPU뿐 아니라 OS CPU와 Run Queue를 함께 확인합니다.

6. Load Profile의 두 관점

AWR Load Profile은 대표 Statistic을 Per Second와 Per Transaction으로 정규화합니다.

6.1 Transaction 분모 주의

AWR의 Transaction은 일반적으로 Database의 User Commit과 User Rollback 수를 기반으로 하는 Transaction 성격입니다.

Database Transaction
  ≠ Application 주문 1건
  ≠ API Request 1건

한 주문이 여러 번 Commit되거나 여러 주문이 한 Transaction에 묶일 수 있습니다. Application 업무 효율은 주문·API·Message 같은 실제 업무 건수로 별도 계산합니다.

6.2 처리량 증가와 효율 저하

반대로 Transactions/sec가 같은데 Logical Reads/Tx가 증가했다면 Plan·SQL Call 구조·업무 Mix 변화를 확인합니다.

7. Top Foreground Event 해석

Top Event는 근본 원인의 이름이 아니라 DB Time이 소비된 위치입니다.

우선순위는 다음으로 정합니다.

1. Foreground Total Time
2. DB Time에서 차지하는 비중
3. 정상 구간 대비 증가
4. 업무·Transaction당 시간
5. 특정 Service·SQL·Object 집중도
6. Average뿐 아니라 Tail Latency

8. Top SQL 선정

한 기준으로만 정렬하면 중요한 SQL을 놓칩니다.

SQL A
  1,000,000회 × 5ms
  → Total 5,000초

SQL B
  10회 × 30초
  → Total 300초

• System 전체 효과에는 SQL A가 중요할 수 있습니다.
• 사용자 단건 P95에는 SQL B가 더 중요할 수 있습니다.

8.1 Plan Hash 분리

같은 SQL_ID에 여러 Plan Hash가 있다면 Plan별 통계를 분리합니다.

SQL_ID 동일
  Plan A: 빠른 실행
  Plan B: 느린 실행

통합 평균만 보면 Plan Regression이 숨겨질 수 있습니다.

8.2 AWR에 없는 SQL

AWR는 System Load에 영향이 큰 SQL을 선택해 저장합니다. 다음 SQL은 문제와 관련돼도 Top SQL에서 누락될 수 있습니다.

• 매우 짧지만 특정 Application에서 중요한 SQL
• 전체 Database Top 기준에는 들지 못한 Service 전용 SQL
• Snapshot Capture Threshold를 넘지 못한 SQL
• Peak가 너무 짧아 구간 집계에서 작게 보인 SQL

이 경우 Application Log, Custom V$SQL Snapshot, ASH, SQL Trace를 함께 사용합니다.

9. ASH Sample을 읽는 기준

9.1 현재 상태

SESSION_STATE = 'ON CPU'
  → Sample 시점에 CPU 사용 또는 CPU 실행 상태

SESSION_STATE = 'WAITING'
  → EVENT·WAIT_CLASS가 Non-Idle Wait Context

ON CPU Sample에서 EVENT를 현재 Wait 원인으로 해석하지 않습니다.

9.2 실행 식별

같은 SQL_ID가 반복 실행될 때 다음 조합으로 실행을 구분할 수 있습니다.

SQL_ID
SQL_EXEC_ID
SQL_EXEC_START

SQL_CHILD_NUMBER와 SQL_PLAN_HASH_VALUE를 함께 보면 Child·Plan별 실행을 분리할 수 있습니다.

9.3 Plan Line

SQL_PLAN_LINE_ID는 Sample 시점에 Active했던 Plan Operation을 좁히는 데 도움을 줍니다.

ASH
  → 어느 Plan Line에 Active Time이 집중됐는가

ALLSTATS LAST·SQL Monitor
  → 실제 A-Rows·Starts·Buffers는 얼마인가

ASH Plan Line Sample 수를 실제 처리 Row 수로 해석하지 않습니다.

9.4 Blocking Session

Row Lock 등 Application Wait에서는 다음을 연결합니다.

• Waiter의 SESSION_ID·SERIAL#
• BLOCKING_SESSION
• SQL_ID·Module·Action
• Transaction 시작 시각
• Target Object·Row·Unique Key
• Application 갱신 순서

ASH는 시간대의 Waiter·Blocker 관계를 좁히고, 현재 V$SESSION·Transaction View·Application Log로 검증합니다.

10. ASH Sample과 AAS

In-memory ASH가 약 1초 Sampling일 때 일정 구간의 Sample 수는 Active Time과 관련된 근사치를 제공합니다.

AAS 근사
  ≈ Active Samples / Elapsed Seconds

예를 들어 60초 구간에 같은 Filter 범위의 Active Sample이 180개면 AAS는 약 3으로 해석할 수 있습니다.

단, 다음 조건을 확인합니다.

• In-memory 1초 Sample인지 Historical 축약 표본인지
• RAC Instance·PDB 범위가 일치하는지
• Foreground·Background·Session Type Filter
• 동일 Sample Time 중복·병렬 Process 범위
• Report가 Sampling 가중치를 이미 반영하는지

Historical ASH Raw Row Count를 별도 보정 없이 1초 Sample 수로 계산하지 않습니다.

11. AWR에서 ASH로 좁히는 사례

주문 조회 화면이 10:07~10:10에 느렸다고 가정합니다.

11.1 증상 범위

정상 P95: 0.8초
문제 P95: 6.5초
Service  : ORDER_SVC
Module   : ORDER_API
Action   : SEARCH

11.2 AWR 구간

• 10:00~10:15 DB Time 정상 대비 4배
• Top Foreground Event: db file sequential read
• SQL ordered by Elapsed: SQL_ID abc123
• 같은 SQL_ID에 새 Plan Hash 등장
• Logical Reads/Transaction 증가

11.3 ASH 시간축

• 10:07~10:10 ORDER_API/SEARCH Sample 집중
• SQL_ID abc123·새 Plan Hash 중심
• Table Access Plan Line의 I/O Wait 집중
• 특정 SQL_EXEC_ID 실행이 긴 시간 Active
• 다른 Session의 Row Lock은 주요 비중이 아님

11.4 실제 Plan 검증

Index A-Rows        200,000
Table A-Rows        180,000
Final A-Rows             20
Buffers              350,000

Top-N 20행을 위해 과도한 ROWID와 Table Block을 읽은 구조가 확인됩니다.

11.5 개선 검증

• 같은 Bind·데이터량·동시 사용자
• P95·P99
• DB Time·AAS
• Buffer Gets/Execution
• I/O Wait/Execution
• 다른 Bind·DML Regression

12. Statspack 운용 기준

Statspack Snapshot은 SNAP_ID, DBID, Instance Number로 구분합니다.

다음을 관리합니다.

• Snapshot Level과 SQL Threshold
• Snapshot 주기
• 보존·Purge
• PERFSTAT Schema Statistics
• RAC Instance 범위
• 같은 Startup 범위
• 정상 Baseline

Snapshot Level 증가
  → 더 많은 통계 수집
  → Repository·수집 부하 증가

Statspack Report의 Top SQL·Wait와 AWR Report의 수치를 동일 항목으로 기계적으로 비교하지 않습니다. 각 도구의 수집 Level과 기준을 확인합니다.

13. Multitenant·RAC 범위

13.1 RAC

• Local Instance AWR Report인지 Global RAC Report인지 구분합니다.
• Top Cluster Event는 Remote Instance·Object·Block 전송과 연결합니다.
• Instance별 부하 편중을 확인합니다.
• ASH Query에는 INST_ID 범위를 명확히 합니다.

13.2 CDB·PDB

AWR 데이터는 CDB Root와 PDB 범위가 다를 수 있습니다.

• Report Header의 Container 확인
• CDB Level Snapshot과 PDB Level Snapshot 구분
• AWR_ROOT, AWR_PDB, DBA_HIST_CON View 범위 확인
• PDB Snapshot을 수집하지 않았다면 PDB Local Historical View에 데이터가 없을 수 있음

CDB 전체 Top SQL과 한 PDB의 Application 지표를 범위가 다른 상태로 비교하지 않습니다.

14. 라이선스와 기능 경계

Oracle Licensing Information 기준으로 다음 기능은 Management Pack과 연결됩니다.

Tuning Pack은 Diagnostics Pack을 전제로 합니다.

CONTROL_MANAGEMENT_PACK_ACCESS는 Database 기능을 NONE, DIAGNOSTIC, DIAGNOSTIC+TUNING으로 제어하지만, Parameter가 활성화됐다는 사실 자체가 계약상 사용 권한을 부여하지 않습니다.

기능이 보임
  ≠ 라이선스 보유

View 조회 가능
  ≠ 자유 사용 가능

Edition·Cloud Service에 포함된 범위가 다를 수 있으므로 최신 Licensing Manual, 계약과 조직 정책을 확인합니다.

15. Pack을 사용할 수 없는 환경

Diagnostics Pack을 사용하지 않는 환경에서는 다음을 조합합니다.

Statspack
+ V$SYSSTAT·V$SYSTEM_EVENT Snapshot Delta
+ Custom V$SQL·V$SQLSTATS Snapshot
+ 짧은 간격의 V$SESSION 표본
+ Application APM·Log
+ SQL Trace·TKPROF
+ OS CPU·I/O·Network Monitoring

Custom Sampling은 ASH와 동일한 기능·정밀도를 자동 제공하지 않습니다.

• Sampling 간격과 부하
• Session Identity
• SQL_ID·Wait·Blocker
• 보존·Purge
• 민감정보·권한
• Clock Synchronization

을 설계해야 합니다.

16. 분석 순서

1. 사용자 증상 시각과 Service·Module·Action을 확정한다.
2. Report의 DBID·Instance·Startup·PDB 범위를 확인한다.
3. 문제를 포함하는 가장 짧은 Snapshot 구간을 선택한다.
4. 같은 업무 조건의 정상 Baseline을 준비한다.
5. DB Time·DB CPU·Top Foreground Event와 AAS를 계산한다.
6. Load Profile의 초당·Transaction당·Application 업무당 값을 비교한다.
7. Top SQL을 Total·Per Execution·Plan Hash 기준으로 선정한다.
8. ASH에서 SQL 실행·Wait·Plan Line·Blocker의 시간축을 좁힌다.
9. Actual Cursor Plan·SQL Trace로 행 수·I/O·Call을 검증한다.
10. 같은 조건으로 변경 후 재측정한다.
11. Pack 라이선스와 접근 권한을 문서화한다.

17. 자주 혼동하는 판단