AWR(Automatic Workload Repository) 개념과 활용 방법

 

1. 개요

데이터베이스 성능을 모니터링하고 최적화하는 것은 서버 운영의 핵심 과제 중 하나다. 특히, 성능 저하가 발생하는 원인을 정확히 파악하고 적절한 해결책을 마련하는 것은 IT 시스템의 안정성을 유지하는 데 필수적이다.

Oracle Database에서는 이러한 성능 문제를 분석하고 해결하는 데 유용한 도구로 AWR(Automatic Workload Repository) 을 제공한다. AWR은 데이터베이스의 성능 관련 정보를 일정한 주기로 자동으로 수집하고 저장하는 기능을 수행하며, 이를 통해 시스템의 부하 상태와 문제점을 분석할 수 있다.

본 글에서는 AWR의 개념, 주요 기능, 활용 방법, 그리고 SQL 최적화와의 연계 방안을 상세히 설명한다.

---

2. AWR(Automatic Workload Repository)란?

AWR(자동 워크로드 리포지토리)은 Oracle 10g부터 도입된 기능으로, 데이터베이스의 성능 관련 정보를 자동으로 수집하고 저장하는 역할을 한다. 이는 Oracle 데이터베이스의 MMON(Manageability Monitor) 프로세스에 의해 자동으로 실행되며, 기본적으로 1시간마다 수집된 스냅샷을 유지한다.

AWR의 주요 역할은 다음과 같다.

• 데이터베이스 성능 모니터링: CPU 사용량, 메모리 소비, I/O 부하 등 성능 지표를 분석할 수 있다.
• SQL 실행 분석: 실행 빈도 높은 SQL, 성능 저하를 유발하는 SQL 등을 식별할 수 있다.
• 백그라운드 프로세스 동작 감시: 오라클 내부 프로세스의 동작 상태를 모니터링할 수 있다.
• 오래된 데이터 자동 관리: 기본적으로 7일 동안 데이터를 저장하며, 오래된 정보는 자동으로 삭제된다.

AWR 데이터를 활용하면 데이터베이스 성능 저하의 원인을 분석하고, 적절한 해결 방안을 마련할 수 있다.

---

3. AWR 주요 기능 및 수집 항목

AWR은 데이터베이스의 다양한 성능 관련 정보를 저장하며, 대표적인 수집 항목은 다음과 같다.

1) 시스템 성능 데이터 수집

• CPU 사용률, 메모리 사용량, 디스크 I/O 상태 등 시스템 자원 활용 정보를 저장한다.
• CPU 사용량이 높은 SQL이나 대기 이벤트를 분석할 수 있다.

2) SQL 실행 계획 및 성능 분석

• 실행 빈도가 높은 SQL 목록을 저장하여 성능 저하 원인을 파악할 수 있다.
• SQL 실행 계획을 분석하여, 비효율적인 실행 방식(예: Full Table Scan, Hash Join 등)을 확인할 수 있다.

3) 데이터베이스 대기 이벤트 기록

• 데이터베이스 성능 저하의 주요 원인 중 하나는 대기 이벤트(wait event) 다.
• AWR을 통해 대기 시간이 긴 이벤트를 추적하고, 어떤 리소스에서 병목이 발생했는지 확인할 수 있다.

4) 상위 소비 SQL 및 세션 분석

• 가장 자원을 많이 소비하는 SQL 및 세션 정보를 저장하여, 부하를 유발하는 쿼리를 최적화할 수 있다.

---

4. AWR 리포트 생성 및 분석 방법

AWR 데이터는 사용자가 직접 보고서를 생성하여 분석할 수 있다. 이를 위해 다음과 같은 방법을 사용할 수 있다.

1) AWR 리포트 생성 방법

SQL*Plus 또는 SQL Developer에서 다음 명령을 실행하여 AWR 리포트를 생성할 수 있다.

