08 Statspack / AWR

Statspack

ORACLE 8.1.6 이상 사용 가능.
과거 utlbstat/utlestat이 제공하던 기능을 수정 보완한 성능분석 도구임.
성능관련 통계정도들이 perfstat 유저에 누적 저장되어 원하는 기간별로 비교 분석이 가능.
dbms_job(DB레벨) 이나 cron(OS레벨)등을 사용하여 주기적으로 데이타를 수집할 수 있음.
한 시점의 성능 데이타들을 snapshot 이라고 하며, Statspack Report는 두 시점의 snapshot 들로부터 얻어짐.
성능 수집 데이타의 별도 관리가 필요함. 기간 유지 기능이 없음.
<ORACLE 10G 이전에서 주로 사용>

DB 대기 이벤트 및 통계 정보
시스템 통계 정보
데이터베이스 부하 정보
SQL 수행 정보
활동 세션 정보

1.설치
SYS> @?/rdbms/admin/spcreate.sql

PERFSTAT_PASSWORD의 값을 입력하십시오: perfstat
DEFAULT_TABLESPACE의 값을 입력하십시오: [enter]
TEMPORARY_TABLESPACE의 값을 입력하십시오: [enter]

2.성능정보 수집
SYS> conn perfstat/perfstat
perfstat> exec statspack.snap
perfstat> exec statspack.snap

3.SNAPSHOT 확인
SELECT snap_id, 
       To_char(snap_time, 'YYYY-MM-DD HH24:MI:SS') snap_time 
FROM   stats$snapshot;

   SNAP_ID SNAP_TIME
---------- -------------------
         1 2016-12-16 22:34:44
         2 2016-12-16 22:34:53


4.리포트 추출
SQL> @?/rdbms/admin/spreport.sql

Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
orcl         ORCL                 1 16 Dec 2016 22:34     5
                                  2 16 Dec 2016 22:34     5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Enter value for end_snap: 2
Enter value for report_name:

AWR

AWR = Automatic Workload Repository
ORACLE 10g 이상 사용 가능.
성능 정보 수집 및 리포트 제공.
Statspack의 기능을 대폭 업그레이드한 툴임. (기존 Statspack은 별도 사용가능함)
자동으로 메모리 모니터링(MMON, MMNL) 백그라운드 프로세스에 의해 데이터가 수집되어 SYSAUX 테이블스페이스에 저장됨. Statspack은 수동임.
(default: 10g->7일간, 11g->8일간 유지)

SYS> @?/rdbms/admin/awrrpt.sql

Enter value for report_type: [html(default) / text]
Enter value for num_days: [SNAPHOST 구간 일자 확인]
Enter value for begin_snap: [SNAPHOST 시작점]
Enter value for end_snap: [SNAPHOST 종료점]
Enter value for report_name:

• 문제점을 찾아 성능 이슈를 해결할 목적이라면 peak 시간대 또는 장애가 발생한 시점을 전후의 가능한 한 짧은 구간을 선택해야 함.

• 사용자 인터뷰를 통해 성능 저하 현상을 경험했던 시간대를 파악.

• OS 모니터링 도구(sar, top, topas, vmstat, mpstat, iostat, osstat 등)를 이용해 CPU, MEMORY, I/O 사용량 정보를 수집하고 peak 시간대를 파약.

• 트랜잭션 개수가 commit 또는 rollback 수행 횟수를 단순히 더한 값이어서 의미 없는 수치로 받아들여질 대가 종종 있음.
  조회 위주의 시스템이라면 I/O 수치는 계속 누적되는 반면 commit 발생 횟수는 적기 때문에 트랜잭션당 Logical reads와 Physical reads 항목이 매우 높게 나타남.
  실제 업무적인 의미에서의 트랜잭션과 괴리가 있다는 사실과, 본인이 관리하는 시스템의 특성을 이해한 상태에서 수치를 해설할 필요가 있음.

• CPU Time이 Total Call Time에서 차지하는 비중이 가장 높아 Top 1에 위치한다면 일단 DB의 건강상태가 양호하다는 청신호.
  반대로 CPU Time 비중이 아래쪽으로 밀려날수록 어딘가 이상이 발생했다는 적신호임.

• 실제 시스템에 약영향을 주었는지에 대한 세부적인 분석 없이 대기 이벤트 순위가 상위로 매겨졌다는 이유만으로 이상 징후로 해석하는 우를 범해서는 안됨.

• 래치나 Lock 관련 대기 이벤트 순위가 상위로 매겨졌다면, 문제가 발생했음을 나타내는 위험 신호일 가능성이 높지만 래치의 경우는, CPU 사용률까지 같이 분석해 봐야 함.

• 래치 경험은 CPU 사용률을 높이는 주원인임. 그 당시 CPU 사용률이 높지 않았다면 다른 이벤트보다 상대적으로 많이 발생한 것에 불과할 수 있음.

• 트랜잭션 처리 위주의 시스템이라면 log file sync 대기 이벤트가 Top 5 내에 포함되었다고 무조건 이상 징후로 보기 어려움.

• 이벤트가 많이 발생한 것만으로 불필요한 커서를 자주 날렸다고 판단해서는 안됨.

• I/O 관련 대기 이벤트가 상위로 올라오는 것은, 상황에 따라 다르게 해석해야 함.

• 데이터베이스느 I/O 집약적인 시스템이므로 db file sequential read, db file scattered read 대기 이벤트가 상위에 매겨지는 게 정상.

• OLTP 시스템이냐 DW, OLAP 시스템이냐에 따라 둘 간의 순서가 바뀔 수는 있지만, I/O 대기 이벤트가 높게 나타나는 것은 대개 정상.
  다만, 이 두 대기 이벤트가 CPU time 보다 높은 점유율을 차지하고, OS 모니터링 결과 CPU 사용률도 매우 높은 상황이 지속된다면
  I/O 튜닝이 필요한 시스템일 가능성이 높음. 결론적으로, 이 두 개기 이벤트는 I/O 효율화 튜닝이 필요한 시스템에도 순위가 높게 매겨지지만
  튜닝이 잘된 시스템에도 마찬가지 결과가 나오므로 상세한 분석을 통한 결론을 도출해야 함.
  반대로, 대기 이벤트 발생 현황만을 놓고 보면 별 문제가 없어 보이지만 실제 사용자가 느끼는 시스템 성능은 매우 느리 경우가 많음.
  아무리 peak time 전후로 리포트 구간을 짧게 가져가더라도 시스템 레벨로 측정한 값이기 때문.
  Top-N 대기 이벤트 분석에 의한 성능 진단이 갖는 한계가 바로 여기에 있음.
  Ratio에 기반한 인스턴스 효율성 분석이 갖는 한계점과 같다고 할 수 있음. 세션 레벨의 상세한 분석이 추가로 이루어져야 함.

• % Block changed per Read: 읽은 블록 중 갱신이 발생하는 비중.

• Rollback per transantion %: 최종적으로 커밋되지 못하고 롤백된 트랜잭션 비중.

• Recursive Call %: 전체 Call 발생 횟수에서 Recursive Call이 차지하는 비중.
  사용자 정의 함수/프로시저를 많이 사용하면 이 수치가 높아지며, 하드파싱에 의해서도 영향을 받음.

• Rows per Sort: 소트 수행 시 평균 몇 건씩 처리했는지를 나타냄.