성능 문제 식별 방법론과 튜닝 접근법

성능 문제 식별 방법론과 튜닝 접근법

성능 문제 분석 방법론

Top-Down 접근법 (시스템 → 세션 → SQL)

• • 단계별 분석 순서:**

``` 시스템 전체 성능 → 인스턴스 레벨 → 세션 레벨 → SQL 레벨 → 오브젝트 레벨 ```

• • 장점:**

- 전체적인 시스템 상황 파악 가능 - 우선순위가 높은 문제부터 식별 - 리소스 사용률 기반의 객관적 분석

• • 분석 도구:**

- ADDM (Automatic Database Diagnostic Monitor) - AWR (Automatic Workload Repository) - Statspack (AWR 대체)

Bottom-Up 접근법 (SQL → 세션 → 시스템)

• • 적용 시나리오:**

- 특정 애플리케이션의 성능 문제 - 특정 SQL 문장의 성능 저하 - 사용자 불만 기반 문제 해결

• • 분석 도구:**

- SQL Trace (10046 Event) - ASH (Active Session History) - SQL Tuning Advisor

성능 문제 분류 체계

문제 유형별 분류

응답 시간 문제 (Response Time Issues)

-- 응답 시간 분석을 위한 기본 쿼리
SELECT 
    s.sid, s.serial=, s.username, s.machine, s.program, s
    s.status, s.last_call_et,
    w.event, w.state, w.wait_time, w.seconds_in_wait,
    sq.sql_text
FROM v$session s, v$session_wait w, v$sqlarea sq
WHERE s.sid = w.sid WHERE
  AND s.sql_address = sq.address(+) AND
  AND s.sql_hash_value = sq.hash_value(+) AND
  AND s.status = 'ACTIVE' AND
ORDER BY s.last_call_et DESC;

처리량 문제 (Throughput Issues)

-- 시간당 트랜잭션 처리량 분석
SELECT 
    TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24') as hour,
    (SELECT value FROM dba_hist_sysstat dhss2 
     WHERE dhss2.snap_id = dhs.snap_id  WHERE
       AND dhss2.stat_name = 'user commits') /  AND
    (EXTRACT(HOUR FROM (end_interval_time - begin_interval_time)) * 3600 + 
     EXTRACT(MINUTE FROM (end_interval_time - begin_interval_time)) * 60) as tps
FROM dba_hist_snapshot dhs
WHERE begin_interval_time >= SYSDATE - 7 WHERE
ORDER BY begin_interval_time;

리소스 고갈 문제 (Resource Exhaustion)

-- 메모리 사용률 분석
SELECT 
    component, 
    current_size/1024/1024 as current_mb,
    max_size/1024/1024 as max_mb,
    (current_size/max_size)*100 as usage_pct
FROM v$memory_dynamic_components
WHERE max_size > 0
ORDER BY usage_pct DESC;

문제 심각도 분류

긴급도 매트릭스

| 심각도 | 영향 범위 | 응답 시간 | 비즈니스 영향 | |--------|-----------|-----------|---------------| | P1 | 전체 시스템 | > 300% 증가 | 서비스 중단 | | P2 | 특정 모듈 | 100-300% 증가 | 기능 제한 | | P3 | 개별 기능 | 50-100% 증가 | 사용자 불편 | | P4 | 미미한 영향 | < 50% 증가 | 성능 개선 여지 |

Wait Event 기반 근본 원인 분석

Wait Event 분류 및 진단

User I/O 관련 Wait Events

-- I/O 관련 대기 이벤트 상세 분석
SELECT 
    event, 
    total_waits, 
    total_timeouts,
    time_waited,
    average_wait,
    time_waited/total_waits*1000 as avg_wait_ms
FROM v$system_event 
WHERE wait_class = 'User I/O' WHERE
  AND total_waits > 0
ORDER BY time_waited DESC;

-- 파일별 I/O 통계
SELECT 
    df.tablespace_name,
    df.file_name,
    fs.phyblkrd as blocks_read,
    fs.phyblkwrt as blocks_written,
    fs.readtim as read_time,
    fs.writetim as write_time,
    CASE WHEN fs.phyblkrd > 0 
         THEN fs.readtim/fs.phyblkrd*10 
         ELSE 0 END as avg_read_time_ms
