DB스터디 - 사용자 기여 [ko]

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

2025-10-22T06:25:56Z

Jhwang327: /* 처리량 문제 (Throughput Issues) */

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

== 성능 문제 분석 방법론 ==

=== 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) ====
::::<source lang=sql>
-- 응답 시간 분석을 위한 기본 쿼리
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;
</source>

==== 처리량 문제 (Throughput Issues) ====
::::<source lang=sql>
-- 시간당 트랜잭션 처리량 분석
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;
</source>

==== 리소스 고갈 문제 (Resource Exhaustion) ====
<source lang=sql>
-- 메모리 사용률 분석
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;
</source>

=== 문제 심각도 분류 ===

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

== Wait Event 기반 근본 원인 분석 ==

=== Wait Event 분류 및 진단 ===

==== User I/O 관련 Wait Events ====
<source lang=sql>
-- 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;
</source>

**주요 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 ====
<source lang=sql>
-- 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
</source>

**주요 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 트렌드 ====
<source lang=sql>
-- 시간대별 주요 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;
</source>

== 시스템 리소스 분석 ==

=== CPU 사용률 분석 ===

==== OS Level CPU 분석 ====
<source lang=sql>
-- 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;
</source>

==== Oracle Process CPU 사용률 ====
<source lang=sql>
-- 세션별 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;
</source>

=== 메모리 사용률 분석 ===

==== SGA 구성요소별 분석 ====
<source lang=sql>
-- 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;
</source>

==== PGA 메모리 분석 ====
<source lang=sql>
-- 세션별 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;
</source>

== SQL 레벨 근본 원인 분석 ==

=== Top SQL 식별 및 분석 ===

==== 리소스 소비량 기준 Top SQL ====
<source lang=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;
</source>

=== 실행 계획 분석 ===

==== 비효율적인 실행 계획 패턴 식별 ====
<source lang=sql>
-- 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;
</source>

== 애플리케이션 레벨 분석 ==

=== Connection Pool 분석 ===
<source lang=sql>
-- 세션 상태별 분포
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;
</source>

=== 트랜잭션 패턴 분석 ===
<source lang=sql>
-- 장기 실행 트랜잭션 식별
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;
</source>

== 실전 진단 시나리오 ==

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

**진단 순서:**
1. **즉시 확인 사항**
<source lang=sql>
-- 현재 활성 세션 및 대기 이벤트
SELECT event, count(*) as sessions
FROM v$session_wait
WHERE wait_class != 'Idle' WHERE
GROUP BY event
ORDER BY sessions DESC;
</source>

2. **시스템 리소스 확인**
<source lang=sql>
-- CPU 및 메모리 사용률 확인
SELECT metric_name, value, metric_unit
FROM v$sysmetric
WHERE metric_name IN (
'Host CPU Utilization (%)',
'Current OS Load',
'Memory Usage (%)'
);
</source>

3. **AWR 스냅샷 생성 및 분석**
<source lang=sql>
-- 수동 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;
</source>
== 자동화된 근본 원인 분석 도구 ==

=== ADDM 보고서 자동 생성 스크립트 ===
<source lang=sql>
-- 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;
/
</source>

[[category:oracle]]

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

2025-10-22T06:25:45Z

Jhwang327: /* 응답 시간 문제 (Response Time Issues) */

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

== 성능 문제 분석 방법론 ==

=== 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) ====
::::<source lang=sql>
-- 응답 시간 분석을 위한 기본 쿼리
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;
</source>

==== 처리량 문제 (Throughput Issues) ====
<source lang=sql>
-- 시간당 트랜잭션 처리량 분석
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;
</source>

==== 리소스 고갈 문제 (Resource Exhaustion) ====
<source lang=sql>
-- 메모리 사용률 분석
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;
</source>

=== 문제 심각도 분류 ===

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

== Wait Event 기반 근본 원인 분석 ==

=== Wait Event 분류 및 진단 ===

==== User I/O 관련 Wait Events ====
<source lang=sql>
-- 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;
</source>

**주요 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 ====
<source lang=sql>
-- 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
</source>

**주요 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 트렌드 ====
<source lang=sql>
-- 시간대별 주요 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;
</source>

== 시스템 리소스 분석 ==

=== CPU 사용률 분석 ===

==== OS Level CPU 분석 ====
<source lang=sql>
-- 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;
</source>

==== Oracle Process CPU 사용률 ====
<source lang=sql>
-- 세션별 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;
</source>

=== 메모리 사용률 분석 ===

