모니터링과 성능 진단

-- AWR 스냅샷 목록 확인
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;

-- AWR 리포트 생성 (HTML)
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- 수동 스냅샷 생성 (장애 전후 비교용)
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- 보관 주기 변경 (30일, 30분 간격)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 43200,  -- 30일 (분 단위)
    interval  => 30      -- 30분 간격
);

-- 최근 5분간 가장 많이 대기한 이벤트
SELECT event, COUNT(*) AS samples,
       ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM v$active_session_history
WHERE sample_time > SYSDATE - 5/1440
GROUP BY event
ORDER BY COUNT(*) DESC;

-- 특정 SQL이 무엇을 대기하는지
SELECT event, COUNT(*)
FROM v$active_session_history
WHERE sql_id = 'abc123def456'
  AND sample_time > SYSDATE - 1/24
GROUP BY event
ORDER BY COUNT(*) DESC;

-- 시간대별 활성 세션 추이
SELECT TO_CHAR(sample_time, 'HH24:MI') AS time_slot,
       COUNT(DISTINCT session_id) AS active_sessions
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY TO_CHAR(sample_time, 'HH24:MI')
ORDER BY time_slot;

-- ADDM 리포트 자동 확인 (최근 스냅샷)
SELECT dbms_advisor.get_task_report(task_name) AS report
FROM dba_advisor_tasks
WHERE task_name LIKE 'ADDM%'
ORDER BY created DESC
FETCH FIRST 1 ROW ONLY;

-- ADDM 권고안 확인
SELECT f.finding_name, r.type, r.message
FROM dba_advisor_findings f
JOIN dba_advisor_recommendations r
    ON f.task_id = r.task_id AND f.finding_id = r.finding_id
WHERE f.task_id = (SELECT MAX(task_id) FROM dba_advisor_tasks
                   WHERE advisor_name = 'ADDM');

-- Slow Query 상위 10개
SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1000000000000, 3) AS total_sec,
       ROUND(AVG_TIMER_WAIT/1000000000000, 3) AS avg_sec,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 현재 실행 중인 쿼리
SELECT thread_id, event_name, sql_text,
       ROUND(timer_wait/1000000000000, 3) AS elapsed_sec
FROM performance_schema.events_statements_current
WHERE sql_text IS NOT NULL;

-- 테이블별 I/O 통계
SELECT object_schema, object_name,
       count_read, count_write,
       ROUND(sum_timer_read/1000000000000, 3) AS read_sec,
       ROUND(sum_timer_write/1000000000000, 3) AS write_sec
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'performance_schema')
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- 인덱스 사용 현황
SELECT object_schema, object_name, index_name,
       count_star AS total_access,
       count_read, count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND object_schema NOT IN ('mysql', 'performance_schema')
ORDER BY count_star DESC;

-- 현재 설정 확인
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 동적으로 Slow Query Log 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1초 이상 쿼리 기록
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 인덱스 미사용 쿼리

-- InnoDB 상태 확인
SHOW ENGINE INNODB STATUS;
-- → TRANSACTIONS, SEMAPHORES, BUFFER POOL 섹션 확인

-- pg_stat_statements 확장 (가장 중요한 도구)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 가장 시간이 많이 걸리는 쿼리 상위 10개
SELECT query,
       calls,
       ROUND(total_exec_time::numeric, 2) AS total_ms,
       ROUND(mean_exec_time::numeric, 2) AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 테이블 통계 (순차 스캔 vs 인덱스 스캔)
SELECT relname,
       seq_scan, idx_scan,
       CASE WHEN seq_scan + idx_scan > 0
            THEN ROUND(idx_scan * 100.0 / (seq_scan + idx_scan), 1)
            ELSE 0 END AS idx_scan_pct,
       n_tup_ins, n_tup_upd, n_tup_del,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- 인덱스 사용률이 낮은 인덱스 (삭제 후보)
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan < 50
ORDER BY idx_scan;

-- 캐시 적중률
SELECT
    sum(heap_blks_hit) * 100.0 / (sum(heap_blks_hit) + sum(heap_blks_read))
        AS cache_hit_ratio
FROM pg_statio_user_tables;

-- VACUUM 필요성 확인 (Dead Tuple 비율)
SELECT relname,
       n_live_tup, n_dead_tup,
       ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

패턴 1: 갑작스러운 Slow Query 증가
  → 원인: 통계 정보 갱신으로 실행 계획 변경
  → 조치: 실행 계획 확인, 힌트 또는 통계 재수집

패턴 2: 특정 시간대 성능 저하
  → 원인: 배치 작업과 온라인 작업 충돌
  → 조치: 배치 시간 조정, 리소스 분리

패턴 3: 점진적 성능 저하
  → 원인: 데이터 증가, 인덱스 단편화
  → 조치: 파티셔닝, 인덱스 리빌드, 아카이빙

패턴 4: Lock 대기 급증
  → 원인: 긴 트랜잭션, 교착 상태
  → 조치: 트랜잭션 범위 축소, 접근 순서 통일

패턴 5: Connection 부족
  → 원인: 커넥션 풀 설정 부적절, 커넥션 누수
  → 조치: 풀 크기 조정, 애플리케이션 코드 점검