CH 10: DB 모니터링 & 튜닝

Ch 10. Monitoring and Tuning the Database (DB 모니터링 & 튜닝)

실습 환경

OS	Oracle Linux 6.10 (VMware Virtual Machine, 호스트명: oel6v10)
DB	Oracle Database 11g
접속 툴	SQL*Plus, MobaXterm(SSH)

---

0. 준비 단계

 

EM 접속 (기본주소: https://<호스트명 또는 IP 주소>:1158/em)

파이어폭스에서 sys계정으로 EM 접속

 

orcl 인스턴스로 접속 완료

 

부하 발생시키기

-- CPU 및 I/O 부하를 일으키는 쿼리를 위한 테이블 생성 (오래 걸림)
CREATE TABLE test_load 
AS SELECT * FROM all_objects;

-- 아래 쿼리를 실행해두고 EM 화면으로 넘어감 (오래 걸림)
SELECT count(*) FROM test_load a, test_load b;

CTAS로 all_objects 복사 테이블 생성

카티산 곱을 일으키는 쿼리 실행 후 EM 화면으로 넘어감

---

1. 사전 예방적 데이터베이스 모니터링

Home 화면에서 Host CPU의 Load 수치가 실시간으로 올라가는 것을 확인

Performace -> Top Activity 탭에서 과부하 일으키는 쿼리 확인

실행 결과는 무려 51억 개

SQL ID를 클릭하면 sql 텍스트 원문과 성능 그래프 확인 가능

Details -> Plan에서 SQL 실행계획도 확인 가능

---

2. ADDM 활용한 성능 문제 진단

 

AWR 스냅샷 생성

Server -> Automatic Workload Repository 클릭

Snapshots 클릭

Create 클릭

정말 생성할 건지 확인, Yes 클릭

스냅샷 생성 중

화면이 하얗게 변하면 새로고침 누른 뒤, 다시 snapshots로 들어가 생성된 스냅샷 확인

 

ADDM 실행 및 결과 확인

ADDM 리포트 생성 스크립트 실행

스냅샷 8, 9 선택 후 리포트 파일 이름은 Enter 키로 기본값 설정

생성완료

ls 명령어로도 확인 가능

파일 내용 확인하면 findings는 없음 (쿼리가 생각보다 약했음)

 

강한 부하 발생시키기 

-- ADDM 확인 위해 강한 부하를 주는 쿼리 실행
-- 쿼리 실행 전 스냅샷 찍기 
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

-- 무거운 쿼리 실행 (쿼리가 끝날 때까지 대기, 오래 걸림)
SELECT count(*) FROM dba_source a, dba_source b;

-- 쿼리 종료 후 스냅샷 찍기 
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

 

스냅샷 찍은 후 강한 부하를 주는 쿼리 실행

Top Activity에서도 확인 가능

3. 어드바이저를 사용한 DB 성능 최적화

 

쿼리 실행이 오래 걸리니 그 사이에 Tuning Advisor 실습

Top Activity에서 부하 주는 SQL ID 선택 후 'Schedul SQL Tuning Advisor' 클릭

시간은 1분씩, Scope는 Comprehensive로 주고 Submit

실행 후 권고사항이 다양하게 뜨는 것을 확인

권고사항의 의미

1. Statistics (통계 정보 갱신)
Findings: SYS.SOURCE$ 테이블의 통계 정보가 너무 오래됨(Stale)

2. SQL Profile (SQL 프로파일 적용) 
Findings: 분석한 결과, 쿼리를 더 빠르게 실행할 수 있는 새로운 실행 계획 발견

3. Restructure SQL (SQL 문장 구조 변경)
Findings: 쿼리 2번째 줄에서 '카티션 곱(Cartesian product)' 작업 발견

---

4. 메모리 사용량 최적화

Server 탭 Database Configuration -> Memory Advisors 클릭

AMM 활성화된 것 확인, Advice 클릭

메모리 사이즈를 늘려도 DB Time 변화 없는 것 확인 (현상 유지가 좋음)

---

5. 부하 실습 마무리

부하 발생시키던 SQL 쿼리 수동 중지 후 스냅샷 찍기

가장 최근에 찍은 두 개의 스냅샷을 기준으로 ADDM 리포트 생성

리포트 파일 생성 확인 후 vi 편집기로 내용 확인

이전과 달리 Finding 생긴 것 확인

---