AWR 리포트 분석

ORACLE SQL TUNING · DIAGNOSTICS

AWR 리포트 분석

DB Time 중심의 분석법 — 리포트 생성부터 섹션별 해석, ASH 연계, 문제 SQL 도출까지

선수 학습 · SQL 튜닝 입문 9장커리큘럼 · 11주차라이선스 · Diagnostics Pack

ℹ AWR·ASH 및 DBA_HIST_* 뷰 조회는 Enterprise Edition의 Diagnostics Pack 라이선스가 필요하다. 라이선스가 없다면 Statspack(무료)으로 같은 분석 흐름을 적용할 수 있다. 사용 여부는 CONTROL_MANAGEMENT_PACK_ACCESS 파라미터로 확인한다.

1. AWR 개요

항목	내용
AWR (Automatic Workload Repository)	인스턴스 성능 통계를 주기적으로 스냅샷으로 저장하는 저장소 (SYSAUX)
스냅샷 주기 / 보관 (기본)	60분 / 8일
리포트 원리	두 스냅샷 사이의 누적 통계 차이(Delta)를 보여준다
ASH (Active Session History)	활성 세션 상태를 1초마다 메모리에 샘플링, 그중 1/10을 AWR(DBA_HIST_ACTIVE_SESS_HISTORY)에 저장
ADDM	AWR 스냅샷을 자동 분석해 권고 사항을 제시

-- 현재 설정 확인 / 변경 (예: 30분 주기, 31일 보관)
SELECT snap_interval, retention FROM dba_hist_wr_control;
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30, retention => 31*24*60);

-- 수동 스냅샷 (부하 테스트 전후 등)
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

-- 스냅샷 목록
SELECT snap_id, begin_interval_time, end_interval_time
  FROM dba_hist_snapshot
 WHERE begin_interval_time >= SYSDATE - 1
 ORDER BY snap_id;

2. 리포트 생성

스크립트 ($ORACLE_HOME/rdbms/admin)	용도
awrrpt.sql	현재 인스턴스 AWR 리포트
awrrpti.sql	특정 DBID·인스턴스 지정
awrgrpt.sql	RAC 전체 인스턴스 통합 (Global) 리포트
awrddrpt.sql	두 구간 비교 (Compare Periods) — "어제 vs 오늘"
awrsqrpt.sql	특정 SQL_ID 상세 리포트 (계획별 통계)
ashrpt.sql	ASH 리포트 (분 단위 짧은 구간 분석)
addmrpt.sql	ADDM 분석 결과

-- SQL*Plus에서
SQL> @?/rdbms/admin/awrrpt.sql
-- report_type: html (권장) / text, num_days, begin_snap, end_snap, report_name 입력

-- 또는 함수로 직접 생성
SELECT output
  FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
               l_dbid => (SELECT dbid FROM v$database),
               l_inst_num => 1, l_bid => 1234, l_eid => 1235));

💡 구간 선택이 분석의 절반 — 문제 시점을 포함한 짧은 구간(1~2개 스냅샷)을 선택한다. 24시간 리포트는 평균에 묻혀 문제가 보이지 않는다. 문제 구간과 정상 구간을 awrddrpt.sql로 비교하면 원인이 훨씬 빨리 드러난다.

3. 분석의 핵심 — DB Time

DB Time = 모든 포그라운드 세션이 DB에서 보낸 시간의 합 = DB CPU + Non-Idle Wait Time
AAS (Average Active Sessions) = DB Time ÷ Elapsed Time

AAS 해석	의미
AAS < CPU 코어 수	대체로 여유 있음 (그래도 특정 대기가 응답시간을 지배할 수 있음)
AAS ≈ CPU 코어 수	포화 근접
AAS ≫ CPU 코어 수	세션이 CPU 또는 자원을 기다리며 줄 서 있음 → 대기 이벤트 확인

• 예: Elapsed 60분, DB Time 480분 → AAS 8 — 평균 8개 세션이 동시에 일하거나 기다리고 있었다.

4. 섹션별 읽는 법

4.1 분석 순서

① 헤더
구간·DB Time·AAS

② Load Profile
부하의 양과 성격

③ Top Events
시간을 어디에 썼나

