Admin 실습 06: 성능 모니터링 & AWR, Resumable

실습 스크립트 전체 보기: https://github.com/nsylove97/Seongryeol-OracleDB-Portfolio

실습 환경

OS	Oracle Linux 7.9 (VMware Virtual Machine)
DB	Oracle Database 19c
접속 툴	SQL*Plus, MobaXterm(SSH)

---

1. 데이터베이스 관리 개요

• response time = 실행 time(CPU 처리 시간) + wait time(대기 시간)
  • 실행 시간을 줄이는 것 → SQL 튜닝
  • 대기 시간을 줄이는 것 → 서버 튜닝

오라클은 성능 문제를 진단하고 자동으로 관리하기 위한 여러 도구를 제공함

구성 요소	설명
AWR	성능 스냅샷 자동 수집(Top SQL, 대기 이벤트 등) → 튜닝 근거 제공
Advisors	메모리 / 세그먼트 / SQL에 대해 개선 권고 제시
Automated Tasks	옵티마이저 통계 갱신, 세그먼트 점검 등 정기 자동 작업 수행
Server Alerts	공간 부족 같은 임계치 경보 발생 시 알림
ADR	에러 / 크래시 발생 시 로그·트레이스 자동 보관 → 원인 분석 지원

 

대기 이벤트 확인

-- DB가 어떤 이유로 대기하는지 이벤트 목록 확인
SELECT name, wait_class FROM v$event_name;

/*
 [결과]
   NAME                          WAIT_CLASS
   ----------------------------  ----------
   ...                           ...
   → 1000개 이상의 대기 이벤트 정의됨
*/

대기 이벤트 1920개 확인

---

2. 성능 모니터링

성능 저하의 주요 원인

원인	설명
Memory allocation issues	메모리(캐시/임시공간)가 부족하거나 잘못 배분되어 성능 저하
Resource contention	여러 작업이 같은 자원(락, 래치, CPU)을 동시에 사용하려다 대기 발생
Network bottlenecks	클라이언트↔DB 간 통신 지연으로 응답 지연
I/O device contention	디스크 읽기/쓰기 속도 한계로 대기 발생
Application code problems	비효율 SQL·로직으로 불필요한 처리 증가

 

Top Sessions 조회

-- 현재 DB에서 리소스를 가장 많이 사용하는 세션 조회
-- (CPU / PGA / Logical Reads / Physical Reads / Hard Parse / Sort 기준)
SELECT * FROM v$sess_time_model
ORDER  BY value DESC
FETCH FIRST 10 ROWS ONLY;

Top Sessions 조회, 백그라운드 프로세스가 가장 많이 리소스 사용

지표	설명
CPU	CPU를 가장 많이 사용하는 세션
PGA Memory	작업용 메모리를 많이 사용하는 세션
Logical Reads	메모리에서 블록을 많이 읽음 (풀 스캔 의심)
Physical Reads	디스크에서 많이 읽음 (I/O 부담 큼)
Hard Parse	매번 실행 계획을 새로 생성 (바인드 변수 미사용 의심)
Sort	정렬 작업이 많아 Temporary 테이블스페이스 사용 가능성 있음

 

메모리 리사이즈 이력 확인

-- 오라클이 자동으로 메모리 크기를 늘리거나 줄인 이력 조회
SELECT component, oper_type, final_size, target_size, start_time
FROM   v$memory_resize_ops
ORDER  BY start_time DESC;

메모리 리사이즈 이력 확인, 변화 없음 (리사이즈 이력 없음)

-- SGA 각 구성 요소의 현재 크기 및 최소/최대 범위 확인
SELECT component, current_size, min_size, max_size, granule_size
FROM   v$memory_dynamic_components;

SGA 구성 요소 현재 크기 및 최소/최대 범위 확인

---

3. 오라클 메모리 관리 방식

방식	설정 파라미터	특징
AMM	MEMORY_TARGET	SGA+PGA 통합 자동 관리. 간단하지만 Memory Advisor만 사용 가능
ASMM	SGA_TARGET	SGA만 자동 배분. SGA 관련 Advisor + PGA Advisor 사용 가능
수동	개별 파라미터 직접 지정	Shared Pool, Buffer Cache 등 각 영역을 직접 크기 설정

 

Memory Advisor — 메모리 크기 변경 효과 예측

-- MEMORY_TARGET을 늘리거나 줄였을 때 DB 성능 변화를 예상해주는 뷰
SELECT memory_size, memory_size_factor, estd_db_time, estd_db_time_factor
FROM   v$memory_target_advice
ORDER  BY memory_size;

