행위

오라클 AWR

DB CAFE

thumb_up 추천메뉴 바로가기

1 AWR 개요[편집]

자동으로 DB에 대한 통계 및 성능자료 등을 수집해 스냅샷으로 만들어 일정기간 보관하고, 이를 활용할 수 있게 해주는 기능


1.1 AWR 리포트 출력 방법[편집]

1.1.1 awrrpt.sql 실행[편집]

  • awr*.sql 파일 위치($ORACLE_HOME/rdbms/admin) 으로 이동
cd $ORACLE_HOME/rdbms/admin 
ls awr*

-- awrrpt.sql 은 현재 노드 리포트 출력 
-- awrrpti.sql 은 RAC 에서 다른 노드(DB서버)의 리포트 출력
sqlplus / as sysdba;

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

1.1.2 리포트 저장 타입[편집]

  • 리포트 저장 타입 입력 : 기본 html
Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1632937457 BAP                1 BAP1


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

1.1.3 리포트 일수[편집]

  • 리포트 일수 입력
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1632937457        1 BAP         BAP1        acc1
  1632937457        2 BAP         BAP2        acc2

Using 1632937457 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

--  리포트할 일자 입력 
Enter value for num_days: 1

1.1.4 시작~종료시간 스냅 ID 선택[편집]

  • 시작 ~ 종료 스냅 id 입력(시간)
Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
BAP1        BAP              2665 13 May 2021 00:00      1
                               2666 13 May 2021 01:00      1
                               2667 13 May 2021 02:00      1
                               2668 13 May 2021 03:00      1
                               2669 13 May 2021 04:00      1
                               2670 13 May 2021 05:00      1
                               2671 13 May 2021 06:00      1
                               2672 13 May 2021 07:00      1
                               2673 13 May 2021 08:00      1
                               2674 13 May 2021 09:00      1
                               2675 13 May 2021 10:00      1
                               2676 13 May 2021 11:00      1
                               2677 13 May 2021 12:00      1
                               2678 13 May 2021 13:00      1
                               2679 13 May 2021 14:00      1
                               2680 13 May 2021 15:00      1
                               2681 13 May 2021 16:00      1
                               2682 13 May 2021 17:00      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2665
Begin Snapshot Id specified: 2665

Enter value for end_snap: 2682
End   Snapshot Id specified: 2682

1.1.5 리포트명 입력 및 종료[편집]

  • 리포트 명 입력 및 종료
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_2665_2682.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: BAP_AWR_20210513.html


Report written to BAP1_AWR_20210513.html
SQL> exit
  • 종료하면 해당 폴더에 리포트를 확인 한다.

1.2 AWR Report 항목별 정보[편집]

1.2.1 Cache Sizes[편집]

- 버퍼캐시, Shared Pool, 로그 버퍼 등 SGA를 이루는 주요 캐시 영역에 대한 크기 정보

1.2.2 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)');

1.3 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)');

1.4 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이 낭비되고 있을을 의미한다. ||

1.5 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 비중이 아래쪽으로 밀려날수록 어딘가 이상이 발생했다는 적신호로 받아들여야 한다.

2 AWR 관련 뷰[편집]

  • DBA_HIST_SNAPSHOT
  • DBA_HIST_SQLSTAT
  • DBA_HIST_SYSSTAT
  • DBA_HIST_SEG_STAT
  • DBA_HIST_SEG_STAT_OBJ
  • DBA_HIST_SQLTEXT
  • DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_BASELINE
