#!/usr/bin/env bash
#==============================================================================
# 05_부하_돌리기.sh  —  PKG_LOAD_DRIVER 발사기 (Git Bash)          15화 산출물 · 16·17화가 쓴다
#
#   먼저 한 번: 05_PKG_LOAD_DRIVER.sql 설치 (이 폴더 README §부하 발생기)
#
#   쓰는 법 (이 폴더에서) — 인자는 전부 '이름=값' 환경 변수로 준다. 안 주면 괄호 안의 기본값
#     ./05_부하_돌리기.sh                                         # STEADY · 10분 · 2세션
#     PROFILE=STEADY MINUTES=3 SESSIONS=2 ./05_부하_돌리기.sh
#     PROFILE=SPIKE  MINUTES=3 SESSIONS=2 SPIKE_AT=1 SPIKE_LEN=1 ./05_부하_돌리기.sh
#     PROFILE=SPIKE  MINUTES=60 SESSIONS=3 SPIKE_AT=30 SPIKE_LEN=5 SPIKE_KIND=NL ./05_부하_돌리기.sh
#
#     PROFILE        STEADY | SPIKE                                    (STEADY)
#     MINUTES        몇 분 돌고 스스로 끝나나 (1~240)                   (10)
#     SESSIONS       동시에 띄울 sqlplus 세션 수 (1~8 · CPU 2개라 2~4 권장) (2)
#     THINK_MS       조각 사이에 쉬는 평균 ms (0 = 쉬지 않음)             (200)
#     WEIGHTS        조각 가중치                       (Q1=30,Q2=5,Q3=10,Q4=40,N1=14,P1=1)
#     SEED           난수 씨앗 — 같은 값이면 같은 순서                   (15)
#     SPIKE_AT       SPIKE 가 시작하는 분 (런 시작 기준)                 (1)
#     SPIKE_LEN      SPIKE 길이 (분)                                     (1)
#     SPIKE_KIND     NL · LIT · LOCK · LOOP 를 쉼표로                     (NL,LIT,LOCK)
#     SPIKE_WEIGHTS  SPIKE 구간에만 쓸 가중치 (비우면 WEIGHTS)           ()
#     HOLD_SEC       LOCK 구간에 세션 1 이 번호 행을 쥐는 초              (10)
#     P1_ROWS        P1 한 번에 적재할 행                                (100000)
#     NOTE           런 기록에 남길 메모                                  ()
#     C              컨테이너 이름                                        (ora-hanorder)
#
#   하는 일
#     ① 원본 지문(앞)  ② START_RUN  ③ RUN_SESSION(1..N) 을 백그라운드 sqlplus 로 N 개  ④ wait
#     ⑤ END_RUN  ⑥ 조각별 · 분별 횟수  ⑦ ASH 의 MODULE/ACTION 분포  ⑧ 원본 지문(뒤)  ⑨ 남은 세션 수(기대 0)
#   Ctrl+C 를 누르면 STOP_RUN 을 보내고 세션들이 스스로 끝나기를 기다린다 (1초 안팎).
#   세션마다의 출력은 $TMPDIR/ld_logs/run_<시각>_s<번호>.log 에 남는다 (LOGDIR=... 로 바꿀 수 있다).
#
#   ⚠️ AWR 스냅샷은 이 발사기가 찍지 않는다 (16화가 찍는다). 대신 T_LD_RUN 에 시작 · 끝 시각이 남는다.
#==============================================================================
set -u
C="${C:-ora-hanorder}"
PROFILE="${PROFILE:-STEADY}"
MINUTES="${MINUTES:-10}"
SESSIONS="${SESSIONS:-2}"
THINK_MS="${THINK_MS:-200}"
WEIGHTS="${WEIGHTS:-Q1=30,Q2=5,Q3=10,Q4=40,N1=14,P1=1}"
SEED="${SEED:-15}"
SPIKE_AT="${SPIKE_AT:-1}"
SPIKE_LEN="${SPIKE_LEN:-1}"
SPIKE_KIND="${SPIKE_KIND:-NL,LIT,LOCK}"
SPIKE_WEIGHTS="${SPIKE_WEIGHTS:-}"
HOLD_SEC="${HOLD_SEC:-10}"
P1_ROWS="${P1_ROWS:-100000}"
NOTE="${NOTE:-}"

