As it was in the beginning, is now, and ever shall be, world without end.

AWR Report 본문

Oracle/Tuning

AWR Report

송지헌 2017. 9. 12. 10:34

AWR(Automatic Workload Repository)

Table of Contents

*       AWR Parameters

*       AWR Report

Statspack/AWR

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

구분

Statspack

AWR

조회방식

SQL을 이용한 딕셔너리 조회

DMA(Direct Memory Access)방식으로 SGA를 직접 액세스하기 때문에 좀 더 빠르게 정보수집가능

자료수집

정보수집의 부하 때문에 자주 스냅샷 수행 어려움. 그래서 사용자가 수동으로 statspack.snap 명령을 날리거나 정해진 기간동안만 JOB에 등록해 수집

자동으로 성능자료 수집해 일정기간 보관

사용뷰

PERFSTAT계정 밑에 'stats$'로 시작하는 뷰를 통해 수집된 성능 정보들을 조회

SYS계정 밑에 'dba_hist_'로 시작하는 뷰를 이용

보고서출력 스크립트

SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

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

  •  

Per Second

각 측정 지표 값들을 측정 시간(Snapshot Interval, 초)으로 나눈 것이다. 따라서 초당 부하(Load)발생량을 의미한다.

Per Transaction

각 측정 지표 값들을 트랜잭션 개수로 나눈 것이다. 한 트랜잭션 내에서 평균적으로 얼만큼의 부하(Load)가 발생하는지를 나타내는 것인데, 사실 트랜잭션개수가 commit 또는 rollback 수행횟수를 단순히 더한 값이어서 의미 없는 수치로 받아들여질 때가 종종 있다.

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

Redo size

select value rsize from v$sysstat where name = 'redo size';

Logical reads

select value gets from v$sysstat where name = 'session logical reads';

Block changes

select value chng from v$sysstat where name = 'db block changes';

Physical reads

select value phyr from v$sysstat where name = 'physical reads';

Physical writes

select value phyw from v$sysstat where name = 'physical writes';

User calls

select value ucal from v$sysstat where name = 'user calls';

Parses

select value prse from v$sysstat where name = 'parse count (total)';

Hard parses

select value hprse from v$sysstat where name = 'parse count (hard)';

Sorts

select srtm+srtd
from (select value srtm from v$sysstat where name = 'sorts (memory)'),
(select value srtd from v$sysstat where name = 'sorts (disk)');

Logons

select value logc from v$sysstat where name = 'logons cumulative';

Executes

select value exe from v$sysstat where name = 'execute count';

Transactions

select ucom+urol
from (select value ucom from v$sysstat where name = 'user calls'),
(select value urol from v$sysstat where name = 'user rollbacks');

  •  
  •  

% Blocks changed per Read

읽은 블록 중 갱신이 발생하는 비중을 나타낸다.

select round(100*chng/gets,2) "% Blocks changed per Read"
from (select value chng from v$sysstat where name = 'db block changes'),
(select value gets from v$sysstat where name = 'session logical reads');

Rollback per transaction %

최종적으로 커밋되지 못하고 롤백된 트랜잭션 비중을 나타낸다.

select round(100*urol/(ucom+urol),2) "Rollback per transaction %"
from (select value ucom from v$sysstat where name = 'user calls'),
(select value urol from v$sysstat where name = 'user rollbacks');

Recursive Call %

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

select round(100*recr/(recr+ucal),2) "Recursive Call %"
from (select value recr from v$sysstat where name = 'recursive calls'),
(select value ucal from v$sysstat where name = 'user calls');

Rows per Sort

소트 수행 시 평균 몇 건씩 처리했는지를 나타낸다.

select decode((srtm+srtd), 0, to_number(null), round(srtr/(srtm+srtd),2))
from (select value srtm from v$sysstat where name = 'sorts (memory)'),
(select value srtd from v$sysstat where name = 'sorts (disk)'),
(select value srtr from v$sysstat where name = 'sorts (rows)');