/*
 [결과 예시]
   MEMORY_SIZE  MEMORY_SIZE_FACTOR  ESTD_DB_TIME  ESTD_DB_TIME_FACTOR
   -----------  ------------------  ------------  -------------------
   400          0.5                 ...           ...
   800          1.0                 ...           1.0                 ← 현재 값
   1200         1.5                 ...           ...
*/

메모리를 늘이거나 줄여도 DB Time 변화 없음

-- statistics_level 파라미터 확인 (AWR 정상 작동 전제 조건)
SHOW PARAMETER statistics_level

/*
 [결과]
   NAME               TYPE    VALUE
   -----------------  ------  -------
   statistics_level   string  TYPICAL  ← TYPICAL이어야 AWR 정상 작동 (BASIC이면 AWR 미수집)
*/

statistics_level 파라미터 TYPICAL 확인

---

4. AWR (Automatic Workload Repository)

• DB 성능 스냅샷(ASH, Top SQL, 대기 이벤트 등)을 주기적으로 자동 수집·보관하는 저장소
• 데이터는 SYSAUX 테이블스페이스에 저장됨
• 과거 시점 성능 원인 분석 및 기간 비교 리포트 생성에 활용

AWR 스냅샷 현황 확인

-- AWR 스냅샷 목록 확인
-- (날짜 포맷 먼저 설정해두면 보기 편함)
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

SELECT snap_id, begin_interval_time, end_interval_time
FROM   dba_hist_snapshot
ORDER  BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;

AWR 스냅샷 목록 확인

AWR 스냅샷 수동 생성

-- AWR 스냅샷 즉시 한 번 더 수집
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- 생성 확인
SELECT snap_id, begin_interval_time
FROM   dba_hist_snapshot
ORDER  BY snap_id DESC
FETCH FIRST 3 ROWS ONLY;

수동 생성된 AWR 스냅샷 확인 가능

 

AWR 보관 기간 및 수집 주기 변경

-- 현재 AWR 설정 확인 (보관 기간, 스냅샷 주기)
SELECT snap_interval, retention
FROM   dba_hist_wr_control;

/*
 [결과 예시]
   SNAP_INTERVAL     RETENTION
   ----------------  ----------------
   +00000 01:00:00   +00008 00:00:00   ← 1시간 주기, 8일 보관
*/

현재 AWR 스냅샷 주기 및 보관 기간 확인 (1시간 주기, 8일 보관)

-- AWR 보관 기간 변경 (단위: 분)
-- 스냅샷 주기 60분, 보관 기간 14일(20160분)으로 변경
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    interval  => 60,
    retention => 20160
);

-- 변경 확인
SELECT snap_interval, retention
FROM   dba_hist_wr_control;

스냅샷 보관 기간을 14일로 변경 후 확인

AWR 베이스라인 생성

-- AWR 스냅샷 목록 확인
SELECT snap_id, begin_interval_time
FROM   dba_hist_snapshot
ORDER  BY snap_id DESC

-- 정상 운영 구간(예: 3월 7일)을 베이스라인으로 이름 붙여 저장
-- 이후 특정 구간과 비교할 때 기준으로 활용
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    start_snap_id => 20,
    end_snap_id   => 25,
    baseline_name => 'NORMAL_PERIOD'
);

-- 베이스라인 목록 확인
SELECT baseline_name, start_snap_id, end_snap_id
FROM   dba_hist_baseline;

/*
 [결과]
   BASELINE_NAME   START_SNAP_ID  END_SNAP_ID
   --------------  -------------  -----------
   NORMAL_PERIOD   20             25            ← 방금 생성한 베이스라인
   SYSTEM_MOVING…  …              …             ← 기본 이동 베이스라인
*/

AWR 스냅샷 목록 확인

3월 7일 구간을 베이스라인으로 생성 후 확인

AWR 리포트 생성

-- AWR 리포트 생성 스크립트 실행
-- @?/rdbms/admin/awrrpt.sql

/*
 [실행 순서]
   1) 리포트 유형 입력: html 또는 text
   2) 며칠치 스냅샷 보여줄지 입력: 숫자 (또는 Enter → 기본값)
   3) 시작 SNAP_ID 입력: 31
   4) 종료 SNAP_ID 입력: 32
   5) 파일명 입력: (Enter → 자동 생성 awrrpt_1_31_32.txt)

 [결과]
   awrrpt_1_31_32.txt 파일이 현재 경로에 생성됨
   → DB 전체 성능 요약, Top SQL, 주요 대기 이벤트 원인 포함
*/

AWR 리포트 실행 스크립트 실행

AWR 리포트 생성 확인

