1. Statspack/AWR
2. AWR Report 분석
   1. AWR Parameters
   2. AWR Report
   3. 이문서의 내용은 아래 책,문서를 참고했습니다.

Statspack/AWR

• Statspack과 AWR은 거의 같은 내용을 담고 잇으며, 다른 점이 있다면 정보를 수집하는 방식에 있다

AWR Report 분석

• AWR뷰를 잘 활용하면 상용 모니터링 툴 도움 없이도 DBA, 개발자 누구나 다양한 성능 진단 보고서를 만들어 낼 수 있다. 그렇게 만들어진 보고서를 가지고 병목 원인을 파악하고 해결방안을 마련할 수 있어야 하는데, 보고서 내용을 해석하는 것부터 쉽지않다.
• AWR 리포트는 10g EM에서 스냅샷 생성 및 리포트 생성등등을 다 할 수 있지만, 여기서는 EM말고 쿼리를 이용하여 생성하고 리포트를 보는 법에 대해서만 기술할 것이다.
• 위에서 말했듯이 AWR 리포트를 분석하는 것은 처음 접하는 사람은 정말 쉽지 않다. 그리고 내용도 상당히 많기 때문에 정말 정신이 없다. 하지만 AWR 리포트 첫장에 나오는 DB 상태를 해석하는 방법만 잘 알아두면 될 것 같다.

• 그럼 이제부터 AWR리포트 생성을 위한 스냅샷 조정과 리포트 출력 및 분석법에 대해 알아보자~~~
• AWR 리포트를 분석하려는 이슈가 성능 이슈를 해결할 목적이라면 peak 시간대 또는 장애가 발생한 시점을 전후해 가능한 한 짧은 구간을 선택해야 한다.

• 아래 쿼리는 정해진 기간 동안 각 구간별로 SQL 수행횟수(execute count)를 뽑아보는 쿼리의 예시이다.

-- 정해진 기간 동안 각 구간별로 SQL 수행횟수를 뽑아보는 쿼리
select to_char(min(s.begin_interval_time), 'hh24:mi') begin
     , to_char(min(s.end_interval_time), 'hh24:mi') end
     , sum(b.value-a.value) "execute count"
from   dba_hist_sysstat a, dba_hist_sysstat b, dba_hist_snapshot s
where  s.instance_number = &instance_number
and    s.snap_id between &begin_snap and &end_snap
and    a.stat_name = 'execute count'
and    b.stat_id = a.stat_id
and    b.snap_id = s.snap_id
and    a.snap_id = b.snap_id - 1
and    a.instance_number = s.instance_number
and    b.instance_number = s.instance_number
group by s.snap_id
order by s.snap_id ;

AWR Parameters

Snap Interval (default 60 minutes)

• Minimum interval is 10 minutes
• Maximum interval is 52,560,000 minutes
• 0 : automatic and manual snapshots will be disabled

Retention (default 7 days)

• Minimum retention is 1 day (1440 minutes)
• Maximum retention is 100 years
• 0 : 영구보관

• Top SQL (default dependent on STATISTICS_LEVEL)

• Snap Interval (default 60 minutes)

-- 스냅샷 시간확인
select *
  from DBA_HIST_WR_CONTROL;

-- snapshot 확인
select *
  from DBA_HIST_SNAPSHOT
 order by begin_interval_time desc;

-- AWR 사용법 : 스냅샷 기간,시간등 변경
BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
        ( retention  => 1440 * 30,  -- 스냅샷 저장기간[30일] (1 day:1440 minutes)
          interval   => 60,         -- 스냅샷  간격(단위:분)
          dbid       => 0000012212
        );
END;

-- AWR REPORT 생성
SELECT output
FROM TABLE
        (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT
            (0000012212   /* dbid */
             , 1               /* inst_num */
             , 15987             /* start snap_id */
             , 15990             /* end   sanp_id */
             )
        ) ;

