테마
16화 · 파고들기
조회용 문서다. 처음 읽을 때는
1_사건.md만으로 충분하다. 이 문서의 숫자는 모두ora-hanorder(15화 종료 상태)에서 잰 실측값이다. 스크립트 번호를 함께 적었다. 구간 A와 B는 두 번씩 돌려 판정이 같게 나오는지 확인했다(범위는 §15). AWR 숫자는 그 구간의 평균이다. 초와 퍼센트는 장비에 따라 흔들리므로, 판정은 비율의 방향과 순위, 실행 수로 한다.
| 찾는 것 | 바로 가기 |
|---|---|
| AWR이 무엇을 저장하나: 스냅샷 · 델타 · 자동과 수동 · 간격과 보존 · 저장소 | §1 검침 대장 |
| 루트에서 보나 PDB에서 보나: 이 버전(23.26 Free)의 PDB 레벨 AWR 실측 | §2 루트와 PDB |
| AWR SOP 9단계 · 읽는 순서 9칸 | §3 SOP |
| ① 머리: Elapsed · DB Time · 평균 활성 세션 | §4 머리 |
| ② Load Profile · ③ Instance Efficiency의 함정 | §5 비율 |
| ④ Top Foreground Events: 히트율이 못 보는 I/O | §6 이벤트 |
| ⑤ Host CPU · Instance CPU: Free의 CPU 2개 · "CPU를 늘리면" | §7 CPU |
| ⑥ Time Model: 겹치는 줄 · 맞지 않는 줄 | §8 Time Model |
⑦ SQL ordered by 4관점 · 실행 0인 PL/SQL · 목록이 뽑히는 기준 · AWR_SQL_REPORT_TEXT | §9 SQL |
| ⑧ Segments by Logical Reads · ⑨ 대조 | §10 세그먼트 · 대조 |
| 두 구간 비교(awrddrpt) | §11 비교 |
DBA_HIST_* 직접 조회: 칸마다 어느 뷰인가 | §12 DBA_HIST |
| 부하 모델의 Q2는 운영의 Q2인가 | §13 모델 |
| 한 시간 구간 H: AWR이 내린 결론 | §14 한 시간 |
| 측정값 전부 · 두 번 돌린 범위 | §15 측정 |
| Free에서 못 잰 것 · 봉인한 것 | §16 한계 |
| 원천 좌표 | §17 좌표 |
1. 스냅샷과 델타 — 검침 대장
1.1 AWR은 뺄셈이다
V$SYSSTAT, V$SYS_TIME_MODEL, V$SYSTEM_EVENT, V$SQLSTATS, V$SEGMENT_STATISTICS는 인스턴스가 뜬 뒤로 올라가기만 하는 계량기다(← 01화 V$MYSTAT의 누적). AWR은 그 계량기를 정해진 때에 읽어 SYSAUX의 WRH$_* 테이블에 적는다. 이것이 스냅샷이다. 리포트의 모든 숫자는 두 스냅샷의 값을 뺀 것(델타)이다.
| 방재실 | AWR |
|---|---|
| 층별 계량기(계속 올라간다) | V$ 누적 통계 |
| 매시 정각 검침 · 대장에 한 줄 | 자동 스냅샷(snap_flag 0) · 1시간마다 |
| "지금 하나 더 읽어 둬" | 수동 스냅샷 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(snap_flag 1) |
| 두 줄을 빼서 얻는 그 시간의 사용량 | AWR 리포트(AWR_REPORT_TEXT) |
| 대장 보관 기간 | 보존(retention) · 이 DB 8일 |
| 계약 전력 · 수요 제어기가 에어컨을 번갈아 끈다 | cpu_count 2 · resmgr:cpu quantum(§7) |
| 역률 99.6%(얼마나 쓰는지는 알 수 없다) | Instance Efficiency(§5) |
| 층별 사용량 순위 | SQL ordered by · Segments by(§9 · §10) |
| 그 한 시간 안의 5분 정전은 대장에 한 줄로 묻힌다 | 평균: 구간 안의 '언제'는 리포트에 없다(→ 17화) |
주의: 재시작하면 계량기가 0에서 다시 시작한다. 두 스냅샷 사이에 인스턴스가 재시작했다면 그 구간의 리포트는 만들 수 없다(뺄셈이 안 된다).
DBA_HIST_SNAPSHOT.STARTUP_TIME이 두 스냅샷에서 같은지 본다(08이 그렇게 거른다).
1.2 이 DB의 설정 (실습/01)
| 항목 | 값 |
|---|---|
control_management_pack_access | DIAGNOSTIC+TUNING(ISDEFAULT FALSE: 이미지가 켜 두었다) |
statistics_level | TYPICAL |
간격 · 보존(CDB_HIST_WR_CONTROL) | 1시간 · 8일: 루트(con_id 0)와 FREEPDB1(con_id 3)이 따로 갖는다 · TOPNSQL = DEFAULT |
AWR 저장소(V$SYSAUX_OCCUPANTS의 SM/AWR) | 85.6MB(16화 시작 때) |
V$RESOURCE_LIMIT processes · sessions | 84 / 200 · 110 / 322(부하 세션 3 + 스냅샷 세션 1이 들어갈 자리가 넉넉하다) |
이 화에서는 간격과 보존을 바꾸지 않았다. 구간은 수동 스냅샷으로 잡는다. 바꿔야 할 때는 MODIFY_SNAPSHOT_SETTINGS(interval => 분, retention => 분)를 쓰고, 끝나면 반드시 원래 값으로 되돌린다.
1.3 권한
sqlp는 SELECT ANY DICTIONARY가 있어도 DBMS_WORKLOAD_REPOSITORY를 부를 수 없다. PLS-00201: identifier 'DBMS_WORKLOAD_REPOSITORY' must be declared가 난다(실측, 15화 DBMS_HPROF와 같은 모양). 이 화의 스냅샷 스크립트와 리포트 스크립트는 모두 SYS로 돈다.
2. 어디서 보나 — 루트와 PDB
26ai Free는 CDB$ROOT + FREEPDB1 구조다. AWR 스냅샷은 루트와 PDB가 따로 갖는다(DBID도 다르다: 루트 = V$DATABASE.DBID, PDB = V$DATABASE.CON_DBID).
2.1 설계와 달랐던 것 — awr_pdb_autoflush_enabled
개정계획 L03은 이 파라미터의 기본값을 false(19c ~ 23ai)로 적었다. 이 버전(23.26.2 Free)에서는 TRUE이고 ISDEFAULT = TRUE다(실습/01 · 07 [1]). 그래서 자동 스냅샷은 루트와 PDB에 둘 다 생긴다. 교재 실측으로는 16화 시작 때 루트 자동 9개, PDB 자동 9개가 같은 시각에 있었다.
2.2 수동 스냅샷은 따로다 (실습/07 [2])
| 실측 | 결과 |
|---|---|
루트에서 CREATE_SNAPSHOT | 루트에만 생긴다. 같은 순간 PDB 쪽(AWR_PDB_SNAPSHOT)에 생긴 스냅샷은 0 |
PDB에서(ALTER SESSION SET CONTAINER = FREEPDB1) CREATE_SNAPSHOT | PDB에만 생긴다 · 번호는 PDB의 순번 |
| 번호 | 루트와 PDB가 따로 센다(교재의 구간 A: 루트 15 · 16 / PDB 12 · 13. 번호는 DB마다, 런마다 다르다) |
그래서 02는 S1과 S2마다 루트와 PDB에 하나씩 찍는다.
2.3 리포트 세 가지 — 9칸이 있나 (실습/07 [4] · 구간 A)
| 칸 | (가) 루트 리포트 (루트 스냅샷) | (나) PDB 리포트 (PDB 스냅샷) | (다) PDB 리포트 (루트 스냅샷) |
|---|---|---|---|
| 제목 | WORKLOAD REPOSITORY report for | … PDB report (PDB snapshots) | … PDB report (root snapshots) |
| 어디서 뽑나 | CDB$ROOT · 루트 DBID | FREEPDB1 · PDB DBID | FREEPDB1 · 루트 DBID |
| DB Time | 28.08분 | 27.30분 | 27.30분 |
| ① 머리 · ② Load Profile | ○ | ○ | ○ |
| ③ Instance Efficiency | ○ | ✗ | ✗ |
| ④ Top 10 Foreground Events | ○ | ○ | ○ |
| ⑤ Host CPU · Instance CPU | ○ | ✗ | ✗ |
| ⑥ Time Model | ○ | ○ | ○ |
| ⑦ SQL ordered by (4관점) | ○ | ○ | ○ |
| ⑧ Segments by Logical Reads | ○ | ○ | ○ |
| Top ADDM Findings (머리) | ○ | ○ | ✗ |
| 리포트 줄 수 | 5,228 | 2,609 | 2,424 |
- PDB 리포트에는 인스턴스 전체의 칸(Instance Efficiency · Host CPU · Instance CPU · Buffer Pool · Latch · OS 통계 …)이 없다. 9칸 중 둘이 빠진다.
- DB Time이 다르다. 루트 리포트의 28.08분에는 루트의 포그라운드(SYS 세션 · 스냅샷 · 리포트 세션)가 들어 있다. PDB 리포트에는 그 PDB의 몫(27.30분)만 들어간다.
2.4 PDB 안의 사용자에게 보이는 것 (실습/07 [3] · sqlp · SELECT ANY DICTIONARY)
| 뷰 (루트 DBID · 구간 A) | 행 | 뜻 |
|---|---|---|
DBA_HIST_SQLSTAT | 74 | 루트 스냅샷의 SQL 통계 중 이 PDB의 것은 보인다 |
DBA_HIST_SEG_STAT | 84 | 세그먼트도 보인다 |
DBA_HIST_CON_SYSSTAT | 299 | 이 PDB 몫의 통계 |
DBA_HIST_OSSTAT | 42 | 호스트 통계는 보인다 |
DBA_HIST_SYSSTAT | 0 | 인스턴스 전체의 계량기는 안 보인다 |
DBA_HIST_SYS_TIME_MODEL | 0 | DB Time도 안 보인다 |
AWR_REPORT_TEXT 호출 | — | ORA-00904: "DBMS_WORKLOAD_REPOSITORY"."AWR_REPORT_TEXT": invalid identifier(실행 권한 없음) |
PDB 계정에 EXECUTE ON DBMS_WORKLOAD_REPOSITORY를 주면 그 계정으로 PDB 스냅샷을 찍고 PDB 리포트(나 · 다)를 뽑을 수 있다. 이 화를 준비하며 한 번 시험하고 권한을 거뒀다(실측). 루트 리포트(가)는 여전히 루트에서만 나온다.
2.5 AWR_SQL_REPORT_TEXT의 l_con_dbid
루트 구간에서 PDB의 SQL 리포트를 뽑을 때 다섯 인자(dbid, inst, bid, eid, sql_id)만 주면 "No data exists for this section of the report."만 나온다(실측). 마지막 인자 l_con_dbid에 PDB의 DBID를 줘야 나온다(실습/06). 공식문서에는 If NULL (default), then l_con_dbid is set equal to l_dbid라고 되어 있다. 비워 두면 루트의 SQL을 찾는다는 뜻이다.
sql
SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_TEXT(:root_dbid, :inst, :s1, :s2, :sql_id, 0, :pdb_dbid));판정: 이 교재의 리포트는 루트에서 뽑는다. 9칸이 다 있는 것은 루트 리포트뿐이다. PDB 리포트는 PDB가 여럿일 때 '누구 몫인가'를 가르는 데 쓴다. (00_자료준비/02에 "16·17화 AWR은 CDB$ROOT에 그대로 있어야 한다"고 적어 둔 이유가 이것이다.)
3. AWR SOP 9단계와 읽는 순서 9칸
3.1 SOP 9단계 (16화 제정 · 스크립트)
| # | 단계 | 스크립트 | 빠뜨리면 |
|---|---|---|---|
| 1 | 어디서 볼지 정한다: CDB$ROOT · 팩 · 스냅샷이 쌓이나 | 01 | 리포트가 텅 비거나 칸이 빠진다(§2) |
| 2 | 간격 · 보존을 확인한다: 바꾸면 끝에서 되돌린다 | 01 [5] | 다음 사람의 AWR이 망가진다 |
| 3 | 부하를 정한다: T7 · V$RESOURCE_LIMIT 여유 | 00 · 01 [4] · T7 | 리포트가 무엇의 사진인지 모른다 |
| 4 | 부하를 걸고 데운다(2분) | 발사기 | 출발이 섞인다 |
| 5 | S1: 루트 · PDB 하나씩 | 02 | — |
| 6 | 측정 구간: 정상 상태 · 사건은 구간 안에 | 02(보정 배치 +4분) | 사건이 구간 밖으로 샌다 |
| 7 | S2: 부하가 아직 도는 동안 | 02 | 꼬리(세션 종료)가 섞인다 |
| 8 | 리포트를 뽑아 9칸을 정해진 순서로 읽는다 | 03(리포트) · 04(같은 뺄셈을 직접) | 칸 하나로 결론을 낸다 |
| 9 | 적고 되돌린다: T5 · 원복 · 남긴 것 | T5 · 99 | — |
@?/rdbms/admin/awrrpt.sql, awrddrpt.sql, awrsqrpt.sql은 대화형이다(리포트 종류, 구간, 스냅샷 번호를 묻는다). 이 화에서는 같은 일을 DBMS_WORKLOAD_REPOSITORY의 세 함수 AWR_REPORT_TEXT, AWR_DIFF_REPORT_TEXT, AWR_SQL_REPORT_TEXT로 한다. 스냅샷 번호는 런의 시작과 끝 시각(T_LD_RUN) 사이에 찍힌 루트 수동 스냅샷을 스크립트가 스스로 찾는다.
3.2 읽는 순서 9칸
| # | 칸 | 묻는 것 | 결론에 쓰나 |
|---|---|---|---|
| ① | 머리: Elapsed · DB Time | 얼마나 바빴나(평균 활성 세션) | ○ |
| ② | Load Profile | 초당 무슨 일을 얼마나 | ○ |
| ③ | Instance Efficiency | (비율) | ✗: 옆 칸의 분자 · 분모와 함께만 |
| ④ | Top 10 Foreground Events | 시간이 어디로 갔나 | ○ |
| ⑤ | Host CPU · Instance CPU | CPU는 누구 것이고, 기다림은 몇 %인가 | ○ |
| ⑥ | Time Model | DB Time을 일의 종류로(겹친다) | △: 다른 칸과 맞을 때만 |
| ⑦ | SQL ordered by: Elapsed · CPU · Gets · Executions | 누가(실행당 값과 실행 수를 나눠) | ○: 네 목록을 나란히 |
| ⑧ | Segments by Logical Reads | 어디서 | ○ |
| ⑨ | 대조: 아는 것과 맞춰 본다 | 리포트가 부하를 맞게 봤나 | 맞지 않는 줄을 적는다 |
순서에는 이유가 있다. 양(①②) → 비율(③, 읽고 넘어간다) → 시간이 간 곳(④⑤⑥) → 누가 · 어디서(⑦⑧) → 확인(⑨). 위에서 아래로 갈수록 좁아진다. 1위 SQL(⑦)부터 읽으면 '그 시간이 CPU였나 기다림이었나'를 모른 채 범인을 고르게 된다.
리포트 맨 위의 Top ADDM Findings(23ai 리포트에 붙어 나온다)는 9칸에 넣지 않았다. ADDM은 17화에서 다룬다. 권고는 가설이고, 그 가설이 기댄 칸은 이 9칸으로 먼저 대조한다.
4. 머리 — DB Time과 평균 활성 세션
구간 A(실습/03 · 04 [1]) | 값 |
|---|---|
| Elapsed | 9.00분(539.9초) |
| DB Time | 28.08분(1,685.0초) |
| 평균 활성 세션(Average Active Sessions) = DB Time ÷ Elapsed | 3.12 |
| DB CPU | 1,086.4초 = 2.01초/초 |
cpu_count | 2 |
- DB Time = 포그라운드 세션이 DB 안에서 보낸 시간의 합(CPU + Idle이 아닌 대기). 백그라운드 프로세스는 넣지 않는다.
- 평균 활성 세션 3.12 > CPU 2 → 매 순간 1명 남짓이 CPU를 기다렸다(§7의 25.1%와 같은 이야기다).
- 3세션인데 3을 조금 넘는 이유: 같은 구간의 PDB 리포트는 DB Time 27.30분(평균 3.03명)이다(§2.3). 나머지 0.78분은 루트의 포그라운드(스냅샷을 찍는 SYS 세션 같은 것) 몫이다. 0.03은 보정 배치 세션 등이다.
5. Instance Efficiency — 비율로는 일의 양을 알 수 없다
5.1 식 (원천 I권 03장 05절 p.194 ~ 198 · 04 [3]으로 같은 값을 다시 계산했다)
| 비율 | 식(DBA_HIST_SYSSTAT 델타) | 구간 A | 세지 않는 것 |
|---|---|---|---|
| Buffer Hit % | 100 × (1 − (physical reads − physical reads direct − physical reads direct (lob)) ÷ session logical reads) | 99.99 | 쓰기 전부 · 캐시를 건너뛴 direct path · 읽은 양 |
| Soft Parse % | 100 × (1 − parse count (hard) ÷ parse count (total)) | 96.17 | 파스 횟수 자체 |
| Execute to Parse % | 100 × (1 − parse count (total) ÷ execute count) | 73.05 | 실행이 필요했는가 |
| % Non-Parse CPU | 100 × (1 − parse time cpu ÷ CPU used by this session) | 99.10 | CPU를 얼마나 썼나 |
| Library Hit % | 100 × Σ pinhits ÷ Σ pins (DBA_HIST_LIBRARYCACHE) | 98.05 | |
| Latch Hit % | 100 × (1 − Σ misses ÷ Σ gets) (DBA_HIST_LATCH) | 99.81 | 래치를 몇 번 잡았나(← 07화 100만 번) |
5.2 옆에 둘 것 — 분자와 분모 (구간 A · 초당)
| 값 | |
|---|---|
| session logical reads | 272,105.0 |
| physical reads cache | 38.2 |
| physical writes (전체) | 3,475.2 |
| physical writes direct | 3,253.0 |
Buffer Hit가 99.99%인데도 초당 3,475블록을 썼다. 그 쓰기를 기다린 시간이 DB Time의 11.1%다(§6).
5.3 ☠️ 고쳤더니 '효율'이 떨어진다 (구간 A → B · 04 [3])
| A (보정 rev00) | B (보정 rev01) | |
|---|---|---|
| Execute to Parse % | 73.05 | 63.28 |
| Library Hit % | 98.05 | 97.65 |
| Executes / 초 | 2,112.6 | 1,550.9 |
| Parses / 초 | 569.3 | 569.5 |
rev00은 파스 없이 30만 번을 더 실행했다. 쓸데없는 실행이 분모(execute count)를 키워 비율을 올렸다. rev01이 그 낭비를 없애자 비율이 10포인트 떨어졌다. 07화의 히트율과 같은 구조다. 낭비할수록 올라가는 비율이다.
T5 ③ 칸의 주석(필수): 비율은 결론에 쓰지 않는다. 분자와 분모의 초당 값을 옆에 적는다. 히트율은 쓰기와 direct path를 세지 않는다. 낭비가 줄면 비율이 떨어질 수 있다.
6. Top Foreground Events — 시간이 간 곳
6.1 구간 A (실습/03 · 04 [4])
| Event | Waits | Time (s) | Avg | % DB time | 무엇을 기다렸나 |
|---|---|---|---|---|---|
| DB CPU | 1,086.4 | 64.5 | (기다림이 아니라 일) | ||
resmgr:cpu quantum | 1,633 | 404.1 | 247.43ms | 24.0 | CPU 차례(§7) |
enq: RO - fast object reuse | 6,255 | 110.6 | 17.68ms | 6.6 | TRUNCATE가 그 객체의 블록을 캐시에서 내보내기를 |
local write wait | 10,053 | 54.5 | 5.42ms | 3.2 | 그 블록들이 디스크에 써지기를 |
direct path sync | 3,351 | 21.7 | 6.48ms | 1.3 | APPEND로 캐시를 건너뛰고 쓴 블록이 디스크에 닿기를 |
셋째~다섯째 줄(합 11.1%)은 모두 부하 발생기의 P1 rev02(14화, 세션마다 TRUNCATE + INSERT /*+ APPEND */ 10만 건)에서 온다. 이 세 이벤트는 쓰기 쪽 I/O 대기이고, Buffer Hit %는 셋 다 세지 않는다.
⚠️ 모델의 왜곡: 운영에서 P1은 밤에 한 번 돈다. 부하 발생기(가중치 1%)는 9분 동안 3,354번 돌렸다. 이 11.1%는 모델의 사실이지 운영의 사실이 아니다(T7 9번 칸).
6.2 대기 클래스 (구간 A · 리포트 'Wait Classes')
| 클래스 | Time (s) | % DB time |
|---|---|---|
| DB CPU | 1,086 | 64.5 |
| Scheduler | 422 | 25.1 |
| System I/O(백그라운드 · DB Time에 안 든다) | 272 | 16.1 |
| Application | 111 | 6.6 |
| User I/O | 79 | 4.7 |
System I/O는 DBWn과 LGWR의 대기다. % DB time이 붙어 나오지만 DB Time에는 포함되지 않으므로, 더하면 100을 넘는다.
7. Host CPU · Instance CPU — CPU는 누구 것인가
7.1 구간 A (04 [5])
| 칸 | 값 | 뜻 |
|---|---|---|
Host CPUs(NUM_CPUS) | 32 | 컨테이너가 도는 기계의 CPU |
| Host %Busy | 7.5 | 기계 전체는 92.5% 놀았다 |
cpu_count | 2 | 이 DB가 쓸 수 있는 CPU(Free의 한계) |
| DB CPU / 초 | 2.01 | 이 DB는 자기 몫 2개를 꽉 썼다 |
| % of total CPU for Instance | 6.4 | 기계 전체 CPU 중 이 인스턴스 몫(≈ 2 ÷ 32) |
| % of busy CPU for Instance | 84.5 | 기계가 바빴던 시간 중 이 인스턴스 몫 |
| %DB time waiting for CPU (Resource Mgr) | 25.1 | CPU 차례를 기다린 시간 ÷ DB Time(Scheduler 대기 클래스, 포그라운드만 세면 24.0) |
7.2 "CPU 100%니 CPU를 늘리자"
- "100%"는 어느 숫자인가: 이 기계에서 DB가 쓸 수 있는 몫은 100%였고 기계 전체는 7.5%였다. 대시보드의 CPU가 어느 쪽인지 먼저 묻는다.
- 늘리면 줄어드는 것: CPU를 기다린 25.1%다. 나머지 64.5%는 일 자체라 CPU를 늘려도 그대로다. 일을 줄이면 둘 다 준다.
- 일의 절반은 누구 것인가: ⑦ SQL ordered by CPU의 1위 Q2 rev01이 DB CPU의 47.5%를 쓴다(
실습/06'CPU Time % Snap Total').
Free에서는 이것이 안 되므로 이렇게 대신한다.
cpu_count를 늘려 '늘린 뒤'를 재 볼 수 없다(Free는 CPU 2개가 상한이다). 대신 '늘리면 줄어드는 최대치'를 Scheduler 대기의 % DB time으로 적는다. 코어를 늘린 서버에서의 실측은 별권(대규모 EE 환경)에서 다룬다.
7.3 resmgr:cpu quantum은 무엇인가
Free는 리소스 매니저로 CPU를 2개에 묶는다. 세 번째 세션이 CPU를 원하면 차례가 올 때까지 이 이벤트로 기다린다(← 07화 실습/03의 동시 4세션에서 처음 봤다). 방재실 비유로 말하면, 계약 전력을 넘을 때 수요 제어기가 에어컨을 번갈아 끄는 것과 같다. 에어컨이 고장 난 게 아니라 차례를 기다리는 것이다.
8. Time Model — 겹치는 줄과 맞지 않는 줄
8.1 구간 A (04 [6] · 리포트 'Time Model Statistics')
| Statistic | Time (s) | % DB Time |
|---|---|---|
| sql execute elapsed time | 1,683.3 | 99.9 |
| sequence load elapsed time | 1,561.0 | 92.6 |
| DB CPU | 1,086.4 | 64.5 |
| PL/SQL execution elapsed time | 65.9 | 3.9 |
| parse time elapsed | 21.2 | 1.3 |
| hard parse elapsed time | 18.4 | 1.1 |
| DB time | 1,685.0 | |
| background elapsed time (DB Time 밖) | 302.7 |
- 줄끼리 겹친다. SQL을 실행하는 동안 CPU도 쓰고, 그 안에서 파스도 한다. %를 더하지 않는다.
8.2 ☠️ 맞지 않는 줄 — sequence load elapsed time
| 같은 구간의 다른 칸 | 값 |
|---|---|
Time Model sequence load elapsed time | 1,561.0초(DB Time의 92.6%) |
그 시퀀스(SQ_STLM_LD)를 쓰는 유일한 SQL인 N1 rev01의 INSERT … SQ_STLM_LD.NEXTVAL의 Elapsed(⑦) | 6.6초(46,076번) |
부하 발생기가 잰 N1 rev01의 시간(런 12분 전체 · V_LD_PIECE) | 11.4초 |
| DB Time − 이 줄 | 124초. 그런데 Q2 하나의 Elapsed가 663.6초다 |
- 이 줄이 맞다면 시퀀스를 쓰는 INSERT의 Elapsed가 1,561초 이상이어야 한다. 실제는 6.6초다. 같은 리포트의 두 칸이 230배 어긋난다.
- 구간 B에서도 1,398.9초였고, 두 번째로 돌린 판에서도 같은 모양이었다(§15). 생각 시간을 둔 런이 든 구간, 곧 15화 시운전(생각 시간 200ms)이 든 1시간 반짜리 자동 스냅샷 구간에서는 0.0초였다.
- 왜 이렇게 세어지는지는 이 화에서 밝히지 못했다. 확인한 것은 '다른 칸과 맞지 않는다'는 데까지다.
- 리포트 맨 위 ADDM 요약의 1위가 이 줄에서 나온다. 1위는 Sequence Usage · Avg act 3.12 · 92.64%, 권고는 "Use a larger cache size for those sequences."다 → 17화(권고를 그대로 적용하기 전에 무엇으로 검증하나).
규칙: 다른 칸과 맞지 않는 칸은 적어 두고, 그 칸으로는 결론을 내지 않는다. T5 작성 규칙에 넣었다.
9. SQL ordered by — 1위는 넷이다
9.1 네 목록의 1위 (구간 A · 실습/03 · 04 [7])
| 관점 | 1위 | 값 | 실행 | 실행당 | 2위 이하에서 눈여겨볼 것 |
|---|---|---|---|---|---|
| Elapsed | Q2 rev01 | 663.6초(39.4%) | 16,525 | 40.2ms | 2~4위 BEGIN PKG_LOAD_DRIVER.RUN_SESSION(n): 실행 0 · 540.5초 · 32.1%씩 |
| CPU | Q2 rev01 | 516.1초(DB CPU의 47.5%) | 16,525 | 31.2ms | 5위 Q3 rev01 288.4초 |
| Gets | Q2 rev01 | 96,414,870(65.6%) | 16,525 | 5,834.5블록 | 실행당 값은 바닥(← 07화 5,869) |
| Executions | 보정 배치 rev00의 INSERT INTO T_STLM_ADJ_C16 | 99,870 | Rows per Exec 1.0 | 2위 Q1 rev03 98,955 · 3위 Q4 rev03(다음 페이지) 78,894 · 4위 update seg$ 48,737. 둘째 판에서는 Q1 100,939가 1위, INSERT가 2위(§15) |
- "1위가 범인"은 네 목록 중 하나만 본 말이다. Q2는 세 목록에서 1위지만 실행당 5,834블록으로 이미 바닥이다(06화 175배). 1위인 이유는 9분에 16,525번 호출돼서다. 물어야 할 것은 '이 SQL을 누가 이렇게 자주 부르나'다.
- Executions 1위는 한 번에 0.012ms짜리다. 실행 수 1위는 대개 싼 문장이다.
9.2 실행 0인데 32% — PL/SQL 호출
리포트 머리말에 이런 줄이 있다. Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. 부하 발생기의 RUN_SESSION은 12분짜리 호출 하나다. 구간 동안 끝나지 않았으니 실행 수는 0이고, 그 안에서 부른 SQL의 시간은 전부 자기 몫으로 쌓였다. 그래서 이렇게 나온다.
-> Captured SQL account for 77.4% of Total DB Time
-> Captured PL/SQL account for 100.6% of Total DB Time운영에서는 이 자리에 배치 패키지나 화면의 PL/SQL 호출이 온다. 그것을 1위로 들고 가면 '그 패키지가 범인'이 되는데, 그 안의 무엇이 범인인지는 아무것도 알 수 없다.
9.3 ☠️ 목록에 없다고 안 돈 게 아니다 — 뽑히는 기준
같은 구간에 보정 배치 rev00은 정산 한 건에 세 문장을 불렀다(15화: 등급 조회 100,020 · COUNT(*) 99,870 · INSERT 99,870). 목록에는 INSERT 하나만 있었다. DBA_HIST_SQLTEXT에도 나머지 둘은 없다(실측).
SQL ordered by Executions
-> Total Executions: 1,140,499
-> Captured SQL account for 67.2% of TotalAWR은 스냅샷 때 공유 풀의 SQL을 전부 저장하지 않는다. TOPNSQL(이 DB는 DEFAULT, statistics_level TYPICAL에서는 기준마다 상위 30)만큼 기준별 상위를 고른다. 공식문서에 나온 기준은 Elapsed Time · CPU Time · Parse Calls · Shareable Memory · Version Count다(arpls DBMS_WORKLOAD_REPOSITORY · MODIFY_SNAPSHOT_SETTINGS의 topnsql). topnsql => 'MAXIMUM'이면 커서 캐시의 SQL을 전부 담지만, 그만큼 SYSAUX가 커진다. 실행 횟수는 줄 세우는 기준이지 뽑는 기준이 아니다. 한 번에 수 µs짜리를 10만 번 부른 문장은 경과 시간 상위에 들지 못해 저장되지 않는다.
그래서 "Executions 목록에 없다"는 "안 돌았다"가 아니다. 목록 머리의 Captured … % 값을 같이 적는다.
9.4 같은 일이 SQL_ID 여럿으로 쪼개지면
| 조각 | SQL_ID 수 | 이유 |
|---|---|---|
| Q4 rev03 | 3 | 첫 페이지 · 다음 페이지 · 날짜로 가기: 다른 문장 셋 |
| P1 rev02 | 3(+ TRUNCATE 3) | 세션마다 다른 작업 테이블(T_STLM_S1_LD …). 테이블 이름이 문장에 들어 있다 |
P1은 DB Time의 상당 부분(§6의 11.1% + CPU)을 쓰지만, 문장마다 4% 안팎이라 Elapsed 목록 6위 아래로 흩어진다. 쪼개진 일은 순위에서 사라진다. 11화의 리터럴 SQL과 같은 원리다(← 11화).
9.5 SQL 하나의 리포트 (AWR_SQL_REPORT_TEXT · 실습/06)
| 칸 (Q2 rev01 · 구간 A) | 값 | % Snap Total |
|---|---|---|
| Elapsed Time | 663,575ms | 39.4 |
| CPU Time | 516,105ms | 47.5 |
| Executions | 16,525 | 1.4 |
| Buffer Gets | 96,414,870(실행당 5,834.5) | 65.6 |
| Disk Reads | 0 | 0.0 |
| Plan Hash | 77778225 |
실행계획에는 옵티마이저가 만든 뷰 VIEW VW_GBC_5(Group-by Placement, 먼저 묶기)가 있다(← 08화, §13).
10. Segments by Logical Reads와 대조
10.1 Segments by Logical Reads (구간 A · 04 [8])
| 세그먼트 | 종류 | Logical Reads | % of Total |
|---|---|---|---|
| SQLP.T_ORD_BIG | TABLE | 97,747,248 | 66.5 |
| SQLP.T_ORD_DET | TABLE | 26,699,632 | 18.2 |
| SQLP.IX_ORD_BIG_DT_AMT | INDEX | 9,111,776 | 6.2 |
| SQLP.T_ORD | TABLE | 2,775,232 | 1.9 |
| SQLP.PK_T_ITM_EVL | INDEX | 1,058,832 | 0.7 |
session logical reads 146,897,529의 3분의 2가 T_ORD_BIG이다. Q2의 96,414,870이 거의 그대로 여기에 있다. ⑦과 ⑧이 같은 이야기를 한다.
10.2 ⑨ 대조 — 부하 발생기가 센 것 vs AWR이 센 것 (구간 A · 04 [9])
부하 발생기 T_LD_LOG의 분 2~10(런 +2:00 ~ +11:00 = S1 ~ S2)과 DBA_HIST_SQLSTAT의 실행 수를 ACTION(드라이버가 조각마다 붙인 이름)으로 묶어 맞춘다. SQLP가 파싱한 SELECT와 INSERT만 센다.
| 조각 | 부하 발생기 | AWR | SQL_ID | 판정 |
|---|---|---|---|---|
| N1 rev01 | 46,121 | 46,076 | 1 | 맞다(구간 경계에 걸친 판) |
| P1 rev02 | 3,354 | 2,131 | 3 | 1,223이 빈다 |
| Q1 rev03 | 99,103 | 98,955 | 1 | 맞다 |
| Q2 rev01 | 16,544 | 16,525 | 1 | 맞다 |
| Q3 rev01 | 33,115 | 33,080 | 1 | 맞다 |
| Q4 rev03 | 131,801 | 131,644 | 3 | 맞다 |
P1만 빈다. 공유 풀의 P1 INSERT 커서는 실행 수에 비해 다시 올라온 횟수가 많다. 구간 A 직후 INSERT /*+ APPEND */ INTO T_STLM_S1_LD …는 executions 67 · loads 2,058 · invalidations 2,057이었고, 구간 H까지 돈 뒤에는 세 문장이 각각 loads 10,456 · 10,574 · 10,532였다(invalidations도 거의 같은 수, executions 972 · 780 · 126). 14화의 TRUNCATE가 그 테이블을 쓰는 커서를 매번 무효화하고, 무효가 된 커서의 실행 수는 스냅샷 때 다 모이지 않는다. 맞지 않는 줄의 이유는 리포트 밖에 있다.
10.3 부하 위의 보정 배치 (02 [6] · T_ADJ_LOG_C16)
| 혼자(15화) | 부하 위(16화 구간 A · B) | |
|---|---|---|
| rev00 경과 | 214~224cs | 232~252cs(CPU 139~141) |
| rev01 경과 | 27~28cs | 30~38cs(CPU 14~15) |
| rev00 ÷ rev01 | 약 8 | 6~8 |
| recursive calls | 301,185 / 2,457 | 301,547 ~ 301,589 / 2,722 ~ 2,913 |
| 보정 / 반송 | 99,850 / 0(+170 사라짐) · 99,850 / 170 | 99,850 / 0 · 99,850 / 170 |
혼자일 때의 '8배'는 부하 위에서 '6~8배'로 흔들렸다. 운영에서 몇 분 걸리는지는 이 표로 정하지 않는다. 운영 로그(오과장)로는 rev01이 6/17 10:00 → 10:07, 6/24 10:00 → 10:06이다.
11. 두 구간 비교 — awrddrpt
AWR_DIFF_REPORT_TEXT(dbid1, inst1, bid1, eid1, dbid2, inst2, bid2, eid2)로 뽑는다(실습/05). 구간 A와 B는 같은 부하(STEADY · 3세션 · 생각 시간 0 · 씨앗 15)이고, 다른 것은 +4분에 얹은 보정 배치의 판 하나다.
| Workload Comparison (초당) | 1st (A · rev00) | 2nd (B · rev01) | %Diff | 읽기 |
|---|---|---|---|---|
| DB time (분, 구간 전체) | 28.1 | 28.2 | 0.4 | 시스템 전체의 시간은 그대로 |
| CPU time | 2.0 | 2.0 | 0.0 | |
| Logical read (blocks) | 272,105.0 | 270,705.7 | −0.5 | 같은 부하(바뀌지 않아야 할 것) |
| Physical write (blocks) | 3,475.2 | 3,472.1 | −0.1 | 〃 |
| Executes (SQL) | 2,112.6 | 1,550.9 | −26.6 | 바뀐 것의 자국: 9분에 30만 번(15화의 그 30만 번) |
| Redo size (bytes) | 773,747.1 | 681,451.4 | −11.9 | 〃 |
| Block changes | 6,483.4 | 5,710.4 | −11.9 | 〃 |
| Parses (SQL) | 569.3 | 569.5 | 0.0 | 파스는 그대로 → Execute to Parse %가 떨어진다(§5.3) |
| Instance Activity (구간 전체) | A | B | %Diff |
|---|---|---|---|
| execute count | 1,140,499 | 837,271 | −26.6 |
| recursive calls | 2,386,640 | 2,084,275 | −12.7 |
읽는 법: %Diff가 큰 줄은 '바뀐 것'의 자국이고, 0 근처의 줄은 '같은 부하였다'는 증거다. 둘 다 있어야 비교가 성립한다. 같은 부하가 아니면(로지컬 리드가 크게 다르면) 비교 리포트로는 아무것도 증명할 수 없다.
판정: 보정 배치 자체는 6~8배(232~252 → 30~38cs) 빨라졌다. 그러나 9분 구간의 DB Time 1,680~1,690초 가운데 2초 남짓이었다. 비교 리포트의 DB time %Diff는 두 판에서 +0.4와 −0.2였다. 운영 전체가 느린 원인은 보정 배치에 있지 않았다.
12. DBA_HIST_* 직접 조회 — 칸마다 어느 뷰인가
리포트의 숫자는 아래 뷰들의 snap_id = S2 값 − snap_id = S1 값이다(…_DELTA 칸이 있는 뷰는 S1 < snap_id ≤ S2의 합). 실습/04가 리포트(03)와 같은 값을 직접 꺼내고, 두 값이 같은 것을 확인했다(Buffer Hit 99.99 · Soft Parse 96.17 · Execute to Parse 73.05 · Library Hit 98.05 · Latch Hit 99.81 · DB Time 1,685.0 · logical reads 146,897,529).
| 칸 | 뷰 | 키 | 델타 |
|---|---|---|---|
| ① DB Time · DB CPU | DBA_HIST_SYS_TIME_MODEL | stat_name | S2 − S1 |
| ② Load Profile · ③ 비율 | DBA_HIST_SYSSTAT | stat_name | S2 − S1 |
| ③ Library Hit | DBA_HIST_LIBRARYCACHE | namespace | S2 − S1 |
| ③ Latch Hit | DBA_HIST_LATCH | latch_hash | S2 − S1 |
| ④ 대기 이벤트 | DBA_HIST_SYSTEM_EVENT | event_id · 포그라운드는 …_FG 칸 | S2 − S1 |
| ⑤ Host CPU | DBA_HIST_OSSTAT | stat_name(BUSY_TIME · IDLE_TIME은 1/100초) | S2 − S1 |
| ⑦ SQL | DBA_HIST_SQLSTAT(+ DBA_HIST_SQLTEXT) | sql_id · module · action · parsing_schema_name | …_DELTA의 합 |
| ⑧ 세그먼트 | DBA_HIST_SEG_STAT(+ DBA_HIST_SEG_STAT_OBJ) | obj# · dataobj# · con_dbid | …_DELTA의 합 |
| 구간 | DBA_HIST_SNAPSHOT | dbid · snap_id · snap_flag · startup_time | — |
sql
-- 모양 하나 (② 의 한 줄) — 구간 S1 · S2 의 초당 logical reads
SELECT (e.value - b.value) /
((CAST(se.end_interval_time AS DATE) - CAST(sb.end_interval_time AS DATE)) * 86400) AS per_sec
FROM dba_hist_sysstat b JOIN dba_hist_sysstat e ON e.dbid = b.dbid AND e.stat_id = b.stat_id
JOIN dba_hist_snapshot sb ON sb.dbid = b.dbid AND sb.snap_id = b.snap_id
JOIN dba_hist_snapshot se ON se.dbid = e.dbid AND se.snap_id = e.snap_id
WHERE b.dbid = :dbid AND b.snap_id = :s1 AND e.snap_id = :s2 AND b.stat_name = 'session logical reads';DBA_HIST_SQLSTAT.ACTION과MODULE은 그 커서가 처음 파싱될 때의 값이다. 부하 발생기가 조각마다SET_MODULE('LOAD_DRIVER', 'Q2 rev01')을 붙인 덕분에 SQL을 조각 이름으로 묶을 수 있었다(⑨). 운영에서도 화면과 배치가 MODULE과 ACTION을 붙이게 하면 같은 일을 할 수 있다(← 15화 · → 17화).
13. 부하 모델의 Q2는 운영의 Q2인가
13.1 발견 — 실행계획에 VW_GBC_5가 있다 (실습/06)
구간 A의 SQL 리포트(§9.5)에 나온 Q2의 실행계획은 HASH JOIN 아래에 VIEW VW_GBC_5 → HASH GROUP BY → TABLE ACCESS FULL T_ORD_BIG였다. 옵티마이저가 주문을 고객별로 먼저 묶은 것이다(Group-by Placement, ← 08화 §2). 08화에서 운영 Q2 rev01은 지역명 스칼라 서브쿼리 때문에 이 변환이 막혔었다(GBP: Bypassed: Unsupported argument to aggregate function).
리포트에 나온 SQL 문장은 SELECT C.CUS_GD, COUNT(*), SUM(O.ORD_AMT) FROM T_ORD_BIG O, M_CUS C WHERE C.CUS_ID = O.CUS_ID GROUP BY C.CUS_GD다. 지역명 스칼라가 없다. 부하 발생기(15화)의 q2_rev01은 운영 Q2 rev01(06화 리비전/Q2_rev01.sql)에서 MAX((SELECT r.RGN_NM …)) 한 줄을 뺀 문장이다.
13.2 세 문장을 나란히 (실습/09 · 부하 없이 · 예열 2번 뒤 10번씩)
| 문장 | Buffers / 회 | CPU(10번 합 · cs) | 실행계획 해시 | 실행계획 |
|---|---|---|---|---|
| 1 부하 발생기의 Q2(스칼라 없음) | 5,856 | 29~30 | 77778225 | VIEW VW_GBC_5(먼저 묶기) |
| 2 운영 Q2 rev01(스칼라 있음) | 5,868 | 113~116 | 2292855265(← 06화와 같은 해시) | HASH JOIN → HASH GROUP BY |
| 3 08화 보류안: 먼저 묶고 붙이기(스칼라 유지) | 5,868 | 29~32 |
- 1과 2의 등급, 건수, 금액은 양방향
MINUS0 / 0으로 같다. 다른 것은 지역명 칸 하나다. - 블록은 셋이 같다. CPU는 2가 1의 3.9배다. 그리고 1은 3(보류안)과 같은 값이다. 부하 모델의 Q2는 보류안이 이미 적용된 Q2와 같다.
- 08화 실측(rev01 114~118 · 보류안 30~34)과 같은 수준이다.
13.3 08화 재검토 조건의 판정
| 조건 | 측정 | 판정 |
|---|---|---|
| "16화에서 시스템 전체를 잴 때 이 SQL이 CPU 상위에 오르면 rev02로 올린다" | 부하 모델의 Q2가 구간 A의 SQL ordered by CPU 1위(DB CPU의 47.5%) | 충족 |
| 그런데 모델의 Q2는 운영 문장이 아니다 | 운영 rev01은 실행당 CPU가 모델의 3.9배 | 운영 문장이었다면 몫은 더 크다. 조건은 더 단단하게 충족 |
| 이 화에서 rev02로 올리나 | 이 화는 진단만 한다 · 운영 문장으로 부하를 다시 만든 판이 없다 | 올리지 않는다. T1 절차(정합성 체크리스트 포함)로 → 18화 |
T7 9번 칸 「운영과 다른 점」의 첫 줄: 부하 발생기의 Q2에는 운영 Q2 rev01의 지역명 스칼라 서브쿼리가 없다. 그래서 실행계획이 다르고(
VW_GBC), 실행당 CPU가 운영의 약 1/4이다. 이 모델의 리포트에서 Q2의 CPU 몫은 운영보다 작게 나온다. 부하 발생기를 고치지 않은 이유: 15화가 만든 환경 폴더의 물건이고, 17화가 같은 인자로 같은 런을 다시 만든다. 바꾸면 그 런은 더 이상 같은 런이 아니다. 바꿀지는 18화에서 표준을 제정할 때 정한다.
14. 한 시간 구간 H — 리포트 한 장의 결론
선택 실습. 17화에서 같은 인자로 같은 런을 다시 만들어 연다. 이 화에서는 AWR 1시간 리포트 한 장만 보고 결론을 적는다.
14.1 인자 (고정 · T7 WP-16H)
bash
PROFILE=SPIKE MINUTES=62 SESSIONS=3 THINK_MS=0 SPIKE_AT=31 SPIKE_LEN=5 SPIKE_KIND=LOCK HOLD_SEC=10 SEED=15 NOTE=C16-H ./05_부하_돌리기.shpowershell
.\05_부하_돌리기.ps1 -LoadProfile SPIKE -Minutes 62 -Sessions 3 -ThinkMs 0 -SpikeAt 31 -SpikeLen 5 -SpikeKind LOCK -HoldSec 10 -Seed 15 -Note C16-H나머지 인자는 기본값이다(WEIGHTS=Q1=30,Q2=5,Q3=10,Q4=40,N1=14,P1=1 · SPIKE_WEIGHTS 없음 · P1_ROWS=100000). 스냅샷은 02가 런 +1:00(S1)과 +61:00(S2)에 루트와 PDB에 하나씩 찍는다(구간 60분).
14.2 9칸 (04 · 03을 C16-H로)
| 칸 | 구간 H(60분 · 루트 스냅샷 S1 · S2 사이에 정각 자동 스냅샷 하나) | 구간 A(견줌) |
|---|---|---|
| ① Elapsed · DB Time · 평균 활성 세션 | 60.00분 · 181.09분 · 3.02 · DB CPU 1.84초/초 | 9.00 · 28.08 · 3.12 · 2.01 |
| ② Load Profile(초당) | logical read 251,864.8 · physical write 3,165.3 · executes 1,415.7 · redo 595,615 | 272,105.0 · 3,475.2 · 2,112.6 · 773,747 |
| ③ Instance Efficiency | Buffer Hit 99.99 · Soft Parse 96.55 · Execute to Parse 63.38 · Library Hit 97.96 · Latch Hit 99.80 | 99.99 · 96.17 · 73.05 · 98.05 · 99.81 |
| ④ Top Foreground Events | DB CPU 61.0 · resmgr:cpu quantum 23.0 · enq: RO 6.0 · enq: TX - row lock contention 5.5%(599.0초 · 853 waits · 평균 702.20ms) · local write wait 2.9 · direct path sync 1.2 | (enq: TX는 상위 10 밖) |
| ⑤ Host CPU · Instance CPU | Host %Busy 7.0 · % of busy CPU for Instance 82.9 · %DB time waiting for CPU 23.8 | 7.5 · 84.5 · 25.1 |
| ⑥ Time Model | sql execute 10,859.8초 · sequence load 9,676.7초(89%): 여전히 맞지 않는 칸 | 1,561.0(92.6%) |
| ⑦ SQL ordered by | Elapsed · CPU · Gets 1위 Q2 rev01(4,000.5초 · 102,398번 · DB Time의 36.8%) · 그 아래 RUN_SESSION(3) 3,602.6 · (2) 3,602.6 · (1) 3,302.6 · Q3 2,308.2 / Executions 1위 Q1 611,986 · FOR UPDATE 문장은 네 목록 어디에도 없다 · Captured SQL 66.5% of DB Time | |
| ⑧ Segments | Logical Reads: T_ORD_BIG 66.8% · T_ORD_DET 18.1% · IX_ORD_BIG_DT_AMT 6.2% / Row Lock Waits: SYS.OPTSTAT_HIST_CONTROL$ 930(87.82%) · SQLP.T_STLM_NUM_LD 129(12.18%) | Row Lock Waits: OPTSTAT_HIST_CONTROL$ 68(100%) |
| ⑨ 대조 | 하지 않는다. 운영처럼 리포트 한 장으로만 읽는다(부하 발생기의 기록과 맞추는 것은 17화) |
⚠️ AWR 텍스트 리포트 뒤쪽에는
Active Session History (ASH) Report와ADDM Reports절이 붙어 나온다(이 버전 실측, 'Top Blocking Sessions'나 'Activity Over Time' 같은 칸이 있다). 16화의 SOP 9칸은 그 절을 읽지 않는다. ASH와 ADDM은 17화에서 다룬다.
14.3 이 리포트 한 장의 결론
16화가 리포트 한 장으로 내린 결론이다. T5-001에 그대로 남기고, 17화에서 다시 연다.
1. 이 한 시간은 구간 A(9분)와 같은 모양이다. CPU 2개에 평균 3.02명 — CPU 를 기다린 몫 23.8 %.
일의 대부분은 Q2 · Q3 (Q2 rev01 이 DB Time 의 36.8 %). P1 의 쓰기 대기가 10 % 남짓 (6.0 + 2.9 + 1.2).
2. 구간 A 에 없던 것은 enq: TX - row lock contention 5.5 % — 853번 · 평균 702 ms.
Segments by Row Lock Waits 로 행 락의 88 % 는 SYS.OPTSTAT_HIST_CONTROL$ (P1 의 APPEND 가 모으는 온라인 통계),
12 % 는 부하 발생기의 채번 작업 테이블. 락을 기다린 SQL 은 SQL 목록에 없다.
3. 판정 — 이 시스템의 문제는 CPU 를 먹는 일의 양이다. 락은 한 시간의 5.5 %, 한 번에 1초가 안 되는 부차적인 기다림.
DB CPU 가 구간 A(2.01)보다 낮은 1.84 인 것도 그 기다림만큼이다.
4. 조민우 씨의 "10시 반 몇 분" 은 이 리포트의 칸으로는 확인할 수 없다 (한 시간이 한 줄).이 화에서 확인하지 않은 것(17화로): 그 853번, 599초가 한 시간 동안 어떻게 흩어져 있었나, 누가 그 행을 쥐고 있었나, RUN_SESSION(1)만 다른 둘보다 300초 짧은 이유는 무엇인가, ⑧의 '88%'는 무엇의 88%인가.
15. 이 화의 측정값 전부
15.1 구간 A · B — 두 번 돌린 값 (첫 판 · 둘째 판)
같은 인자(STEADY · 3세션 · THINK_MS 0 · 씨앗 15)로 00 → 구간 A → 구간 B를 두 번 돌렸다. 판정은 모두 같았다(아래 '판정' 열).
| 칸 | A 첫 판 | A 둘째 판 | B 첫 판 | B 둘째 판 | 판정 |
|---|---|---|---|---|---|
| Elapsed (초) | 539.9 | 540.2 | 539.9 | 540.0 | 9분 |
| DB Time (분) | 28.08 | 28.05 | 28.18 | 28.00 | — |
| 평균 활성 세션 | 3.12 | 3.12 | 3.13 | 3.11 | CPU 2를 넘는다 |
| DB CPU / 초 | 2.01 | 2.01 | 2.01 | 2.01 | = cpu_count |
| Logical read / 초 | 272,105.0 | 279,612.0 | 270,705.7 | 277,890.4 | |
| Physical write / 초 | 3,475.2 | 3,542.6 | 3,472.1 | 3,527.0 | 쓰기가 읽기(약 38)의 90배 |
| Executes / 초 | 2,112.6 | 2,138.8 | 1,550.9 | 1,575.4 | A → B −26.6 · −26.3% |
| Redo / 초 | 773,747 | 785,665 | 681,451 | 692,110 | A → B −11.9 · −11.9% |
| Buffer Hit % | 99.99 | 99.99 | 99.99 | 99.99 | |
| Execute to Parse % | 73.05 | 72.96 | 63.28 | 63.07 | 고쳤더니 떨어진다 |
| Library Hit % | 98.05 | 98.05 | 97.65 | 97.87 | |
| DB CPU % DB time | 64.5 | 64.4 | 64.3 | 64.5 | |
resmgr:cpu quantum % | 24.0 | 24.0 | 24.5 | 24.2 | |
쓰기 I/O 대기 %(enq: RO + local write wait + direct path sync) | 11.1 | 11.2 | 10.7 | 11.1 | 히트율이 안 세는 I/O |
| %DB time waiting for CPU (Scheduler) | 25.1 | 24.7 | 25.3 | 25.1 | 늘려서 줄어드는 최대치 |
| Host %Busy | 7.5 | 7.6 | 7.8 | 7.6 | 호스트는 논다 |
| sequence load elapsed time(초 · %) | 1,561.0 · 92.6 | 1,460.1 · 86.7 | 1,398.9 · 82.7 | 1,187.8 · 70.7 | 맞지 않는 칸(N1 INSERT 4.8~7.2초) |
| Q2 rev01 Elapsed(초) · 실행 | 663.6 · 16,525 | 647.2 · 16,844 | 652.5 · 16,523 | 648.2 · 16,798 | Elapsed · CPU · Gets 1위 |
| Q2 실행당 Gets | 5,834.5 | 5,839.6 | 5,835.7 | 5,828.5 | 바닥 |
Q2 CPU % Snap Total(06) | 47.5 | 47.4 | DB CPU의 절반 가까이 | ||
| Executions 1위 | 보정 INSERT 99,870(Q1 98,955) | Q1 100,939(보정 INSERT 99,870) | Q1 99,021 | Q1 100,641 | 1위는 둘 중 하나. 둘 다 Rows per Exec 1.0 |
| Captured SQL: Executions | 67.2% | 보정 배치의 등급 조회 · COUNT(*)는 목록 밖 | |||
| P1 부하 기록 vs AWR | 3,354 vs 2,131 | 3,419 vs 2,892 | 3,352 vs 2,211 | 3,408 vs 2,308 | P1만 빈다(나머지 다섯 조각은 ±0.2%) |
보정 배치(02 [6]) | rev00 232cs | rev00 252cs | rev01 38cs | rev01 30cs | 배치 자체는 6~8배 |
| 비교 리포트 DB time %Diff | +0.4 | −0.2 | 시스템 전체는 그대로 | ||
T_ORD_BIG Logical Reads % | 66.5 | 66.0 | 66.9 | 66.2 |
독립 재측정(같은 인자 · 다른 컨테이너 · 1회): A는 Elapsed 540.2초 · DB Time 28.14분 · 평균 활성 세션 3.13 · DB CPU/초 2.01 · Buffer Hit 99.98 ·
resmgr:cpu quantum23.9% · Host %Busy 7.8 · sequence load 1,362.7초(80.7%) · Q2 rev01 651.1초 · 16,863회. B는 DB Time 28.09분 · Host %Busy 7.7 · sequence load 1,442.2초. 판정은 위 표와 모두 같다. 숫자는 이 범위 안팎에서 흔들린다.
- 초당 값은 장비 속도에 따라 달라진다(둘째 판이 2~3% 더 많이 돌았다). 순위, 방향, 비율의 크기는 같다.
- Executions 1위는 9분 동안 Q1이 몇 번 도느냐에 따라 보정 배치의
INSERT(99,870)와 자리를 바꾼다. 두 판에서 한 번씩 그랬다. - P1이 비는 크기(527~1,223)는 판마다 다르다. 커서가 언제 무효가 되고 스냅샷이 언제 찍히느냐에 달렸다. '빈다'는 판정은 네 번 모두 같다.
15.2 구간 H (60분 · 한 번)
| 칸 | 값 |
|---|---|
| 스냅샷 | 루트 S1 · S2(사이에 정각 자동 스냅샷 하나) · PDB도 같은 시각 |
| Elapsed · DB Time · 평균 활성 세션 · DB CPU / 초 | 60.00분 · 181.09분 · 3.02 · 1.84 |
| Load Profile / 초 | logical 251,864.8 · physical read 34.7 · physical write 3,165.3 · redo 595,615.4 · executes 1,415.7 · hard parses 17.9 |
| Instance Efficiency | Buffer Hit 99.99 · Soft Parse 96.55 · Execute to Parse 63.38 · Library Hit 97.96 · Latch Hit 99.80 · % Non-Parse CPU 99.16 |
| Top FG Events | DB CPU 6,632.5초 61.0% · resmgr:cpu quantum 2,501.6초 23.0% · enq: RO 654.7초 6.0% · enq: TX - row lock contention 599.0초 5.5% · 853 waits · 평균 702.20ms · local write wait 319.6초 2.9% · direct path sync 130.1초 1.2% |
| Host · Instance CPU | Host %Busy 7.0 · % of total 5.8 · % of busy 82.9 · %DB time waiting for CPU 23.8 |
| Time Model | sql execute 10,859.8 · PL/SQL 432.1 · parse 113.6 · hard parse 96.3 · sequence load 9,676.7 |
| SQL: Elapsed | Q2 rev01 4,000.5초 · 102,398번(실행당 5,835블록) · RUN_SESSION(3) 3,602.6 · (2) 3,602.6 · (1) 3,302.6 · Q3 rev01 2,308.2 |
| SQL: Executions | Q1 rev03 611,986 · Q4 rev03 489,718 · N1 rev01 284,840 |
| Captured SQL | 66.5% of DB Time · 85.5% of CPU · 94.5% of Gets |
FOR UPDATE 문장 | SQL 목록과 DBA_HIST_SQLSTAT의 구간 H에 없다(리포트 뒤쪽 ASH 절에만 이름이 나온다. 17화) |
| Segments by Logical Reads | T_ORD_BIG 605,463,680(66.8%) · T_ORD_DET 18.1% · IX_ORD_BIG_DT_AMT 6.2% |
| Segments by Row Lock Waits | SYS.OPTSTAT_HIST_CONTROL$ 930(87.82%) · SQLP.T_STLM_NUM_LD 129(12.18%) |
| 리포트 머리 ADDM | Sequence Usage 96.63 · 88.06 · Top SQL Statements 80.95 · 77.15 · Resource Manager CPU Throttling 24.13(자동 스냅샷이 구간 사이에 있어 과제가 둘) |
| 원본 지문 앞 = 뒤 · 남은 세션 · 에러 | 같다 · 0 · 0 |
15.3 그 밖
| 무엇 | 값 | 출처 |
|---|---|---|
| 부하 모델의 Q2(부하 없이 · 10번 · 두 번 돌림) | Buffers 5,856 · CPU 29~30cs · plan 77778225(VW_GBC_5) | 09 [A][B] |
| 운영 Q2 rev01 | Buffers 5,868 · CPU 113~116cs · plan 2292855265 | 〃 |
| 08화 보류안 | Buffers 5,868 · CPU 29~32cs | 〃 |
| 1 vs 2 | MINUS 0 / 0 | 09 [C] |
| PDB 리포트(구간 A) | DB Time 27.30분 · 2,609줄(PDB 스냅샷) · 2,424줄(루트 스냅샷) · 루트 리포트 5,228줄 | 07 [4] |
| 루트 수동 스냅샷 직후 PDB에 생긴 스냅샷 | 0 | 07 [2] |
sqlp의 DBMS_WORKLOAD_REPOSITORY | PLS-00201(PL/SQL) · ORA-00904(SQL) | 01 머리 · 07 [3] |
AWR 저장소 SM/AWR | 85.6MB → 101.1MB(이 화 하루) | 01 [3] · 08 ⑤ |
| SQLP 휴지통 | 22개 · 133.1MB(LOB 하나 130.3MB: 15화 DBMS_HPROF 결과 테이블) | 08 ⑤ |
인덱스 사용(DBA_INDEX_USAGE · 누적) | IX_ORD_BIG_DT 384회 · 마지막 사용은 이 화의 부하보다 앞 · 구간 A의 실행계획 0 / IX_ORD_BIG_DT_AMT 880,972 · IX_ORD_BIG_DT_SEQ 2,697,749 · PK_T_ITM_EVL 287,939 | 08 ⑥ |
V$RESOURCE_LIMIT | processes 84 / 200 · sessions 110 / 322 | 01 [4] |
측정 환경
Oracle AI Database 26ai Free Release 23.26.2.0.0 · cpu_count 2(호스트 32 CPU · 16코어 · 15.41GB) · SGA 1,536MB(버퍼 캐시 800MB · 공유 풀 480MB) · pga_aggregate_target 512M · statistics_level TYPICAL · control_management_pack_access DIAGNOSTIC+TUNING · AWR 1시간 · 8일 · job_queue_processes 1 · NOARCHIVELOG · 온라인 리두 로그 10MB × 2(← 14화). 데이터: 15화 종료 상태(T_ORD_BIG 1,000,000 / 255,398,623,700 · 인덱스 3 · ORD_ST FREQUENCY · T_ORD_STLM 100,000 · T_STLM_NUM 0). 보정 배치의 무대는 C16 복사본이다(정산 100,020 · 고객 1,997).
16. Free에서 못 잰 것 · 봉인한 것
| 못 잰 것 / 하지 않은 것 | 왜 | 대신 한 것 |
|---|---|---|
| CPU를 늘린 뒤의 리포트 | Free는 cpu_count 2가 상한 | 늘려서 줄어드는 최대치 = Scheduler 대기 25.1% · 대규모 EE 환경은 별권 |
| 수십 세션 규모의 부하 | CPU 2 | 3세션 · 생각 시간 0으로 '붐비는 오전' · 부하 발생기는 8세션까지 받는다 |
| 운영의 AWR | 운영 DB에 손대지 않는다 | 연습 DB 재현 · T7의 '운영과 다른 점' |
sequence load elapsed time이 왜 그렇게 세어지는가 | 이 화에서 원인을 밝히지 못했다 | 다른 칸과 맞지 않는다는 것까지 실측(§8.2) |
| 구간 안의 시간 분포 · 누가 누구를 막았나 | AWR 리포트는 구간 평균이다 | → 17화(ASH) |
| ADDM의 Finding · Recommendation | 17화의 몫 | 리포트 머리의 Top ADDM Findings를 적어만 두었다(§8.2) |
| SQL Monitor · 팩 없는 현장(Statspack) | 17화의 몫 | — |
| 기준선(Baseline) · SPM | 18화의 몫 | — |
| Q2 rev02(08화 보류안) 적용 | 이 화는 진단만 한다 | 조건 충족을 기록(§13) → 18화 |
17. 원천 좌표
전체 지도는
00_자료준비/06_원서와_공식문서_지도.md에 있다. PDF 페이지 = 책 페이지 − 2 ·성능고도화I_목차색인.md(공개 사이트에는 없음)
성능고도화 I권 (주 원천 — 03장 오라클 성능 관리)
| 주제 | 책 페이지 |
|---|---|
| 03장 오라클 성능 관리 (장 머리) | 153 |
05절 V$SYSSTAT: Ratio 기반 분석 · Buffer Nowait % · Buffer Hit % · Library Hit % · Execute to Parse %의 식 | 191 · 194 ~ 198 |
06절 V$SYSTEM_EVENT · 대기 이벤트 | 203 |
| 07절 Response Time Analysis 방법론 | 205 |
| 08절 Statspack / AWR · (1) 기본 사용법 · (2) 리포트 분석: Load Profile · Instance Efficiency · Top 5 Timed Events | 210 · 212 · 214 ~ 221 |
| 09절 ASH (→ 17화) | 224 |
| (06장) 버퍼 캐시 히트율 | 441 |
오라클 공식문서
| 주제 | 매뉴얼 | 장 제목 |
|---|---|---|
| AWR 개념 · 스냅샷 · 리포트 · 비교 리포트 · PDB 레벨 AWR | tgdba | Gathering Database Statistics (Automatic Workload Repository) · Comparing Database Performance Over Time |
DBMS_WORKLOAD_REPOSITORY: CREATE_SNAPSHOT · MODIFY_SNAPSHOT_SETTINGS(topnsql) · AWR_REPORT_TEXT · AWR_DIFF_REPORT_TEXT · AWR_SQL_REPORT_TEXT | arpls | DBMS_WORKLOAD_REPOSITORY |
| DB Time · Time Model · 평균 활성 세션 | tgdba | Measuring Database Performance |
AWR_PDB_AUTOFLUSH_ENABLED · CONTROL_MANAGEMENT_PACK_ACCESS · CPU_COUNT | refrn | Initialization Parameters |
DBA_HIST_* · AWR_PDB_* · AWR_ROOT_* · CDB_HIST_* | refrn | Static Data Dictionary Views |
| 진단 · 튜닝 팩 라이선스 (Free / EE) | dblic | Licensing Information |
다음 화가 여기서 가져가는 것
| 화 | 가져가는 것 |
|---|---|
| 17 | 구간 H(§14): 같은 인자로 같은 런을 다시 만들어 시간 축으로 편다 · 16화의 결론(§14.3) · 조민우 씨의 "6/17 10시 반 몇 분" |
| 17 | ADDM 요약 1위 Sequence Usage 92.64와 그 근거 줄(§8.2) · 권고 "larger cache size"를 무엇으로 검증하나 |
| 17 | MODULE · ACTION으로 SQL을 조각에 묶는 법(§12): ASH에도 같은 칸이 있다 |
| 18 | Q2 보류안: 08화 재검토 조건 충족(§13) · T1 절차 · W01 원장 재측정과 함께 |
| 18 | T2-002 IX_ORD_BIG_DT 폐기: 선결 조건 중 'AWR 실행계획 · 인덱스 사용'을 오늘 쟀다(T8 1호 ⑥) · 운영 SQL 전수 조사는 남았다 |
| 18 | T5 · T7 · T8에서 사내 튜닝 표준(T6)에 넣을 조문 후보: 칸 하나로 결론을 내지 않는다 · 비율 옆에 분자 · 분모 · 프로파일 ID 없는 리포트는 무효 |