AWR (Automatic Workload Repository), 오라클 DB의 진술문

1. AWR 개요

1.1 AWR이란?

Oracle AWR(Automatic Workload Repository)은 오라클 데이터베이스의 성능 데이터를 자동으로 수집, 저장, 분석하는 시스템이다. 오라클 DB 성능 문제를 해결하고 최적화할 때 사용하게 되는 도구입니다.

1.2 AWR이 필요한 이유

운영 중인 데이터베이스에서 성능이 느려지는 순간이 있다면, 그 원인을 어떻게 찾을까? CPU 문제일까? 메모리 부족일까? 특정 SQL이 너무 많은 리소스를 사용하고 있는 것일까? 이러한 모든 문제를 한눈에 파악하고, 데이터를 기반으로 정확한 분석을 수행하려면 AWR이 많은 도움이 될 것 입니다.

 

1.3 AWR의 주요 기능

• DB 성능 데이터 수집 및 분석: CPU, 메모리, I/O 사용량, 세션 활동 추적

• SQL 성능 분석 및 튜닝 정보 제공: TOP SQL 확인, 실행 계획 정보

• 시스템 리소스 사용량 추적: CPU 대기 시간, I/O 지연, 메모리 사용 패턴 분석

• 데드락 및 록 대기 시간 분석: 트랜잭션 병목 현상 해결

• SQL 실행 계획 변화 감지: 비효율적인 실행 계획 자동 탐지

---

2. AWR 활성화 여부 확인

AWR이 정상적으로 동작하는지 확인하려면 아래 SQL을 실행합니다.

SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;

• SNAP_INTERVAL: AWR 스냅샷이 생성되는 주기 (기본값: 1시간)

• RETENTION: AWR 데이터가 보관되는 기간 (기본값: 8일)

---

2. AWR 리포트 생성 및 조회

2.1 AWR 리포트 생성 (HTML, TEXT)

AWR 리포트는 DB 성능 문제를 분석하는 핵심 문서이다. SQL을 실행하여 생성할 수 있다.

@?/rdbms/admin/awrrpt.sql

 

AWR 리포트 생성 & 조회

 

SQL*Plus 또는 SQLcl 접속

sqlplus / as sysdba

 

AWR 리포트 실행

@?/rdbms/admin/awrrpt.sql

 

출력값 입력

 

• HTML 또는 TEXT 형식 선택

• 최근 몇 일간의 스냅샷을 조회할 것인지 입력 (예: num_days = 10 입력 시 최근 10일 데이터)

• 시작 스냅샷 ID, 종료 스냅샷 ID 입력

만약 이 스크립트 파일이 어디에 있는지 직접 찾고 싶다면, 오라클 홈 디렉터리에서 찾아볼 수 있습니다.

find $ORACLE_HOME -name "awrrpt.sql"

보통 $ORACLE_HOME/rdbms/admin/ 경로에 위치해 있습니다.

 

* 리포트 생성 오류 해결 방법

 

• ORA-20200: The instance was shutdown between snapshots 오류 발생 시

  • 특정 스냅샷 ID를 선택할 때 DB가 재시작된 구간을 피해야 함

  • 예를 들어, begin_snap=934, end_snap=1137에서 DB 재시작이 있었다면 934~1137을 피하고 다른 연속된 스냅샷을 선택

• SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC;

---

3. AWR을 활용한 성능 분석

3.1 CPU 사용량 분석

SELECT * FROM DBA_HIST_SYSMETRIC_SUMMARY WHERE METRIC_NAME = 'Database CPU Time Ratio';

내 DB의 CPU 사용량 분석하기

1. 위 SQL을 실행하여 CPU 사용 비율을 조회한다.

2. CPU Time Ratio 값이 70% 이상이면 CPU 부하가 높은 상태로 판단할 수 있다.

3. 추가적으로, 프로세스별 CPU 사용량을 확인해보자:

4. SELECT SQL_ID, EXECUTIONS_TOTAL, CPU_TIME_TOTAL FROM DBA_HIST_SQLSTAT ORDER BY CPU_TIME_TOTAL DESC FETCH FIRST 5 ROWS ONLY;

5. 결과 분석:

   • CPU_TIME_TOTAL이 가장 높은 SQL을 찾아 튜닝 대상인지 확인한다.

   • 실행 횟수(EXECUTIONS_TOTAL)가 높은데 CPU 사용량도 높다면 인덱스 추가 또는 실행 계획 튜닝이 필요할 수도 있음

---

3.2 가장 부하가 큰 SQL 조회

SELECT SQL_ID, ELAPSED_TIME_TOTAL, EXECUTIONS_TOTAL FROM DBA_HIST_SQLSTAT ORDER BY ELAPSED_TIME_TOTAL DESC FETCH FIRST 10 ROWS ONLY;

성능을 저하시키는 SQL 찾기

1. 위 SQL을 실행하여 전체 실행 시간(ELAPSED_TIME_TOTAL)이 가장 높은 SQL을 찾는다.

2. SQL_ID를 복사하여 해당 SQL의 원본을 조회한다.

3. SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID = '복사한 SQL_ID';

4. 결과 분석:

   • 실행 시간이 비정상적으로 긴 SQL이라면 실행 계획을 확인한다.
   • 실행 횟수가 많은데 실행 시간이 길다면, 쿼리 최적화 또는 인덱스 튜닝이 필요할 가능성이 높다

---

4. AWR 리텐션 기간 변경

AWR 데이터가 보관되는 기간을 조정할 수 있습니다.

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (retention => 2592000);

---

5. 끝으로

AWR은 데이터베이스의 진술문이며, 모든 성능 문제의 단서입니다. 데이터베이스의 성능 저하 원인, 병목 현상, 과도한 리소스 소비는 AWR 리포트에 기록됩니다. 이를 정확히 해석할 수 있다면, 감이나 추측이 아닌 데이터 기반의 확실한 분석과 튜닝이 가능해집니다.

여기까지 AWR에 관해 알아보았습니다. 이를 잘 활용한다면, 여러분들에게 단순한 문제 해결을 넘어서 데이터베이스의 성능을 최적의 상태로 유지하고 지속적으로 개선하는 도구가 되줄 것입니다. 

---