-- 특정 SQL의 AWR 히스토리 조회 (sql_id 필요)
-- 먼저 sql_text로 sql_id 확인
SELECT sql_id, sql_text
FROM   v$sql
WHERE  sql_text LIKE '%employees%'
AND    rownum <= 5;

-- sql_id로 AWR 히스토리 조회
SELECT snap_id, sql_id, executions_delta, elapsed_time_delta, cpu_time_delta
FROM   dba_hist_sqlstat
WHERE  sql_id = '&sql_id'
ORDER  BY snap_id;

테스트 위해 hr계정에서 같은 쿼리 20번 반복

해당하는 sql_id 확인

sql_id로 AWR 히스토리 조회 (20번 실행되었고 총 11.5ms 걸림)

---

5. ADDM (Automatic Database Diagnostic Monitor)

• AWR 스냅샷 구간을 자동 분석하여 전체 성능을 늦춘 가장 큰 지연 원인을 찾고, 해결책까지 제시해주는 도구
• AWR 스냅샷이 찍힐 때마다 자동으로 실행됨

ADDM 리포트 조회

-- ADDM 분석 결과 목록 확인
SELECT task_name, status, execution_start, execution_end
FROM   dba_advisor_log
WHERE  task_name LIKE 'ADDM%'
ORDER  BY task_id DESC;

ADDM 분석 결과 목록 확인

-- ADDM 분석 권고 내용 확인
SELECT finding_name, type, message
FROM   dba_advisor_findings
WHERE  task_name = 'ADDM:1752501865_1_2';

ADDM 분석 권고 내용 확인

---

6. Automated Tasks (자동 유지보수 작업)

• 오라클은 매일 정해진 시간에 아래 작업을 자동으로 실행함

작업	설명
옵티마이저 통계 수집	실행 계획 최적화를 위해 테이블/인덱스 통계 갱신
세그먼트 점검	공간 낭비(Segment Advisor)를 찾아 권고
문제 SQL 진단	SQL Tuning Advisor로 비효율 SQL 자동 분석

-- 자동 유지보수 작업 현황 확인
SELECT client_name, status
FROM   dba_autotask_client;

자동 유지보수 작업 현황 확인

통계 정보 수동 갱신 실습

-- STEP 1: 현재 통계 정보 확인 (갱신 전)
SELECT table_name, num_rows, blocks, last_analyzed
FROM   dba_tables
WHERE  owner = 'HR'
AND    table_name = 'EMPLOYEES';

hr의 employees 테이블 현재 통계 정보 확인

-- 통계 수동 갱신
CONN / AS SYSDBA
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- 통계 정보 확인 (갱신 후)
SELECT table_name, num_rows, blocks, last_analyzed
FROM   dba_tables
WHERE  owner = 'HR'
AND    table_name = 'EMPLOYEES';

/*
 [결과 — 갱신 후]
   TABLE_NAME   NUM_ROWS  BLOCKS  LAST_ANALYZED
   -----------  --------  ------  -------------------
   EMPLOYEES    107       5       09-MAR-26  ← 통계 반영 완료
*/

통계 수동 갱신 후 통계 정보 확인

---

7. ADR (Automatic Diagnostic Repository)

• 오라클이 에러·충돌 발생 시 로그와 트레이스 파일을 자동 보관하는 통합 저장소
• Alert Log, Trace File, Incident 정보가 모두 여기에 저장됨

-- ADR 기본 경로 확인
SHOW PARAMETER diagnostic_dest

/*
 [결과]
   NAME             TYPE    VALUE
   ---------------  ------  -------------------
   diagnostic_dest  string  /u01/app/oracle
*/

-- Alert Log 실시간 확인 (터미널에서 실행)
tail -f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

ADR 기본 경로 확인 및 Alert Log 실시간 확인

---

8. Resumable Space Allocation

• 공간 부족(테이블스페이스 / Quota 초과 / Undo 부족)으로 SQL이 실패하지 않고 일시 정지된 후, DBA가 공간을 늘리면 자동으로 재개되는 기능

Resumable 모드 실습

-- STEP 1: [SYS] 실습용 소용량 테이블스페이스 생성

CONN / AS SYSDBA
CREATE TABLESPACE small_tbs
    DATAFILE '/u01/app/oracle/oradata/ORCL/small_tbs01.dbf'
    SIZE 5M
    AUTOEXTEND OFF;    -- AUTOEXTEND OFF: 공간 부족 상황을 재현하기 위해 자동 확장 끔

-- hr에게 small_tbs 사용 권한 부여 (무제한 쿼터)
ALTER USER hr QUOTA UNLIMITED ON small_tbs;

-- STEP 2: [hr] 실습용 대용량 테이블 생성