FROM dba_data_files df, v$filestat fs
WHERE df.file_id = fs.file= WHERE
ORDER BY avg_read_time_ms DESC;

• • 주요 I/O Wait Events:**

- `db file sequential read`: 인덱스 스캔, ROWID 액세스 - `db file scattered read`: Full Table Scan - `direct path read`: 병렬 처리, LOB 액세스 - `log file sync`: 커밋 대기

Concurrency 관련 Wait Events

-- Lock 경합 분석
SELECT 
    s1.sid as blocker_sid,
    s1.username as blocker_user,
    s2.sid as waiter_sid,
    s2.username as waiter_user,
    lo.object_id,
    do.object_name,
    lo.locked_mode,
    s2.seconds_in_wait
FROM v$locked_object lo, dba_objects do, v$session s1, v$session s2
WHERE lo.object_id = do.object_id WHERE
  AND lo.session_id = s1.sid AND
  AND s2.blocking_session = s1.sid; AND

• • 주요 Concurrency Wait Events:**

- `enq: TX - row lock contention`: 행 레벨 lock 경합 - `enq: TM - contention`: 테이블 lock 경합 - `latch: cache buffers chains`: Buffer Cache 경합 - `library cache lock`: 공유 SQL 영역 경합

Wait Event 패턴 분석

시간대별 Wait Event 트렌드

-- 시간대별 주요 Wait Event 분석 (AWR 기반)
SELECT 
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') as snapshot_time,
    e.event_name,
    e.total_waits - LAG(e.total_waits) OVER (
        PARTITION BY e.event_name ORDER BY s.snap_id
    ) as waits_delta,
    ROUND((e.time_waited_micro - LAG(e.time_waited_micro) OVER (
        PARTITION BY e.event_name ORDER BY s.snap_id
    ))/1000000, 2) as time_waited_sec
FROM dba_hist_system_event e, dba_hist_snapshot s
WHERE e.snap_id = s.snap_id WHERE
  AND s.begin_interval_time >= SYSDATE - 1 AND
  AND e.wait_class != 'Idle' AND
  AND e.event_name IN (
      'db file sequential read',
      'db file scattered read', 
      'log file sync',
      'enq: TX - row lock contention'
  )
ORDER BY snapshot_time, time_waited_sec DESC;

시스템 리소스 분석

CPU 사용률 분석

OS Level CPU 분석

-- CPU 사용률 히스토리 (AWR 기반)
SELECT 
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') as time,
    os1.value as num_cpus,
    os2.value as load_avg,
    ROUND(os2.value/os1.value * 100, 2) as cpu_util_pct
FROM dba_hist_osstat os1, dba_hist_osstat os2, dba_hist_snapshot s
WHERE os1.snap_id = os2.snap_id WHERE
  AND os1.snap_id = s.snap_id AND
  AND os1.stat_name = 'NUM_CPUS' AND
  AND os2.stat_name = 'LOAD' AND
  AND s.begin_interval_time >= SYSDATE - 1 AND
ORDER BY time;

Oracle Process CPU 사용률

-- 세션별 CPU 사용률
SELECT 
    s.sid, s.serial=, s.username, s.machine, s.program, s
    ss.value/100 as cpu_used_seconds,
    s.last_call_et as seconds_since_last_call
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid WHERE
  AND ss.statistic= = sn.statistic= AND
  AND sn.name = 'CPU used by this session' AND
  AND ss.value > 0
ORDER BY ss.value DESC;

메모리 사용률 분석

SGA 구성요소별 분석

-- SGA 구성요소별 사용률
SELECT 
    component,
    current_size/1024/1024 as current_mb,
    min_size/1024/1024 as min_mb,
    max_size/1024/1024 as max_mb,
    ROUND((current_size/max_size)*100, 2) as usage_pct,
    resize_ops,
    last_oper_type
FROM v$memory_dynamic_components
WHERE current_size > 0
ORDER BY current_mb DESC;

-- Buffer Cache Hit Ratio 상세 분석
SELECT 
    name,
    physical_reads,
    db_block_gets,
    consistent_gets,
    ROUND((1 - (physical_reads / (db_block_gets + consistent_gets))) * 100, 2) as hit_ratio
