Oracle - 성능 모니터링

Oracle 성능 모니터링

오라클 데이터베이스의 성능을 모니터링하고 문제를 진단하는 방법을 다룹니다.

성능 모니터링은 데이터베이스의 현재 상태를 파악하고 병목 지점을 찾아 개선하는 데 필수적입니다. 다양한 모니터링 도구와 기법을 활용하여 시스템 성능을 지속적으로 관리할 수 있습니다.

AWR (Automatic Workload Repository)

AWR 스냅샷 관리

-- 수동 스냅샷 생성
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

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

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

-- AWR 리포트 생성 (PL/SQL)
SELECT output
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
  l_dbid => (SELECT dbid FROM v$database),
  l_inst_num => 1,
  l_bid => 100,  -- 시작 스냅샷 ID
  l_eid => 110   -- 종료 스냅샷 ID
));

AWR 설정 변경

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

-- 현재 설정 확인
SELECT snap_interval, retention
FROM dba_hist_wr_control;

대기 이벤트 분석

세션별 대기 이벤트

-- 현재 세션 대기 이벤트
SELECT event,
       total_waits,
       time_waited,
       average_wait,
       time_waited/SUM(time_waited) OVER () * 100 as pct
FROM v$session_event
WHERE sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1)
  AND wait_class != 'Idle'
ORDER BY time_waited DESC;

-- 모든 active 세션의 대기 이벤트
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       s.event,
       s.wait_class,
       s.state,
       s.seconds_in_wait
FROM v$session s
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
  AND s.wait_class != 'Idle'
ORDER BY s.seconds_in_wait DESC;

시스템 전체 대기 이벤트

-- 시스템 전체 대기 이벤트
SELECT event,
       total_waits,
       time_waited/100 as time_waited_sec,
       average_wait,
       wait_class
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

-- 대기 이벤트 히스토그램
SELECT event,
       wait_time_milli,
       wait_count
FROM v$event_histogram
WHERE event = 'db file sequential read'
ORDER BY wait_time_milli;

SQL 성능 분석

느린 SQL 찾기

-- 가장 느린 SQL 찾기 (경과 시간 기준)
SELECT sql_id,
       elapsed_time/1000000 as elapsed_sec,
       executions,
       elapsed_time/executions/1000 as avg_ms,
       cpu_time/1000000 as cpu_sec,
       buffer_gets,
       disk_reads,
       SUBSTR(sql_text, 1, 100) as sql_text
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- CPU 사용량 높은 SQL
SELECT sql_id,
       cpu_time/1000000 as cpu_sec,
       executions,
       cpu_time/executions/1000 as avg_cpu_ms,
       buffer_gets,
       SUBSTR(sql_text, 1, 100) as sql_text
FROM v$sql
WHERE executions > 0
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

-- 물리적 I/O 많은 SQL
SELECT sql_id,
       disk_reads,
       executions,
       disk_reads/executions as avg_disk_reads,
       buffer_gets,
       SUBSTR(sql_text, 1, 100) as sql_text
FROM v$sql
WHERE executions > 0
ORDER BY disk_reads DESC
FETCH FIRST 10 ROWS ONLY;

SQL 실행 통계

-- SQL 상세 통계
SELECT sql_id,
       plan_hash_value,
       executions,
       rows_processed,
       rows_processed/NULLIF(executions,0) as avg_rows,
       buffer_gets,
       buffer_gets/NULLIF(executions,0) as avg_gets,
       disk_reads,
       elapsed_time/1000000 as elapsed_sec,
       cpu_time/1000000 as cpu_sec
FROM v$sql
WHERE sql_id = 'your_sql_id';

-- SQL 실행 계획 변경 이력
SELECT sql_id,
       plan_hash_value,
       timestamp,
       optimizer_cost,
       optimizer_mode
FROM dba_hist_sql_plan
WHERE sql_id = 'your_sql_id'
ORDER BY timestamp DESC;

세션 모니터링

-- Active 세션 목록
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       s.machine,
       s.status,
       s.sql_id,
       s.event,
       s.blocking_session,
       ROUND(s.last_call_et/60, 2) as idle_min
FROM v$session s
WHERE s.username IS NOT NULL
ORDER BY s.last_call_et DESC;

-- 세션별 리소스 사용량
SELECT s.sid,
       s.username,
       t.value/1024/1024 as pga_mb,
       n.value as logical_reads
FROM v$session s,
     v$sesstat t,
     v$sesstat n
WHERE s.sid = t.sid
  AND s.sid = n.sid
  AND t.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'session pga memory')
  AND n.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'session logical reads')
  AND s.username IS NOT NULL
ORDER BY t.value DESC;

-- Blocking 세션 찾기
SELECT blocking_session,
       sid,
       serial#,
       username,
       sql_id,
       event,
       seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

성능 지표 모니터링

-- 데이터베이스 성능 지표
SELECT metric_name,
       value,
       metric_unit
FROM v$sysmetric
WHERE group_id = 2  -- 60초 평균
  AND metric_name IN (
    'Database CPU Time Ratio',
    'Database Wait Time Ratio',
    'Buffer Cache Hit Ratio',
    'Library Cache Hit Ratio',
    'Executions Per Sec',
    'User Transaction Per Sec'
  );