Instance Efficiency Percentages (Target 100%)

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

Buffer Nowait %

버퍼 블록을 읽으려 할 때 buffer busy waits 대기 없이 곧바로 읽기에 성공한 비율이다.

select round(100*(1-bfwt/gets),2)
from (select sum(count) bfwt from v$waitstat),
(select value gets from v$sysstat where name = 'session logical reads');

Redo NoWait %

Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율을 말한다.
이 비율이 낮다면, 로그스위칭이 느리거나 너무 자주 발생함을 의미한다.
로그 스위칭 횟수가 문제라면 Redo 로그 파일 크기를 증가시킬 필요가 있다.
로그 스위칭이 자주 발생하지 않는데도 이 항목이 낮은 수치를 보인다면,
I/O 서브 시스템이 느린 것이 원인일 것이다. Redo 로그 파일을 덜 바쁜 디스크 또는
Redo 로그만을 위한 전용 디스크로 옮기는 것을 고려해야 한다. 비용이 허락된다면
더 빠른 디바이스로 교체하는 것도 방법이다.

select round(100*(1-rlsr/rent),2) "Redo NoWait %"
from (select value rlsr from v$sysstat where name = 'redo log space requests'),
(select value rent from v$sysstat where name = 'redo entries');

Buffer Hit %

디스크 읽기를 수반하지 않고 버퍼 캐시에서 블록 찾기에 성공한 비율이다.

select round(100*(1-(phyr-phyrd-nvl(phyrdl,0))/gets),2) "Buffer Hit %"
from (select value phyr from v$sysstat where name = 'physical reads'),
(select value phyrd from v$sysstat where name = 'physical reads direct'),
(select value phyrdl from v$sysstat where name = 'physical reads direct (lob)'),
(select value gets from v$sysstat where name = 'session logical reads');

Latch Hit %

래치 경합없이 첫 번째 시도에서 곧바로 래치를 획득한 비율을 말한다.

select round(100*(1-sum(misses)/sum(gets)),2) "Latch Hit %"
from v$latch;

Library Hit %

이 항목부터 '% Non-Parse CPU'까지는 파싱 부하와 관련 있는 측정 항목들이다.
라이브러리 캐시 히트율(Hit Ratio)은 Get 히트율과 Pin 히트율로 나눌수 있는데, 여기서는 그 중 Pin 히트율을 표시하고 있다.

 

Pin 히트율

실행 단계와 관련있다. 라이브러리 캐시에 이미 적재된 SQL 커서를 실행하거나
오브젝트 정보를 읽으려 할 때 해당 커서 또는 오브젝트 정보가 힙(Hip)영역에서 찾아진다면 히트(Hit)에 성공한 것이다.
만약 캐시에서 밀려나 찾을수 없는 경우가 빈번하게 발생한다면 히트율이 낮게 나타나고,
그만큼 다시 로드해야 하는 부하가 생기므로 라이브러리 캐시 효율이 좋지 않음을 뜻한다.
참고 : Get 히트율 : Parse 단계와 관련 있다. 이 수치가 낮다면 해당 SQL 커서 또는 오브젝트에
대한 핸들을 찾을 수 없어 하드파싱 또는 최초 로드가 자주 발생하는 경우이다.

Pin 히트율
select round(100 * sum(pinhits)/sum(pins),2) "Library Cache Pin Hit %"
from v$librarycache;
Get 히트율
select round(100 * sum(gethits)/sum(pins),2) "Library Cache Get Hit %"
from v$librarycache;

Soft Parse %

실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL을 수행한 비율을 말한다.
공식 : (전체Pase Call 횟수 - 하드파싱 횟수)/(전체 Parse Call 횟수)*100
이 비율이 낮다면 바인드 변수를 사용하도록 애플리케이션을 개선해야 한다.