==== SGA 구성요소별 분석 ====
<source lang=sql>
-- 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;
</source>

==== PGA 메모리 분석 ====
<source lang=sql>
-- 세션별 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;
</source>

== SQL 레벨 근본 원인 분석 ==

=== Top SQL 식별 및 분석 ===

==== 리소스 소비량 기준 Top SQL ====
<source lang=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;
</source>

=== 실행 계획 분석 ===

==== 비효율적인 실행 계획 패턴 식별 ====
<source lang=sql>
-- 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;
</source>

== 애플리케이션 레벨 분석 ==

=== Connection Pool 분석 ===
<source lang=sql>
-- 세션 상태별 분포
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;
</source>

=== 트랜잭션 패턴 분석 ===
<source lang=sql>
-- 장기 실행 트랜잭션 식별
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;
</source>

== 실전 진단 시나리오 ==

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

**진단 순서:**
1. **즉시 확인 사항**
<source lang=sql>
-- 현재 활성 세션 및 대기 이벤트
SELECT event, count(*) as sessions
FROM v$session_wait
WHERE wait_class != 'Idle' WHERE
GROUP BY event
ORDER BY sessions DESC;
</source>

2. **시스템 리소스 확인**
<source lang=sql>
-- CPU 및 메모리 사용률 확인
SELECT metric_name, value, metric_unit
FROM v$sysmetric
WHERE metric_name IN (
'Host CPU Utilization (%)',
'Current OS Load',
'Memory Usage (%)'
);
</source>

3. **AWR 스냅샷 생성 및 분석**
<source lang=sql>
-- 수동 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;
</source>
== 자동화된 근본 원인 분석 도구 ==

=== ADDM 보고서 자동 생성 스크립트 ===
<source lang=sql>
-- 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;
/
</source>

[[category:oracle]]

분석 단계

2025-10-22T06:24:15Z

Jhwang327:

=== [[분석 단계]] ===
==== [[산출물]] ====
::::# [[인터뷰 계획서]]
::::# [[인터뷰 결과서]]
::::# [[요구사항 정의서]]
::::# [[현행 시스템 분석서]]
::::## 별첨1)현행시스템분석서-DB아키텍처)-별첨1-현행_데이터파일현황.xlsx
::::## 별첨2)현행시스템분석서-DB아키텍처)-별첨1-현행_테이블스페이스현황.xlsx
::::# [[아키텍처 정의서]]
* ㅁㄴㅇㄹ호
==== [[주요 작업 TASK]] ====
# [[AS-IS 구조 분석 기술환경 파악]]
# [[요구사항 및 문제점 도출]]
# [[TO-BE 설계를 위한 기초자료 확보]]

----

분석 단계

2025-10-22T06:23:20Z

Jhwang327:

=== [[분석 단계]] ===
==== [[산출물]] ====
# [[인터뷰 계획서]]
# [[인터뷰 결과서]]
# [[요구사항 정의서]]
# [[현행 시스템 분석서]]
## 별첨1)현행시스템분석서-DB아키텍처)-별첨1-현행_데이터파일현황.xlsx
## 별첨2)현행시스템분석서-DB아키텍처)-별첨1-현행_테이블스페이스현황.xlsx
# [[아키텍처 정의서]]
* ㅁㄴㅇㄹ호
==== [[주요 작업 TASK]] ====
# [[AS-IS 구조 분석 기술환경 파악]]
# [[요구사항 및 문제점 도출]]
# [[TO-BE 설계를 위한 기초자료 확보]]

----

테스트3

2025-10-22T06:22:32Z

Jhwang327:

=글쓰기 테스트=

==제목==
=== ㅇㅇㅇㅇ ===
# 11111
# 하나
# 둘
# 둘2
# 다섯
# 넷

4.셋
=== 333 ===
# ㅠ111
#@ 111

테스트3

2025-10-22T06:21:48Z

Jhwang327:

=글쓰기 테스트=

==제목==

# 11111
# 하나
# 둘
# 둘2
# 다섯
# 넷

4.셋

===제목2===

====제목3====

테스트3

2025-10-22T06:19:29Z

Jhwang327:

글쓰기 테스트2

제목1

# 하나]
# 둘
# 다섯
# 넷

4.셋

제목2

제목3

테스트3

2025-10-22T06:17:45Z

Jhwang327:

글쓰기 테스트2

제목1

제목2

제목3

테스트3

2025-10-22T06:17:31Z

Jhwang327: 새 문서: 글쓰기 테스트1 제목1 제목2 제목3

글쓰기 테스트1

제목1

제목2

제목3