테마
17화 · 파고들기
필요할 때 찾아보는 문서다. 처음 읽을 때는
1_사건.md만으로 충분하다. 이 문서의 숫자는 모두ora-hanorder(16화 종료 상태)에서 실측한 값이다. 스크립트 번호를 함께 적는다. 같은 한 시간(SPIKE · 62분 · 3세션 ·THINK_MS0 ·SEED15 · LOCK 31분째부터 5분)을 세 번 돌렸다. 16화의 런(C16-H), 이 화의 첫 런, 이 화의 정본 런(둘 다 C17-H)이다. 런 이름은 아래처럼 줄여 쓴다.
줄임 런 무엇 L 16화 C16-H 지난주 한 시간. 이 화에서는 디스크의 10초 표본으로만 본다 R1 17화 C17-H 첫 판 스냅샷 스크립트가 0.5초, 1초마다 깨어나던 판. 대기 루프가 PDB의 DB Time에 섞였다(§15). 부하 발생기 세션만 센 값은 쓴다 R2 17화 C17-H 정본 실습/02최종판으로 독자와 똑같이 돌린 판시각은 두 가지로 적는다. 운영 시계는 16화 T7 WP-16H에서 정한 약속(런 +1:00 = 10:00)을 따르고, DB 시계는 연습 DB의 실제 시각(런을 돌린 때마다 다르다)이다.
| 찾는 것 | 바로 가기 |
|---|---|
| ASH가 무엇을, 얼마나 자주, 어디에 남기나(메모리와 디스크) | §1 녹화기 |
| 표본 수학: 표본 한 장 ≈ DB Time 한 칸 · 실측 대조 | §2 표본 수학 |
V$ACTIVE_SESSION_HISTORY vs DBA_HIST_ACTIVE_SESS_HISTORY: 이 DB의 실측 | §3 메모리 · 디스크 |
| 시간 축으로 자르기: 분 · 5분 창 · 한 칸 · 쿼리 모양 | §4 칸 |
| 16화 결론이 왜 뒤집혔나: 평균 · 칸 · 단위 · 횟수 | §5 뒤집힘 |
| 블로킹 체인의 시계열: 12화의 트리를 과거로 · TX 이름표 · 막은 쪽이 안 찍히는 이유 | §6 블로킹 체인 |
ASH 리포트(ashrpt): 읽는 칸 · l_data_src 함정 · 칸 경계 | §7 ASH 리포트 |
| ADDM을 검증하는 법: Finding · Impact · Recommendation · Rationale | §8 ADDM |
| ADDM 1위 Sequence Usage의 정체 · 권고를 적용한 결과 | §9 Sequence Usage |
AWR '행 락 88%'의 정체: OPTSTAT_HIST_CONTROL$ | §9.5 88% |
| SQL Monitor: 언제 켜지나 · 실측 · Free의 병렬 | §10 SQL Monitor |
팩 없는 현장: CONTROL_MANAGEMENT_PACK_ACCESS = NONE에서 막힌 것 · 남은 것 | §11 NONE |
| Statspack: PDB에만 · 설치 · 스냅샷 · spreport · AWR과 다른 점 | §12 Statspack |
| 진단 도구 4열 대응표(요약) | §13 4열 |
| 측정값 전부 · 세 런 대조 | §14 측정 |
| 관찰자 효과: 대기 루프가 DB Time이 된다 | §15 관찰자 |
| Free에서 못 잰 것 · 봉인한 것 | §16 한계 |
| 원천 좌표 | §17 좌표 |
1. ASH는 무엇을 찍나 — 녹화기
ASH(Active Session History)는 1초에 한 번, 그 순간 활성 상태인 세션(CPU 위에 있거나 Idle이 아닌 대기 중인 세션)의 상태를 한 줄씩 적는다. 쉬는 세션(SQL*Net message from client, PL/SQL lock timer 같은 Idle 대기)은 적지 않는다.
| 방재실 녹화기 | ASH |
|---|---|
| 1초에 한 장 | 1초에 한 번 표본(V$ACTIVE_SESSION_HISTORY.SAMPLE_ID · SAMPLE_TIME) |
| 움직이는 게 있을 때만 찍는다(동작 감지) | 활성 세션만 찍는다. Idle 대기는 표본이 없다 |
| 장마다 찍힌 사람 수를 더하면 사람 × 초 | 표본 수를 더하면 DB Time 초(§2) |
| 원본 하드는 하루쯤 지나면 덮어쓴다 | 메모리의 고리 버퍼(ASH buffers · 이 DB는 4MB). 가득 차면 오래된 표본부터 덮어쓴다 |
| 열 장에 한 장씩 떼어 일주일 넘게 보관 | 디스크로 10개에 1개(DBA_HIST_ACTIVE_SESS_HISTORY) · AWR 보존 기간(이 DB는 8일)만큼 |
| 검침 대장에는 그 시간 엘리베이터 전력이 '평소보다 조금 적은 한 줄'로 남는다 | AWR은 5분 동안 0이었던 것까지 섞어 그 한 시간의 DB CPU를 1.84로 적는다 |
| 엘리베이터 안에는 카메라가 없다. 문을 잡은 사람은 화면에 없다 | 막은 세션이 쉬고 있었으면 표본에 없다. 막힌 쪽 표본의 BLOCKING_SESSION에 이름만 남는다(§6) |
| 줄 선 사람들은 8층 표시등을 쳐다보고 있다 | 기다린 표본마다 BLOCKING_SESSION · BLOCKING_SESSION_SERIAL# · P1 · P2 · P3 |
1.1 한 줄에 무엇이 있나(이 화에서 쓴 칸)
| 칸 | 뜻 | 이 화에서 |
|---|---|---|
SAMPLE_ID · SAMPLE_TIME | 표본 번호 · 시각 | 분 · 5분 칸(§4) · 같은 순간의 트리(§6.3) |
SESSION_ID · SESSION_SERIAL# · SESSION_TYPE | 누구 · 포그라운드 / 백그라운드 | T_LD_SESS와 맞춰 '런의 몇 번 세션'인지 찾는다 |
SESSION_STATE · EVENT · WAIT_CLASS | ON CPU / WAITING · 무엇을 기다리나 | CPU 몫 · enq: TX 몫 |
SEQ# | 그 세션의 대기 번호. 같은 대기가 이어지는 동안은 값이 같다 | 대기 하나하나의 길이(§6.2) |
BLOCKING_SESSION · BLOCKING_SESSION_SERIAL# · BLOCKING_SESSION_STATUS | 지금 나를 막는 세션(바로 앞사람 하나) | 블로킹 체인(§6) |
P1 · P2 · P3 | 대기의 인자. enq: TX면 이름|모드 · USN×65536+SLOT · WRAP | TX 이름표(§6.4 ← 12화 V$LOCK) |
CURRENT_OBJ# | 그 순간 다루던 객체 | T_STLM_NUM_LD. 단, 대기 클래스가 Application · Concurrency · Cluster · User I/O일 때만 믿는다(원천 I권 p.221) |
SQL_ID · TOP_LEVEL_SQL_ID | 그 순간의 문장 · 맨 위 호출 | 9ph8h7j79zbh7(FOR UPDATE) |
MODULE · ACTION · CLIENT_ID | 애플리케이션이 붙인 이름표(← 16화 §12) | LOAD_DRIVER · N1 hotfix · LD22-S2 |
IN_SQL_EXECUTION · IN_PARSE · IN_SEQUENCE_LOAD … | 그 순간 어느 단계였나(깃발) | ADDM 1위의 근거와 대조(§9) |
1.2 권한과 보이는 범위(실측)
sqlp(SELECT ANY DICTIONARY)는 PDB 안에서V$ACTIVE_SESSION_HISTORY와DBA_HIST_ACTIVE_SESS_HISTORY를 읽을 수 있다. 다만 그 PDB의 표본만 보인다. 그래서03,04,05는 sqlp로 돌린다.- PDB 안의
DBA_HIST_ACTIVE_SESS_HISTORY에는 같은 표본이 두 번 들어 있다. 루트 스냅샷 때 흘려보낸 것(DBID= 루트 1514739689)과 PDB 스냅샷 때 흘려보낸 것(DBID= PDB)이다. L의 한 시간에서 각각 1,087행이었다(실습/03을 준비하며 실측). 그래서DBID하나로 거른다(v$database.dbid= 루트 DBID). ASH_REPORT_TEXT와DBMS_ADDM은 SYS로 돌린다(06,07).
2. 표본 수학 — 한 장 ≈ DB Time 한 칸
메모리 표본 1건 ≈ DB Time 1초. 디스크 표본 1건 ≈ DB Time 10초. 평균 활성 세션(AAS) = 표본 수 × 표본 간격 ÷ 구간 초다. 1분 칸이라면 메모리 표본은 60으로 나누고, 디스크 표본은 × 10 ÷ 60, 곧 6으로 나눈다.
2.1 실측 대조 — 디스크 10초 표본(L · S1~S2 60분 · 이 PDB의 포그라운드 · 실습/03 [4])
| ASH 표본 | × 10초 | AWR이 잰 시간(DBA_HIST_CON_*) | |
|---|---|---|---|
| DB time(모든 표본) | 1,050 | 10,500 | 10,504.5 |
DB CPU(ON CPU) | 668 | 6,680 | 6,624.2 |
enq: TX - row lock contention | 60 | 600 | 599.0 |
2.2 실측 대조 — 메모리 1초 표본(실습/04 [4])
| R1 | R2 | |
|---|---|---|
(가) enq: TX: S1~S2 한 시간 | 메모리 600장 · 디스크 60 × 10 = 600 · AWR 598.9초(908 waits) | 메모리 599장 · 디스크 59 × 10 = 590 · AWR 598.8초(1,061 waits) |
| (나) DB time: 메모리가 다 덮은 구간(사이에 낀 자동 스냅샷 ~ S2 · R1 20.2분 · R2 9.1분) | 메모리 3,812장 · AWR 3,811초 · 디스크 383 × 10 = 3,830 | 메모리 1,639장 · AWR 1,643.2초 · 디스크 163 × 10 = 1,630 |
| (나) DB CPU: 같은 구간 | 메모리 2,399장 · AWR 2,420.3초 · 디스크 237 × 10 = 2,370 | 메모리 1,082장 · AWR 1,094.9초 · 디스크 118 × 10 = 1,180 |
읽는 법. 표본은 추정값이다. 1초에 한 번 찍으니 1초보다 짧은 일은 찍히거나 안 찍히거나 둘 중 하나다. 그래도 수천 장을 더하면 계량기와 몇 초 차이 안에서 맞는다. 짧은 일이 많으면 맞고, 드물면 맞지 않는다. 4ms 미만 대기 793번(§9.5)이 표본에 거의 남지 않는 것도 그래서다.
2.3 표본으로 세면 안 되는 것
| 왜 | |
|---|---|
| 대기 횟수 · 실행 횟수 | 표본은 시간을 센다. 1초에 수천 번 도는 문장도 1초에 한 장이다 |
| 1초보다 짧고 드문 사건 | 안 찍힐 수 있다(KSXM 갱신 73µs, §9.5) |
| 쉬던 세션 | 처음부터 안 찍힌다(막은 쪽, §6) |
3. 메모리와 디스크 — 이 DB의 실측
V$ACTIVE_SESSION_HISTORY(메모리) | DBA_HIST_ACTIVE_SESS_HISTORY(디스크 · AWR) | |
|---|---|---|
| 간격 | 1초 | 10초(메모리 표본 10개에 1개) |
| 어디에 | SGA 공유 풀의 ASH buffers: 4,194,304바이트(V$SGASTAT · V$ASH_INFO.TOTAL_SIZE) | SYSAUX(AWR) |
| 얼마나 남나 | 고리 버퍼. 세 세션이 쉬지 않으면 한 시간을 다 못 담는다 · 런이 끝난 순간 가장 오래된 표본은 런 15.3분째(R1) · 13.6분째(R2) | AWR 보존 기간(이 DB는 8일) |
| 언제 디스크로 | 스냅샷 때(또는 버퍼가 2/3까지 찰 때 비상 흘림, AWR_FLUSH_EMERGENCY_COUNT 0) | — |
| 쓰는 때 | 방금 끝난 일 · 1초 해상도가 필요할 때 | 지난 일 · 메모리에서 밀려난 뒤 |
| 이 화 | 04 · 05 · 07 [5] · 08 | 03 · 04의 D · L 열 · 06 [A][B] |
⚠️ "곧바로"인 이유. 이 화를 시작할 때(L이 끝나고 1시간 10분 뒤) 메모리에 남은 가장 오래된 표본은 L의 폭발이 끝나고 5분 뒤의 것이었다. 그래서 L은 디스크로만 본다. 이번 주 런은 끝나자마자
04를 돌린다.
4. 시간 축으로 자르기 — 분 · 5분 · 한 칸
4.1 쿼리 모양(실습/03 [2] · [3])
sql
-- 런 시작(:t0) 기준 초 → 분 칸
FLOOR((EXTRACT(HOUR FROM d) * 3600 + EXTRACT(MINUTE FROM d) * 60 + EXTRACT(SECOND FROM d)) / 60) -- d = sample_time - :t0
-- 5분 칸 (S1 = +60초부터)
FLOOR((초 - 60) / 300)
-- 운영 시계
TO_CHAR(DATE '2000-01-01' + (599 + 분) / 1440, 'HH24:MI') -- 런 +1분 = 10:00GROUP BY의 칸만 바꿔도 같은 표본에서 전혀 다른 결과가 나온다. 구간의 시작을 런 시각에 맞추는 이유는 두 가지다. 스냅샷 번호는 런마다 다르고, 벽시계 기준 5분 칸은 폭발을 둘로 자를 수 있다(§7.3).
4.2 지난주 한 시간(L · 디스크 10초 · MODULE = LOAD_DRIVER)
| 운영 시계 | 표본 | AAS | CPU % | CPU 차례 % | enq: TX % |
|---|---|---|---|---|---|
| 10:00~10:05 | 90 | 3.00 | 63.3 | 27.8 | 0 |
| 10:05~10:10 | 90 | 3.00 | 63.3 | 25.6 | 0 |
| 10:10~10:15 | 90 | 3.00 | 71.1 | 18.9 | 0 |
| 10:15~10:20 | 90 | 3.00 | 77.8 | 16.7 | 0 |
| 10:20~10:25 | 90 | 3.00 | 63.3 | 23.3 | 0 |
| 10:25~10:30 | 90 | 3.00 | 67.8 | 24.4 | 0 |
| 10:30~10:35 | 60 | 2.00 | 0 | 0 | 100 |
| 10:35~10:40 | 90 | 3.00 | 60.0 | 25.6 | 0 |
| 10:40~10:45 | 90 | 3.00 | 78.9 | 10.0 | 0 |
| 10:45~10:50 | 90 | 3.00 | 72.2 | 16.7 | 0 |
| 10:50~10:55 | 90 | 3.00 | 58.9 | 21.1 | 0 |
| 10:55~11:00 | 90 | 3.00 | 67.8 | 18.9 | 0 |
| 한 칸(60분) | 1,050 | 2.92 | 63.8 | 19.6 | 5.7 |
분 단위로 보면 31~35분(10:30~10:34) 다섯 칸은 표본이 12개이고 전부 enq: TX, CPU는 0이다. 나머지 57칸은 표본이 18개(세 세션 × 6)다.
4.3 세 런을 나란히 — 같은 모양(실습/04 [2] · [3])
| L(10초) | R1(10초 · 1초) | R2(10초 · 1초) | |
|---|---|---|---|
| 폭발 분(31~35)의 표본 | 12/분 · 전부 enq: TX | 12/분 · 120/분 · 전부 enq: TX | 12/분 · 120/분 · enq: TX(마지막 분은 디스크 11 · 메모리 119) |
| 그 밖의 분 | 18/분 | 18/분 · 180/분 | 18/분 · 180/분 |
| 10:30~10:35 | AAS 2.00 · enq: TX 100% | AAS 2.00(1초 600장) · 100% | AAS 2.00(1초 600장) · 99.8%(디스크 98.3%) |
| 한 칸(디스크) | 1,050 · 2.92 · CPU 63.8% · enq: TX 5.7% | 1,047 · 2.91 · 61.7% · 5.7% | 1,044 · 2.90 · 65.6% · 5.7% |
첫 · 마지막 enq: TX(런 기준) | +31:06 · +35:56(10초) | (재지 않았다) | 1초 +31:00.1 · +35:59.2 · 10초 +31:00.1 · +35:50.2(폭발 +31:00 ~ +36:00) |
결정적인 부하. 같은 씨앗이면 같은 분에 같은 모양이 나온다. 실행 횟수만 장비 속도에 따라 조금 흔들린다(세션당 703,790~709,305회). 그래서 이 화에서는 지난주 한 시간을 다시 만들어 1초 표본으로 볼 수 있었다.
5. 16화 결론이 왜 뒤집혔나 — 평균 · 칸 · 단위 · 횟수
16화 T5-001의 결론(16화 2_파고들기 §14.3)과 이 화의 실측을 나란히 놓는다.
| 16화에서 쓴 문장 | 근거로 쓴 칸 | 뒤집힌 이유 | 이 화의 실측 |
|---|---|---|---|
| 락은 한 시간의 5.5%, 부차적 | Top FG Events enq: TX 599.0초 ÷ DB Time | 평균: 분모는 3,600초인데 분자 599초는 300초 안에 몰려 있었다 | 10:30~10:35에 enq: TX 100% · 그 5분의 AAS 2.00 |
| 한 번에 1초가 안 되는 기다림(평균 702ms) | 같은 줄의 Avg Wait | 평균: 두 무리를 섞은 평균 | 853번 = 8~16초 60번 + 4ms 미만 793번(이벤트 히스토그램) · 702ms짜리 대기는 없었다 |
| CPU 2개에 평균 3.02명, 문제는 CPU를 먹는 일의 양 | 머리 · Host CPU | 칸: 한 시간을 한 칸으로 셌다 | 55분은 맞다(AAS 3.00 · CPU 60~79%). 5분은 AAS 2.00 · CPU 0% |
| DB CPU가 1.84인 것도 그 기다림만큼 | Load Profile | 맞았다. 그러나 '조금 덜 바빴다'가 아니라 '5분 동안 0'이었다 | 2.01 × 55 ÷ 60 = 1.84 |
| 행 락의 88%는 SYS 통계 테이블 | Segments by Row Lock Waits | 횟수: 그 표는 대기 '횟수'를 센다 | 시간으로는 0초(§9.5) · 시간의 100%는 T_STLM_NUM_LD(ASH · ADDM) |
| 락을 기다린 SQL은 목록에 없다 | SQL ordered by | 단위: 목록은 SQL의 누적 시간으로 뽑는다 | ASH에는 그 문장이 9ph8h7j79zbh7로 60장(L) 찍혔다 · AWR 리포트 뒤쪽 ASH 절의 Top SQL에도 있다 |
| 조민우 씨의 10시 반은 확인할 수 없다 | (한 시간이 한 줄) | 그 리포트의 앞쪽만 보면 맞았다 | 같은 리포트 뒤쪽 ASH 절의 'Activity Over Time' 칸에 처음부터 있었다(§7.3) |
네 가지를 한 줄씩
- 평균: 리포트의 숫자는 모두 구간 전체로 나눈 값이다. 분자가 한곳에 몰려 있어도 분모는 구간 전체다.
- 칸: 한 시간을 한 칸으로 세면 '언제'가 사라진다. 같은 표본을 5분 칸으로 세면 5.7%가 100%가 된다.
- 단위: AWR은 계량기 누적값(시간, 횟수)의 차이를 구한다. 순간을 남기지 않으니 다시 쪼갤 수 없다. ASH는 순간을 남긴다.
- 횟수와 시간: '88%'는 횟수의 88%였다. 횟수 1등과 시간 1등은 다를 수 있다.
6. 블로킹 체인의 시계열 — 12화의 트리를 과거로
6.1 10초마다 본 체인(실습/05 [1] · 1초 표본)
| 런 시각 | 표본 | 기다림 | ON CPU | 기다린 세션 | 막은 세션 |
|---|---|---|---|---|---|
| +30:50 | 30 | 0 | 22 | ||
| +31:00 ~ +35:50(30칸) | 20(칸마다) | 20 | 0 | S2, S3 | S1 |
| +36:00 | 30 | 0 | 19 |
(R1 · R2 실측. R2는 +35:30 칸 하나만 기다림이 19다.) 10초 칸 30개가 모두 같다. 두 세션이 10초 내내 기다렸고, 막은 쪽은 내내 S1이었다.
6.2 대기 하나하나(실습/05 [2])
같은 세션에서 같은 SEQ#가 이어진 표본을 대기 한 번으로 친다. 1초 표본이라 대기 길이 ≈ 표본 수(초)다.
| 기다린 쪽 → 막은 쪽 | SQL | 대기 수 | 표본 | 평균 · 최대 · 최소(초) |
|---|---|---|---|---|
| S2 → S1 | 9ph8h7j79zbh7 | 30 | 300 | 10 · 10 · 10 |
| S3 → S1 | 9ph8h7j79zbh7 | 30 | 300 | 10 · 10 · 10 |
| (R2) S2 → S1 · S3 → S1 | 같다 | 30 · 30 | 299 · 300 | 10 · 10 · 9(하나) / 10 · 10 · 10 |
부하 발생기의 기록과 맞춰 보면(04 [6] · V_LD_PIECE) N1 hold는 30번 × 10.00초(S1이 번호 행을 쥔 시간 · 세 런 모두)이고, N1 hotfix는 L 47번 평균 12.74초, R1 53번 11.30초, R2 56번 10.69초로 합은 셋 다 599초 안팎이다. 실행 횟수가 대기 60번보다 적은 이유는 이렇다. S1이 커밋하자마자 다시 행을 잡으면, 기다리던 쪽은 새 트랜잭션 앞에 다시 줄을 선다(대기 번호가 바뀐다). 그래서 N1 hotfix 한 번이 대기 두 번에 걸치기도 한다.
6.3 과거의 블로킹 트리(실습/05 [3])
폭발 한가운데(+33:30) 표본 하나를 골라, SESSION_ID → BLOCKING_SESSION 관계로 12화의 CONNECT BY를 그대로 쓴다. 막은 쪽은 표본이 없으니 막힌 쪽의 BLOCKING_SESSION에서 '이름만' 가져와 뿌리로 세운다.
SID_TREE SERIAL# EVENT ACTION CLIENT_ID WHO (R1 · DB 시계 11:53:18)
217 34019 (no sample - idle?) S1
53 9021 enq: TX - row lock contention N1 hotfix LD11-S2 S2
216 10889 enq: TX - row lock contention N1 hotfix LD11-S3 S3같은 런에서 2초 앞서 V$SESSION으로 찍은 12화의 트리(R1 · 11:53:16):
SID_TREE SERIAL# ACTION CLIENT_ID EVENT SECS BLOCKER
217 34019 N1 hold LD11-S1 PL/SQL lock timer 0
53 9021 N1 hotfix LD11-S2 enq: TX - row lock contention 8 217
216 10889 N1 hotfix LD11-S3 enq: TX - row lock contention 8 217
V$LOCK 217 TX LMODE 6 BLOCK 1 · 53 · 216 TX REQUEST 6 — 같은 usn 1 slot 4 wrap 11476R2에서는 같은 초(13:42:19 = +33:30)에 둘을 찍었다. V$SESSION은 뿌리 223 · 41986 N1 hold PL/SQL lock timer · 17 · 203 enq: TX BLOCKER 223, V$LOCK은 223 TX LMODE 6 · BLOCK 1 · 17 · 203 REQUEST 6(usn 9 slot 5 wrap 20938), ASH 트리는 223 · 41986 (no sample) · 17 · 44014 · 203 · 49536이다.
모양은 같다. 다른 것은 뿌리 한 줄이다. V$SESSION에는 뿌리가 무엇을 하며 쉬는지(PL/SQL lock timer · N1 hold)가 나오고, ASH에는 이름(SID · SERIAL#)만 있다.
6.4 TX 이름표 — 표본의 P1 · P2 · P3(실습/05 [5] ← 12화 §2)
| 칸 | 값(R1) | 풀이 |
|---|---|---|
P1 | 0x54580006 | 0x54 = 'T' · 0x58 = 'X' · 모드 6(배타) |
P2 · P3 | 서로 다른 짝 30개(R1 · R2) · 첫 짝 1/33/11455(R1) · 1/13/16146(R2) | P2 = USN × 65,536 + SLOT · P3 = WRAP. 12화 V$LOCK의 ID1 · ID2와 같은 값 |
TX 30개는 S1이 10초씩 쥔 트랜잭션 30개(N1 hold 30번)다. L의 ASH 리포트 Top Event P1/P2/P3 Values에도 "1415053318"(= 0x54580006)이 찍혀 있다.
6.5 막은 쪽은 왜 표본에 없나 — 그리고 어디서 찾나
| L(10초) | R1 · R2(1초) | |
|---|---|---|
| 막은 세션 | SID 175 · SERIAL# 62822 = 세션 1 | 217 · 34019(R1) · 223 · 41986(R2) = 세션 1 |
| 그 세션의 표본: 폭발 전 · 중 · 뒤 | 186 · 0 · 156 | 폭발 전 마지막 표본 +30:59 · 폭발 중 0 · 폭발 뒤 첫 표본 +36:00(R1 · R2 같다) |
| ASH 리포트 Top Blocking Sessions(폭발 5분) | 175,62822 ** NOT FOUND ** … 0/30 [0%] | R2 223,41986 ** NOT FOUND ** … 0/300 [0%] · R1 217,34019 … 1/300(1장은 폭발 1초 전 표본. 리포트 시작 시각이 초 단위로 잘린 탓) |
| 그 5분 동안 무엇을 했나 | DBMS_SESSION.SLEEP. PL/SQL lock timer는 Idle 대기라 표본이 남지 않는다 | 같다 |
어디서 찾나. ① 그 세션이 일하던 때의 표본(폭발 앞뒤의 ACTION · CLIENT_ID · MODULE) ② 애플리케이션이나 배치의 기록(이 화에서는 T_LD_SESS · V_LD_MINUTE) ③ 그 세션의 맨 위 호출. RUN_SESSION(1)의 Elapsed가 다른 둘보다 300초 짧았다(16화 AWR 3,302.6 vs 3,602.6 · R2 AWR 3,299.6 vs 3,599.6 · R2 SQL Monitor 3,420 vs 3,720). 쉬던 5분이 DB Time에서 빠진 흔적이다. 운영에서는 막은 쪽이 대개 일하는 중(배치의 CPU · I/O)이라 표본에 찍힌다. 12화 1호의 주간 정산 보정 배치가 그런 경우다. 트랜잭션을 연 채 자리를 뜬 화면처럼 쉬면서 행을 쥐고 있으면(SQL*Net message from client), 이 화에서처럼 이름만 남는다.
7. ASH 리포트(ashrpt) — 읽는 칸과 함정
7.1 함수(실습/06)
@?/rdbms/admin/ashrpt.sql은 대화형이다. 같은 일을 DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(l_dbid, l_inst_num, l_btime, l_etime, l_options, l_slot_width, l_sid, l_sql_id, l_wait_class, l_service_hash, l_module, l_action, l_client_id, l_plsql_entry, l_data_src, l_container)로 할 수 있다(이 버전의 인자는 DESC로 확인했다).
| 인자 | 뜻 |
|---|---|
l_btime · l_etime | DATE. 초 아래는 잘린다 |
l_slot_width | 'Activity Over Time' 칸 너비(초). 비우면 10칸 이하로 나눈다(5분 창이면 1분 칸) |
l_data_src | 1 = 메모리 · 2 = 디스크 · 생략 = 둘을 이어 붙인다 |
l_sid · l_sql_id · l_module · l_action · l_client_id | 한 세션 · 한 문장 · 한 조각만 |
7.2 읽는 칸(이 화의 순서)
| # | 칸 | 묻는 것 | L 폭발 5분(디스크) | R2 폭발 5분(메모리) |
|---|---|---|---|---|
| 1 | 머리 Sample Count · Average Active Sessions · Data Source | 얼마나 · 어디서 읽었나 | 60 · 2.00 · DBA_HIST(snap 20) | 605 · 2.02 · V$(R1 606 · 2.02) |
| 2 | Top User Events | 시간이 어디로 | enq: TX 100% | enq: TX 99.01%(R1 98.68) |
| 3 | Top Blocking Sessions | 누가 막았나 | 175,62822 100% · NOT FOUND 0/30 | 223,41986 99.01% · NOT FOUND 0/300 |
| 4 | Top DB Objects | 어디서 | SQLP.T_STLM_NUM_LD 100% | SQLP.T_STLM_NUM_LD 99.01% |
| 5 | Top SQL with Top Events | 무엇이 | 9ph8h7j79zbh7 · FOR UPDATE 100% · 표본에 찍힌 실행 45 | 같은 문장 99.01% · 표본에 찍힌 실행 50 |
| 6 | Top Client IDs | 어느 프로그램 | LD8-S2 50% · LD8-S3 50% | LD22-S2 49.59% · LD22-S3 49.59% |
| 7 | Activity Over Time | 언제 | 09:37:26(2.6분) 30 · 09:40:00(2.4분) 30 | 1분 칸: 13:39:49(11초) 24 · 118 · 122 · 118 · 122 · 13:44(49초) 95(enq: TX) |
| — | Top Phases of Execution | (참고) | SQL Execution 100 · Sequence Load 100 | 99.34 · 99.17(§9의 깃발) |
7.3 한 시간 리포트의 'Activity Over Time' — 칸 경계
벽시계 5분에 맞춘 칸이라, 폭발이 칸 경계와 어긋나면 두 칸으로 나뉜다.
| 런 | 폭발(DB 시계) | 칸 | enq: TX 표본(디스크) |
|---|---|---|---|
| L | 09:37:26~09:42:26 | 09:35 칸(표본 85) · 09:40 칸(86) | 30 · 30. 두 칸 모두 1등은 CPU + Wait for CPU(32 · 30) · 16화 AWR 리포트 뒤쪽 ASH 절 |
| R1 | 11:50:47~11:55:47 | 11:50 칸(표본 70 중 50) · 11:55 칸(106 중 10) | 50 · 10 |
| R2 | 13:39:49~13:44:49 | 13:35 칸(108) · 13:40 칸(66) | 4(상위 3 밖) · 55(13:40 칸의 1등) |
16화의 awr_H.txt 뒤쪽 ASH 절에 이 칸이 이미 있었다. 16화의 SOP 9칸에는 그 절이 빠져 있었다.
7.4 ⚠️ l_data_src를 생략하면(이 버전 실측)
R1의 한 시간(+1:00 ~ +61:00)을, 앞쪽이 메모리에서 밀려난 뒤에 뽑았다.
l_data_src | Data Source | Sample Count | AAS | enq: TX |
|---|---|---|---|---|
| 생략(이어 붙이기) | DBA_HIST(snap 28~29) + V$ | 313 | 0.87 | 없음 |
| 1(메모리) | V$ | 9,755 | 2.71 | 6.15%: 앞 15분이 비어 있는데 60분으로 나눴다 |
| 2(디스크) | DBA_HIST(snap 28~30) | 1,290 | 3.58 | 4.65%: 루트 · 백그라운드 표본 포함 |
R2의 한 시간으로 다시 재도 같았다. 생략하면 표본 267 · AAS 0.74 · enq: TX 없음(Data Source: snap 39 → 40)이었다. 생략하면 디스크 표본을 다음 스냅샷까지만 읽고 메모리 표본은 붙이지 못한 리포트가 나왔다. 틀린 리포트가 에러 없이 나오는 것이다. 그래서 06은 한 시간을 l_data_src => 2로 뽑고, 메모리가 다 덮은 5분은 인자를 생략(= 메모리)해서 뽑는다. 머리의 Data Source 두 줄부터 먼저 본다.
7.5 한 시간으로 보면 '막은 세션' 순위도 흔들린다(디스크 · 한 시간)
| Top Blocking Sessions | R1 % Activity · # Samples Active | R2 % Activity · # Samples Active | 원인 이벤트 |
|---|---|---|---|
| S1(런의 세션 1) | 4.65 · 329/360 [91%] | 4.84 · 329/360 [91%] | enq: TX - row lock contention |
| 172(CKPT) | 5.50 · 3/360 | 4.51 · 0/360(NOT FOUND) | enq: RO - fast object reuse |
| 171(DBW0) | 2.25 · 69/360 | 2.05 · 59/360 | local write wait |
한 시간으로 보면 1등이 런마다 바뀐다(R1에서는 CKPT가 1등이다. P1의 TRUNCATE가 기다린 체크포인트다). 그리고 S1은 두 런 모두 '한 시간 내내 91% 활성'인 세션으로 보인다. 막지 않을 때는 열심히 일했기 때문이다. 같은 S1이 폭발 5분 동안에는 활성 0/300이다.
8. ADDM을 검증하는 법 — Rationale을 표본으로
8.1 ADDM의 한 줄은 네 칸
| 칸 | 뜻 | 검증 |
|---|---|---|
| Finding | 무엇이(문제 · 증상) | 그 이름이 가리키는 표본을 센다 |
| Impact | 얼마나: 평균 활성 세션 · %(구간 평균) | ASH의 그 몫 ÷ 구간 초와 맞나 · 언제 몰렸나는 따로 본다(§4) |
| Recommendation | 무엇을 하라(Action) | 따르면 최대 얼마가 줄어드나: 그 일을 하는 SQL의 시간이 상한이다 |
| Rationale | 왜 그렇게 말하나(지목한 세션 · SQL · 객체 · 수치) | 적힌 사실을 ASH로 다시 센다 |
Rationale이 없는 Finding은 그 Finding이 기댄 칸(시간 모델, 대기 이벤트)을 직접 대조한다. 맞지 않으면 기각한다.
8.2 실행(실습/07)
sql
DBMS_ADDM.ANALYZE_DB(:task_name, :s1, :s2); -- addmrpt.sql 의 대화형 질문 대신
SELECT DBMS_ADDM.GET_REPORT(:task_name) FROM dual; -- 리포트
-- 표로 : DBA_ADDM_FINDINGS (impact = 마이크로초) · DBA_ADVISOR_RECOMMENDATIONS · DBA_ADVISOR_RATIONALE스냅샷마다 자동 ADDM이 이미 돈다. 과제 이름은 ADDM:<dbid>_1_<끝 스냅샷>이다(DBA_ADDM_TASKS · HOW_CREATED = AUTO). 16화의 한 시간에도 19 → 20, 20 → 21 두 과제가 있었다.
8.3 두 주의 ADDM
| Finding | L(자동 과제 19 → 20 · 53분 · DB time 9,598초 · AAS 3.01) | R1(C17H_HOUR 28 → 30 · 60분 · 11,070초 · 3.07) | R2(C17H_HOUR 39 → 41 · 60분 · 10,536초 · 2.93) |
|---|---|---|---|
| Sequence Usage | 2.65 | 88.06% | 2.88 | 93.74% | 2.85 | 97.54% |
| Top SQL Statements | 2.32 | 77.15% | 2.21 | 71.88% | 2.26 | 77.32% |
| Resource Manager CPU Throttling | .69 | 22.88% | .75 | 24.47% | .6 | 20.45% |
| Row Lock Waits | .19 | 6.24% | .17 | 5.38% | .17 | 5.68% |
| Checkpoints Due to DROP or TRUNCATE | .18 | 6.13% | .19 | 6.13% | .19 | 6.45% |
| "User I/O" wait Class | .13 | 4.19% | .13 | 4.16% | .13 | 4.4% |
| PL/SQL Execution | .1 | 3.46%: SYS.DBMS_SESSION.SLEEP 271초 · 루트(§15) | .15 | 4.97%(대기 루프 · §15) | 없다(02를 고친 뒤) |
8.4 검증 결과
| Finding | Rationale(L) | ASH로 다시 센 값 | 판정 |
|---|---|---|---|
| Row Lock Waits | T_STLM_NUM_LD (75696) · SQL 9ph8h7j79zbh7 blocked · session 175 serial 62822 = 100%(L) · 223 · 41986(R2) | L 디스크: 기다린 표본 60장 전부 BLOCKING_SESSION 175 · 62822 = 세션 1 · R2 메모리: 599장 전부 223 · 41986 = 세션 1(100%) · Impact는 L 599초 · R2 598.8초 vs ASH 599장(0.166 AAS, 폭발 5분만 보면 2.00) | 채택. 단, Impact는 평균이다. 순위(4~5위)로 중요도를 정하지 않는다 |
| Sequence Usage | (없음) · Action "Use a larger cache size" | Impact 초 = 인스턴스 Time Model sequence load elapsed time(R1 10,376.3 · R2 10,277.5, 소수점까지 같다) · ASH IN_SEQUENCE_LOAD = Y가 시퀀스를 쓰지 않는 표본에도 켜져 있다: L Q2 391/420 · N1 hotfix 60/60 · R1 · R2 전 조각 100% | 기각(§9) |
| Top SQL Statements | Q2 · Q3 · FOR UPDATE · TRUNCATE · Q1 · Run SQL Tuning Advisor | Q2 · Q3가 CPU의 대부분이라는 것은 16화 9칸과 같은 이야기 | 보류 → 18화(SQL Tuning Advisor) |
| Resource Manager CPU Throttling | Wait class Scheduler | 16화 ⑤ %DB time waiting for CPU 23.8%와 같은 이야기 | 채택(16화와 같은 결론) |
| PL/SQL Execution(L) | SYS.DBMS_SESSION.SLEEP 271 s · root | 16화 02의 대기 루프. 0.5초마다 깨어날 때 CPU 차례를 기다렸다(§15) | 측정 도구가 남긴 흔적 |
9. ADDM 1위 Sequence Usage의 정체
9.1 실측(07 [4] · 08 · 08b · 이 화를 준비하며 한 격리 시험)
| 시험 | 결과 |
|---|---|
| ADDM Impact의 출처 | Sequence Usage impact = 인스턴스 Time Model sequence load elapsed time: R1 10,376.3 = 10,376.3초 · R2 10,277.5 = 10,277.5초(DBA_HIST_SYS_TIME_MODEL · 루트). 이 줄은 PDB의 DBA_HIST_CON_SYS_TIME_MODEL에는 없다 |
| 그 시퀀스를 실제로 부른 문장 | N1 rev01 INSERT … SQ_STLM_LD.NEXTVAL: S1~S2 R1 31.9초(286,056번) · R2 31.1초(285,626번) · 16화 구간 A 6.6초 |
| ASH 깃발(메모리 · S1~S2 중 메모리가 덮은 부분) | MODULE = LOAD_DRIVER 표본이 전부(100%) IN_SEQUENCE_LOAD = Y다: R2 Q2 rev01 3,029/3,029 · P1 rev02 2,263/2,263 · Q3 rev01 1,793/1,793 · N1 hotfix 599/599 · N1 rev01 50/50(R1도 전부 100%) |
| ASH 리포트 Top Phases of Execution | 폭발 5분(전부 FOR UPDATE 대기)이 Sequence Load 100%(L) · 99.17%(R2) |
08 혼자 · 여섯 판 | CACHE 20 · 1000 · 순서 바꾸기 · 0.2초 쉬기 · 호출 나누기: 전부 sequence load 0.00초 · 깃발 0(DB time 2.94~3.04초 · 번호 2,000개 받기는 0.01초) |
| 세 세션이 같은 함수를 동시에(CACHE 20 · CACHE 2) | sequence load 0 · 0 · 0(세션마다 DB time 2.3~4.1초) |
발사기 · 1세션 · 1분(가중치 넷: Q2 · N1+Q2 · P1+Q2 · N1+P1+Q2) | 깃발 0 |
| 발사기 · 3세션 · 기본 가중치 · 2분 | 세션 1은 +49초에, 세션 2는 +110초에 켜졌다 · 세션 3은 안 켜졌다 · 켜지면 그 호출이 끝날 때까지 꺼지지 않는다 |
발사기 · 3세션 · N1=0(시퀀스를 한 번도 안 부른다) · 3분 | 세션 1이 +136초에 켜졌다 |
발사기 · 3세션 · P1=0 · 3분 | 세션 2가 +127초에 켜졌다 |
9.2 권고를 그대로 해 본다(08b · 3세션 STEADY 7분 · 그 5분을 ADDM으로)
| CACHE 20(드라이버 원래 값) | CACHE 1000(ADDM의 권고) | |
|---|---|---|
| DB time(5분) | 900.7초 | 902.2초 |
sequence load elapsed time | 721.5초 | 717.8초 |
| ADDM Sequence Usage | 2.41 AAS · 80.10% | 2.39 AAS · 79.56%, 같은 권고를 다시 한다 |
| 그 시퀀스를 부른 INSERT의 Elapsed(26,169 · 26,233번) | 1.98초 | 2.87초 |
| 깃발이 처음 켜진 때(세션 1 · 2 · 3) | +55 · +237 · +25초 | +55 · +156 · +147초 |
권고를 적용해도 아무것도 바뀌지 않았다. DB Time은 그대로였고, 정작 시퀀스를 쓰는 문장의 시간은 잡음 수준으로 흔들렸을 뿐이다. 게다가 시퀀스를 한 번도 부르지 않는 부하에서도 깃발이 켜지므로, 캐시를 키울 '그 시퀀스'가 처음부터 없었다.
9.3 판정과 남긴 것
- 이 연습 DB에서
sequence load elapsed time과 ASH의IN_SEQUENCE_LOAD는 '시퀀스 캐시를 채우는 시간'을 세지 않는다. 한 번 켜지면 그 세션의 맨 위 호출(RUN_SESSION, 62분짜리 PL/SQL 호출 하나)이 끝날 때까지 꺼지지 않는 깃발이다. 16화에서 "생각 시간을 둔 런에서는 0.0초"라고 적은 것과도 맞는다. 그때는 CPU가 넘치지 않았다. - 켜지는 조건은 이 화에서 밝히지 못했다. 확인한 것은 이렇다. 한 세션으로는 안 켜진다(여섯 판 + 발사기 1세션 넷). CPU 2개를 세 세션이 넘치게 쓰면 1~4분 안에 켜진다. 시퀀스를 안 불러도(
N1=0) 켜진다. 캐시를 키워도 같다. - ADDM 1위는 다른 칸(SQL의 경과 시간)과 맞지 않는 칸 하나에서 나왔다. 16화의 규칙 "맞지 않는 칸으로는 결론을 내지 않는다"에 따라 ADDM 1위를 기각한다.
- 드라이버 시퀀스
SQ_STLM_LD는08b가 마지막에 CACHE 20으로 되돌렸다(99가 한 번 더 확인).
9.5 AWR의 '행 락 88%' — OPTSTAT_HIST_CONTROL$의 정체
| 실측 | 값 |
|---|---|
| L Segments by Row Lock Waits | SYS.OPTSTAT_HIST_CONTROL$ 930(87.82%) · SQLP.T_STLM_NUM_LD 129(12.18%). 자동 스냅샷 앞 구간(19 → 20) 780 · 129 / 뒤 구간(20 → 21) 150 · 0 |
같은 두 구간의 enq: TX(DBA_HIST_SYSTEM_EVENT) | 741 waits · 599초 / 112 waits · 0초. 폭발이 없는 구간에도 대기는 있었지만 시간은 0이었다 |
L 이벤트 히스토그램(DBA_HIST_EVENT_HISTOGRAM 19 → 21) | 16µs 이하 505(그중 8~16µs 칸 461) · 16µs~4ms 288 · 8~16초 60 · 합 853 |
| R1 히스토그램(28 → 30) | ≤ 2ms 848 · 8~16초 60 · 합 908(AWR 리포트 Wait Event Histogram: 93.2%가 512µs 미만 · 6.6%가 8~16초) |
ASH의 enq: TX 표본 중 CURRENT_OBJ# = OPTSTAT_HIST_CONTROL$ | 0(L · R1 모두. 4ms 미만 대기는 표본에 남지 않는다) |
그 행을 고치는 문장(V$SQL) | update /* KSXM:TIME_UPD */ optstat_hist_control$ set sval2 = systimestamp where sname = :param_name. 그 행은 SNAPSHOT_UPD_TIME · 한 번에 73.3µs(08 [3]) |
무엇이 그 문장을 부르나(08 [3] · 복사본 T_C17_KS) | 비어 있지 않은 테이블에 APPEND 10번 → 1 · TRUNCATE만 10번 → 0 · TRUNCATE + APPEND 10번 → 10 |
빈 세그먼트에 APPEND하면 | 그 자리에서 통계를 모은다: NUM_ROWS 1000 · 컬럼 통계 NOTES = STATS_ON_LOAD(온라인 통계 · 12c부터) |
정체. P1 rev02(TRUNCATE + INSERT /*+ APPEND */)가 빈 작업 테이블에 적재할 때마다 온라인 통계를 모으고, 그때 OPTSTAT_HIST_CONTROL$의 한 행을 고친다. 세 세션의 P1이 1분에 수백 번 그 한 행을 고치니 아주 짧게 부딪친다. 그래서 횟수는 1등인데 시간은 0이다. 16화의 해석(P1의 온라인 통계)은 맞았다. 틀린 것은 그 88%를 시간으로 읽은 것이다.
10. SQL Monitor — 언제 켜지나
10.1 시작 조건(tgsql Monitoring Database Operations)
① 한 번 실행할 때 CPU + I/O를 5초 넘게 썼다 ② 병렬로 돈다 ③ /*+ MONITOR */ 힌트가 있다. 전제 조건은 STATISTICS_LEVEL TYPICAL 이상, CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC+TUNING(튜닝 팩)이다.
10.2 실측(실습/09)
| 시험 | 한 번 실행 | 감시 | 읽기 |
|---|---|---|---|
런의 N1 hotfix FOR UPDATE(발사기 기록: 한 번에 평균 10.69~12.74초 기다림, 세 런) | 경과 > 5초 · CPU ≈ 0 | ✗ (0줄 · R1 · R2) | 기다림은 5초에 들지 않는다. 락은 ASH로 본다 |
런의 BEGIN PKG_LOAD_DRIVER.RUN_SESSION(n); END; | 3,419.8(S1) · 3,719.8 · 3,719.8초(R1) · 3,420 · 3,720 · 3,720(R2) | ○ | 맨 위 PL/SQL 호출도 감시된다 · S1만 300초 짧다(§6.5) |
| [M2a] CPU만 쓰는 문장(3,000 × 3,000) | 1.47~1.52초 · Buffers 0 | ✗ | 다섯 번 재서 다섯 번 다 그렇다 |
| [M2b] 같은 모양(7,000 × 7,000) | 7.98~8.16초 · Buffers 0 | ○ | 리포트 First Refresh Time이 시작 6초 뒤 |
[M2c] 06화 Q2 rev00(USE_NL · 100만 번 왕복) | 0.27초 · Buffers 1,026,838(1,027,211 · 1,027,025로 흔들린다) | ✗ | 블록 100만 개를 읽어도 1초가 안 되면 감시되지 않는다 |
[M3] /*+ MONITOR */ + 부하 발생기의 Q2 | 0.03초 | ○ | |
[M4] /*+ FULL(o) PARALLEL(o 4) */ | 실행계획이 직렬(TABLE ACCESS FULL · PX 줄 없음) | ✗ | Queries Parallelized 0 · PX_SERVERS_EXECUTIONS 0 · parallel_max_servers 1 |
Free에서 안 되는 것과 그 대신 한 것. ② 병렬 조건은 Free에서 발동하지 않는다(병렬 힌트를 줘도 직렬 실행계획이 나오고 감시도 없다. 실측). 그래서 감시는 ①과 ③으로 확인했다. 병렬 실행의 감시(
PX_SERVERS_REQUESTED·ALLOCATED· DFO)는 대규모 EE 환경을 다루는 별권에서 다룬다.
10.3 리포트
DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(sql_id => …, type => 'TEXT', report_level => 'TYPICAL')로 뽑는다. 리포트는 Global Information(상태 · 세션 · 모듈 · 시작 · 마지막 갱신), Global Stats(Elapsed · CPU · Other Waits), SQL Plan Monitoring Details(실행계획 줄마다 Rows (Actual) · Execs · Time Active(s) · Start Active · Activity (%))로 이루어진다. ALLSTATS LAST와 다른 점은 문장이 도는 동안 볼 수 있다는 것이다. type => 'ACTIVE'는 브라우저로 여는 HTML이다(M2b 6,036 · 5,991 · 5,987자. 이 화에서는 길이만 쟀다).
11. 팩 없는 현장 — NONE에서 막히는 것과 남는 것
11.1 라이선스(dblic · ← 16화 T5 7번)
| 에디션 | 진단 팩(AWR · ASH · ADDM) | 튜닝 팩(SQL Monitor · STA · Profile) | CONTROL_MANAGEMENT_PACK_ACCESS 기본값 |
|---|---|---|---|
| Free(이 교재) | 포함 | 포함 | DIAGNOSTIC+TUNING |
| EE | 따로 산다 | 따로 산다(진단 팩이 먼저) | DIAGNOSTIC+TUNING. 계약이 없으면 NONE으로 내려야 한다 |
| SE2 | 살 수 없다 | 살 수 없다 | NONE |
11.2 실측 — 메모리에서만 NONE으로(실습/10 · spfile은 그대로)
| # | 시험 | NONE | 되돌린 뒤 |
|---|---|---|---|
| a | AWR 스냅샷 CREATE_SNAPSHOT | 됐다(snap 35) | 됐다(36) |
| b | AWR 리포트 AWR_REPORT_TEXT(R1 S1~S2) | 됐다(5,995줄) | 됐다 |
| c | ASH 리포트 ASH_REPORT_TEXT(R1 폭발 5분) | 됐다(297줄) | |
| d | ADDM DBMS_ADDM.ANALYZE_DB | ORA-13716: Diagnostic Package License is needed for using this feature. | |
| e | DBMS_SQL_MONITOR.REPORT_SQL_MONITOR | 됐다(21자, 거의 빈 리포트) | |
| f | ASH 표본: 내 세션이 CPU 3초 | 0장 | 3장 |
| g | SQL Monitor 감시: /*+ MONITOR */ 문장 | 0줄 | |
| h | SELECT … DBA_HIST_ACTIVE_SESS_HISTORY | 됐다(1,293행) | |
| i | V$SESSION · V$SYSSTAT · V$SYSTEM_EVENT | 됐다 | |
| j | Statspack STATSPACK.SNAP (PDB) | 됐다 |
막힌 것은 셋이다. ADDM(에러), ASH의 새 표본, SQL Monitor의 새 감시. 새로 모으는 것만 멈출 뿐, 이미 쌓인 것을 읽는 것은 막지 않는다. AWR 스냅샷, AWR 리포트, ASH 리포트, DBA_HIST_* 조회는 NONE에서도 실행된다. 그래도 쓰면 위반이다. 라이선스를 지키는 것은 스위치가 아니라 계약이다.
원복 확인:
V$PARAMETERDIAGNOSTIC+TUNING(ISMODIFIED = SYSTEM_MOD) ·V$SPPARAMETERDIAGNOSTIC+TUNING(바꾼 적 없음) · 되돌린 뒤 ASH 3장 · 스냅샷 됨.ISPDB_MODIFIABLE = FALSE라서 PDB에서는 바꿀 수 없다(루트에서 인스턴스 전체를 바꾼다).
11.3 팩 없는 현장에서 '언제'를 보는 법
| 방법 | 무엇 | 비용 |
|---|---|---|
| Statspack 스냅샷 간격을 좁힌다 | 5분 간격이면 5분 칸(§12.3) | 스냅샷 한 번 1.2초 · 저장 |
| 블로킹 트리를 주기적으로 찍어 남긴다 | 12화 T4 1호 재발방지 3번: 1분마다 · 30초 넘게 막힌 세션이 있으면 알림 | V$SESSION은 팩이 필요 없다 |
민원이 오면 그 시각에 바로 V$SESSION · V$LOCK을 찍는다 | 12화의 첫 5분 | 지나가면 사라진다 |
ASH가 있으면 지나간 다음에도 칸을 다시 자를 수 있다. 팩이 없으면 칸을 미리 좁혀 둬야 한다.
12. Statspack — 팩이 필요 없는 검침 대장
12.1 설치와 지우기(실습/01 · 99)
- 설치는
?/rdbms/admin/spcreate.sql로 한다. 묻는 질문 셋(PERFSTAT 암호, 기본 테이블스페이스, 임시 테이블스페이스)은DEFINE으로 미리 답해 둔다. 지우기는spdrop.sql과 같은 일을 한다(공용 동의어 · SYS의STATS$뷰 8개 ·PERFSTAT사용자). - PDB에만 깐다.
spdoc.txt에 이렇게 적혀 있다. "Statspack installation and reporting is not supported at the root level (CDB$ROOT)" · "some data sources in the report are for the entire instance and may not be restricted to the PDB." - 실측: 설치 2초 · 테이블 73 · 인덱스 73 · 공용 동의어 83 ·
PERFSTAT세그먼트 132.8MB(스냅샷 0. 대부분 처음 잡는 익스텐트) → 스냅샷 15개 뒤 138.8MB · 스냅샷 한 번(level 7) 1.18초.
12.2 스냅샷과 리포트
sql
EXEC :s := PERFSTAT.STATSPACK.SNAP(i_snap_level => 7); -- level 7 = SQL · 세그먼트 통계까지
-- spreport.sql 의 질문 넷(num_days · begin_snap · end_snap · report_name)을 DEFINE 으로 미리 → 묻지 않는다 (`11`)
-- ⚠️ spreport 를 한 세션에서 두 번 부르면 둘째 판에 SP2-0311 — sprepins.sql 의 'set escape' 탓. 둘째 판 전에 SET ESCAPE OFFjob_queue_processes가 1이라 spauto.sql(잡으로 매시 스냅샷) 대신 02에서 5분마다 직접 찍는다.
12.3 같은 한 시간 — AWR과 나란히(R1)
| AWR 루트(S1~S2) | Statspack(PDB · +1 ~ +61) | Statspack(PDB · +31 ~ +36 = 10:30~10:35) | |
|---|---|---|---|
| Elapsed | 60.01분 | 60.00분 | 4.98분 |
| DB time | 184.50분 | 184.21분(인스턴스 몫) | 10.02분 |
| DB CPU | 6,627.2초 | 110.27분 | 0.06분 |
| 평균 활성 세션 | 3.07(ADDM) | 3.1 | 2.0 |
enq: TX - row lock contention | 908 waits · 595.9초 · 656ms · 5.4% | 909 · 599초 · 659ms · 5.4% | 127 · 599초 · 4,715ms · 99.6% |
| ASH · 블로킹 · '언제' | 리포트 뒤쪽 ASH 절 | 없다 | 칸을 좁혔으니 보인다 |
R2: 한 시간(스냅샷 1 → 13) 60.02분 · DB time 175.51분 · DB CPU 110.50분 · AAS 2.9 · enq: TX 1,060 · 599초 · 565ms · 5.7% / 5분(스냅샷 7 → 8 = 정확히 +31:00 → +36:00) 5.00분 · DB time 10.10분 · DB CPU 0.11분 · AAS 2.0 · enq: TX 143 · 599초 · 4,187ms · 99.5%. 두 번 돌려 같은 리포트가 나왔다.
Statspack의 한 시간 리포트에도 AWR 한 시간 리포트와 같은 평균의 함정이 있다. 다른 점은 ASH 절이 없다는 것이다. 그래서 '언제'는 스냅샷 간격만큼만 보인다.
13. 진단 도구 4열 대응표(요약)
정본은 40_보고서와_표준/템플릿/진단도구_4열_대응표.md다.
| 질문 | AWR | ASH | ADDM | Statspack |
|---|---|---|---|---|
| 무엇을 남기나 | 계량기 누적값의 스냅샷(뺄셈) | 활성 세션의 순간 사진 | 두 스냅샷 사이의 분석(과제) | 계량기 누적값의 스냅샷(뺄셈) |
| 얼마나 바빴나 | DB Time ÷ 구간 | 표본 × 간격 ÷ 구간 | Activity(AAS) | DB time ÷ 구간 |
| 언제 | ✗(구간 평균) | ○ 분 · 초 | ✗ | 스냅샷 간격만큼 |
| 누가 누구를 막았나 | ✗(Row Lock Waits는 횟수) | ○ BLOCKING_SESSION | Rationale에 이름 | ✗ |
| 무엇을 하라 | ✗ | ✗ | ○(가설) | ✗ |
| 팩 | 진단 | 진단 | 진단 | 필요 없음 |
| 이 화의 L · R2 | enq: TX 5.5% · 5.7% | 10:30~10:35 100% · 99.8% | Row Lock .19 · .17 AAS(채택) / Sequence 88 · 98%(기각) | (R2) 한 시간 5.7% · 5분 99.5% |
14. 이 화의 측정값 전부
14.1 세 런
| L(16화 C16-H) | R1(17화 첫 판) | R2(17화 정본) | |
|---|---|---|---|
| 런(DB 시계) | run 8 · 09:06:26 | run 11 · 11:19:47 | run 22 · 13:08:49 |
| 세션 실행 수 | 703,790 · 702,892 · 702,941 | 709,305 · 706,369 · 706,509 | 706,614 · 707,376 · 706,626 |
| 에러 · 원본 지문 앞 = 뒤 · 남은 세션 | 0 · 같다 · 0 | 0 · 같다 · 0 | 0 · 같다 · 0 |
N1 hold | 30 × 10,004.67ms | 30 × 10,003.33ms | 30 × 10,004ms |
N1 hotfix | 47 × 12,744.68ms(합 599.0초) | 53 × 11,299.62ms(합 598.9초) | 56 × 10,691.96ms(합 598.8초) |
| 폭발 분의 Q1(1분) | 15~31(그 밖은 약 11,000) | 16~29 | 11~35 |
| AWR 루트 리포트 DB Time · AAS | 181.09분 · 3.02(16화 · 루트 대기 루프 포함) | 184.50분 · 3.07(PDB 대기 루프 포함) | 175.60분 · 2.93 |
enq: TX(AWR 루트) | 853 · 599.0초 · 702.20ms · 5.5% | 908 · 595.9초 · 656.23ms · 5.4% | 1,061 · 598.8초 · 564.35ms · 5.7% |
| Row Lock Waits: OPTSTAT · T_STLM_NUM_LD | 930(87.82%) · 129 | 990(88.47%) · 128 | 1,146(88.70%) · 143 |
| DB CPU(AWR) | 1.84/초 | 1.8/초(6,627.2초) | 1.8/초(6,636.9초) |
sequence load elapsed time | 9,676.7초(89%) | 10,376.3초(93.7%) | 10,277.5초(97.5%) |
| 막은 세션 | 175 · 62822 | 217 · 34019 | 223 · 41986 |
| 메모리 ASH의 가장 오래된 표본(런 끝난 직후) | (이 화를 시작할 때 이미 폭발 뒤) | 런 15.3분째 | 런 13.6분째 |
14.2 그 밖
| 무엇 | 값 | 출처 |
|---|---|---|
| ASH 버퍼 | 4,194,304바이트 · 간격 1,000ms | V$ASH_INFO · V$SGASTAT |
parallel_max_servers · parallel_min_servers | 1 · 1 | 00 |
AWR 저장소 SM/AWR(루트 · PDB) | 138.4MB · 57.4MB | V$SYSAUX_OCCUPANTS |
| Statspack 저장 | 132.8MB(설치 직후) → 138.8MB(스냅샷 15) | DBA_SEGMENTS |
| 대기 루프의 표본(R1 · PDB · S1 뒤 37분) | resmgr:cpu quantum 314장 | V$ACTIVE_SESSION_HISTORY(§15) |
| 원본 지문 | T_ORD_BIG 1,000,000 / 255,398,623,700 · T_ORD_STLM 100,000 / 25,519,833,100 · T_STLM_NUM 0. 세 런, 모든 시험의 앞뒤가 같다 | V_LD_FINGERPRINT |
측정 환경
Oracle AI Database 26ai Free Release 23.26.2.0.0 · cpu_count 2(호스트 32 CPU · 16코어) · control_management_pack_access DIAGNOSTIC+TUNING · AWR 1시간 · 8일 · job_queue_processes 1 · parallel_max_servers 1 · ASH 버퍼 4MB. 데이터: 16화 종료 상태.
15. 관찰자 효과 — 기다리는 스크립트도 표본에 찍힌다
| 실측 | 값 |
|---|---|
| L의 자동 ADDM 7위 PL/SQL Execution | SYS.DBMS_SESSION.SLEEP 271초 · 루트. 16화 02가 S2를 기다리며 0.5초마다 깨어난 루프 |
| L의 디스크 표본 중 루트의 sqlplus 포그라운드 | resmgr:cpu quantum 30장(≈ 300초) |
| R1의 PDB 표본 중 이 화의 첫 대기 루프 둘(1초 · 0.5초마다 깨어남) | resmgr:cpu quantum 314장(S1 뒤 37분 동안) |
| R2(02 최종판: 표시 시각까지 한 번에 잔다) | ADDM에 PL/SQL Execution Finding 없음 · AWR 루트 DB Time 175.60분(L 181.09 · R1 184.50) |
DBMS_SESSION.SLEEP 자체는 Idle 대기(PL/SQL lock timer)다. 그런데 깨어날 때마다 CPU 차례를 기다린다. CPU 2개를 세 세션이 넘치게 쓰는 중이라 리소스 매니저가 줄을 세우기 때문이다. 그 기다림(resmgr:cpu quantum)은 DB Time에 들어간다. 그래서 02 최종판은 표시 시각까지 한 번에 잔다(DBMS_SESSION.SLEEP(남은 초 − 1) + 마지막 1초만 0.1초씩). R1의 PDB 전체 숫자(DB time, ASH 리포트의 AAS)에는 이 314장이 섞여 있다. 이 화에서 R2를 정본으로 삼은 이유다. 부하 발생기 세션만 센 값(MODULE = LOAD_DRIVER)은 R1도 깨끗하다.
16. Free에서 못 잰 것 · 봉인한 것
| 못 잰 것 / 하지 않은 것 | 왜 | 대신 한 것 |
|---|---|---|
| SQL Monitor의 병렬 조건 · PX 감시 | Free에는 병렬 실행이 없다(힌트를 줘도 직렬 · parallel_max_servers 1) | ① 5초 · ③ 힌트 조건을 실측 · 병렬은 대규모 EE 환경을 다루는 별권에서 |
| 진짜 '팩 없는 서버' | Free에는 팩이 들어 있다 | 메모리에서만 NONE으로 내려 막히는 것과 남는 것을 실측하고 되돌렸다 |
| 운영의 6/17 | 운영 AWR 보존 8일. 이미 지워졌다(이야기 속 설정) | 같은 모양을 결정적인 부하로 다시 만들었다 |
IN_SEQUENCE_LOAD 깃발이 켜지는 조건 | 이 화에서 다 밝히지 못했다 | 켜지는 조합과 안 켜지는 조합을 실측(§9.1) · 그 칸으로 결론을 내지 않는다 |
RAC의 ASH(INST_ID · 인스턴스 간 블로킹) | 단일 인스턴스 | — |
| ADDM Top SQL의 SQL Tuning Advisor · SQL Profile · SPM | 18화의 몫 | Finding만 적어 두었다(§8.4) |
실시간 ADDM(DBMS_ADDM.REAL_TIME_ADDM_REPORT) · Emergency Monitoring | 이 화에서는 지나간 한 시간을 본다 | — |
17. 원천 좌표
전체 지도 →
00_자료준비/06_원서와_공식문서_지도.mdPDF 페이지 = 책 페이지 − 2. 성능고도화 I권 03장의 절 시작 페이지는 원천 목차(PDF p.8)로 다시 맞췄다. 09절 ASH는 책 p.220이다(16화 §17의 '224'는 고친다).
성능고도화 I권(주 원천 — 03장 오라클 성능 관리)
| 주제 | 책 페이지 |
|---|---|
| 07절 Response Time Analysis 방법론과 OWI | 205 |
| 08절 Statspack / AWR: (1) 기본 사용법 · (2) 리포트 분석 | 210 · 212 · 214 |
09절 ASH (Active Session History): 1초 샘플링 · ASH 버퍼 · V$ACTIVE_SESSION_HISTORY의 칸 · 블로킹 세션 · CURRENT_OBJ#의 주의점(대기 클래스) · 1/10을 AWR에 · 분석 순서(이벤트 추세 → 그 이벤트를 기다린 세션 → 블로킹 세션 → 그 시점의 SQL · 프로그램) | 220~224 |
10절 V$SQL | 225 |
오라클 공식문서
| 주제 | 매뉴얼 | 장 제목 |
|---|---|---|
| ASH 개념 · ASH 리포트 해석(Top Events · Top SQL · Activity Over Time) | tgdba | Analyzing Sampled Data |
| ADDM: Finding · Recommendation · Rationale · ADDM 뷰 | tgdba | Automatic Performance Diagnostics |
| Real-Time SQL Monitoring: 전제 · 시작 조건 | tgsql | Monitoring Database Operations |
ASH_REPORT_TEXT · CREATE_SNAPSHOT · DBMS_ADDM · DBMS_SQL_MONITOR | arpls | DBMS_WORKLOAD_REPOSITORY · DBMS_ADDM · DBMS_SQL_MONITOR |
V$ACTIVE_SESSION_HISTORY · DBA_HIST_ACTIVE_SESS_HISTORY · V$ASH_INFO · V$SQL_MONITOR | refrn | Dynamic Performance Views · Static Data Dictionary Views |
CONTROL_MANAGEMENT_PACK_ACCESS | refrn | Initialization Parameters |
| 진단 · 튜닝 팩 · 에디션 | dblic | Licensing Information |
| Statspack | ?/rdbms/admin/spdoc.txt | (컨테이너 안의 문서) |
다음 화가 여기서 가져가는 것
| 화 | 가져가는 것 |
|---|---|
| 18 | 진단 도구 4열 대응표와 이 화의 질문 셋(평균인가 · 횟수인가 · 언제인가)을 사내 튜닝 표준(T6)의 조문 후보로 |
| 18 | ADDM Top SQL Statements의 권고: SQL Tuning Advisor · Q2 보류안(08화 · 16화) · SPM |
| 18 | T5 2호의 규칙: ADDM 권고는 Rationale을 표본으로 다시 센 뒤에 채택한다 · 한 시간 리포트로 현업의 '몇 분'을 기각하지 않는다 |
| 18 | 그동안 쓴 리포트 전부(T1~T8): 이 화의 교차검증판처럼 지난 결론을 지우지 않고 옆에 정정을 다는 방식 |