④ Time Model
SQL·Parse·PL/SQL

⑤ SQL Statistics
범인 SQL 찾기

⑥ Segment 등
객체·RAC·IO 상세

4.2 Report Header

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     51234 04-Nov-26 10:00:12       812       3.1
  End Snap:     51235 04-Nov-26 11:00:05       845       3.3
   Elapsed:               59.88 (mins)
   DB Time:              478.92 (mins)          ← AAS ≈ 8.0

4.3 Load Profile

지표	의미	보는 법
DB Time(s) / DB CPU(s) per second	초당 DB Time·CPU = AAS / CPU 사용 세션 수	DB CPU 비중이 낮으면 대기가 지배
Logical read (blocks)	초당 Logical Read	평소 대비 급증 → 비효율 SQL 의심
Physical read (blocks)	초당 디스크 읽기
Redo size (bytes)	초당 Redo 생성량	배치·대량 DML 규모
User calls	클라이언트 호출 수	애플리케이션 호출 패턴
Parses / Hard parses (SQL)	초당 파싱 / 하드 파싱	Hard parse가 초당 수십 이상이면 바인드 변수 미사용 의심
Executes (SQL)	초당 SQL 실행 수
Transactions	초당 커밋+롤백	업무량 기준값

4.4 Instance Efficiency Percentages

• Buffer Hit %, Library Hit %, Soft Parse %, Execute to Parse % 등
• 비율만으로 판단하지 않는다. 비효율 SQL이 같은 블록을 반복해 읽으면 Buffer Hit는 오히려 99%를 넘는다.
• 참고용으로 보되, 평소 값과 크게 다를 때(예: Soft Parse % 급락) 단서로 활용한다.

4.5 Top 10 Foreground Events by Total Wait Time

Event                              Waits   Total Wait Time (sec)  Avg Wait  % DB time  Wait Class
------------------------------  --------  ---------------------  --------  ---------  ----------
DB CPU                                                    9,820              34.2
db file sequential read        8,412,330                  8,110    0.96ms    28.2      User I/O
enq: TX - row lock contention      1,204                  4,950    4.11 s    17.2      Application
log file sync                    912,440                  1,830    2.01ms     6.4      Commit
gc buffer busy acquire           310,221                    920    2.97ms     3.2      Cluster

대기 이벤트	의미	1차 조치 방향
DB CPU	CPU 사용 (대기 아님)	Logical Read 상위 SQL 튜닝, 하드 파싱 확인
db file sequential read	Single Block I/O (인덱스, 랜덤 액세스)	SQL ordered by Reads/Gets — 인덱스·랜덤 액세스 튜닝
db file scattered read	Multi Block I/O (Full Scan, 버퍼 캐시 경유)	Full Scan SQL 확인, 인덱스·파티션 검토
direct path read	Full Scan을 PGA로 직접 읽음 (Serial Direct Read, 병렬)	대량 스캔 SQL, Exadata에서는 Smart Scan 여부
direct path read/write temp	정렬·해시 Temp 사용	PGA 부족 또는 대량 정렬 SQL
enq: TX - row lock contention	행 락 대기	애플리케이션 트랜잭션 설계, 블로킹 세션 (ASH)
log file sync	커밋 시 LGWR 기록 대기	커밋 빈도 과다, Redo 로그 디스크 성능
latch: shared pool / library cache: mutex X	파싱 경합	하드 파싱, 바인드 변수
gc buffer busy / gc cr request 등	RAC 블록 전송 대기	핫 블록, 인스턴스 간 같은 데이터 동시 변경
read by other session	다른 세션이 읽는 블록을 기다림	같은 블록을 동시에 대량 읽는 SQL

4.6 Time Model Statistics

항목	해석
sql execute elapsed time	SQL 실행 시간 — 보통 DB Time의 대부분
parse time elapsed / hard parse elapsed time	파싱 비중이 크면 바인드 변수·커서 공유 문제
PL/SQL execution elapsed time	PL/SQL 엔진 내부 처리 시간
connection management call elapsed time	커넥션 생성·종료 과다 → 커넥션 풀 확인

4.7 SQL Statistics — 문제 SQL 찾기