FROM v$buffer_pool_statistics;

PGA 메모리 분석

-- 세션별 PGA 사용량
SELECT 
    s.sid, s.serial=, s.username, s.program, s
    ROUND(p.value/1024/1024, 2) as pga_used_mb,
    ROUND(p2.value/1024/1024, 2) as pga_alloc_mb,
    ROUND(p3.value/1024/1024, 2) as pga_max_mb
FROM v$session s, v$process pr, v$sesstat p, v$sesstat p2, v$sesstat p3,
     v$statname sn, v$statname sn2, v$statname sn3
WHERE s.paddr = pr.addr WHERE
  AND s.sid = p.sid AND p.statistic= = sn.statistic= AND
  AND s.sid = p2.sid AND p2.statistic= = sn2.statistic= AND
  AND s.sid = p3.sid AND p3.statistic= = sn3.statistic= AND
  AND sn.name = 'session pga memory' AND
  AND sn2.name = 'session pga memory max' AND
  AND sn3.name = 'session uga memory max' AND
  AND p.value > 1024*1024
ORDER BY pga_used_mb DESC;

SQL 레벨 근본 원인 분석

Top SQL 식별 및 분석

리소스 소비량 기준 Top SQL

-- CPU 시간 기준 Top SQL
SELECT 
    sql_id,
    child_number,
    executions,
    ROUND(cpu_time/1000000, 2) as cpu_time_sec,
    ROUND(elapsed_time/1000000, 2) as elapsed_time_sec,
    ROUND(cpu_time/executions/1000, 2) as avg_cpu_ms,
    disk_reads,
    buffer_gets,
    rows_processed,
    SUBSTR(sql_text, 1, 100) as sql_text_preview
FROM v$sql
WHERE executions > 0
  AND cpu_time > 0
ORDER BY cpu_time DESC
FETCH FIRST 20 ROWS ONLY;

-- Logical I/O 기준 Top SQL
SELECT 
    sql_id,
    executions,
    buffer_gets,
    ROUND(buffer_gets/executions, 2) as avg_lio_per_exec,
    disk_reads,
    ROUND(disk_reads/executions, 2) as avg_pio_per_exec,
    rows_processed,
    SUBSTR(sql_text, 1, 100) as sql_text_preview
FROM v$sql
WHERE executions > 0
ORDER BY buffer_gets DESC
FETCH FIRST 20 ROWS ONLY;

실행 계획 분석

비효율적인 실행 계획 패턴 식별

-- Full Table Scan이 발생하는 SQL 식별
SELECT DISTINCT
    p.sql_id,
    COUNT(*) as fts_operations,
    s.executions,
    s.cpu_time/1000000 as cpu_sec,
    SUBSTR(s.sql_text, 1, 100) as sql_preview
FROM v$sql_plan p, v$sql s
WHERE p.sql_id = s.sql_id WHERE
  AND p.child_number = s.child_number AND
  AND p.operation = 'TABLE ACCESS' AND
  AND p.options = 'FULL' AND
  AND s.executions > 10
GROUP BY p.sql_id, s.executions, s.cpu_time, s.sql_text
ORDER BY cpu_sec DESC;

-- 카디널리티 추정 오류 식별
SELECT 
    sql_id, child_number, operation, object_name,
    cardinality as estimated_rows,
    last_cr_buffer_gets as actual_buffer_gets,
    CASE WHEN cardinality > 0 
         THEN ROUND(last_cr_buffer_gets/cardinality, 2)
         ELSE 0 END as actual_vs_estimated_ratio
FROM v$sql_plan_statistics_all
WHERE last_cr_buffer_gets > 0
  AND cardinality > 0
  AND ABS(LOG(10, last_cr_buffer_gets/cardinality)) > 1
ORDER BY actual_vs_estimated_ratio DESC;

애플리케이션 레벨 분석

Connection Pool 분석

-- 세션 상태별 분포
SELECT 
    status,
    COUNT(*) as session_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage
FROM v$session
WHERE type = 'USER' WHERE
GROUP BY status
ORDER BY session_count DESC;