SQLP() { docker exec -i "$C" sqlplus -s sqlp/sqlp@localhost/FREEPDB1; }

STAMP=$(date +%Y%m%d_%H%M%S)
LOGDIR="${LOGDIR:-${TMPDIR:-/tmp}/ld_logs}"
mkdir -p "$LOGDIR"

fingerprint() {
SQLP <<'SQL'
SET LINESIZE 150 PAGESIZE 50 FEEDBACK OFF
COL tab FORMAT A12
COL sum_ FORMAT 999,999,999,999
SELECT * FROM V_LD_FINGERPRINT;
EXIT
SQL
}

echo "== [1] 원본 지문 (앞)"
fingerprint

echo "== [2] START_RUN  profile=$PROFILE minutes=$MINUTES sessions=$SESSIONS"
OUT=$(SQLP <<SQL
SET SERVEROUTPUT ON FEEDBACK OFF
WHENEVER SQLERROR EXIT FAILURE
BEGIN
  PKG_LOAD_DRIVER.START_RUN(p_profile => '$PROFILE', p_minutes => $MINUTES, p_sessions => $SESSIONS,
                            p_think_ms => $THINK_MS, p_weights => '$WEIGHTS', p_seed => $SEED,
                            p_spike_at => $SPIKE_AT, p_spike_len => $SPIKE_LEN, p_spike_kind => '$SPIKE_KIND',
                            p_spike_weights => '$SPIKE_WEIGHTS', p_hold_sec => $HOLD_SEC,
                            p_p1_rows => $P1_ROWS, p_note => '$NOTE');
END;
/
EXIT
SQL
)
RC=$?
echo "$OUT"
if [ $RC -ne 0 ]; then echo "START_RUN 실패 — 위 메시지를 보라"; exit 1; fi

stop_run() {
  echo
  echo "== Ctrl+C — STOP_RUN 을 보낸다. 세션들이 스스로 끝나기를 기다린다"
  SQLP <<'SQL'
SET SERVEROUTPUT ON FEEDBACK OFF
EXEC PKG_LOAD_DRIVER.STOP_RUN
EXIT
SQL
}
trap stop_run INT

echo "== [3] 세션 $SESSIONS 개를 띄운다 (출력: $LOGDIR/run_${STAMP}_s<번호>.log)"
PIDS=""
for i in $(seq 1 "$SESSIONS"); do
  SQLP > "$LOGDIR/run_${STAMP}_s$i.log" 2>&1 <<SQL &
SET SERVEROUTPUT ON FEEDBACK OFF
EXEC PKG_LOAD_DRIVER.RUN_SESSION($i)
EXIT
SQL
  PIDS="$PIDS $!"
done

echo "== [4] 기다린다 (약 $MINUTES 분)"
for p in $PIDS; do wait "$p"; done
trap - INT
cat "$LOGDIR"/run_${STAMP}_s*.log | grep -E "^session|ORA-" || true

echo "== [5] END_RUN"
SQLP <<'SQL'
SET SERVEROUTPUT ON FEEDBACK OFF LINESIZE 150
EXEC PKG_LOAD_DRIVER.END_RUN
EXIT
SQL

echo "== [6] 런 · 세션 · 분별 횟수"
SQLP <<'SQL'
SET LINESIZE 200 PAGESIZE 200 FEEDBACK OFF
COL profile FORMAT A7
COL spike_kind FORMAT A14
COL weights FORMAT A36
COL started_at FORMAT A23
COL ended_at FORMAT A23
COL exit_reason FORMAT A12
COL piece FORMAT A5
COL rev FORMAT A7
VAR r NUMBER
EXEC :r := PKG_LOAD_DRIVER.LAST_RUN
SELECT RUN_ID, PROFILE, MINUTES, SESSIONS, THINK_MS, SPIKE_AT, SPIKE_LEN, SPIKE_KIND, STATUS,
       TO_CHAR(STARTED_AT, 'YYYY-MM-DD HH24:MI:SS') AS started_at, TO_CHAR(ENDED_AT, 'YYYY-MM-DD HH24:MI:SS') AS ended_at
  FROM T_LD_RUN WHERE RUN_ID = :r;
