테마
18화 · 파고들기
조회용 문서다. 처음 읽을 때는
1_사건.md만으로 충분하다. 이 문서의 숫자는 전부ora-hanorder(17화 종료 상태)와ora-newshop(졸업 과제, 이 문서에는 싣지 않는다)에서 잰 값이다. 스크립트 번호를 함께 적는다. 같은 스크립트를 두 번씩 돌려 멱등인지 확인했다. 두 번의 값이 다르면 범위로 적는다.
| 찾는 것 | 바로 가기 |
|---|---|
| W01 재측정: '동일 조건'이 불가능할 때 무엇을 같게 하나 · rev00이 오늘 빨라진 이유 | §1 W01 |
| SQL Plan Management: 베이스라인 · 계획 이력 · ACCEPTED · ENABLED · FIXED · 진화 | §2 SPM |
Free의 SPM 자동 기능: ORA-38153 · 건드리면 남는 흔적 | §3 자동 |
| SQL Tuning Advisor · SQL 프로파일: 권고 · 프로파일의 정체 · 서명 | §4 STA |
| 팩 라이선스 확인란: 팩을 끄면 막히는 것 · 에러 없이 사라지는 것 · 남는 것 | §5 팩 |
| Q2 rev02: Buffers가 같은데 리비전인 이유 · 옵티마이저가 왜 안 묶었나 | §6 Q2 |
| 리소스 매니저: 그룹 · 플랜 · 매핑 · 네 판 · 빈 값의 함정 | §7 RM |
| awrddrpt: 두 구간 비교 · DB time이 같은 이유 · 가장 큰 %Diff가 가짜일 때 | §8 awrddrpt |
| 표준은 어떻게 만들어졌나: 승격 규칙 · 센 것 · 떨어진 후보 | §9 승격 |
| 앞 화들이 넘긴 것: 하나씩의 결론 | §10 넘겨받은 것 |
| 측정값 전부 · 측정 환경 | §11 측정 |
| Free에서 못 잰 것 · 봉인한 것 · 이 화가 남긴 흔적 | §12 한계 |
| 원천 좌표 | §13 좌표 |
1. W01 재측정 — 같은 규약, 지금 상태
1.1 '동일 조건'을 무엇으로 바꿨나
3월 4일의 원장에는 전제가 적혀 있다. 그중 두 가지가 지금은 다르다.
| 원장의 칸 | 3월 4일 (01화) | 7월 8일 (18화) | 되돌릴 수 있나 |
|---|---|---|---|
T_ORD_BIG 인덱스 | 0개 | 3개 (IX_ORD_BIG_DT · _DT_AMT 02화 · _DT_SEQ 09화) | 지우면 되지만, 그러면 3월의 숫자를 다시 얻을 뿐이다 |
ORD_ST 통계 | 히스토그램 없음 | FREQUENCY (10화 정책서 · METHOD_OPT 선호 FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_ST) | 같다 |
| 부하 | 단일 세션 | 단일 세션 | 같게 했다 |
| 측정 도구 | STATISTICS_LEVEL=ALL + V$SQL 태그 | 같다 · Buffers는 V$SQL_PLAN_STATISTICS_ALL의 가장 큰 줄 (MAX(last_cr + last_cu)) | 같게 했다 |
| 데이터 | 1,000,000 / 100,000 / 200,000 / 2,000 | 같다 (원본 지문 V_LD_FINGERPRINT · 00) | — |
개정계획에 적힌 그대로다. "동일 조건 재현"이 아니라 "01화 원장의 6조각을 현재 상태에서 같은 규약으로 재측정"이다. 달성할 수 없는 조건을 졸업 기준으로 두면 졸업 판정이 의미가 없어진다. 그래서 두 가지를 잰다. 같은 SQL(rev00)을 오늘 재고, 오늘의 운영판(최신 rev)도 오늘 잰다. 앞의 것으로 '바닥이 바뀐 몫'을, 뒤의 것으로 '판을 바꾼 몫'을 가른다.
1.2 Q1 ~ Q4c (실습/02 · 스크립트 두 번 · 두 번째 판 — 01화의 '두 번째 실행'과 같은 조건 · 두 판의 Buffers는 같았다)
| 조각 | 01화 Buffers | rev00 · 오늘 | 계획 해시 (rev00 · 오늘) | 최신판 · 오늘 | 계획 해시 (최신) | 결과 |
|---|---|---|---|---|---|---|
| Q1 | 5,827 | 5,826 | 914281838 (01화와 같다) | 91 (rev03) | 92368507 | 27,390건 · 6,982,065,200 |
| Q2 | 1,026,849 | 1,026,849 | 2289018667 (같다) | 5,868 (rev01) | 2292855265 | 3행 |
| Q3 | 841 | 840 | 119122671 (같다) | 839 (rev01) | 2231864090 | rev00 금액 ×50 · rev01 원장과 같다 |
| Q4 (100페이지) | 5,826 | 1,007 | 3040227993 (01화 513693594) | 14 (rev03) | 3610158121 | 10행 (rev03은 11번째로 '다음 있음') |
| Q4c | 5,826 | 3,365 | 97409517 (01화 914281838) | — | — | 1,000,000 |
| 합 | 1,045,169 | 1,037,887 (−0.7%) | 6,812 (−99.3%) |
- Q4 rev00이 1,007이 된 이유: 09화의
IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ)가 rev00의 정렬 키(ORDER BY ORD_DT DESC, ORD_SEQ DESC)와 같다. 그래서 계획이SORT ORDER BY STOPKEY + TABLE ACCESS FULL에서INDEX FULL SCAN DESCENDING + COUNT STOPKEY로 바뀌었다. 09화 rev01은 SQL이 rev00과 한 글자도 다르지 않은 판(인덱스만 더한 판)이었으므로, 오늘의 rev00은 사실상 09화 rev01이다. - Q4c 3,365: 총건수를 한 번 셀 때 테이블(5,826블록) 대신
IX_ORD_BIG_DT_SEQ전체를INDEX FAST FULL SCAN으로 읽는다(ORD_SEQ가 NOT NULL이라 인덱스에 100만 행이 다 들어 있다. 09화 머리말 참고). - Q1 rev00의 두 번째 자식: 두 번째 실행부터 자식 커서 1이 생긴다(statistics feedback used for this statement). 첫 실행의
E-Rows2,500이 틀렸다는 것을 옵티마이저가 기억했다가 27,390으로 고쳐 잡는다. 계획과 Buffers는 같다(5,826 · 914281838). 3월의 원장은 두 번째 실행 하나만 쟀기 때문에 이 줄이 없었다. - ms: 01화 Q1 112 · Q2 774 · Q3 14 · Q4 42 · Q4c 9 / 오늘 rev00 93 ~ 99 · 751 ~ 761 · 12 ~ 14 · 2 · 53 / 최신 5 · 241 ~ 244 · 11 ~ 12 · 1 ~ 2. T0는 ms를 증거로 치지 않는다(Q4c는 블록이 줄었는데도 ms가 9 → 53으로 늘었다).
1.3 P1 · N1 (실습/02 [4] · 복사본 T_ORD_STLM_C18 · SQ_STLM_SEQ_C18 · 같은 조건 두 판 · 둘째 판)
3월의 원장은 P1과 N1에 ms만 적었다. 오늘은 그 옆에 일의 양을 붙였다. 원본 정산 테이블에는 쓰지 않는다. 복사본에 적재하고, 결과가 원본과 같은지(100,000행, 25,519,833,100) 확인한 뒤 지운다.
| 조각 | 판 | ms | CPU ms | 논리 읽기 | redo MB | undo MB | 커밋 |
|---|---|---|---|---|---|---|---|
| P1 (10만) | 01화 rev00 | 2,480 | — | — | — | — | — |
| rev00 · 오늘 | 850 ~ 1,010 | 430 ~ 470 | 110,130 ~ 110,420 | 28.60 ~ 28.62 | 6.49 ~ 6.50 | 100 ~ 101 | |
| rev02 · 오늘 | 100 ~ 110 | 70 | 2,869 ~ 2,961 | 0.08 ~ 0.10 | 0.02 ~ 0.03 | 2 | |
| N1 (100회) | 01화 rev00 | 440 | — | — | — | — | — |
| rev00 · 오늘 | 80 ~ 90 | 80 ~ 90 | 56,800 (= 568 × 100) | 0 | 0 | 0 | |
| rev01 · 오늘 | 0 | 0 ~ 10 | 0 ~ 23 (20번에 한 번 캐시 채우기) | 0 | 0 | 0 |
- P1 rev00의 2,480 → 1,000 안팎: 같은 코드다. 3월의 값에는 '설치 후 두 번째 실행'이라는 조건만 남아 있다. 초가 왜 달라졌는지는 이 원장으로 가를 수 없다. 그래서 오늘 열에는 논리 읽기와 redo, 커밋 수를 같이 적었다. 다음 재측정은 그 열들로 비교한다.
- N1 rev00 논리 읽기 56,800: 번호 하나를 매길 때마다
T_ORD_STLM전체(568블록, 12화)를 읽는다는 뜻이다.
1.4 그때는 왜 옳았고, 지금은 왜 다른가 — 조각마다 한 줄
| 조각 | 3월의 숫자가 옳았던 이유 | 지금 숫자가 다른 이유 | 근거 |
|---|---|---|---|
| Q1 | 인덱스가 0개이고 컬럼이 가공돼 있어서 어떤 인덱스로도 못 탄다 | SQL이 바뀌었다(가공 제거 · 커버링 · 바인드) | 02 · 03화 |
| Q2 | 힌트가 그대로 실행됐다. 회의록의 3초 → 0.4초는 그 서버에서는 사실이었다 | 힌트를 뗐다. 2만 건에서도 해시가 138배 이겼다. 손해가 10ms라 안 보였을 뿐이다 | 06화 |
| Q3 | 블록 수는 옳았다 | 블록이 아니라 답이 바뀌었다: 평가 다건 허용 뒤 금액 ×50 | 08화 |
| Q4 · Q4c | 인덱스가 0개라 클릭할 때마다 전체 정렬과 전체 카운트 | 인덱스(바닥) 덕에 rev00까지 빨라졌고, 나머지는 판(포스트잇 · 총건수 제거 · 바인드)을 바꿔서 얻었다 | 09 · 11화 |
| P1 | 행마다 INSERT, 1,000건마다 커밋: 2020.07 언두 부족 장애 뒤에는 옳은 처방이었다 | 한 문장 + 직접 경로로 바꿔 언두를 거의 안 쓰게 됐다 | 13 · 14 · 15화 |
| N1 | MAX+1: 동시에 받는 사람이 없을 때는 옳았다 | 시퀀스로 업무 트랜잭션에서 떼어 냈다 | 12화 |
2. SQL Plan Management — 합격증이 붙은 계획만 운행한다
2.1 말 셋
| 말 | 뜻 | 1층 엘리베이터 홀로 |
|---|---|---|
| SQL plan baseline | 한 문장(서명)마다 받아들인(ACCEPTED) 계획을 모아 둔 묶음. 옵티마이저는 계획을 만든 뒤, 그 계획이 베이스라인에 없으면 쓰지 않고 베이스라인 안에서 가장 싼 계획을 쓴다 | 합격증이 붙은 승강기만 운행한다 |
| plan history | 옵티마이저가 그 문장에 만든 적이 있는 계획 전부. 받아들인 것과 대기 줄(ACCEPTED = NO)을 합친다 | 들여왔지만 검사를 기다리는 승강기 |
| evolve (진화) | 대기 줄의 계획을 실제로 돌려 받아들인 계획과 비교하고, 기준을 넘으면 받아들인다 | 시험 운행 · 검사 |
| 속성 | 값 | 뜻 (이 화의 실측) |
|---|---|---|
ACCEPTED | YES / NO | YES만 운행된다. 새 계획은 NO로 들어온다(ORIGIN = AUTO-CAPTURE) |
ENABLED | YES / NO | NO면 그 계획은 없는 것과 같다. 베이스라인의 모든 계획이 NO면 베이스라인 없이 계획을 세운다(§2.4) |
FIXED | YES / NO | 실측한 것은 속성이 YES로 바뀐 데까지다(§2.4 [8]). '고정 계획이 우선하고 새 계획을 대기 줄에 더하지 않는다'는 tgsql SQL Plan Management의 설명이고, 이 화에서는 재지 않았다 |
REPRODUCED | YES / NO | 그 계획을 지금 다시 만들 수 있나(인덱스가 사라지면 NO) |
계획을 지키는 장치는 셋이다. 힌트(코드 안), SQL 프로파일(코드 밖 · 튜닝 팩 · §4), SQL plan baseline(코드 밖 · 팩 아님 · §5)이다. 셋 중 '새 계획이 더 나은지 검사하는 절차'가 있는 것은 베이스라인뿐이다.
2.2 베이스라인을 만드는 길 (이 화가 쓴 것)
| 길 | 어떻게 | 실측 |
|---|---|---|
| 커서 캐시에서 | DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id, plan_hash_value) | ORIGIN = MANUAL-LOAD-FROM-CURSOR-CACHE · ACCEPTED YES |
| 자동 캡처 | OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE (세션에서만 켰다) | 두 번째 실행에 AUTO-CAPTURE · ACCEPTED YES. 첫 실행 뒤 0개 → 두 번째 뒤 1개 |
| 베이스라인이 이미 있는 문장의 새 계획 | (캡처 설정과 상관없이) 옵티마이저가 다른 계획을 만들면 | AUTO-CAPTURE · ACCEPTED NO: 대기 줄 |
⚠️ 자동 캡처의 '두 번째'는 오라클의 문장 기록(SQL statement log)으로 센다. 그 기록은 베이스라인을 지워도 남는다. 같은 글자의 문장을 다시 돌리자 첫 실행에서 바로 잡혔다(준비하며 실측). 그래서
03[9]는 돌릴 때마다 태그에 시각을 붙여 처음 보는 문장을 만든다(S18_AC_시분초).
2.3 장면 A — 합격증이 좋은 계획을 막는다 (03 [1] ~ [3] · 복사본 T_ORD_BIG_C18)
| 순서 | 한 일 | 계획 | Buffers | Note · 베이스라인 |
|---|---|---|---|---|
| [1] | 인덱스 없는 복사본 · Q1 rev03 꼴 · FULL을 커서 캐시에서 올림 | TABLE ACCESS FULL · 937136365 | 5,821 | …863f0086 MANUAL-LOAD · YES |
| [2] | 복사본에 IX_C18_DT_AMT (ORD_DT, ORD_AMT) (02화 rev02의 인덱스) | 여전히 FULL · 937136365 | 5,821 | SQL plan baseline …863f0086 used · 대기 줄에 …e79918c6 AUTO-CAPTURE · NO |
| [3] | CREATE_EVOLVE_TASK → EXECUTE_EVOLVE_TASK → IMPLEMENT_EVOLVE_TASK | INDEX RANGE SCAN · 4132545450 | 91 | 두 계획 다 YES · …e79918c6 used |
진화 리포트(FINDINGS):
1. The plan was verified in 0.25000 seconds. It passed the benefit criterion
because its verified performance was 63.70970 times better than that of the
baseline plan.(네 번 돌려 63.70970 ~ 63.71093이 나왔다. 시험 운행은 실제로 실행하기 때문에 매번 조금씩 다르다.)
2.4 장면 B — 합격증이 나쁜 계획을 막는다 · 10화의 그 일요일 밤 (03 [4] ~ [8])
10화 실습/01과 같은 순서를 복사본에서 밟는다. TRUNCATE → 빈 테이블의 통계(NUM_ROWS 0 · STALE_STATS YES) → INSERT /*+ APPEND NO_GATHER_OPTIMIZER_STATISTICS */로 100만 행을 넣는다. 통계는 0행인 채로 남는다(03 [5]: NUM_ROWS 0 · BLOCKS 0 · REAL_ROWS 1000000).
| 순서 | 상태 | 계획 | T_ORD_BIG_C18 E-Rows | Buffers | 비고 |
|---|---|---|---|---|---|
| [4] | 정상 통계 · HASH를 베이스라인으로 | HASH JOIN · 590468458 | 1000K | 5,863 | …ed7345b9 MANUAL-LOAD |
| [5] | 빈 테이블 통계 · 베이스라인 켬 | HASH JOIN · 590468458 | 1 | 5,868 | …ed7345b9 used · 대기 줄에 NL …6f0725d0 NO |
| [6] | 같은 통계 · 내 세션만 OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE | NESTED LOOPS · 3504423805 | 1 | 1026K | PK_M_CUS Starts 1000K · 21,011 · M_CUS 1000K |
| [7] | 진화: NL을 시험 운행 | — | — | — | failed the benefit criterion … 0.33281 times worse (네 번: 0.31727 ~ 0.33281) · NL은 대기 줄에 남는다 |
| [8] | FIXED = YES (HASH) → ENABLED = NO (HASH) | NESTED LOOPS · 3504423805 | 1 | 1026K | ENABLED를 내리자 베이스라인이 없을 때처럼 계획을 골랐다. 그 일요일 밤의 계획이 돌아왔다 |
| — | 되돌림: ENABLED YES · FIXED NO · 복사본 통계를 실제로 다시 모음 | NUM_ROWS 1000000 |
베이스라인이 막아 준 것은 '통계가 틀려서 생긴 새 계획'이다. 통계가 틀린 것 자체는 고치지 않는다(E-Rows 1은 그대로다). 다른 SQL은 여전히 그 통계를 보기 때문에, 베이스라인이 있어도 10화의 정책서(적재 직후 수집 · 잠금)는 필요하다.
2.5 베이스라인의 유효기간
| 질문 | 답 (이 화) |
|---|---|
| 베이스라인은 '옳은 계획'인가 | 아니다. 그때의 데이터에서 시험을 통과한 계획이다. M_CUS가 2만 명이 되어 NL이 이기는 날이 와도, 진화를 돌리지 않으면 HASH가 계속 운행된다 |
| 누가 진화를 돌리나 | EE에는 자동 과제(SYS_AUTO_SPM_EVOLVE_TASK)가 있다. Free에는 없으므로(§3) 사람이 돌린다. T8 정기 점검에 줄 하나를 더한다: 대기 줄(ACCEPTED = NO)이 있는 베이스라인 → 진화 |
| 무엇을 같이 적나 | 힌트 유효기간 대장과 같은 칸: 그때의 규모 · 재검토 조건(T6 제7조 · 마지막 조항) |
3. Free의 SPM 자동 기능 — 없다
실습/04_SPM_자동기능_SYSDBA.sql(읽기 + '켜 보기' 한 번).
| 무엇 | 루트 (CDB$ROOT) | PDB (FREEPDB1) |
|---|---|---|
AUTO_SPM_EVOLVE_TASK | OFF · 켜면 ORA-38153: Software edition is incompatible with SQL plan management. | OFF · 켜도 에러 없이 그대로 OFF (준비하며 실측) |
SYS_AUTO_SPM_EVOLVE_TASK | INITIAL · 실행 0회 | INITIAL · 실행 0회 |
SYS_AUTO_SQL_TUNING_TASK | INITIAL · 실행 0회 · ACCEPT_SQL_PROFILES = FALSE | — |
| 자동 작업 창 | Auto SPM Task · Automatic SQL Tuning Advisor Task: 간격 3600 · LAST_RUN 없음 | — |
⚠️
DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'OFF')는 부르지 않아야 한다. 값은 이미OFF라 바뀌지 않지만,DBA_SQL_MANAGEMENT_CONFIG.LAST_MODIFIED에 그 시각이 찍히고 되돌릴 방법이 없다(이 화를 준비하며 한 번 불렀고, 그 흔적이 연습 DB에 남아 있다 · §12).04는 읽기와ON한 번만 한다. 루트의ON은 에러로 끝나 아무것도 바꾸지 않는다. 수동 SPM(캡처 · 진화 · 속성)은 Free에서 다 된다(§2). 자동만 없다.
4. SQL Tuning Advisor와 SQL 프로파일 — 권고문은 검사필증이 아니다
4.1 과제 여섯 (실습/05 · 커서 캐시에서 · 각 300초 한도 · 실제 1 ~ 4초)
17화 ADDM Top SQL Statements에 나온 SQL_ID 셋은 AWR 보존 기간이 지나도 다시 만들 수 있다. 부하 발생기와 같은 글자의 PL/SQL 정적 SQL을 한 번 돌리면 같은 SQL_ID가 커서 캐시에 올라온다(05 [1]).
| 과제 | 대상 | SQL_ID | 권고 | 예상 이득 |
|---|---|---|---|---|
| T18_ADDM_Q2 | 부하 발생기 Q2 (rev01 · 스칼라 없음) | 8ac5jyu193w2j | 없음: There are no recommendations | — |
| T18_ADDM_Q3 | Q3 rev01 | d8v5hzdaa2zt4 | 없음 (FINDINGS 절 자체가 없다) | — |
| T18_ADDM_Q1 | Q1 rev03 | 9jg2gb66dwsvu | 없음 | — |
| T18_R0_Q1 | Q1 rev00 (TO_CHAR(ORD_DT)) | 0b364yrjnvs43 | RESTRUCTURE SQL × 2: expression on indexed column … prevents the optimizer from selecting indices · Rewrite the predicate … Alternatively, create a function-based index | — |
| T18_R0_Q2 | Q2 rev00 (LEADING(o c) USE_NL(c)) | gzz2aj34mafrf | SQL PROFILE × 1 | 99.42% |
| T18_R0_Q3 | Q3 rev00 (금액 ×50) | 8wuqujhqbs4vn | 없음 | — |
- 도구는 빠른지를 본다. 맞는지는 보지 않는다. 50배로 틀린 Q3 rev00에 아무 권고가 없다(08화가 고친 것은 성능이 아니라 답이었다).
- Q1 rev00의 권고는 02화와 같다. '가공을 풀어 쓰라, 아니면 함수 기반 인덱스'다. 둘 중 어느 쪽이 옳은지(02화는 앞쪽, 03화는
VARCHAR2날짜에서 둘 다 검토)는 도구가 정하지 않는다.
4.2 Q2 rev00의 프로파일 — 검증 결과 (05 · 두 번)
Original Plan With SQL Profile % Improved
Elapsed Time (s): .741584 .238911 67.78 %
CPU Time (s): .741426 .238321 67.85 %
Buffer Gets: 1026849 5868 99.42 %
Notes: 1. Statistics for the original plan were averaged over 2 executions.
2. Statistics for the SQL profile plan were averaged over 5 executions.(첫 판 · 준비 때는 .666733 → .149735, 77.54%였다. 초는 매번 다르고, Buffer Gets는 같다.)
4.3 프로파일의 정체
| 무엇 | 실측 |
|---|---|
받은 뒤 (ACCEPT_SQL_PROFILE) | HASH JOIN · 2292855265 (rev01과 같은 계획) · Buffers 5,869 · Note: SQL profile PROF_T18_Q2 used |
프로파일 안의 힌트 (DBA_SQL_PROFILES.SIGNATURE → SYS.SQLOBJ$DATA.COMP_DATA의 XML) | OPTIMIZER_FEATURES_ENABLE(default) · IGNORE_OPTIM_EMBEDDED_HINTS: 두 줄뿐 |
| 튜닝 리포트의 힌트 보고서 | LEADING(o c) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS · USE_NL(c) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS |
| 띄어쓰기 · 대소문자만 다른 같은 문장 | 프로파일이 붙는다(SQL profile PROF_T18_Q2 used). 프로파일은 글자가 아니라 정규화한 서명(EXACT_MATCHING_SIGNATURE)에 붙는다 |
지운 뒤 (DROP_SQL_PROFILE) | NESTED LOOPS · 2289018667 · 1026K |
프로파일이 한 일은 06화에 사람이 한 일(힌트를 뗀 것)을 코드 밖에서 한 것이다. 소스에는
USE_NL이 그대로 남는다. 다음 사람은 소스를 읽고 NL이라고 믿겠지만, 계획을 떠 보면 해시이고, 그 이유는 딕셔너리 안에 있다. 그래서 18화에서는 기각했다(T1 13호). 기각한 근거는 셋이다. ① 정체(힌트를 무시하라는 두 줄) ② 팩 설정 하나로 에러 없이 사라진다(§5) ③ 같은 계획을 코드(rev01)가 이미 가지고 있다. 프로파일이 옳은 자리도 있다. 소스를 고칠 수 없는 패키지 제품이나 긴급 조치가 그렇다. 그때도 T6 제10 · 16조를 따른다. 정체를 적고, 팩 확인란을 채우고, 코드로 고칠 날짜를 적는다.
5. 팩 라이선스 확인란 — 막히는 것 · 에러 없이 사라지는 것 · 남는 것
실습/06_팩_확인란_SYSDBA.sql은 CONTROL_MANAGEMENT_PACK_ACCESS를 메모리에서만 바꾼다(SCOPE = MEMORY · spfile은 그대로 · 17화 10과 같은 방법). 판마다 Q2 rev00과 S18_Q1의 커서를 DBMS_SHARED_POOL.PURGE로 지우고 잰다.
| 판 | (1) STA 과제 만들기 | (2) SPM 베이스라인 올리기 | (3) 받아 둔 프로파일 (Q2 rev00) | (4) 베이스라인 (S18_Q1) |
|---|---|---|---|---|
DIAGNOSTIC+TUNING | OK | OK | 쓰인다 · 2292855265 · 5,869 · SQL profile used | 쓰인다 · baseline used |
DIAGNOSTIC | ORA-13717: Tuning Package License is needed for using this feature. | OK | 에러 없이 무시 · 2289018667 · 1026K · Note에서 프로파일 줄이 빠진다 | 쓰인다 |
NONE | ORA-13717 | OK | 에러 없이 무시 · 1026K | 쓰인다 |
DIAGNOSTIC+TUNING (되돌림) | OK | OK | 쓰인다 · 5,869 | 쓰인다 |
| 마지막 확인 | 메모리 DIAGNOSTIC+TUNING (ISMODIFIED SYSTEM_MOD) · spfile DIAGNOSTIC+TUNING · 프로파일 0 |
왜 커서를 지우나? 지우지 않으면 앞 판에서 만든 커서를 그대로 다시 쓴다. 팩을 꺼도 프로파일로 만든 계획이 계속 보여서 "팩을 꺼도 프로파일은 쓰인다"는 틀린 결론이 나온다(준비하며 실제로 그렇게 나왔다). 17화와 합쳐 보면 이렇다.
NONE은 AWR · ASH 리포트 ·DBA_HIST_*를 막지 않았고(17화), 튜닝 팩의 산출물은 에러 없이 꺼뜨렸다(18화). 둘 다 "막혔으니 괜찮다"가 성립하지 않는 경우다. 팩 확인란의 서식 → T6 §4.
6. Q2 rev02 — 블록이 같은 리비전
실습/09_Q2_보류안_T1.sql · 리비전/Q2_rev02.sql · T1 12호.
6.1 계획 둘
rev01 2292855265 rev02 653275336
HASH GROUP BY 3 5,869 HASH GROUP BY 3 5,868
HASH JOIN 1000K 5,857 1685K (0) HASH JOIN 2,000 5,856 1565K (0)
TABLE ACCESS FULL M_CUS 2000 30 TABLE ACCESS FULL M_CUS 2000 30
TABLE ACCESS FULL T_ORD_BIG 1000K 5,827 VIEW 2,000 5,826
HASH GROUP BY 2,000 5,826 1489K (0)
TABLE ACCESS FULL T_ORD_BIG 1000K 5,8266.2 CPU (10회 묶음 × 3 · 스크립트 두 번 · CPU used by this session · 1/100초)
| 묶음 1 | 2 | 3 | 4 | 5 | 6 | |
|---|---|---|---|---|---|---|
| rev01 | 115 | 117 | 116 | 115 | 115 | 115 |
| rev02 | 33 | 34 | 33 | 33 | 33 | 34 |
| 다른 날 rev01 | 119 | 119 | 121 | 117 | 115 | 118 |
| 다른 날 rev02 | 32 | 33 | 32 | 35 | 35 | 35 |
위 표의 기울임 두 줄은 다른 날, 같은 17화 종료 상태에서 같은 스크립트를 두 번 돌려 다시 잰 값이다. 묶음마다 몇 cs씩 흔들려도 3.3 ~ 3.8배라는 범위는 같았다. 16화 §13.2에서 잰 세 문장(부하 발생기 Q2 29 ~ 30 · rev01 113 ~ 116 · 보류안 29 ~ 32)과도 같은 수준이다.
6.3 왜 CPU가 3분의 1이 되나 · 왜 옵티마이저가 안 했나
- 해시 조인에서 100만 행을 하나씩
M_CUS해시 테이블에 맞춰 보던 일이 2,000번으로 준다. 대신T_ORD_BIG100만 행을CUS_ID로 묶는 일(HASH GROUP BY)이 더해지는데, 묶는 쪽이 더 싸다(해시 테이블이 2,000칸). - 옵티마이저도 같은 변환(Group-by Placement ·
VW_GBC)을 할 줄 안다. 지역명 스칼라 서브쿼리가 없는 부하 발생기의 Q2에서는 옵티마이저가 스스로 묶었다(16화 §13.2, 계획에VIEW VW_GBC_5). 운영 rev01에서는 스칼라 한 줄 때문에 묶지 않았다. 뜻이 같은 문장인데 모양 하나가 변환을 막은 것이다. 그래서 사람이 묶었다.
6.4 정합성 (08화 체크리스트)
| # | 항목 | 결과 |
|---|---|---|
| A | 기준 | rev01 (06화 이후 운영의 답) |
| B | 건수 · 합계 · MINUS 양방향 | 3 = 3 · 255,398,623,700 = 255,398,623,700 · 0 / 0 |
| C | 잘라 내기 전 전체 | (TOP N 없음 · B와 같다) |
| D | 조인마다 1쪽 | M_CUS는 CUS_ID로 1행 (PK) · 인라인 뷰도 CUS_ID로 1행 (GROUP BY) |
| E | 경계 (복사본 M_CUS_C18 · M_RGN_C18) | ① 지역 코드 이중 적재 → 두 판 다 ORA-01427 ② 지역 코드 고아 → 두 판 같은 답 (MINUS 0 / 0) |
| F | 계획 해시와 상관없이 | 해시가 다르다(2292855265 · 653275336). 그래서 B와 E를 다 돌렸다 |
| G | 변환을 근거로 쓰지 않았나 | 썼다면 '옵티마이저가 하니 괜찮다'가 됐을 것이다. 옵티마이저가 하지 않았다는 사실을 근거로 직접 썼다 |
| H | 영향 분석 | 이 화면의 문장 하나 (6조각 중 Q2) |
6.5 판 번호 — Buffers가 같은데 왜 rev02인가
T0는 Buffers N → M으로 판정한다. 그 규약으로는 rev02라고 할 근거가 없다. 08화가 이 판을 '보류안'으로 둔 것도 그래서다. 18화에서 바뀐 것은 두 가지다.
- T6 제2조: Buffers로 안 보이는 값(CPU)을 같은 조건으로 재서 함께 적으면 근거가 된다(6묶음 · 두 번).
- 그 값이 시스템에서 얼마짜리인지 확인됐다: 16화 T5-001에서 부하 모델의 Q2가 DB CPU의 47.5%였다(운영 문장이면 더 크다 · 16화 §13.3).
그래서 리비전 대장 §3에 규칙 한 줄을 더했다. Buffers가 같아도, T6 제2조의 값이 같은 조건으로 바뀌고 그 몫이 시스템에서 확인되면 번호를 올린다.
부하 발생기의 Q2는 고치지 않는다(16화가 18화에 넘긴 결정). 그 문장은 처음부터 스칼라가 없는 모델이라 실행당 CPU가 이미 rev02 수준이다(29 ~ 30cs). rev02가 운영에 나가면 모델과 운영의 차이(T7 9번 칸 첫 줄)는 오히려 사라진다. 고치면 16 · 17화의 런을 같은 인자로 다시 만들 수 없게 된다.
7. 리소스 매니저 — 비율을 약속했는데 지키지 않았다
7.1 만든 것 (실습/07a_리소스플랜_만들기_SYSDBA.sql · PDB FREEPDB1 안에서)
| 객체 | 정의 |
|---|---|
| 소비자 그룹 | C18_ONLINE_GRP · C18_BATCH_GRP |
플랜 C18_DAYTIME_PLAN | ONLINE MGMT_P1 90 · BATCH MGMT_P1 10 · OTHER_GROUPS MGMT_P2 100 |
플랜 C18_CAP_PLAN | 위와 같고 BATCH에 UTILIZATION_LIMIT 10 (상한) |
| 매핑 | MODULE_NAME: LOAD_DRIVER → ONLINE (부하 발생기 · 15화) · MONTH_CLOSE → BATCH (07_배치_일꾼.sql이 DBMS_APPLICATION_INFO.SET_MODULE로 설정) · 그룹 전환 권한 sqlp |
| 기록 테이블 | SQLP.T_C18_BATCH_LOG: 일꾼이 분마다 판 수 · 경과 · CPU를 적는다 |
만들고 지우는 일은 전부 보류 영역(
CREATE_PENDING_AREA→VALIDATE→SUBMIT) 안에서 한다. 지울 때는 플랜마다DELETE_PLAN을 부른 뒤DELETE_CONSUMER_GROUP을 부른다. 한 그룹이 두 플랜에 들어 있으면DELETE_PLAN_CASCADE가ORA-29388로 멈춘다(준비하며 실측).
7.2 네 판 (07_격리_세_판.sh · 판마다 5분 · 센 것은 1 ~ 4분 · 두 번 돌림)
| 판 | 플랜 (PDB · SCOPE = MEMORY) | 온라인 1분당 실행 | 평균 ms | Q2 평균 ms | 배치 1분당 판 · 판당 경과 |
|---|---|---|---|---|---|
| A 온라인만 | DEFAULT_PLAN | 41,741 (첫 판 42,464) | 2.87 | 33.03 | — |
| B + 배치 둘 | DEFAULT_PLAN | 20,497 (20,619) · −51% | 5.83 | 68.61 | 991 (1,015) · 12.1cs |
| C + 배치 둘 | C18_DAYTIME_PLAN (90 / 10) | 23,754 (24,594) · B의 +16 ~ 19% | 5.03 | 55.72 | 836 (834) · 14.3cs |
| D + 배치 둘 | C18_CAP_PLAN (+ 상한 10%) | 23,321 (23,737) | 5.12 | 57.11 | 861 (866) · 13.9cs |
판이 끝난 직후 V$RSRC_CONSUMER_GROUP | 배치 그룹 CPU 초 | 온라인 그룹 CPU 초 | 배치 몫 |
|---|---|---|---|
| C (90 / 10) | 258 | 349 | 42.5% |
| D (상한 10%) | 265 (첫 판 266) | 342 (340) | 43.7% |
| 메모리 ASH (모듈별 · 표본 ≈ 초) | 온라인 CPU 대기 % | 배치 CPU 대기 % |
|---|---|---|
| A | 0 (480 / 480 ON CPU) | — |
| B | 51.7 | 47.7 |
| C | 42.1 | 59.0 |
| D | 41.5 | 56.3 |
읽는 법. 판 B에서 일하는 세션이 넷(온라인 2 · 배치 2)이 되자 CPU 두 개를 반씩 나눠 썼다. 온라인 처리량이 정확히 절반이 된 것은 그래서다. 플랜을 켜자 대기의 기울기가 바뀌었다(온라인 52 → 42% · 배치 48 → 59%). 플랜은 일하고 있다. 다만 90 / 10은커녕, 상한 10%를 건 그룹이 CPU의 44%를 썼다. 비율이 약속 근처에도 가지 않았다.
7.3 확인한 것 · 못 밝힌 것
| 의심 | 확인 | 결과 |
|---|---|---|
| 세션이 그룹에 안 들어갔다 | 20초째 V$SESSION.RESOURCE_CONSUMER_GROUP | 들어갔다 (LOAD_DRIVER → ONLINE 2 · MONTH_CLOSE → BATCH 2) |
| 플랜이 안 켜졌다 | V$RSRC_PLAN · V$PARAMETER | C18_DAYTIME_PLAN / C18_CAP_PLAN · IS_TOP_PLAN TRUE · CPU_MANAGED ON |
| 매핑 속성 탓 | (준비하며) CLIENT_ID로 매핑한 판 | 같다: 온라인 24,348 /분 · 배치 CPU 256초 vs 온라인 351초 |
resource_manager_cpu_scope | INSTANCE_ONLY | 서버 전체 CPU 중 어디까지 쓰는지 정하는 설정이다. 그룹 사이 비율과는 다른 이야기 |
| 대기가 안 걸렸다 | ASH resmgr:cpu quantum | 걸렸다 (위 표) |
| 왜 비율이 지켜지지 않나 | — | 밝히지 못했다. 이 Free는 CDB 차원에서 CPU 2개를 리소스 매니저로 지키는데(16화), PDB 플랜이 그 안에서 어떻게 겹치는지를 이 화의 시험으로는 가를 수 없었다 |
판정(T3 3호). 잰 것은 '이 장비에서 이 플랜이 약속대로 나누지 않았다'는 데까지다. 그래서 리소스 매니저를 근거로 낮 실행을 허락하지 않는다. 대신 시간으로 나눈다(19:00 이후). 운영 EE에서 같은 네 판을 재기 전에는 플랜을 격리의 근거로 쓰지 않는다 → 별권.
7.4 ⚠️ 빈 값으로 끄면 — INTERNAL_PLAN (준비하며 실측)
| 한 일 | 결과 |
|---|---|
PDB에서 ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '' SCOPE = BOTH | 돌던 플랜이 이 PDB의 원래 값 DEFAULT_PLAN으로 돌아가지 않고 INTERNAL_PLAN으로 바뀐다 · PDB의 spfile(pdb_spfile$)에 줄이 생긴다 |
ALTER SYSTEM RESET RESOURCE_MANAGER_PLAN | ORA-32010 |
| 되돌린 방법 | SET … = 'DEFAULT_PLAN' SCOPE = SPFILE → RESET … SCOPE = SPFILE → 메모리 SET … = 'DEFAULT_PLAN' SCOPE = MEMORY: V$SPPARAMETER.ISSPECIFIED FALSE (pdb_spfile$의 줄은 남는다 · §12) |
그래서 이 화의 스크립트는 플랜을 메모리에서만 바꾸고, 끌 때는 DEFAULT_PLAN으로 되돌린다(07 · 99). 17화의 팩 설정과 같은 방침이다. 바꾸는 것은 메모리뿐이고 spfile은 그대로 둔다.
8. awrddrpt — 옛 판과 지금 판
8.1 무엇을 비교했나 (실습/08_옛판_지금판.sh · 08 · 08b · 08c)
@?/rdbms/admin/awrddrpt는 실행하면 두 구간(스냅샷 네 개)을 묻는다. 이 화에서는 그 질문을 함수 하나로 대신했다. DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_TEXT(dbid, inst, 첫구간 시작, 끝, dbid, inst, 둘째구간 시작, 끝)이다(16화 05와 같은 방법 · 루트 스냅샷).
| 옛 판 (C18-OLD · 런 28) | 지금 판 (C18-NOW · 런 29) | |
|---|---|---|
| 프로파일 (T7 3호) | WP-18O: PROFILE=SPIKE MINUTES=10 SESSIONS=3 THINK_MS=0 SPIKE_AT=0 SPIKE_LEN=10 SPIKE_KIND=NL,LIT,LOOP | WP-18N: PROFILE=STEADY MINUTES=10 SESSIONS=3 THINK_MS=0 |
| 나쁜 판으로 돈 조각 | Q2 rev00 (USE_NL) · Q4 lit (포스트잇 이어 붙이기) · P1 rev00 (커서 루프 · 1,000건 커밋) | — |
| 지금 판 그대로인 조각 | Q1 rev03 · Q3 rev01 · N1 rev01 | 여섯 다 |
| 스냅샷 | 루트 50 – 51 (+1분 · +9분) | 루트 52 – 53 |
⚠️ 이 비교는 '3월 vs 7월'이 아니다. 3월의 AWR은 보존 기간(8일)이 지나 지워졌다. 부하 발생기가 나쁜 판으로 바꿀 수 있는 조각은 셋(Q2 · Q4 · P1)뿐이다. 그래서 같은 날, 같은 장비, 같은 3세션으로 연달아 두 구간을 만들었다.
awrddrpt의 %Diff가 의미를 가지려면 두 구간의 조건이 같아야 한다. 개정계획이 이 리포트를 18화 안에서만 쓰라고 한 것도 그래서다.
8.2 머리 — DB time이 같다
Snapshot Set Begin Snap Id End Snap Id Avg Active Users Elapsed Time (min) DB time (min)
1st 50 51 3.0 8.0 24.0
2nd 52 53 3.0 8.0 24.1
%Diff: 0.7% 0.0% 0.5%DB time은 '얼마나 바빴나'를 나타낸다. 생각 시간이 0인 세션 셋이 8분 내내 일했으니 두 구간 모두 3 × 8 = 24분이다. 이 칸이 같다는 것은 '아무것도 안 바뀌었다'는 뜻이 아니라 '두 구간의 조건이 같았다'는 확인이다.
8.3 무엇을 했나 — Load Profile · 부하 발생기의 셈
| (초당) | 옛 판 | 지금 판 | %Diff |
|---|---|---|---|
| Logical read (blocks) | 5,042,205.5 | 275,051.0 | −94.5 |
| Redo size (bytes) | 30,228,711.0 | 651,251.1 | −97.8 |
| Block changes | 206,151.5 | 5,619.6 | −97.3 |
| Executes (SQL) | 100,721.5 | 1,515.3 | −98.5 |
| Parses (SQL) · Hard parses | 92.5 · 11.6 | 553.0 · 19.7 | +498 · +70 |
| CPU time (초/초) | 1.9 | 2.0 | +8.1 |
| Transactions | 114.9 | 97.2 | −15.4 |
조각 (부하 발생기 T_LD_LOG · 런 전체 10분) | 옛 판 실행 · 평균 ms | 지금 판 실행 · 평균 ms |
|---|---|---|
| Q1 rev03 | 17,758 · 0.80 | 109,005 · 0.96 |
| Q2 | rev00 2,882 · 294.56 | rev01 18,190 · 40.43 |
| Q3 rev01 | 5,885 · 9.53 | 36,504 · 11.38 |
| Q4 | lit 23,464 · 0.49 | rev03 145,310 · 0.07 |
| N1 rev01 | 8,057 · 6.83 | 50,682 · 0.14 |
| P1 | rev00 594 · 1,370.05 | rev02 3,676 · 142.46 |
| 합 | 58,640 | 363,367 (6.2배) |
- 옛 판의
Executes초당 10만은 P1 rev00의 루프에서 나왔다. 정산 한 번에 INSERT를 10만 번 한다(15화P1_rev02.sql머리말의 '보내는 쪽'). - 지금 판의
Parses가 6배인 것은 우리 SQL 때문이 아니다. Top SQL Comparison by Parse Calls의 위쪽은 P1 rev02의TRUNCATE+APPEND가 부르는 재귀 SQL이다(통계 기록optstat_hist_control$14,710번 ·tab_stats$·superobj$정리 · 온라인 통계 기록wri$_optstat_opr·ST_OPR_ID_SEQ.NEXTVAL5,868번). 총 파스는 44,385 → 265,425다. 14화에서 본 '직접 경로 적재의 온라인 통계'를 한 번에 치르는 값이다. - 지금 판의 CPU가 8% 많은 것은 일을 6.2배 했기 때문이다. CPU 두 개가 한계에 닿았다(아래 대기).
8.4 대기 — 1위가 바뀌었다
| 순위 | 옛 판 | %DB time | 지금 판 | %DB time |
|---|---|---|---|---|
| — | CPU time | 62.2 | CPU time | 66.8 |
| 1 | log file switch (checkpoint incomplete) | 22.9 | resmgr:cpu quantum | 23.0 |
| 2 | db file async I/O submit | 8.3 | log file parallel write | 9.7 |
| 3 | resmgr:cpu quantum | 7.6 | enq: RO - fast object reuse | 6.6 |
옛 판은 redo를 초당 30MB씩 쓰느라 로그 전환을 기다렸다(P1 rev00). 지금 판은 CPU 차례를 기다린다. Free의 리소스 매니저가 cpu_count 2를 지키느라 생기는 대기다(16화). enq: RO는 P1 rev02의 TRUNCATE에서 나온다(세션별 적재 테이블을 비우는 몫).
8.5 가장 큰 %Diff가 가짜일 때 — sequence load elapsed time
시간 모델 표는 'Diff의 절댓값' 순서로 줄을 세운다. 맨 위 줄은 이렇다.
Statistic Name % of DB time 1st 2nd Diff Time (s) 1st 2nd
sequence load elapsed time 0.0 48.3 48.3 0.1 698.2지금 판에서 시퀀스를 부르는 조각은 N1 하나다. N1은 10분 동안 50,682번, 한 번에 0.14ms씩 돌았으니 합쳐 약 7초다. 698초가 나올 수 없다. ASH(메모리 · 1초 표본)를 조각(ACTION)별로 셌다(IN_SEQUENCE_LOAD = 'Y' 표본 수 / 전체).
| 조각 | 옛 판 | 지금 판 |
|---|---|---|
| Q2 | 0 / 854 | 363 / 747 |
| P1 | 0 / 809 | 231 / 500 |
| Q3 | 0 / 63 | 205 / 421 |
| Q1 | 0 / 10 | 61 / 117 |
| Q4 | 0 / 9 | 5 / 10 |
| N1 | 0 / 55 | 1 / 4 |
시퀀스를 한 번도 부르지 않는 조각의 표본에서 깃발이 조각마다 절반쯤 켜져 있다. 시간 모델의 그 줄은 이 깃발을 센 값으로 보인다. 17화에서 'ADDM 1위 Sequence Usage: 시퀀스를 안 불러도 깃발이 켜진다'고 적고 조건을 열어 둔 바로 그 깃발이다. 오늘 두 구간에서 더한 관찰은 이것이다. 옛 판(같은 3세션 · 같은 N1 rev01 · 시퀀스 SQ_STLM_LD를 똑같이 부른다)에서는 0, 지금 판에서는 약 절반이다.
같은 두 런을 다른 날 같은 인자로 다시 돌리면 이 줄은 같은 숫자로 나오지 않는다.
| 그날 (위) | 다른 날 다시 | |
|---|---|---|
시간 모델 sequence load elapsed time (% of DB time · 초) | 0.0 → 48.3% · 0.1 → 698.2초 | 3.9 → 66.4% · 55.8 → 958.9초 |
| ASH 깃발 표본 (옛 판 · 지금 판 / 전체) | 0 / 1,800 · 866 / 1,799 | 114 / 1,800 · 1,194 / 1,800 |
두 번 모두 같았던 것은 '시퀀스를 부르지 않는 조각에도 켜지고, 지금 판에서 훨씬 많다'는 것뿐이다. 옛 판도 0이 아닐 수 있다. 다른 칸(실행 수 · Logical read · Redo · Top Events)은 두 번 모두 몇 % 안에서 같았다. 이 줄만 런마다 움직인다는 사실도 결론에 쓰지 않을 이유가 된다.
단서가 하나 있다. 지금 판에만 있는 재귀 SQL, SELECT ST_OPR_ID_SEQ.NEXTVAL FROM DUAL 5,868번이다(P1 rev02의 APPEND가 부르는 온라인 통계 기록 · §8.3). 옛 판의 P1 rev00은 일반 INSERT라 이 시퀀스를 부르지 않는다. 그 시퀀스를 적재한 뒤에 깃발이 세션에 남는 것인지는 확인하지 못했다. 그래서 T5-003은 이 줄을 결론에 쓰지 않는다(T6 제1조: 다른 칸에서 같은 결과가 나올 때까지).
9. 표준은 어떻게 만들어졌나 — 승격 규칙
9.1 절차 (T6 §0)
- 18주의 리포트에서 원인 칸의 말을 그대로 뽑는다(원인 칸이 비어 있으면 그 화 본문의 말).
- 같은 원인끼리 묶는다. 조각이나 화면, 장비가 아니라 무엇 때문에 틀렸나로 묶는다.
- 서로 다른 리포트 둘 이상 → 조문. 하나 → 대기 줄.
- 각주 셋은 근거 리포트를 쓴 화의 「원천 좌표」 절에 이미 있는 것만 옮긴다(없으면 좌표 미확인이라 적는다. 이 화에서는 일곱 칸이다).
9.2 센 결과 (요약 — 전체는 T6 §0 승격 판정표)
| 원인 | 리포트 수 | 판정 |
|---|---|---|
| 결정에 그때의 규모 · 근거가 함께 없다 | 9 | 마지막 조항 |
| Buffers로 안 보이는 것이 회귀 · 측정 조건이 운영과 다름 | 6 · 6 | 제2 · 3조 |
| '이 데이터에서 같다'를 '맞다'로 | 5 | 제5조 |
| 한 칸으로 결론 · 행마다 건너감 · 처방을 모양으로 · 영향 범위 · 리스크 칸 | 4 · 4 · 4 · 4 · 4 | 제1 · 6 · 8 · 12 · 11조 |
| 통계 · 스위치 제안 · 도구 권고 · 에러 없이 사라짐 | 3 · 3 · 3 · 3 | 제7 · 9 · 10 · 14조 |
| 컬럼 가공 · 요구가 늙음 · 숨은 입력 · 팩에 기댐 | 2 · 2 · 2 · 2 | 제4 · 13 · 15 · 16조 |
9.3 떨어진 후보 (조문이 되지 못한 것)
| 후보 | 왜 | 어디에 남았나 |
|---|---|---|
| 바인드 · 리터럴 · 커서 (코딩 가이드 1 ~ 6) | 리포트 하나 (T1 9호). 숫자는 컸다 | 코딩 가이드 v1.0 대기 조문 · 체크리스트 A · B |
날짜 BETWEEN (가이드 7) | 하나 (T1 1호) · 03화는 반례 | 대기: 졸업 과제의 ECOMMERCE가 두 번째 후보 |
| 조인 상대가 그 키로 한 줄인가 (가이드 9) | 하나 (T1 6호) | 대기: 졸업 과제가 두 번째 후보 |
| 키 기반 페이징 (가이드 10) | 하나 (T1 7호) | 대기 |
적재 방식 · LOAD AS SELECT 확인 | 하나 (T3 1호) | T2 · T3의 칸이 이미 강제 |
FORALL · ERROR_INDEX | 하나 (T3 2호) | 대기 |
| 한 시간 리포트로 '몇 분'을 기각하지 않는다 | 하나 (T5-002). 원인이 제1조와 같다 | 제1조 세칙으로 흡수 |
이관할 때 ORDER BY | 하나 (T2-001) | 제12조의 예 |
| 지난 결론을 지우지 않고 정정 | 원인이 아니라 방법 | T6 §5 문서 운용 |
"한 번 나온 것은 사건이고, 두 번 나온 것은 구조다." 대기 줄에 세운다고 버리는 것이 아니다. 두 번째 근거가 오는 날 조문으로 올린다. 승격 규칙은 표준 자신에게도 똑같이 적용한다.
9.4 SQL 코딩 가이드 v1.0의 판정
| 조문 | v0.1 근거 | v1.0 |
|---|---|---|
| 8 컬럼 가공 금지 | 2회 (T1 1 · 2호) | 조문 (T6 제4조) |
11 결과 캐시 · DETERMINISTIC | 1회 (T1 11호) | 넓혀서 조문: '숨은 입력은 인자로'로 넓히면 T1 2호(03화 NLS)가 같은 원인이다 → 2회 (T6 제15조) |
| 12 읽기와 쓰기를 떼어 놓는다 (신설) | T4 1 · 2호 · T3 1 · 2호 | 조문 (T6 제8조) |
| 13 예외를 삼키지 않는다 (신설) | T3 2호 · T4 2호 · T3 1호 | 조문 (T6 제14조) |
| 14 행마다 SQL을 부르지 않는다 (신설) | T1 5호 · T3 1 · 2호 | 조문 (T6 제6조) |
| 1 ~ 7 · 9 · 10 | 1회씩 | 대기 조문 (번호 유지) |
10. 앞 화들이 넘긴 것 — 처리 결과
| 넘긴 화 | 넘긴 것 | 18화의 결론 | 근거 |
|---|---|---|---|
| 01 · 개정계획 | W01 6조각 재측정 · 2열 | 완료: 01화 표는 그대로 · 18화 절 추가 | §1 · W01 원장 |
| 01 · 개정계획 | _막힘로그 결산 → 재학습 큐 | 절차로 남김: 저장소의 _막힘로그.md는 독자의 기록장이라 비어 있다. 결산 절차(3회 이상 → 재학습 큐 → 좌표로 돌아가 다시 설명)를 _막힘로그.md 끝과 3_실습 STEP 13에 적었다 | — |
| 06 | 힌트 유효기간 대장 졸업 재측정 | 완료: 1 · 2번 폐기 확인 · 3번 → rev02로 대체 · 4번 (rev02) · 5번 (APPEND 소급) 추가 · 3번 날짜 정정 | 대장 |
| 08 | 정합성 체크리스트: 6조각 원장 대조 | 불일치 0: Q1 27,390 / 6,982,065,200 · Q2 3행 / 255,398,623,700 · Q3 rev01 원장과 같음 · Q4 rev03 100페이지 10 = 10 · 2,627,200 · MINUS 0 / 0 (02 [5]) · P1 100,000 / 25,519,833,100 | 02 |
| 15b | 보정률 함수 결과 캐시 재적용 (운영 결과 캐시 크기 · 고객 수 확인 뒤) | 보류 유지: 연습 DB의 칸 1,984 < 고객 2,000이라 다 안 들어간다 (11 [4] does not fit). 운영 확인 조건은 그대로 (운영의 같은 쿼리) | 11 |
| 15b | RESULT_CACHE가 붙은 함수 전수 점검 | 0개 (오라클 내장 스키마 제외) · 공유 풀의 /*+ RESULT_CACHE */ 문장 15개는 전부 옵티마이저의 동적 샘플링(/* OPT_DYN_SAMP */ … RESULT_CACHE(SNAPSHOT=3600))이고, 우리 코드는 0 | 11 [1] [2] |
| 15b | 코딩 가이드 조문 11의 승격 | 승격: '숨은 입력'으로 넓혀 T1 2호와 묶음 | §9.4 |
| 15b | 정합성 체크리스트 경계 목록의 '시각 · 세션' 줄 | 추가 | 체크리스트 |
| 16 | Q2 보류안 (08화 재검토 조건 충족) | 채택: rev02 (T1 12호) | §6 |
| 16 | 부하 발생기의 Q2를 고칠지 | 고치지 않는다 | §6.5 |
| 16 | T2-002 IX_ORD_BIG_DT 폐기 | INVISIBLE 단계로 승인 · DROP은 보류. 연습 DB의 증거 셋이 같은 결론을 가리킨다. AWR 계획 기록 스냅샷 1 ~ 53에 그 인덱스를 쓴 SQL 0 · 공유 풀의 응용 SQL 0 (옵티마이저의 동적 샘플링 DS_SVC 문장 몇 개는 쓴다. 03 · 05가 남긴 것이고 응용 SQL이 아니다) · INVISIBLE로 내려도 6조각 + rev00 셋 + 하루 조회의 계획 해시가 하나도 안 바뀐다. 남은 선결 조건은 운영 소스 전수 조사와 운영 AWR 하나다(16화 T8 ⑥). 연습 DB는 99가 VISIBLE로 되돌린다 | 10 · T2-002 갱신 |
| 16 | T5 · T7 · T8의 규칙 (한 칸 결론 금지 · 비율 옆 분자 · 분모 · 프로파일 ID 없는 리포트는 무효) | 조문: T6 제1 · 3조 | §9 |
| 17 | 진단 도구 4열 대응표 · 질문 셋 | 조문: 제1조 본문 · 세칙 | T6 제1조 |
| 17 | ADDM Top SQL → SQL Tuning Advisor | 돌렸다: 고친 판 셋은 권고 없음 · Q2 rev00 프로파일은 기각 (T1 13호) | §4 |
| 17 | SPM | 수동은 다 된다 · 자동은 Free에 없다 · 제7조 | §2 · §3 |
| 17 | T5-002의 규칙 (Rationale을 표본으로 다시 센 뒤 채택 · '몇 분'을 기각하지 않는다) | 조문: 제10조 ① · 제1조 세칙 | T6 |
| 17 | 지난 결론을 지우지 않고 옆에 정정 | 운용 규칙 (조문은 원인만): W01 2열 · 이커머스 설명서 정정이 그 방식 | T6 §5 |
| 17 | IN_SEQUENCE_LOAD가 켜지는 조건 | 열어 둔다. 오늘 더한 관찰: 옛 판 0 · 지금 판 약 절반, 시퀀스를 안 부르는 조각에도 | §8.5 |
11. 이 화의 측정값 전부
| 무엇 | 값 | 스크립트 |
|---|---|---|
| 원본 지문 | T_ORD_BIG 1,000,000 / 255,398,623,700 · T_ORD 100,000 / 25,519,833,100 · T_ORD_DET 200,000 / 30,272,557,700 · T_ORD_STLM 100,000 / 25,519,833,100 · M_CUS 2,000 (처음과 끝이 같다) | 00 · 99 |
| W01 Q1 ~ Q4c | §1.2 | 02 |
| W01 P1 · N1 | §1.3 | 02 [4] |
| SPM A | FULL 5,821 → 인덱스 생겨도 FULL (베이스라인) → 진화 63.71배 안팎 (네 번) → 91 | 03 |
| SPM B | 빈 통계 + 베이스라인 HASH 5,868 · 끔 NL 1026K · 진화 0.32 ~ 0.33배 탈락 (네 번) · ENABLED NO → NL | 03 |
| SPM 자동 | 루트 ORA-38153 · 과제 실행 0 | 04 |
| STA | §4.1 · Q2 rev00 99.42% · .741584 → .238911 | 05 |
| 팩 | §5 | 06 |
| Q2 rev02 | 5,868 = 5,868 · CPU 115 ~ 121 → 32 ~ 35 (다른 날 다시 잰 값 포함) · MINUS 0 / 0 · 경계 둘 같음 | 09 |
IX_ORD_BIG_DT | DBA_INDEX_USAGE 384회 (그 뒤 0) · AWR 0 SQL / 1 ~ 53 · 공유 풀 응용 SQL 0 (동적 샘플링 DS_SVC 문장은 따로 센다: 10 [3]) · INVISIBLE로 계획 변화 0 · _DT_AMT 2,805,975 · _DT_SEQ 5,151,228 | 10 |
| 결과 캐시 | 칸 4,096 B × 최대 1,984 · 고객 2,000 · RESULT_CACHE 함수 0 · 힌트 문장 우리 것 0 / 동적 샘플링 8 ~ 15 (그때까지 돈 SQL에 따라) | 11 |
| 리소스 매니저 | §7 | 07 |
| awrddrpt | §8 | 08 |
측정 환경
ora-hanorder · Oracle AI Database 26ai Free 23.26.2 · cpu_count 2 · SGA 1,536MB (버퍼 캐시 576MB) · pga_aggregate_target 512MB · NOARCHIVELOG · CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC+TUNING · job_queue_processes 1 · 17화 종료 상태 (인덱스 셋 · ORD_ST FREQUENCY · 부하 런 20개 → 이 화가 런 9개 추가 · 99가 지운다). 호스트는 32 CPU이지만 컨테이너 안의 Free는 CPU 2개만 쓴다.
12. Free에서 못 잰 것 · 봉인한 것 · 남긴 흔적
| 못 잰 것 · 하지 않은 것 | 왜 | 대신 한 것 |
|---|---|---|
| 자동 SPM 진화 · 자동 SQL 튜닝 | Free에서 꺼져 있다 (ORA-38153 · 실행 0) | 수동 진화 · 정기 점검 줄 (§2.5) |
| 리소스 매니저의 비율 · 상한이 지켜지는 모습 | 이 Free의 PDB 플랜에서 지켜지지 않았다 · 원인 미확인 (§7) | 처리량 · CPU를 판마다 적고 '근거로 쓰지 않는다'로 판정 · 운영 EE의 같은 시험 → 별권 |
병렬 · RAC · 인스턴스 간 리소스 관리 (resource_manager_cpu_scope · 인스턴스 케이징) | 단일 인스턴스 · CPU 2 · 병렬 없음 | 없음 → 별권 |
| 3월의 AWR | 보존 기간(8일)이 지나 지워졌다 | 같은 날 연달아 두 구간 (§8.1) |
| 진짜 팩 없는 서버 · SE2 | Free에는 팩이 들어 있다 | 메모리에서만 내려 재고 되돌림 (§5) |
운영의 결과 캐시 크기 · 운영 소스 전수 조사 (IX_ORD_BIG_DT) | 연습 DB 밖 | 조건을 리포트에 남김 (§10) |
이 화가 연습 DB에 남긴 흔적은 99로도 지우지 못한다. 숨기지 않고 적는다.
| 흔적 | 왜 남나 | 해로운가 |
|---|---|---|
DBA_SQL_MANAGEMENT_CONFIG의 AUTO_SPM_EVOLVE_TASK · LAST_MODIFIED (값은 OFF 그대로) | 준비하며 CONFIGURE(…, 'OFF')를 한 번 불렀다 (§3) | 아니다. 값은 같고 시각만 남는다 |
PDB의 pdb_spfile$에 resource_manager_plan 한 줄 | 준비하며 빈 값('')으로 꺼 봤다 (§7.4) | 아니다. RESET 뒤 V$SPPARAMETER는 ISSPECIFIED FALSE · 다시 띄워도 DEFAULT_PLAN |
| AWR 스냅샷 50 ~ 53 · 그 사이의 자동 스냅샷 | AWR은 보존 기간(8일)이 지나면 저절로 지워진다 | 아니다 |
SQ_STLM_LD 시퀀스 값이 앞으로 감 · 부하 발생기 표 T_STLM_REG_LD 등 *_LD의 행 | 부하 발생기가 돈 몫 (15화 설계: 원본에는 한 줄도 안 쓴다) | 아니다 |
진화 · 튜닝 과제의 실행 기록 번호 (EXEC_…) | 과제를 지워도 번호는 앞으로 간다 | 아니다 |
13. 원천 좌표
전체 지도 →
00_자료준비/06_원서와_공식문서_지도.md·원천지도/오라클공식문서_지도.md(18화 줄) 성능고도화 I · II권의 목차에는 SQL Plan Management, SQL Tuning Advisor, Resource Manager 절이 없다. 그래서 좌표 미확인이다. 원서가 다루는 것은 그 앞 단계(힌트 · 통계 · AWR)다.
성능고도화 (이 화가 기대는 앞 화의 좌표)
| 주제 | 좌표 | 처음 적은 화 |
|---|---|---|
| 08절 Statspack / AWR: 기본 사용법 · 리포트 분석 | I권 03장 p.210 · 212 · 214 ~ 221 | 16 · 17화 |
| 05절 Ratio 기반 분석 | I권 03장 p.194 ~ 198 | 16화 |
| 통계정보 II: 전략적인 통계수집 정책 · Statistics Preference | II권 Ch3 08 p.442 ~ 459 | 10화 |
| 힌트 · NL 조인 (힌트로 NL을 제어) | II권 Ch2 01 (2) p.211 | 06화 |
| 해시 조인 사용 기준 | II권 Ch2 03 (6) p.264 | 06화 |
| 쿼리 변환 (Group-by Placement는 공식문서) | II권 Ch4 01 p.462 | 08화 |
| SQL Plan Management · SQL Tuning Advisor · Resource Manager | 좌표 미확인 | — |
오라클 공식문서
| 주제 | 매뉴얼 | 장 제목 |
|---|---|---|
| SQL plan baseline · plan history · 캡처 · ACCEPTED · ENABLED · FIXED · 진화 | tgsql | SQL Plan Management (Overview of SQL Plan Management · Managing SQL Plan Baselines) |
LOAD_PLANS_FROM_CURSOR_CACHE · CREATE_EVOLVE_TASK · EXECUTE_EVOLVE_TASK · IMPLEMENT_EVOLVE_TASK · REPORT_EVOLVE_TASK · ALTER_SQL_PLAN_BASELINE · CONFIGURE | arpls | DBMS_SPM |
| SQL Tuning Advisor · SQL 프로파일 | tgsql | Analyzing SQL with SQL Tuning Advisor (sql-tuning-advisor.html) · Managing SQL Profiles |
CREATE_TUNING_TASK · EXECUTE_TUNING_TASK · REPORT_TUNING_TASK · ACCEPT_SQL_PROFILE · DROP_SQL_PROFILE | arpls | DBMS_SQLTUNE |
CONTROL_MANAGEMENT_PACK_ACCESS · RESOURCE_MANAGER_PLAN · OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES · OPTIMIZER_USE_SQL_PLAN_BASELINES | refrn | Initialization Parameters |
리소스 매니저: 소비자 그룹 · 플랜 · 지시문 · 매핑 · MGMT_P1 · UTILIZATION_LIMIT · PDB의 리소스 관리 | admin | Managing Resources with Oracle Database Resource Manager |
CREATE_PENDING_AREA · CREATE_CONSUMER_GROUP · CREATE_PLAN_DIRECTIVE · SET_CONSUMER_GROUP_MAPPING | arpls | DBMS_RESOURCE_MANAGER · DBMS_RESOURCE_MANAGER_PRIVS |
V$RSRC_PLAN · V$RSRC_CONSUMER_GROUP · DBA_SQL_PLAN_BASELINES · DBA_SQL_PROFILES · DBA_SQL_MANAGEMENT_CONFIG · DBA_INDEX_USAGE | refrn | Dynamic Performance Views · Static Data Dictionary Views |
AWR 비교 리포트 (awrddrpt · AWR_DIFF_REPORT_TEXT) | tgdba · arpls | Comparing Database Performance Over Time · DBMS_WORKLOAD_REPOSITORY |
| 진단 · 튜닝 팩 (SPM은 팩이 아니다 · 프로파일 · STA는 튜닝 팩) | dblic | Licensing Information |