[ETC] AWR Report 보는 방법

AWR 이란?

DBA에게 데이터 베이스 실행 시간과 관련된 스냅샷, 상세한 정보를 제공한다.

이러한 스냅샷 정보는 대기 이벤트, 래치, I/O Volumn Time뿐 아니라 다양한 Memory와 SQL활동 뷰를 제공한다.

 

AWR은 시스템 통계정보의 스냅샷을 저장하는 테이블 그룹을 제공한다. 일반적으로 이러한 스냅샷은 시간 단위로 측정된다. 그리고, 측정 때까지 누적된 대기 인터페이스 통계정보, 탑 SQL, 메모리, 입/출력 정보 등을 포함한다. AWR 레포트를 생성할 때 지정된 구간의 통계정보를 얻기 위해서, 두 개의 스냅샷 데이터를 가져와서 나중 스냅샷 정보에서 먼저 스냅샷 정보를 빼서 요청된 시간 구간과 간련된 통계 값과 정보를 보여주는 델타 레포트를 생성한다.

AWR은 자동으로 생성되는 기존 Statspack 레포트의 발전된 버전이다. 그리고, 오라클 데이터베이스 자동화 튜닝 절차의 핵심이다.

AWR레포트는 내부적으로 매 시간 수행된다. 그리고, OEM 인터페이스로 분석값이 전송된다. 사용자는 OEM 또는 수작업을 이용해서 전체 AWR레포트를 텍스트 또는 HTML 양식으로 생성할 수 있다. 

 

베이스라인 레포트에서 주시할 것은 보통 수준의 특정 대기 이벤트와 랫치 그리고 보통 I/O 프로파일(데이터 파일에 대한 보통 I/O 비율과 타이밍)이다.

 

들여다 봐야 할 또 다른 항목은 정렬(sorts) 타입/갯수, 메모리 레이아웃, PGA 활동 수준이다.

 

AWR 특징

• 데이터베이스 속도를 비교할 수 없을 정도로 향상
• AWR 결과를 해석하여 가치 있고 실행 가능한 통찰 획득
• 플래시 저장장치를 활용해서 데이터 베이스 성능 향상
• 데이터베이스 환경(변수) 최적화

Top-Down Approach

---

Relation Data of  Platform, CPU, Core, Socket, Usable Memory

헤더 내 작업량 관련 통계정보

 

---

Top 5 Wait 통계 정보

※ Wait Event 관련 참고

http://haisins.epac.to/wordpress/?p=4013 

DBA가 해야 할 일 Oracle Wait Event 모니터링 – DBA의 정석

---

 

CPU 시간의 상세 내용 및 측정 구간에 대해 Run Queue 상태(Load Average)

: Run Queue란? 얼마나 많은 프로세스가 실행 대기 상태인지 보여준다. 일반적으로 Run Queue(Load Average의 End 값)가 사용 가능한 CPU의 수(CPU * Cores = 4*4)를 초과하고 CPU가 idle 상태(99.8%, idle 상태)가 아니면 CPU 수를 늘리거나 속도를 업그레이드 해야한다. 위 AWR 레포트에서 보는 거와 같이 I/O 대기(%WIO) 0.0%인 동안 CPU는 99.8% idle(%idle)이다. 현재 I/O대기하는 상태이다. 만약 I/O 대기 %(%WIO)가 %Idle 보다 높으면 디스크 개수를 늘리면 도움이될것이다.

---

DB Instance 별 CPU 자원 효율성

---

상세 시간 통계 정보

CPU 상세 통계 정보

현재 대부분 Using CPU로 사용됐지만, 두번째로 할당된 약 40% 가량 CPU 시간디 SQL Execute Elapsed Time에 사용된 것을 알 수 있다. 만약 Parse ElapsedTime 나 Hard parse elapsed time이 많이 소비된다면, Unique한 SQL문이 다수 차지하는 상황으로 ad-hoc(단말기간 직접 무선 통신) 환경이거나 바인드 변수를 적절하게 사용하지 못하는 프로그램을 의미한다.

 

운영 시스템(OS) 통계 정보

CPU시간이 어떻게 결정 되는지는 알 수 없으나 OS 섹션은 CPU 시간의 상세 정보를 제공해서 이 레포트의 다른 섹션에서 주장하는 사용률(percentage)을 나타낸다. 하지만, 한가지 단점으로는 I/O 대기 시간이 명확하지 않아서 분석을 위해 사용하기 어렵다.

---

Foreground 대기 이벤트

Foreground 대기이벤트는 Foreground 프로세스에서 발생하는 대기 이벤트이며, 사용자 또는 응용 수준의 프로세스다.