섹션	언제 보나
SQL ordered by Elapsed Time	가장 먼저 — DB Time에 가장 많이 기여한 SQL
SQL ordered by CPU Time	DB CPU가 Top 이벤트일 때
SQL ordered by User I/O Wait Time	I/O 대기가 Top 이벤트일 때
SQL ordered by Gets	Logical Read 상위 — 튜닝 효과가 가장 확실한 대상
SQL ordered by Reads	Physical Read 상위
SQL ordered by Executions	실행 횟수 상위 — 건당 비용 × 횟수 관점
SQL ordered by Parse Calls	파싱 과다 SQL
SQL ordered by Version Count	Child Cursor가 많은 SQL — 커서 공유 실패
SQL ordered by Cluster Wait Time	RAC 대기 상위 SQL

두 가지 유형을 구분한다
① 건당 비용이 큰 SQL (Elapsed/Exec, Gets/Exec 큼) — 실행계획 튜닝 대상
② 건당은 작지만 실행 횟수가 막대한 SQL (Executions 상위) — 호출 방식 개선(배열 처리, 캐싱, 집합 처리) 대상

4.8 그 외 주요 섹션

• Segment Statistics — Logical/Physical Reads, Row Lock Waits, ITL Waits, GC Buffer Busy 상위 객체
• IOStat by Function / Filetype — I/O가 어디서 발생하는지 (Buffer Cache Reads, Direct Reads, LGWR, DBWR)
• PGA / SGA Advisory — 메모리 크기 변경 시 예상 효과
• RAC Statistics (RAC) — Global Cache 블록 전송량·지연, 인스턴스 간 Interconnect
• init.ora Parameters — 구간 중 파라미터 값, 변경 이력 확인

5. ASH로 좁혀 들어가기

AWR이 "1시간 평균"이라면, ASH는 "그 순간 누가 무엇을" 하고 있었는지를 보여준다.

-- 특정 시간대 Top SQL / Event (메모리, 최근 수 시간)
SELECT sql_id, NVL(event, 'ON CPU') AS event, COUNT(*) AS samples
  FROM v$active_session_history
 WHERE sample_time BETWEEN TIMESTAMP '2026-11-04 10:20:00' AND TIMESTAMP '2026-11-04 10:30:00'
 GROUP BY sql_id, NVL(event, 'ON CPU')
 ORDER BY samples DESC FETCH FIRST 20 ROWS ONLY;

-- 과거 구간 (AWR 저장분, 샘플 1개 ≈ 10초)
SELECT sql_id, NVL(event, 'ON CPU') AS event, COUNT(*) * 10 AS approx_sec
  FROM dba_hist_active_sess_history
 WHERE sample_time BETWEEN TIMESTAMP '2026-11-03 02:00:00' AND TIMESTAMP '2026-11-03 03:00:00'
 GROUP BY sql_id, NVL(event, 'ON CPU')
 ORDER BY approx_sec DESC FETCH FIRST 20 ROWS ONLY;

-- 락 대기의 블로커 찾기
SELECT sample_time, session_id, blocking_session, sql_id, event
  FROM v$active_session_history
 WHERE event = 'enq: TX - row lock contention'
   AND sample_time >= SYSDATE - 1/24
 ORDER BY sample_time;

6. AWR 데이터 직접 조회

-- 스냅샷 구간별 Top SQL (Elapsed 기준)
SELECT s.sql_id,
       SUM(s.executions_delta)                                   AS execs,
       ROUND(SUM(s.elapsed_time_delta)/1e6)                      AS ela_sec,
       ROUND(SUM(s.buffer_gets_delta)/NULLIF(SUM(s.executions_delta),0)) AS gets_per_exec,
       MAX(s.plan_hash_value)                                    AS phv
  FROM dba_hist_sqlstat s
 WHERE s.snap_id BETWEEN &begin_snap AND &end_snap
 GROUP BY s.sql_id
 ORDER BY ela_sec DESC FETCH FIRST 20 ROWS ONLY;