AWR Report

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     RAC Host        
------------ ----------- ------------ -------- ----------- --- ------------
RMSDB          765921261 RMSDB               1 10.2.0.3.0  NO  rmdb01      

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     15987 08-10월-09 10:00:07       31      11.0
  End Snap:     15990 08-10월-09 13:00:33       39       8.6
   Elapsed:              180.43 (mins)
   DB Time:               49.65 (mins)

Cache Sizes
~~~~~~~~~~~                       Begin        End
                             ---------- ----------
               Buffer Cache:     6,832M     6,832M  Std Block Size:        32K
           Shared Pool Size:       288M       288M      Log Buffer:    14,372K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             34,239.04              4,159.31
              Logical reads:              9,609.31              1,167.33
              Block changes:                161.17                 19.58
             Physical reads:                 26.89                  3.27
            Physical writes:                  1.26                  0.15
                 User calls:                126.96                 15.42
                     Parses:                 62.83                  7.63
                Hard parses:                  6.97                  0.85
                      Sorts:                  5.95                  0.72
                     Logons:                  0.09                  0.01
                   Executes:                 82.14                  9.98
               Transactions:                  8.23

  % Blocks changed per Read:    1.68    Recursive Call %:    83.56
 Rollback per transaction %:    0.11       Rows per Sort:    32.11

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.72    In-memory Sort %:  100.00
            Library Hit   %:   85.99        Soft Parse %:   88.91
         Execute to Parse %:   23.51         Latch Hit %:   99.99
Parse CPU to Parse Elapsd %:   94.27     % Non-Parse CPU:   90.32


 Shared Pool Statistics        Begin    End 
                              ------  ------
             Memory Usage %:   65.41   65.18
    % SQL with executions>1:   85.06   82.98
  % Memory for SQL w/exec>1:   63.28   79.62

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read             211,992       1,117      5   37.5   User I/O
CPU time                                            734          24.7           
db file scattered read                3,475          60     17    2.0   User I/O
log file parallel write              92,603          28      0    0.9 System I/O
log file sync                        87,896          26      0    0.9     Commit


          -------------------------------------------------------------    

Cache Sizes

• 버퍼캐시, Shared Pool, 로그 버퍼 등 SGA를 이루는 주요 캐시 영역에 대한 크기 정보를, 참고삼아 보여주는 것.

Load Profile

• AWR에서 보여지는 위 항목들은 dba_hist_sysstat 뷰에서 얻은 결과이므로 각각 어떤 통계항목을 조회했는지를 안다면 각각의 의미를 어렵지 않게 이해할 수 있다.
• 그런데 dba_hist_sysstat를 이용하려면 여러 조인과 필터 조건 때문에 쿼리가 복잡해 지므로 v$sysstat 뷰를 이용해 설명한다.
• 참고로 v$sysstat 뷰는 인스턴스 기동 후 현재까지의 누적치가 저장되어 있다.

Instance Efficiency Percentages (Target 100%)

• 인스턴스 효율성에 관한 리포트이며, 매우 중요한 성능 지표들이다.

Shared Pool Statistics

• Shared Pool 사용통계는 AWR 리포트 구간 시작 시점의 Shared Pool 메모리 상황과 종료 시점에서의 메모리 상황을 보여준다.

Top 5 Timed Events

• Top 5 Timed Events는 AWR 리포트 구간 동안 누적 대기 시간이 가장 컸던 대기 이벤트 5개를 보여준다.
• CPU time은 대기 이벤트가 아니며 원활하게 일을 수행했던 Servic time이지만, 가장 오래 대기를 발생시켰던 이벤트와의 점유율을 서로 비교핼 볼 수 있도록 Top5 대기 이벤트에 포함해 보여주고 있다.

Total Call(=Response) Time = Service Time + Queue Time 
                             = CPU time + Wate Time

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

이문서의 내용은 아래 책,문서를 참고했습니다.

• 조시형저, 오라클 성능 고도화 원리와 해법I