BackGround 대기 이벤트

백그라운드 대기 이벤트는 오라클 데이터베이스 프로세스 스택의 수많은 백그라운드 프로세스가 생성하는 대기이벤트이며. DBWR, LGWR, SMON, PMON 등이 모두 백그라운드 대기 이벤트에 영향을 준다.

 

---

대기 이벤트 히토그램

오라클은 대기 이벤트에 대해 시간 기반의 히토그램 레포트를 제공한다. 시간에 따른 압도적인 대기이벤트 정렬이 아닌 이벤트 이름 순으로 정렬되어 있어, 중요한 이벤트를 직접 찾아야한다는 단점이 있다.

 

SQL Section

• Total elapsed time
• Total CPU time
•  Total buffer gets
• Total disk reads
• Total executions
• Total parse calls
• Total sharable memory
• Total version count
• Total cluster wait time

Total elapsed time

SQL문이 total elapsed time 영역에 나타나면 CPU 시간과 다른 대기 시간을 더한 시간이 상위에 나타나는 것을 의미한다. 몇 가지 이유로  total elapsed time에서는 상위권에 있지만 total CPU time에는 상위권에 없을 수 있다. 이런 경우에는 해당 SQL문과 관련된 recursion 이슈가 있다는 것을 의미한다. 일반적으로 total elapsed time과 total CPU time 영역에서 동일한 SQL문장을 보게 된다. 최적화되지 않은 parse ratios와 같은 높은 recursion 지표를 보거나 Instance Activity Statistics(SQL 영역 다음에 나오는 섹션)에서 높은 recursion지표를 보면, recursive calls 또는 recursive CPU 사용 통계정보가 높다.

Total CPU time

SQL문장이 Total CPU time 영역에 나타나면 해당 SQL문을 처리하는 중 과도한 CPU cycles를 사용했다는 것을 나타낸다. 과도한 CPU 처리 시간은 정렬, 과도한 함수, 긴 파싱 시간 등에 기인한다. SQL 튜닝 후보를 찾기 위해서 이 섹션에서 들여다 봐야할 지표는 해당 SQL문과 연관된 서비스의 서비스 섹션에 포함된 높은 CPU %이다. SQL문이 대문자인 경우 대부분 사용자 또는 응용에서 나온것이고 소문자인 경우 대부분 내부 또는 백그라운드 프로세스에서 나온 것이다. Total CPU time을 줄이기 위해서, 정렬 제거자(eliminators)로 작용하는 다중 컬럼 인덱스를 사용해서 정렬 작업을 줄이자. 그리고, 파싱 시간을 줄이기 위해서 바인드 변수를 사용하자.

Total buffer gets

Total buffer gets 은 SQL문장이 데이터베이스 블럭 버퍼에서 많은 정보를 읽고 있는 것을 나타낸다. 일반적으로, buffer gets(Statspack에서는 logical reads)은 과도하지만 않다면 바람직하다. 과도한 디스크 읽기와 같이 과도한 beffer gets는 성능 이슈를 야기할 수 있다. 과도한 Total buffer gets을 줄이기 위해서 파티션닝, 인덱스 등을 사용하고 과도한 전체 테이블 스캔을 피하기 위해서 SQL문을 최적화 하자. Total buffer gets은 높은 logical reads, 높은 buffer cache hit ratio (낮은 선택도-selectivity를 가진 인덱스로 유발된다.), 높은 CPU 사용으로 대표된다.

Total disk reads

높은 total disk reads는 SQL문장이 데이터베이스 블럭 버퍼가 아니고 디스크에서 많은 정보를 읽고 있다는 것을 보여준다. 일반적으로, 디스크 읽기(Statspack에서 physical reads)는 바람직하지 않다. 특히 과도할 경우 더 그렇다. 과도한 디스크 읽기는 성능 이슈를 야기한다. 과도한 디스크 읽기를 줄이기 위해서, 파티션닝, 인덱스를 사용하고 과도한 전체 테이블 스캔을 피하기 위해서 SQL문을 최적화 하자. 서버 메모리가 허용한다면, 데이터베이스 버퍼 캐쉬를 크게 할 수도 있다. Total disk reads는 높은 물리 읽기, 낮은 버퍼 캐쉬 히트 율, 높은 I/O 대기 시간을 갖는 낮은 CPU사용 등으로 대표된다. 높은 디스크 읽기를 피할 수 없다면 더 빠른 I/O서브 시스템을 고려해 보자.

Total executions