-- 시간대별 성능 추이 (AWR)
SELECT TO_CHAR(snap.begin_interval_time, 'YYYY-MM-DD HH24:MI') as snap_time,
       metric.metric_name,
       metric.value
FROM dba_hist_sysmetric_summary metric,
     dba_hist_snapshot snap
WHERE metric.snap_id = snap.snap_id
  AND metric.metric_name = 'Database CPU Time Ratio'
  AND snap.begin_interval_time >= SYSDATE - 1
ORDER BY snap.begin_interval_time;

테이블스페이스 모니터링

-- 테이블스페이스 사용률
SELECT tablespace_name,
       ROUND(used_space * 8192 / 1024 / 1024, 2) as used_mb,
       ROUND(tablespace_size * 8192 / 1024 / 1024, 2) as total_mb,
       ROUND(used_percent, 2) as used_pct
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

-- 데이터파일별 I/O 통계
SELECT tablespace_name,
       file_name,
       phyrds as physical_reads,
       phywrts as physical_writes,
       phyblkrd as blocks_read,
       phyblkwrt as blocks_written
FROM v$datafile df,
     v$filestat fs
WHERE df.file# = fs.file#
ORDER BY phyrds + phywrts DESC;

Top SQL 모니터링

-- 리소스 집약적인 SQL Top 10
WITH sql_stats AS (
  SELECT sql_id,
         elapsed_time,
         cpu_time,
         buffer_gets,
         disk_reads,
         executions,
         RANK() OVER (ORDER BY elapsed_time DESC) as rank_elapsed,
         RANK() OVER (ORDER BY cpu_time DESC) as rank_cpu,
         RANK() OVER (ORDER BY buffer_gets DESC) as rank_gets
  FROM v$sql
  WHERE executions > 0
)
SELECT sql_id,
       elapsed_time/1000000 as elapsed_sec,
       cpu_time/1000000 as cpu_sec,
       buffer_gets,
       disk_reads,
       executions,
       rank_elapsed,
       rank_cpu,
       rank_gets
FROM sql_stats
WHERE rank_elapsed <= 10
   OR rank_cpu <= 10
   OR rank_gets <= 10
ORDER BY elapsed_time DESC;

ASH (Active Session History)

-- 최근 활동 세션 분석
SELECT session_id,
       session_serial#,
       user_id,
       sql_id,
       event,
       wait_class,
       COUNT(*) as sample_count
FROM v$active_session_history
WHERE sample_time >= SYSDATE - INTERVAL '1' HOUR
GROUP BY session_id, session_serial#, user_id, sql_id, event, wait_class
ORDER BY sample_count DESC
FETCH FIRST 20 ROWS ONLY;

-- 시간대별 대기 이벤트 분석 (AWR)
SELECT TO_CHAR(h.sample_time, 'YYYY-MM-DD HH24:MI') as sample_time,
       h.event,
       COUNT(*) as wait_count
FROM dba_hist_active_sess_history h
WHERE h.sample_time >= SYSDATE - 1
  AND h.wait_class != 'Idle'
GROUP BY TO_CHAR(h.sample_time, 'YYYY-MM-DD HH24:MI'), h.event
ORDER BY sample_time, wait_count DESC;

성능 모니터링 체크리스트

• AWR 리포트 정기 검토
• Top SQL 모니터링
• 대기 이벤트 분석
• Blocking 세션 확인
• 테이블스페이스 사용률 체크
• 버퍼 캐시 히트율 확인
• CPU 사용률 모니터링
• I/O 성능 분석
• 세션 수 추이 확인
• 장기 실행 쿼리 모니터링

실전 예제

-- 예제 1: 성능 문제 진단 스크립트
-- Step 1: 시스템 전반 성능 확인
SELECT metric_name, value
FROM v$sysmetric
WHERE group_id = 2
  AND metric_name IN (
    'Database CPU Time Ratio',
    'Database Wait Time Ratio',
    'Buffer Cache Hit Ratio'
  );

-- Step 2: Top 대기 이벤트
SELECT event, total_waits, time_waited
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 5 ROWS ONLY;

-- Step 3: Top SQL
SELECT sql_id, elapsed_time/1000000 as elapsed_sec, executions
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

-- 예제 2: 주간 성능 리포트
SELECT TO_CHAR(snap.begin_interval_time, 'YYYY-MM-DD') as day,
       AVG(CASE WHEN metric.metric_name = 'Database CPU Time Ratio'
           THEN metric.value END) as avg_cpu_ratio,
       AVG(CASE WHEN metric.metric_name = 'Executions Per Sec'
           THEN metric.value END) as avg_exec_per_sec,
       AVG(CASE WHEN metric.metric_name = 'User Transaction Per Sec'
           THEN metric.value END) as avg_txn_per_sec
FROM dba_hist_sysmetric_summary metric,
     dba_hist_snapshot snap
WHERE metric.snap_id = snap.snap_id
  AND snap.begin_interval_time >= SYSDATE - 7
GROUP BY TO_CHAR(snap.begin_interval_time, 'YYYY-MM-DD')
ORDER BY day;

관련 문서

• 쿼리 튜닝 가이드
• 실행 계획 분석
• 통계 정보 관리