행위

오라클 AWR

DB CAFE

Dbcafe (토론 | 기여)님의 2023년 5월 23일 (화) 19:21 판
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)');


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