CONN hr/hr
CREATE TABLE big_data (
    id   NUMBER,
    pad  VARCHAR2(2000)
) TABLESPACE small_tbs;

실습용 테이블스페이스 및 테이블 생성

-- STEP 3: [hr] Resumable 모드 OFF 상태에서 대용량 INSERT → 에러 확인
-- 반복 INSERT 프로시저로 공간 부족 유도
BEGIN
    FOR i IN 1..10000 LOOP
        INSERT INTO big_data VALUES (i, RPAD('X', 2000, 'X'));
    END LOOP;
    COMMIT;
END;
/

/*
 [결과 — Resumable OFF]
   ERROR at line 1:
   ORA-01653: unable to extend table HR.BIG_DATA by 128 in tablespace SMALL_TBS
   → 즉시 에러 발생 후 롤백됨 (데이터 없음)
*/

-- 테이블 초기화
TRUNCATE TABLE big_data;

반복 insert 프로시저 실행 후 공간 부족 에러 확인, big_data 테이블 truncate

-- STEP 4: [hr] Resumable 모드 ON 상태에서 동일 작업 → 일시 정지 확인

-- sys에서 hr에게 resumable 권한 부여
GRANT RESUMABLE TO hr;

-- Resumable 모드 활성화 (TIMEOUT: 공간 문제 해결을 기다리는 최대 시간(초))
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'my_insert_job';

-- 동일한 대용량 INSERT 재실행 (세션 1에서 실행 → 공간 부족 시 일시 정지됨)
BEGIN
    FOR i IN 1..10000 LOOP
        INSERT INTO big_data VALUES (i, RPAD('X', 2000, 'X'));
    END LOOP;
    COMMIT;
END;
/
-- 이 세션은 일시 정지 상태로 멈춰 있음

-- STEP 5: [SYS — 세션 2] 일시 정지된 작업 확인

CONN / AS SYSDBA

SELECT session_id, name, status, error_msg
FROM   dba_resumable
WHERE  status = 'SUSPENDED';

/*
 [결과]
   SESSION_ID  NAME            STATUS     ERROR_MSG
   ----------  --------------  ---------  ----------------------------------------
   128         my_insert_job   SUSPENDED  ORA-01653: unable to extend table HR.BIG_DATA...
   → Resumable 덕분에 에러 대신 일시 정지 상태로 대기 중
*/

Resumable 모드 활성화 후 동일 작업 실행, 세션 2에서 일시 정지된 작업 확인

-- STEP 6: [SYS] 데이터파일 추가로 공간 해결 → 세션 1 자동 재개
ALTER TABLESPACE small_tbs
    ADD DATAFILE '/u01/app/oracle/oradata/ORCL/small_tbs02.dbf'
    SIZE 50M;

/*
 [결과]
   Tablespace altered.
   → 세션 1의 일시 정지가 풀리며 INSERT 작업 자동 재개됨
*/

-- STEP 7: [hr — 세션 1] INSERT 완료 확인

CONN hr/hr
SELECT COUNT(*) FROM big_data;

/*
 [결과]
   COUNT(*)
   --------
   10000    ← 일시 정지 후 재개되어 모든 데이터 정상 입력됨
*/

sys에서 데이터파일 추가로 공간 늘리자 세션1 insert 작업 자동 재개되어 완료, 데이터 정상 입력 확인

---

실습 핵심 요약

주제	핵심 포인트
데이터베이스 관리	AWR / Advisors / Automated Tasks / ADR이 핵심 4요소
성능 모니터링	v$sess_time_model, v$memory_dynamic_components로 병목 세션·메모리 확인
AMM / ASMM / 수동	MEMORY_TARGET → AMM, SGA_TARGET → ASMM, 개별 파라미터 → 수동
Memory Advisor	v$memory_target_advice로 메모리 크기 변경 전 효과 예측
AWR 스냅샷	DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()으로 수동 생성
AWR 베이스라인	정상 구간을 이름 붙여 저장 → 이후 비교 분석의 기준으로 활용
AWR 보관 기간	MODIFY_SNAPSHOT_SETTINGS으로 주기·보관 기간 변경
AWR 리포트	@?/rdbms/admin/awrrpt.sql → 시작/종료 SNAP_ID 지정 후 파일로 생성
ADDM	AWR 스냅샷마다 자동 분석 → dba_advisor_findings에서 권고 내용 확인
Automated Tasks	통계 수집 / 세그먼트 점검 / SQL 진단 자동 수행 (dba_autotask_client)
Resumable	ALTER SESSION ENABLE RESUMABLE → 공간 부족 시 중단 후 자동 재개

---