SELECT dbms_workload_repository.create_snapshot FROM dual;

위 명령을 실행하면 즉시 AWR 스냅샷이 생성된다.

이후, 다음 SQL을 실행하여 AWR 리포트를 생성할 수 있다.

 

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

이 명령을 실행하면 리포트 유형(텍스트 또는 HTML)을 선택한 후 분석할 스냅샷 범위를 지정하게 된다.

 

2) AWR 리포트 주요 분석 항목

AWR 리포트는 방대한 정보를 포함하고 있지만, 주요 분석 포인트는 다음과 같다.

• Load Profile(로드 프로파일): 시스템의 부하 상태를 보여주는 주요 지표.
• Top SQL by CPU Usage: 가장 많은 CPU를 소비한 SQL 목록.
• Top SQL by Logical Reads: 가장 많은 논리적 읽기를 수행한 SQL 목록.
• Wait Events(대기 이벤트 분석): 실행 속도를 저하시킨 주요 대기 이벤트.
• I/O Statistics(입출력 통계): 디스크 I/O가 집중된 테이블 또는 인덱스 정보.

이러한 항목을 분석하여 성능 저하의 원인을 식별하고 최적화할 수 있다.

---

5. SQL 힌트(Hint) 적용 후 AWR을 활용한 성능 비교

SQL 힌트를 적용한 후 AWR을 활용하면, 힌트 적용 전후의 실행 성능 변화를 확인할 수 있다. 예를 들어, 옵티마이저가 적절하지 않은 실행 계획을 선택하는 경우, 힌트를 적용하여 이를 개선할 수 있다.

힌트 적용 전 SQL (Full Table Scan 발생)

SELECT emp_id, emp_name FROM employees WHERE salary > 5000;

• 실행 계획: Full Table Scan 수행
• CPU 사용률: 80%
• 실행 시간: 1200ms

힌트 적용 후 SQL (Index Scan 적용)

SELECT /*+ INDEX(emp emp_salary_idx) */ emp_id, emp_name FROM employees WHERE salary > 5000;

• 실행 계획: Index Range Scan 수행
• CPU 사용률: 30%
• 실행 시간: 250ms

이후 AWR 리포트를 통해 실행 시간 및 CPU 사용률이 감소했는지 확인하고, 최적화 효과를 정량적으로 측정할 수 있다.

---

6. AWR을 활용한 지속적인 성능 모니터링

SQL 최적화 외에도 AWR을 활용하면 지속적으로 데이터베이스의 성능을 모니터링하고 문제 발생 시 신속하게 대처할 수 있다.

1. 정기적인 AWR 리포트 생성
   •   일정 주기로 AWR 리포트를 생성하여 데이터베이스 성능 변화를 점검한다.
2. CPU 및 메모리 사용량 변화 감지
   •   CPU 사용량이 급격히 증가하는 패턴이 감지되면, 문제 SQL을 추적하여 성능을 최적화한다.
3. I/O 부하 분석 및 최적화
   •   디스크 읽기/쓰기 부하가 증가하는 경우, 인덱스를 추가하거나 쿼리를 최적화하여 성능을 개선한다.

---

7. 결론

AWR(Automatic Workload Repository)은 데이터베이스의 성능을 실시간으로 모니터링하고, 성능 저하 문제를 해결하는 데 필수적인 도구다. SQL 힌트 적용 후 성능 비교, 대기 이벤트 분석, CPU 및 메모리 사용량 모니터링 등 다양한 기능을 통해 데이터베이스 운영을 최적화할 수 있다.

특히, AWR을 주기적으로 분석하면 서버 이관 후 발생할 수 있는 성능 문제를 사전에 방지하고, 안정적인 운영 환경을 유지하는 데 큰 도움이 된다. 본 프로젝트에서는 AWR을 적극 활용하여 데이터베이스 성능을 지속적으로 점검하고, 최적의 실행 계획을 유지할 예정이다.