높은 Total executions은 데이터베이스에서 SQL문을 바르게 처리하고 있다는 것을 보여준다. 실행(executions) 횟수가 많은 SQL문은 보통 적절하게 재사용된다. 하지만, 실행 횟수가 많은 SQL문은 여러 번 수행되어야 하는 것을 확인하자. 단 한 번 수행되어야 하는 SQL문이 PL/SQL, Java 또는 C 루틴의 루프에서 반복적으로 실행될 수도 있다. 많이 실행되고 논리/물리 읽기를 많이 수행하는 SQL문은 단 한 번 실행되어야 할 때 여러 번 실행되지 않도록 확인하자. 만약 데이터베이스가 과도한 물리/논리 읽기 또는 과도한 I/O 대기 시간을 보인다면, 물리/논리 읽기가 많고 과도한 실행 횟수를 보이는 SQL문장을 들여다 보자.

Parse calls

사용자 또는 프로세스가 SQL문을 발행할 때 SQL문이 SQL풀에 있는 지와 관계없이 파싱 과정을 거친다. 파싱은 hard 파싱 또는 soft 파싱 중 하나다. SQL풀에 동일한 해쉬 시그니처가 없으면 recursive SQL 작업과 다른 모든 파싱작업을 포함하는 hard 파싱을 수행한다. SQL풀에서 동일한 SQL 해쉬 시그니처를 발견하면 관련 오브젝트의 사용자 권한을 확인하는 최소한의 recursion을 포함하는 soft 파싱만을 수행한다. 과도한 파싱 콜은 과도한 실행과 병행한다. SQL문이 안전하지 않은 바인드 변수로 알려진 것을 사용하면 SQL문은 매번 재파싱된다. header parse ratios가 낮으면 여기하고 version count areas를 보자.

Shareable memory

Shareable memory 영역은 SQL문장이 소비하는 공유 풀의 메모리 양과 재사용되는 SQL문장에 대한 정보를 제공한다. 레포트에는 shared memory의 1 MB이상을 사용하는 SQL문장만 보인다. 일반적으로 높은 메모리 소비는 떨어지는 코딩 또는 많은 테이블을 조인하는 상당히 긴 SQL문장에서 기인한다. DSS또는 DWH 환경에서 대규모의 복잡한 SQL문장은 일반적이다. OLTP 데이터베이스에서 대규모 또는 복잡한 SQL문장은 대개 데이터베이스 설계가 너무 정규화됐거나 OLTP시스템을 DWH(DataWareHouse) 또는 DSS(Decision Support Sytem)시스템으로 사용하거나, 떨어지는 코딩 기술에 기인한다. 일반적으로 긴 SQL문장은 과도한 파싱, recursion, 대규모 CPU 사용을 유발한다.

Version count

많은 Version count는 대게 다중 동일-스키마 데이터베이스, 안전하지 않은 바인드 변수, 소프트웨어 버그  때문이다. 오라클 데이터베이스 9i의 버그 때문에 다중 버전을 유발하는 안전하지 않은 바인드 변수가 발생한다. 다중 버전은 shared pool의 SQL메모리 공간을 잡아 먹는다. 많은 Version count 또한 과도한 파싱을 발생시킨다. 문서화 되지 않은 파라메터인 "“_sqlexec_progression_cost" 디폴트 값인 1,000 보다 높게 설정해서 취약한 버전에서 버전닝을 줄일 수 있다. SQL 풀에서 sharable memory의 높은 값은, 한 번이상 수행된 SQL문이 높은 sharable memory를 갖고도 성능이 좋지 않다면, 이슈가 있음을 나타낸다.

Cluster wait time

이름이 의미하는 것과 같이 클러스터 대기 시간은 오라클 RAC 시스템을 사용하는 경우에만 나타난다. 인터컨넥트 상에서 많은 SQL문장을 전송하는 SQL문이 이 섹션에 나타난다. 만약, 블럭 크기가 너무 크거나, 데이터베이스 캐쉬가 각 서버에서 너무 작거나, SQL이 너무 많은 테이블 데이터를 사용한다면  높은 수준의 블럭 전송이 발생한다. 대규모 갱신 SQL문장이 여기 나타날 수 있다. 왜냐하면 갱신 작업은 많은 경우에 현재 블럭에 대해서 블럭 전송을 요구하기 때문이다. 높은 수준의 GC-타입 대기 이벤트는 원인을 제공하는 SQL문장을 찾기 위해서 이 섹션을 체크해야 하는 것 나타낸다.

참고 : https://post.naver.com/viewer/postView.nhn?volumeNo=31276942&memberNo=18071586 

오라클 AWR 레포트 해설