-- 시간대별 DB Time 추이 (부하 패턴 파악)
SELECT sn.begin_interval_time,
       ROUND((tm.value - LAG(tm.value) OVER (ORDER BY sn.snap_id)) / 1e6 / 60, 1) AS db_time_min
  FROM dba_hist_sys_time_model tm
  JOIN dba_hist_snapshot sn
    ON sn.snap_id = tm.snap_id AND sn.dbid = tm.dbid AND sn.instance_number = tm.instance_number
 WHERE tm.stat_name = 'DB time'
   AND sn.instance_number = 1
   AND sn.begin_interval_time >= SYSDATE - 7
 ORDER BY sn.snap_id;

• 인스턴스 재시작 구간에서는 누적값이 초기화되어 음수가 나올 수 있으므로 제외한다.

# Python으로 일일 Top SQL 추출 → 리포트 자동화 예 (oracledb)
import oracledb
sql = """
SELECT sql_id, SUM(executions_delta) execs,
       ROUND(SUM(elapsed_time_delta)/1e6) ela_sec
  FROM dba_hist_sqlstat s JOIN dba_hist_snapshot n
    ON n.snap_id = s.snap_id AND n.dbid = s.dbid AND n.instance_number = s.instance_number
 WHERE n.begin_interval_time >= TRUNC(SYSDATE) - 1
   AND n.begin_interval_time <  TRUNC(SYSDATE)
 GROUP BY sql_id ORDER BY ela_sec DESC FETCH FIRST 20 ROWS ONLY"""
with oracledb.connect(user=USER, password=PWD, dsn=DSN) as conn:
    for row in conn.cursor().execute(sql):
        print(row)

7. 분석 시나리오

시나리오 1. "오전 10시대에 전체 화면이 느렸다"

1. 10:00~11:00 AWR — AAS 8 (평소 2), Top Event db file sequential read 28%, DB CPU 34%
2. Load Profile — Logical Read 평소의 4배
3. SQL ordered by Gets — 1위 SQL_ID 7xk2…, Gets/Exec 125,000, Executions 1,800
4. awrsqrpt.sql — 어제까지 PHV 1234567 (Gets/Exec 40), 오늘 PHV 7654321로 계획 변경
5. 원인 — 새벽 통계 재수집 후 카디널리티 추정 변화로 인덱스 선택 변경
6. 조치 — 이전 계획을 SQL Plan Baseline으로 고정(긴급) → 통계·인덱스 근본 원인 분석

시나리오 2. "특정 업무만 수 초씩 멈춘다"

1. AWR Top Event — enq: TX - row lock contention 17%, Avg Wait 4초
2. Segment Statistics — Row Lock Waits 1위 TB_STOCK
3. ASH — 대기 세션의 BLOCKING_SESSION 추적 → 특정 배치 프로그램이 대량 UPDATE 후 커밋 지연
4. 조치 — 배치 커밋 단위 조정, 처리 순서 통일(데드락 예방), 온라인 시간대 분리

시나리오 3. "CPU가 항상 80% 이상이다"

1. Top Event — DB CPU 70%
2. Load Profile — Hard parses 초당 300, Time Model — hard parse elapsed time 25%
3. SQL ordered by Version Count / Parse Calls — 리터럴 SQL 다수
4. 조치 — 애플리케이션 바인드 변수 적용 (근본), 임시 대응으로 CURSOR_SHARING=FORCE 검토(부작용 테스트 필수)

8. AWR 분석 체크리스트

□ 문제 시점을 포함한 짧은 구간을 선택했는가 / 정상 구간과 비교했는가
□ DB Time과 AAS로 부하 수준을 판단했는가
□ Top Events에서 DB CPU vs 대기 비중과 상위 Wait Class를 확인했는가
□ Load Profile에서 평소 대비 급증한 지표를 찾았는가 (Logical Read, Hard Parse, Redo)
□ Top 이벤트에 대응하는 SQL ordered by … 섹션에서 대상 SQL을 뽑았는가
□ 대상 SQL의 계획 변경 이력(PHV)을 확인했는가
□ Segment Statistics로 핫 객체를 확인했는가
□ 짧은 순간의 현상은 ASH로 세션·블로커까지 좁혔는가
□ 결론을 수치(DB Time 기여도, Gets/Exec)로 문서화했는가

이전 ← 조인 튜닝 심화 · 인덱스 튜닝 심화 · 실행계획 읽기 실습  |  처음으로 → SQL 튜닝 입문