모니터링과 성능 진단

-- 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, wait_class, COUNT(*) AS samples
FROM v$active_session_history
WHERE sql_id = 'abc123def456'
  AND sample_time > SYSDATE - 1/24
GROUP BY event, wait_class
ORDER BY samples DESC;

-- 블로킹 세션 후보
SELECT blocking_session, COUNT(*) AS samples
FROM v$active_session_history
WHERE blocking_session IS NOT NULL
  AND sample_time > SYSDATE - 10/1440
GROUP BY blocking_session
ORDER BY samples DESC;

-- 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개 (정규화된 digest 기준)
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;

-- MySQL 8.0.1+ InnoDB 락 대기
SELECT waiting_engine_transaction_id AS waiting_trx,
       blocking_engine_transaction_id AS blocking_trx
FROM performance_schema.data_lock_waits;

-- 현재 설정 확인
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;  -- 기준선에 맞게 조정
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 미인덱스 쿼리 로그가 폭증할 때는 throttle 검토
SET GLOBAL log_throttle_queries_not_using_indexes = 60;

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

-- postgresql.conf
-- shared_preload_libraries = '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;

-- 현재 실행 중인 세션
SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS running_time,
       query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running_time DESC;

-- 테이블 통계 (순차 스캔 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
    sum(heap_blks_hit) * 100.0 /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- VACUUM 필요성 확인
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;

-- 실행 계획 확인: DML은 트랜잭션 안에서 롤백하며 확인
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
ROLLBACK;