DBA_HIST_BG_EVENT_SUMMARY
DBA_HIST_BUFFERED_QUEUES
DBA_HIST_BUFFERED_SUBSCRIBERS
DBA_HIST_BUFFER_POOL_STAT
DBA_HIST_COMP_IOSTAT
DBA_HIST_CR_BLOCK_SERVER
DBA_HIST_CURRENT_BLOCK_SERVER
DBA_HIST_DATABASE_INSTANCE
DBA_HIST_DATAFILE
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_DLM_MISC
DBA_HIST_ENQUEUE_STAT
DBA_HIST_EVENT_NAME
DBA_HIST_FILEMETRIC_HISTORY
DBA_HIST_FILESTATXS
DBA_HIST_INSTANCE_RECOVERY
DBA_HIST_INST_CACHE_TRANSFER
DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_LATCH
DBA_HIST_LATCH_CHILDREN
DBA_HIST_LATCH_MISSES_SUMMARY
DBA_HIST_LATCH_NAME
DBA_HIST_LATCH_PARENT
DBA_HIST_LIBRARYCACHE
DBA_HIST_LOG
DBA_HIST_METRIC_NAME
DBA_HIST_MTTR_TARGET_ADVICE
DBA_HIST_OPTIMIZER_ENV
DBA_HIST_OSSTAT
DBA_HIST_OSSTAT_NAME
DBA_HIST_PARAMETER
DBA_HIST_PARAMETER_NAME
DBA_HIST_PGASTAT
DBA_HIST_PGA_TARGET_ADVICE
DBA_HIST_PROCESS_MEM_SUMMARY
DBA_HIST_RESOURCE_LIMIT
DBA_HIST_ROWCACHE_SUMMARY
DBA_HIST_RULE_SET
DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT_OBJ
DBA_HIST_SERVICE_NAME
DBA_HIST_SERVICE_STAT
DBA_HIST_SERVICE_WAIT_CLASS
DBA_HIST_SESSMETRIC_HISTORY
DBA_HIST_SESS_TIME_STATS
DBA_HIST_SGA
DBA_HIST_SGASTAT
DBA_HIST_SGA_TARGET_ADVICE
DBA_HIST_SHARED_POOL_ADVICE
DBA_HIST_SNAPSHOT
DBA_HIST_SNAP_ERROR
DBA_HIST_SQLBIND
DBA_HIST_SQLSTAT
DBA_HIST_SQLTEXT
DBA_HIST_SQL_BIND_METADATA
DBA_HIST_SQL_PLAN
DBA_HIST_SQL_SUMMARY
DBA_HIST_SQL_WORKAREA_HSTGRM
DBA_HIST_STAT_NAME
DBA_HIST_STREAMS_APPLY_SUM
DBA_HIST_STREAMS_CAPTURE
DBA_HIST_STREAMS_POOL_ADVICE
DBA_HIST_SYSMETRIC_HISTORY
DBA_HIST_SYSMETRIC_SUMMARY
DBA_HIST_SYSSTAT
DBA_HIST_SYSTEM_EVENT
DBA_HIST_SYS_TIME_MODEL
DBA_HIST_TABLESPACE_STAT
DBA_HIST_TBSPC_SPACE_USAGE
DBA_HIST_TEMPFILE
DBA_HIST_TEMPSTATXS
DBA_HIST_THREAD
DBA_HIST_UNDOSTAT
DBA_HIST_WAITCLASSMET_HISTORY
DBA_HIST_WAITSTAT
DBA_HIST_WR_CONTROL

2.1 AWR 이용한 SQL 실행 계획 추출[편집]

SELECT SQL_ID
       ,PLAN_HASH_VALUE,
       ,ID
       ,PARENT_ID
       ,OPERATION
       ,OPTION
       ,OBJECT_NAME
  FROM DBA_HIST_SQL_PLAN
 WHERE (SQL_ID, PLAN_HASH_VALUE) IN (SELECT SQL_ID, PLAN_HASH_VALUE FROM 튜닝검토대상);

2.2 AWR 일일 수집 스크립트[편집]

2.2.1 일별 성능문제 SQL 목록[편집]

--awr 수집 sh
sqlplus -s dbadmin/pwd001@db01 < < EOF > /oracle/work/AWR/log/awr_$1.log 
set heading off 
set timing on 
set linesize 150
set pagesize 0
--set feedback off
--set termout off
col db_id new_value db_id; 
col inst_num new_value inst_num;
col bid new_value bid; 
col eid new_value eid;
col crtrYmd new_value crtrYmd; 
col stime new_value stime; 
col etime new_value etime;

select '$1' as crtrYmd from dual;

select dbid as db _id from v\$database;

select listagg(instance_number, ',') within group(order by instance_number) as inst_num from gv\$instance;

select min(snap_id) as bid
     , max(snap_id) as eid
  from dba_hist_snapshot
 where BEGIN_INTERVAL_TIME 
between to_date('&crtrYmd 000000','YYYYMMDD HH24MISS') and to_date('&crtrYmd 235959','YYYYMMDD HH24MISS')
--and instance_number = 4
order by snap_id
;

insert into TB_AWR_HIST
( crtr_ymd 
, from_snap_id 
, to_snap_id
, line
, html_rpt_cn
)
select b.crtr_ymd
, b.bid
, b.eid 
, a.line 
, a.output
from ( select output,rownum line
         from table(dbms_workload_repository.awr_global_report_html
 l_dbid = > &db_id
, l_inst_num => '&inst_num'
, l_bid => &bid 
, l_eid => &eid
) a
, (select &crtrYmd crtrymd
&bid bid
&eid eid
from dual
) b
where 1 =1
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_AWR&oldid=8456"