‘AWR’ 카테고리의 보관물

DBMS_WORKLOAD_REPOSITORY

게시 날짜: 2011/09/07, 카테고리: AWR, ORACLE

DBMS_WORKLOAD_REPOSITORY 는 AWR 을 관리하는 하나의 패키지. 패키지에 포함되어 있는 여러 프로시저를 통해 ASH REPORT 및 AWR 의 설정을 변경하거나 SNAPSHOT 을 수행할 수 있다. 패키징 된 프로시저들이 많이 있으니, 천천히 하나씩 추가해 볼란다.


PROCEDURE MODIFY_SNAPSHOT_SETTING(
retention IN NUMBER DEFAULT NULL
-> Snapshot 유지기간을 분을 기준으로 입력.  1 일(MIN_RETENTION) 에서 100 년 (MAX_RETENTION) 사이의 값을 지정한다.  값이 zero 일 경우, snapshot 은 계속 유효하게 남아있는다. NULL 값이 들어올 경우 이전 값이 계속 유지된다.

interval IN NUMBER DEFAULT NULL
->  Snapshot 간격을 분을 기준으로 설정한다. 10분(MIN_RETENTION) 에서 1년(MAX_RETENTION) 사이의 값을 넣을 수 있다. Zero 로 설정할 경우, 더이상 스냅샷을 찍지 않는다. 수동으로도 불가능. Null 설정시 이전 값 사용.

topnsql IN NUMBER DEFAULT NULL or IN VARCHAR2
-> 각 영역의 top N SQL 의 갯수 지정.

dbid IN NUMBER DEFAULT NULL
-> 기본값은 Null 로써 database id 를 입력. Null 일 경우 로컬 dbid 를 사용함.

)

Example
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( interval=>30);

Related View
DBA_HIST_WR_CONTROL


PROCEDURE CREATE_SNAPSHOT(
flush_level IN VARCHAR2 DEFAULT ‘TYPICAL’
-> 스냅샷 플러시 레벨. ‘Typical’ 혹은 ‘ALL’ 중  하나를 선택한다.

)

Example
SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

Related View
DBA_HIST_SNAPSHOT


Continue..

AWR

게시 날짜: 2011/09/05, 카테고리: AWR, ORACLE

AWR(Automatic Workload Repository)

Table of Contents

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

문서정보