-- 장시간 비활성 세션 식별
SELECT 
    sid, serial=, username, machine, program, sid
    status, ROUND(last_call_et/3600, 2) as hours_inactive,
    logon_time
FROM v$session
WHERE type = 'USER' WHERE
  AND status = 'INACTIVE' AND
  AND last_call_et > 3600
ORDER BY last_call_et DESC;

트랜잭션 패턴 분석

-- 장기 실행 트랜잭션 식별
SELECT 
    s.sid, s.serial=, s.username, s.machine, s
    t.start_time,
    ROUND((SYSDATE - t.start_date) * 24 * 60, 2) as duration_minutes,
    t.used_ublk as undo_blocks_used,
    r.name as rollback_segment
FROM v$session s, v$transaction t, v$rollname r
WHERE s.taddr = t.addr WHERE
  AND t.xidusn = r.usn AND
  AND (SYSDATE - t.start_date) * 24 * 60 > 30
ORDER BY duration_minutes DESC;

실전 진단 시나리오

시나리오 1: 갑작스러운 전체 시스템 성능 저하

• • 진단 순서:**

1. **즉시 확인 사항**

   -- 현재 활성 세션 및 대기 이벤트
   SELECT event, count(*) as sessions
   FROM v$session_wait
   WHERE wait_class != 'Idle' WHERE
   GROUP BY event
   ORDER BY sessions DESC;

2. **시스템 리소스 확인**

   -- CPU 및 메모리 사용률 확인
   SELECT metric_name, value, metric_unit
   FROM v$sysmetric
   WHERE metric_name IN (
       'Host CPU Utilization (%)',
       'Current OS Load',
       'Memory Usage (%)'
   );

3. **AWR 스냅샷 생성 및 분석**

   -- 수동 AWR 스냅샷 생성
   EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
   ```

=== 시나리오 2: 특정 시간대 성능 저하 ===

**원인 분석 절차:**
<source lang=sql>
-- 문제 시간대의 Top Wait Events
SELECT 
    event_name,
    wait_class,
    total_waits_delta,
    ROUND(time_waited_delta_micro/1000000, 2) as time_waited_sec
FROM (
    SELECT 
        e.event_name,
        e.wait_class,
        e.total_waits - LAG(e.total_waits) OVER (
            PARTITION BY e.event_name ORDER BY e.snap_id
        ) as total_waits_delta,
        e.time_waited_micro - LAG(e.time_waited_micro) OVER (
            PARTITION BY e.event_name ORDER BY e.snap_id  
        ) as time_waited_delta_micro
    FROM dba_hist_system_event e, dba_hist_snapshot s
    WHERE e.snap_id = s.snap_id WHERE
      AND s.begin_interval_time BETWEEN 
          TO_DATE('2024-01-15 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
          TO_DATE('2024-01-15 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
      AND e.wait_class != 'Idle' AND
)
WHERE total_waits_delta > 0
ORDER BY time_waited_sec DESC;

자동화된 근본 원인 분석 도구

ADDM 보고서 자동 생성 스크립트

-- ADDM 보고서 생성 및 분석
DECLARE
    task_name VARCHAR2(30) := 'ADDM_ANALYSIS_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI'); task_name
    begin_snap NUMBER;
    end_snap NUMBER;
BEGIN
    SELECT MAX(snap_id) - 1, MAX(snap_id) 
    INTO begin_snap, end_snap
    FROM dba_hist_snapshot;
    
    DBMS_ADDM.ANALYZE_DB(
        task_name => task_name, task_name
        begin_snapshot => begin_snap, begin_snapshot
        end_snapshot => end_snap end_snapshot
    );
    
    -- 권고사항 조회
    FOR rec IN (
        SELECT finding_name, impact, message
        FROM dba_addm_findings 
        WHERE task_name = task_name WHERE
        ORDER BY impact DESC
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Finding: ' || rec.finding_name);
        DBMS_OUTPUT.PUT_LINE('Impact: ' || rec.impact);
        DBMS_OUTPUT.PUT_LINE('Message: ' || rec.message);
        DBMS_OUTPUT.PUT_LINE('---');
    END LOOP;
END;
/