select round(100*(1-hprs/prse),2) "Soft Parse %"
from (select value hprs from v$sysstat where name = 'parse count (hard)'),
(select value prse from v$sysstat where name = 'parse count (total)');

Execute to Parse %

Parse Call 없이 곧바로 SQL을 수행한 비율, 즉 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율을 말한다.
애플리케이션 커서 캐싱 기법은 라이브러리 캐싱한 채 반복 수행한 비율을 말한다.

select round((1-prse/exe)*100,2) "Execute to Parse %"
from (select value prse from v$sysstat where name = 'parse count (total)'),
(select value exe from v$sysstat where name = 'execute count');

Parse CPU to Parse Elapsd %

파싱 총 소요 시간 중 CPU time이 차지한 비율이다.
파싱에 소요된 시간 중 실제 일을 수행한 시간 비율을 말하며, 이 값이 낮다면 파싱 도중 대기가 많이 발생했음을 의미한다.
이 수치가 낮다면 Shared Pool과 라이브러리 캐시에 경합이 많다는 것을 뜻한다. 대개 하드 파싱 부하 때문이다.

select decode( prsela, 0, to_number(null), round(prscpu/prsela*100,2)) "Parse CPU to Parse Elapsd %"
from (select value prsela from v$sysstat where name = 'parse time elapsed'),
(select value prscpu from v$sysstat where name = 'parse time cpu');

% Non-Parse CPU

SQL을 수행하면서 사용한 전체 CPU time 중 파싱 이외의 작업이 차지한 비율이다.
이 비율이 낮다면 파싱 과정에서 소비되는 CPU time 비율이 높은 것이므로 파싱 부하를 줄이도록 애플리케이션을 개선해야한다.

select decode( tcpu, 0, to_number(null), round(100*(1-(prscpu/tcpu)),2)) "% Non-Parse CPU"
from (select value tcpu from v$sysstat where name = 'CPU used by this session'),
(select value prscpu from v$sysstat where name = 'parse time cpu');

In-memory Sort %

전체 소트 수행횟수엣 In-memory Sort 방식으로 수행한 비율을 말한다.

select decode( (srtm+srtd)
, 0, to_number(null)
, round(100*srtm/(srtd+srtm),2)) "In-memory Sort %"
from (select value srtm from v$sysstat where name = 'sorts (memory)'),
(select value srtd from v$sysstat where name = 'sorts (disk)');

Shared Pool Statistics

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

Memory Usage %

Shard Pool 내에서 현재 사용 중인 메모리 비중을 말한다.

select 100*(1-sum(decode(name, 'free memory', bytes)) / sum(bytes)) "Memory Usage %"
from v$sgastat
where pool = 'shared pool';

% SQL with executions>1

전체 SQL 개수에서 두번이상 수행된 SQL이 차지하는 비중을 말한다.
이 값이 낮게 나타난다면 조건절에 바인드 변수를 사용하지 않고 Literal상수 값을 이용하는 쿼리의 수행빈도가 높은것을 의미한다.

 

% Memory for SQL w/exec>1

전체 SQL이 차지하는 메모리 중 두번 이상 수행된 SQL이 차지하느느 메모리 비중이다.
이 값이 낮게 나타난다면 조건절에 바인드 변수를 사용하지 않고 Literal 상수 값을 사용하는 쿼리에 의해 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 비중이 아래쪽으로 밀려날수록 어딘가 이상이 발생했다는 적신호로 받아들여야 한다.

문서에 대하여

최초작성자 : 한남주
최초작성일 : 2009년 10월 09일

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

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

문서정보

 

저작자표시 비영리 변경금지 (새창열림)

'Oracle > Tuning' 카테고리의 다른 글

SQL Trace & tkprof  (0) 2017.10.11
dba_hist_sqlstat  (0) 2017.09.12
Table Full Scan  (0) 2014.06.27
'Oracle/Tuning' Related Articles more
Comments