SELECT SESS_NO, SID, EXECS, ERRS, EXIT_REASON, TO_CHAR(BEGAN_AT, 'HH24:MI:SS') AS began, TO_CHAR(ENDED_AT, 'HH24:MI:SS') AS ended
  FROM T_LD_SESS WHERE RUN_ID = :r ORDER BY SESS_NO;
SELECT PIECE, REV, EXECS, ELA_CS, CPU_CS, ERRS, AVG_MS FROM V_LD_PIECE WHERE RUN_ID = :r ORDER BY PIECE, REV;
PROMPT -- 분별 (MIN_NO 0 = 첫 1분)
SELECT * FROM (SELECT MIN_NO, PIECE || ' ' || REV AS k, EXECS FROM V_LD_MINUTE WHERE RUN_ID = :r)
 PIVOT (SUM(EXECS) FOR k IN ('Q1 rev03' AS q1, 'Q2 rev01' AS q2, 'Q2 rev00' AS q2_nl, 'Q3 rev01' AS q3,
                             'Q4 rev03' AS q4, 'Q4 lit' AS q4_lit, 'N1 rev01' AS n1, 'N1 hotfix' AS n1_hot,
                             'N1 hold' AS n1_hold, 'P1 rev02' AS p1, 'P1 rev00' AS p1_loop))
 ORDER BY MIN_NO;
PROMPT -- 에러가 있었다면 (마지막 메시지)
SELECT PIECE, REV, SUM(ERRS) AS errs, MAX(LAST_ERR) AS last_err FROM T_LD_LOG WHERE RUN_ID = :r AND ERRS > 0 GROUP BY PIECE, REV;
EXIT
SQL

echo "== [7] ASH — 런 동안 MODULE = LOAD_DRIVER 의 ACTION 분포 (샘플 1개 ≈ DB Time 1초)"
SQLP <<'SQL'
SET LINESIZE 200 PAGESIZE 200 FEEDBACK OFF
COL action FORMAT A12
COL event FORMAT A40
VAR r NUMBER
EXEC :r := PKG_LOAD_DRIVER.LAST_RUN
SELECT NVL(h.action, '(none)') AS action, COUNT(*) AS samples,
       SUM(CASE WHEN h.session_state = 'ON CPU' THEN 1 ELSE 0 END) AS on_cpu,
       SUM(CASE WHEN h.session_state = 'WAITING' THEN 1 ELSE 0 END) AS waiting
  FROM v$active_session_history h, T_LD_RUN r
 WHERE r.RUN_ID = :r AND h.module = 'LOAD_DRIVER'
   AND h.sample_time BETWEEN r.STARTED_AT AND NVL(r.ENDED_AT, SYSTIMESTAMP)
 GROUP BY h.action ORDER BY samples DESC;
SELECT NVL(h.event, 'ON CPU') AS event, COUNT(*) AS samples
  FROM v$active_session_history h, T_LD_RUN r
 WHERE r.RUN_ID = :r AND h.module = 'LOAD_DRIVER'
   AND h.sample_time BETWEEN r.STARTED_AT AND NVL(r.ENDED_AT, SYSTIMESTAMP)
 GROUP BY h.event ORDER BY samples DESC FETCH FIRST 8 ROWS ONLY;
EXIT
SQL

echo "== [8] 원본 지문 (뒤) — [1] 과 같아야 한다"
fingerprint

echo "== [9] 남은 드라이버 세션 (기대 0)"
SQLP <<'SQL'
SET FEEDBACK OFF
SELECT COUNT(*) AS driver_sessions_left FROM v$session WHERE module = 'LOAD_DRIVER' OR client_identifier LIKE 'LD%-S%';
EXIT
SQL