행위

오라클 AWR

DB CAFE

Dbcafe (토론 | 기여)님의 2023년 5월 23일 (화) 19:30 판 (→‎AWR Report 항목별 정보)
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)');


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=8454"