문제 SQL 탐지와 세션 진단

#핵심 요약

실습 대시보드의 기본 판정 기준은 실행 10초 이상, 세션 논리 읽기 100만 블록 이상, 다른 세션 블로킹 세 가지임
기준은 사이드바에서 바꿈

경보가 뜬 세션의 상세 화면은 SQL 전문, V$SQL_PLAN 실행계획(위험한 줄은 색으로), 진단 문장, 조치 탭의 KILL 명령을 보여 줌

실습에서는 이 판단을 "DBA가 머릿속으로 하던 '이 계획은 위험하다 → 이렇게 조치한다'를 if 문으로 옮긴 것"이라고 설명함

실습에서는 악성 SQL 5종(카테시안 조인, 상관 서브쿼리, 인덱스 컬럼 가공, 락 방치, PL/SQL 무한 루프) 모두 경보부터 KILL까지 통과했다고 기록했음

#개념과 동작 원리

락 시나리오의 핵심 기준은 락을 잡은 세션이 INACTIVE여도 빨간 경보이고, 기다리는 세션은 주황 표시만 된다는 것임
경보는 보유 세션 하나에만 뜸

실습에서는 실행계획을 sql_id + child_number + con_id로 V$SQL, V$SQL_PLAN에서 읽었음
non-CDB라 con_id가 모두 0이었고, CDB 루트에서 PDB 커서를 읽는 경로는 검증하지 못했다고 밝혔음
판정 if 문의 코드 본문은 기재 없음

#필수 명령어 또는 SQL

#시나리오 SQL

-- 1 카테시안 조인: 조인 조건이 없어 2,000 × 200만 조합
select /*+ ordered use_merge(b) */ count(*)
  from big_sales a, big_sales b
 where a.cust_id = 7;

-- 2 상관 서브쿼리: 바깥 행마다 같은 고객 평균을 다시 계산, 힌트로 조인 변환을 막음
select /*+ no_unnest(@sub) */ count(*)
  from big_sales a
 where a.amount > (select /*+ qb_name(sub) */ avg(b.amount)
                     from big_sales b
                    where b.cust_id = a.cust_id);

-- 3 인덱스 컬럼 가공: sale_date 인덱스를 못 쓰고 풀 스캔을 10만 번 반복
declare
  n number;
begin
  for i in 1.. 100000 loop
    select count(*) into n
      from big_sales
     where to_char(sale_date, 'YYYYMMDD') = '20260101';
  end loop;
end;
/

-- 5 PL/SQL 무한 루프: 테이블은 읽지 않고 CPU만 쓴다
declare
  n number := 0;
begin
  loop
    n := mod(n + 1, 7);
  end loop;
end;
/

-- 4 락 걸고 방치: 창 A에서 실행하고 커밋하지 않는다
update hot_row set cnt = cnt + 1 where id = 1;
-- 창 B, 창 C에서 같은 문장 → enq: TX - row lock contention으로 대기
update hot_row set cnt = cnt + 1 where id = 1;

결과 해석: 수업 자료에서 각 문장은 KILL될 때까지 1분 25초~2분 25초 실행되다 ORA-00028: your session has been killed로 끝났음
시나리오 3의 화면은 PL/SQL 안에서 반복되는 SELECT를 대문자로 보여 줬음

#KILL 전후 확인

select sid, serial#, username, status, event from v$session where sid = <SID>;

alter system kill session '<SID>,<SERIAL#>' immediate;

select sid, serial#, username, status, event from v$session where sid = <SID> and serial# = <SERIAL#>;

결과 해석: SID는 재사용되므로 KILL 전에 조치 탭의 SERIAL#과 사용자가 같은지 확인함
실행 중이던 세션은 ORA-00031: session marked for kill이 나오지만 정상이고, 마지막 조회가 no rows selected면 성공임
쉬고 있던(INACTIVE) 세션은 바로 System altered.이고, 당한 쪽은 다음 문장에서 ORA-03113으로 알게 됨
알럿 로그 패널에는 KILL 기록이 Result = ORA-31로 남음
SID, SERIAL#은 실행할 때마다 다르므로 늘 화면 조치 탭의 값을 씀

시나리오 4에서 보유 세션을 KILL한 뒤 hot_row.cnt는 2였음
창 A의 커밋하지 않은 변경은 롤백되고 창 B, C의 변경만 남았음
실습에서는 실무에서는 KILL 전에 그 변경을 버려도 되는지 업무 담당자에게 먼저 확인한다고 적었음

#사용 조건과 주의사항

원래 시나리오 5는 for i in 1.. 1e10 loop였고 ORA-01426: numeric overflow로 0.01초 만에 끝나 대시보드에 나타나지 않았음
PLS_INTEGER 범위는 공식 문서로 확인 불가

SQL 문장이 없는 세션이 경보 줄에 올라오면 앞 60글자를 자르다 TypeError: 'float' object is not subscriptable로 멈췄음
실습에서는 문자열이 아니면 빈 칸으로 보여 주도록 한 줄 고쳤음
pandas가 빈 값을 실수형 NaN으로 담는 점과 관련된 것으로 보이나 설명은 기재 없음

상세 패널의 큰 숫자는 열 너비보다 길면 줄임표로 잘리며, 실습에서는 화면 설계를 바꾸지 않고 그대로 뒀음

SQL*Plus 창을 여러 개 띄우기 어려우면 PC에서 python bad_sql.py lock, python bad_sql.py cartesian 등으로 같은 상황을 만들 수 있음
이 세션을 KILL하면 PC 창에 DPY-4011과 ORA-00028이 든 오류가 찍힘
lock은 보유 세션을 KILL해도 PC 창에 아무것도 찍히지 않아 Ctrl+C로 끝냄

실습 KILL 예의 SID, SERIAL#과 화면 캡처 속 값은 다름
실습에서는 캡처를 나중에 다시 찍었기 때문이라고 밝혔음

같은 구조의 PostgreSQL판 탐지와 세션 종료는 PostgreSQL 지표 수집에 있음
실행계획 읽는 법은 실행계획 읽기, 인덱스를 못 타는 조건은 인덱스를 막는 조건에 있음